Computer Engineering is coming soon

We're still writing and reviewing the notes for every subject, and we'll open it when every subject is ready. Meanwhile you can practise aptitude, ask Aria about any subject, or see the branches that are ready.

Transactions and ACID properties

Transactions group multiple database operations into one atomic unit and ACID (Atomicity, Consistency, Isolation, Durability) are the properties that make transactions reliable and safe under failures and concurrency.

Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.

Why it matters

Two or three sentences: where an engineer uses this.

Database engineers use transactions to ensure a set of operations either all happen or none happen (important for money transfers, inventory updates, booking systems). The ACID properties give precise guarantees needed for correct behaviour under concurrent access and system failures; understanding them is essential when designing OLTP systems, choosing isolation levels, or reasoning about correctness of schedules and recovery.

Key ideas

Build up from first principles; define terms, state exact definitions/theorems and common derivations.

  • Transaction: a sequence of database operations (reads, writes) treated as a single logical unit of work. It begins with a start event and ends with either commit (successful completion) or abort (rollback).

  • Notation students must remember: r_i(X) = transaction Ti reads item X; w_i(X) = Ti writes X; c_i = Ti commits; a_i = Ti aborts.

  • Atomicity (A): either all operations of a transaction take effect or none do. Atomicity implies that if a transaction aborts, the effects of that transaction are undone so the database reflects a state as if the transaction never executed (other committed transactions remain intact). Mechanisms: undo logging and rollback.

  • Consistency (C): each transaction moves the database from one valid state to another valid state, where validity respects integrity constraints (primary keys, foreign keys, application invariants). Important: the DBMS helps preserve integrity constraints but the transaction programmer must ensure application-level invariants. Statement form: If the database satisfies integrity constraints before a transaction and the transaction commits, then the database satisfies integrity constraints after the commit (assuming transaction code is correct).

  • Isolation (I): concurrent execution of transactions produces the same results as some serial execution of those transactions. A serial schedule executes transactions one after another without interleaving; a schedule is serializable if it is equivalent to some serial schedule. The strongest common formalization is serializability. Two common forms:

    • Conflict-serializability: a schedule is conflict-serializable if it is conflict-equivalent to some serial schedule. Construct the precedence graph (or serialization graph): nodes = transactions, add edge Ti -> Tj if an operation of Ti precedes and conflicts with an operation of Tj on the same data item and at least one is a write. A schedule is conflict-serializable iff the precedence graph is acyclic. (Assumption: read/write operations and a total order of operations in the schedule.)
    • View-serializability: a weaker concept based on same read-from and final writes; every conflict-serializable schedule is view-serializable but not vice versa.

    Define conflict: two operations conflict if they are by different transactions, operate on the same item, and at least one is a write (read–write, write–read, write–write conflict; read–read does not conflict).

  • Durability (D): once a transaction commits, its effects persist even if the system crashes immediately after. Implemented by writing necessary log records and data to stable storage before acknowledging commit (write-ahead logging). WAL rules: (1) the log record for an update must be forced to stable storage before the updated data page is written (Write-Ahead rule); (2) the commit record must be forced before acknowledging commit. Steal/no-force buffer policies are common with WAL.

  • Related schedule properties important to isolation/recovery:

    • Recoverable schedule: If transaction Tj reads a value written by Ti, then Ti must commit before Tj commits. This prevents Tj from committing based on a value that may later be undone by Ti aborting.
    • Cascading aborts: A schedule allows cascading aborts if a single abort may force other transactions that read uncommitted values to also abort. To avoid this, use strict schedules or isolation methods that prevent reads of uncommitted writes. A cascadeless (ACA) schedule forbids transactions from reading uncommitted data and therefore avoids cascading aborts; strict schedules are a subset of cascadeless schedules.
    • Strict schedule: No transaction can read or write an item X that was written by an uncommitted transaction. Under strict two-phase locking, all exclusive (write) locks on items written by a transaction are held until commit/abort; this locking discipline ensures strict schedules. Strictness implies recoverability and avoids cascading aborts. Also know standard anomalies: dirty read (read uncommitted), non-repeatable read, phantom; and ANSI SQL isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable (with Serializable preventing all three for predicate reads).
  • Theorem (commonly used exact statement): A schedule S is conflict-serializable iff its precedence graph is acyclic. Assumptions: operations are read/write on named data items, and the schedule is a total order of operations. Two-Phase Locking (2PL): If every transaction follows the two-phase locking protocol (a growing phase of acquiring locks followed by a shrinking phase of releasing them), the resulting schedules are conflict-serializable; Strict 2PL (holding all exclusive locks until commit/abort) additionally ensures strict, recoverable schedules without cascading aborts.

  • How to draw the precedence graph (diagram description): Draw a directed graph. For each transaction Ti in the schedule draw a node labelled Ti. For each pair of conflicting operations where operation p in Ti precedes q in Tj on the same item, draw a directed edge Ti -> Tj. If the resulting graph has a directed cycle, the schedule is not conflict-serializable; otherwise it is conflict-serializable and any topological order of the graph is an equivalent serial order.

Formulas

  • c_i means transaction Ti commits.
  • a_i means transaction Ti aborts.
  • Rule for precedence graph edge: Edge Ti -> Tj if ∃ operations p in Ti and q in Tj on same item X, p precedes q in S, and {p,q} contains at least one write.
  • Recoverable schedule condition: If Tj reads an item written by Ti, then c_i < c_j (commit of Ti occurs before commit of Tj).
  • Strictness condition (lock-based): No read or write by Tj on X is allowed when there exists Ti (uncommitted) such that w_i(X) occurred and c_i not yet executed.

(Each symbol: Ti = transaction i; X = data item; c_i = commit operation of Ti; '<' refers to order in the schedule.)

Worked examples

Example 1 — short model answer: Explain ACID with a money transfer

Question: Explain the ACID properties using a bank transfer (transfer Rs. 100 from account A to account B).

Model answer (steps):

  1. Transaction T: r_T(balance_A), w_T(balance_A := balance_A - 100), r_T(balance_B), w_T(balance_B := balance_B + 100), c_T.
  2. Atomicity: If the system crashes after subtracting from A but before adding to B, atomicity requires rollback — net effect must be none or all changes after recovery; implement via undo log.
  3. Consistency: Before T, total balance (A+B) = V. If T is correct and commits, after commit total should be V (integrity: balances non-negative). Programmer ensures invariants.
  4. Isolation: Concurrent transactions must not see intermediate state (A decreased but B not increased); serializability ensures the observable effect equals some serial order.
  5. Durability: Once commit acknowledged, even if crash happens immediately, both updates remain when system restarts, via redo logs and stable storage.

Example 2 — conflict-serializability check (full steps)

Given transactions: T1: r1(A); w1(A) T2: r2(A); w2(A) Schedule S: r1(A) r2(A) w1(A) w2(A) c1 c2

Step 1: List conflicting operation pairs in schedule order:

  • r2(A) (by T2) precedes w1(A) (by T1) and they conflict (read–write), so edge T2 -> T1.
  • r1(A) precedes w2(A) and they conflict (read–write), so edge T1 -> T2. Step 2: Precedence graph nodes: {T1, T2} edges: T1 -> T2 and T2 -> T1. This graph has a cycle, so by the theorem the schedule is not conflict-serializable. Step 3: Conclusion: S is not serializable; it is possible to observe anomalies such that no equivalent serial order exists.

Example 3 — cascading aborts and strictness

Schedule S2: T1: w1(A) T2: r2(A) w2(B) Schedule: w1(A) r2(A) w2(B) a1 (T1 aborts)

Here T2 read the uncommitted value of A written by T1. When T1 aborts, T2 must also be aborted (cascading abort) because T2 used a value that was rolled back. A strict schedule would have prevented r2(A) until T1 commits or aborts, avoiding cascades.

Common mistakes

  • Using the words "serialisable" and "strict" interchangeably. Strict schedules are stronger and imply no cascading aborts; serializability alone does not prevent cascading aborts.
  • Forgetting read–read operations do not conflict. Only read–write, write–read, write–write on same item cause precedence edges.
  • Wrong direction of edges in precedence graph: edge goes from the transaction whose conflicting operation appears earlier in the schedule to the transaction with the later conflicting operation.

For your exam

Kinds of questions produced and what full-marks answers include:

  • Definitions: Define transaction, commit/abort, ACID properties. Full marks: give clear one-line definition plus a brief example for each property.
  • Short notes: e.g., "Explain serializability". Full marks: define conflict/view serializability, give construction rule for precedence graph, state the acyclicity theorem, and mention limitations.
  • Derivations / proofs: e.g., prove "precedence graph acyclic iff conflict-serializable". Full marks: show construction of equivalent serial order from topological sort (if acyclic) and show cycle prevents any serial order.
  • Numericals / schedules: Given a schedule, determine whether it is conflict-serializable, recoverable, strict. Full marks: list conflicting pairs, draw graph, show acyclic/cycle, check read-from relations for recoverability, and check whether any read/write reads uncommitted writes for strictness.
  • Diagrams: draw precedence graph; full marks: nodes labelled with transaction names, directed edges for each conflicting pair with annotation (which operation pair created the edge) is helpful.
  • Short program/SQL: Rare in this topic; may ask lock sequences for two-phase locking. Full marks: show lock/unlock sequence obeying two-phase locking, explain when lock release occurs (at commit for strict 2PL), and state the 2PL theorem (2PL ensures conflict-serializability; strict 2PL ensures strictness and cascadeless/recoverable schedules).

Quick check

  1. Define a transaction in one line.
  2. What does the I in ACID stand for and one formal statement that captures it?
  3. Give the rule to add an edge Ti -> Tj in a precedence graph.

Answers: 1) A transaction is a sequence of database operations treated as a single logical unit of work that ends with commit or abort. 2) Isolation — concurrent execution must be equivalent to some serial execution; commonly formalized as serializability. 3) Add edge Ti -> Tj if an operation of Ti precedes and conflicts with an operation of Tj on the same data item and at least one is a write.

Finished this topic? Mark it so your progress, study plan and readiness keep up.

Stuck on something here?