Transactions & ACID

A transaction is a sequence of one or more database operations (reads and writes) that the database treats as a single logical unit of work. The key intuition: either all operations in the transaction happen, or none do — and while the transaction is running, its partial effects are invisible to everyone else.

Think of it like a surgeon performing an operation on a patient. You don't want the surgeon to stop halfway through and leave the patient "partially fixed". The transaction says: do all of this as if it were one atomic action.

In SQL, a transaction is delimited with BEGIN and either COMMIT (make it permanent) or ROLLBACK (undo everything):

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';
COMMIT;

If the database crashes after the first UPDATE but before the second, the DBMS — on recovery — will detect the incomplete transaction and automatically undo the first UPDATE. Alice's money is restored. Nothing is lost.

This is the fundamental value proposition: the programmer expresses what "one thing" means, and the database enforces it. Without transactions, the programmer would have to write complex recovery code for every possible failure scenario — an impossible task in practice.

BEGIN;
  -- Debit Alice
  UPDATE accounts SET balance = balance - 100 WHERE user = 'alice';
  -- Credit Bob
  UPDATE accounts SET balance = balance + 100 WHERE user = 'bob';
COMMIT; -- or ROLLBACK on error

Atomicity means a transaction is indivisible. It either commits completely — all writes become permanent — or aborts completely — all writes are undone, as if the transaction never ran. There is no observable half-committed state.

The mechanism that enforces atomicity is the Write-Ahead Log (WAL), also called the redo/undo log. Before any page in the database is modified on disk, the database first writes a log record describing the change to a separate log file on disk. This log record contains:

• The transaction ID
• The page being modified
• The before-image (old value, for UNDO)
• The after-image (new value, for REDO)

If the system crashes mid-transaction, the recovery algorithm (ARIES is the canonical algorithm) replays the log on restart:

1. REDO all changes from committed transactions that may not have reached disk
2. UNDO all changes from transactions that were in-flight when the crash happened

Atomicity also handles logical aborts: if a transaction detects an error (insufficient balance, constraint violation), it can call ROLLBACK explicitly. The undo log is traversed backwards, reversing each operation in reverse order.

A crucial subtlety: atomicity says nothing about what other transactions see during execution — that is isolation's job. Atomicity only guarantees the outcome: all or nothing.

# Python with psycopg2 — explicit transaction control
import psycopg2

conn = psycopg2.connect(dsn)
conn.autocommit = False   # start transaction manually
try:
    cur = conn.cursor()
    cur.execute("UPDATE accounts SET balance = balance - 100 WHERE user = 'alice'")
    cur.execute("UPDATE accounts SET balance = balance + 100 WHERE user = 'bob'")
    conn.commit()           # atomic: both writes persist
except Exception:
    conn.rollback()         # atomic: neither write persists
    raise

Consistency is the "C" in ACID, and it is philosophically different from the other three. Atomicity, Isolation, and Durability are database guarantees — the DBMS enforces them mechanically. Consistency is a joint responsibility: the programmer defines the invariants (rules), and the database enforces them via constraints, but the programmer must write transactions that respect those invariants.

An invariant is a rule that must always be true. Examples:

• The sum of all account balances should not change during a transfer
• A foreign key must point to an existing row
• A student's GPA must be between 0 and 4.0
• No two seats on the same flight can be sold to different passengers

The database enforces declarative constraints (foreign keys, unique constraints, CHECK constraints, NOT NULL) automatically. If a transaction attempts a write that would violate a constraint, the database aborts the transaction.

But many application-level invariants — like "the booking system should never sell more seats than exist" — require the programmer to write the transaction correctly, often by acquiring the right locks or using the right isolation level.

A subtle but important point: in the CAP theorem and distributed systems literature, "consistency" means something entirely different (linearizability / all nodes see the same data). This ACID consistency is about application invariants, not distributed coherence. Jim Gray himself noted this naming collision was unfortunate.

-- Database-enforced consistency via constraints
CREATE TABLE accounts (
    user_id   TEXT PRIMARY KEY,
    balance   NUMERIC NOT NULL CHECK (balance >= 0)  -- invariant enforced
);

-- This transaction will be ABORTED by the DB if alice has < 100:
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';
  -- If CHECK constraint fails, entire transaction rolls back
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 'bob';
COMMIT;

Isolation is the most nuanced and performance-sensitive ACID property. It governs what one transaction can see of another's concurrent work.

The ideal is serializable isolation: the system behaves as if transactions ran one after another in some serial order, even though they actually ran concurrently for performance. This is provably the safest model.

But full serializability is expensive. Databases therefore offer weaker isolation levels, each permitting a specific class of anomaly in exchange for better throughput. Understanding the anomalies is essential:

Dirty Read: Transaction T2 reads a value written by T1, but T1 has not committed yet. If T1 aborts, T2 has read data that "never existed".

Non-repeatable Read: T1 reads a row, T2 updates and commits that row, then T1 reads the same row again and sees a different value. The same query gives different results within one transaction.

Phantom Read: T1 executes a query with a predicate (e.g., WHERE age > 30), T2 inserts a new row matching that predicate and commits, then T1 re-executes the query and sees the new row — a "phantom" that appeared from nowhere.

Lost Update: T1 and T2 both read a value, both increment it, and both write back. One write overwrites the other. A classic example is two airline agents booking the last seat simultaneously.

Write Skew: T1 and T2 each read an overlapping set of rows and make decisions based on what they see; both then write to different rows, but their combined writes violate an invariant. For example: an on-call scheduling system requires at least one doctor on call. T1 reads (Alice=on-call, Bob=on-call) and removes Alice. T2 reads (Alice=on-call, Bob=on-call) and removes Bob. Both commits succeed; now nobody is on-call.

Each isolation level is defined by which anomalies it prevents.

-- Demonstrating a Lost Update (under Read Committed)
-- Session 1:
BEGIN;
SELECT balance FROM accounts WHERE user = 'alice';  -- reads 1000
-- (Session 2 concurrently reads 1000, adds 500, writes 1500, commits)
UPDATE accounts SET balance = 1000 + 200 WHERE user = 'alice';  -- writes 1200
COMMIT;
-- Session 2's 500 deposit is LOST. Alice should have 1700.

-- Fix: use SELECT FOR UPDATE to acquire a lock before reading
BEGIN;
SELECT balance FROM accounts WHERE user = 'alice' FOR UPDATE;
-- Now Session 2 must wait. Safe to read-modify-write.

The SQL standard defines four isolation levels, each a tradeoff between safety and performance. They form a hierarchy from weakest to strongest:

READ UNCOMMITTED is almost never used in practice. It permits dirty reads, which means you can see data from transactions that haven't committed yet — like reading a check before it clears.

READ COMMITTED is the default in PostgreSQL and Oracle. Every read sees only committed data. Dirty reads are impossible. However, two reads of the same row in one transaction can return different values (non-repeatable reads), because another transaction may commit between them.

REPEATABLE READ guarantees that if you read a row, it will return the same value if read again within the same transaction. MySQL's InnoDB implements this with a consistent snapshot (MVCC). However, phantom rows can still appear from inserted rows.

SERIALIZABLE is the gold standard. The database ensures the outcome is equivalent to some serial execution. PostgreSQL implements this with Serializable Snapshot Isolation (SSI), which detects dangerous read-write dependencies between transactions and aborts one if a cycle is found.

A crucial practical note: Snapshot Isolation (SI), used by PostgreSQL's REPEATABLE READ level, prevents more anomalies than the SQL standard requires for REPEATABLE READ — but it does NOT prevent write skew or certain phantom scenarios. True serializability requires SSI.

Choose your isolation level deliberately: financial ledgers deserve SERIALIZABLE; analytics dashboards can tolerate READ COMMITTED.

-- Setting isolation level in PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT balance FROM accounts WHERE user = 'alice';
  -- ... other reads ...
  UPDATE accounts SET balance = balance - 100 WHERE user = 'alice';
COMMIT;

-- For a read-only analytics query, READ COMMITTED is fine:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
  SELECT AVG(balance) FROM accounts;
COMMIT;

Two fundamentally different mechanisms implement isolation in real databases:

Two-Phase Locking (2PL) is the classical approach. Every transaction acquires locks before accessing data and holds them until commit. The protocol has two phases:

1. Growing phase: acquire locks, never release them
2. Shrinking phase: release locks, never acquire new ones

If two transactions conflict (one wants a write lock on a row that another holds a read lock on), one must wait. This prevents anomalies by ensuring conflicting operations are totally ordered. However, 2PL can suffer from deadlocks: T1 holds a lock on row A and wants row B; T2 holds row B and wants row A. The database detects deadlock cycles (via a waits-for graph) and aborts one transaction.

Multi-Version Concurrency Control (MVCC) is the modern approach, used by PostgreSQL, Oracle, and MySQL/InnoDB. Instead of locking rows, the database maintains multiple versions of each row — one per transaction that modified it. Each row version is tagged with the transaction ID that created it and (optionally) the transaction that deleted it.

When T2 reads a row, it sees the most recent version that was committed before T2 started. T1's in-progress writes create new versions but don't disturb T2's snapshot. This means readers never block writers and writers never block readers — a massive throughput win.

The tradeoff: MVCC needs garbage collection (called VACUUM in PostgreSQL) to reclaim old row versions no longer needed by any active transaction. Without it, tables bloat.

MVCC elegantly implements snapshot isolation: every transaction sees a consistent point-in-time view of the database — like a photograph taken the moment the transaction started.

-- MVCC in action: two concurrent sessions in PostgreSQL
-- Time 1: Session A starts
-- Session A: BEGIN; (snapshot taken at T=100)

-- Time 2: Session B starts and commits
-- Session B: BEGIN; UPDATE accounts SET balance=999 WHERE id=1; COMMIT;

-- Time 3: Session A reads — sees balance=1000, NOT 999
-- Session A: SELECT balance FROM accounts WHERE id=1;
-- Returns: 1000  (the version visible at T=100)
-- Session B's committed write is a NEWER version; A's snapshot predates it

-- This is snapshot isolation: each transaction sees a consistent snapshot
-- Readers do NOT block writers. Writers do NOT block readers.

Durability means that once a transaction commits, its changes are permanent — even if the database crashes immediately after the COMMIT returns to the client. A power failure, OS panic, or hardware fault cannot undo a committed transaction.

Durability is enforced by the Write-Ahead Log (WAL). The WAL protocol has one rule: log records describing a change must reach durable storage (disk) before the data page itself is written to disk. This is enforced by calling fsync() on the log file at commit time.

Why does this work? On recovery after a crash:

1. The database reads the WAL from the last checkpoint forward
2. Any transaction whose COMMIT record is in the log but whose data pages haven't been written is redone (the after-images are replayed)
3. Any transaction without a COMMIT record is undone (the before-images are replayed backwards)

This is the ARIES recovery algorithm (1992, IBM Research), which underlies essentially every major relational database.

A critical performance consideration: fsync() is slow — it flushes the entire disk write buffer. Databases use a group commit optimization: batch multiple transactions' log records and call fsync() once for the group. This amortizes the I/O cost.

The durability guarantee is only as strong as the storage. A battery-backed RAID controller or NVMe SSD with power-loss protection is required for true durability. Cloud databases (Amazon RDS, Google Cloud SQL) achieve durability through replication: a commit only returns after the log record is written to multiple replicas in multiple availability zones.

# Simplified WAL write protocol (pseudocode)
def commit_transaction(txn):
    # Step 1: Write all log records to the WAL buffer
    for op in txn.operations:
        wal_buffer.append(LogRecord(
            txn_id=txn.id,
            before_image=op.old_value,
            after_image=op.new_value
        ))
    wal_buffer.append(LogRecord(txn_id=txn.id, type=COMMIT))

    # Step 2: Flush WAL to disk (THIS is what makes it durable)
    wal_buffer.flush_to_disk()  # calls fsync()

    # Step 3: NOW safe to reply 'committed' to client
    # (Data pages may still be in buffer pool, not yet on disk -- that's OK!)
    return 'COMMITTED'

A transaction moves through a well-defined set of states from the moment it begins to its final outcome. Understanding this lifecycle is essential for reasoning about recovery, locking, and the behavior of the ROLLBACK command.

Active: The transaction has started and is executing operations. Reads and writes are being performed. Locks are being acquired (in 2PL). MVCC versions are being created.

Partially Committed: The transaction has issued its final operation and called COMMIT. The database is writing log records and flushing them to disk. It has not yet confirmed durability to the client.

Committed: The commit log record has been flushed to disk. The transaction's writes are now permanent. The database replies to the client. Locks are released (2PL).

Failed: Something went wrong — a constraint violation, deadlock victim selection, explicit ROLLBACK, or crash. The transaction must be rolled back.

Aborted (Rolled Back): The undo log has been applied. All partial writes by this transaction are reversed. The database is in the same state as if this transaction never ran. Locks are released.

Note that "Committed" and "Aborted" are both terminal states. There is no "partially committed" visible state — if the database crashes between "Partially Committed" and "Committed", recovery replays the log and either completes the commit (if the COMMIT log record made it to disk) or undoes the transaction (if it didn't).

This state machine is what makes ACID feel so clean from the programmer's perspective: you either get a committed transaction or a cleanly aborted one. The messy middle is the database's problem, not yours.

To reason formally about isolation, we use the concept of a schedule: an ordering of the operations (reads and writes) of a set of concurrent transactions.

A schedule is serial if all operations of one transaction complete before any operation of the next begins. Serial schedules are always correct by definition — there is no concurrency to cause problems.

A schedule is serializable (or conflict-serializable) if it produces the same result as some serial schedule. This is the formal definition of the SERIALIZABLE isolation level.

Two operations conflict if they:

1. Belong to different transactions
2. Access the same data item
3. At least one of them is a write

A schedule is conflict-serializable if and only if its precedence graph (also called conflict graph) is acyclic:

• Draw one node per transaction
• Draw an edge Ti → Tj if Ti has an operation that conflicts with and precedes a Tj operation
• If there is a cycle, the schedule is NOT serializable

Example: T1 reads X, T2 writes X, T1 reads Y, T2 writes Y.

• T1 reads X before T2 writes X → edge T1→T2
• T1 reads Y before T2 writes Y → edge T1→T2
• No cycle. Conflict-serializable (equivalent to serial order T1, T2).

Another example: T1 reads X, T2 writes X, T2 reads Y, T1 writes Y.

• T1 reads X before T2 writes X → T1→T2
• T2 reads Y before T1 writes Y → T2→T1
• Cycle! NOT serializable. This is the formal definition of a problem.

This graph-theoretic framework is not just theoretical — it directly underpins how SSI in PostgreSQL detects and resolves dangerous schedules at runtime.

# Precedence-graph cycle detection (simplified)
def is_conflict_serializable(schedule):
    """schedule: list of (txn_id, op_type, data_item)"""
    edges = set()
    # Find all conflicting pairs
    for i, (ti, op_i, item_i) in enumerate(schedule):
        for j, (tj, op_j, item_j) in enumerate(schedule):
            if i < j and ti != tj and item_i == item_j:
                if op_i == 'W' or op_j == 'W':  # at least one write
                    edges.add((ti, tj))  # Ti precedes Tj in conflict
    # Check for cycles in the precedence graph
    return not has_cycle(edges)  # True means serializable

Real applications often need finer-grained control than all-or-nothing. Savepoints allow a transaction to mark a point within itself, and roll back to that point without aborting the entire transaction.

This is invaluable in long-running workflows — for example, a multi-step order processing system that wants to retry a single failed step without losing earlier work.

SQL syntax:

BEGIN;
  INSERT INTO orders (id, user) VALUES (42, 'alice');
  SAVEPOINT after_order;
  INSERT INTO payments (order_id, amount) VALUES (42, 99.99);
  -- payment fails
  ROLLBACK TO SAVEPOINT after_order;
  -- try again with a different payment method
  INSERT INTO payments (order_id, amount) VALUES (42, 99.99);
COMMIT;

Savepoints do NOT provide true nested transaction semantics (where an inner transaction can commit independently of the outer). They are sub-boundaries within a single transaction. If the outer transaction aborts, all savepoints within it are also discarded.

True nested transactions (where inner transactions can commit or abort independently) exist in some academic database systems and in the Microsoft SQL Server SAVE TRANSACTION model, but are not universally supported or standardized. In PostgreSQL, autonomous transactions can be simulated via dblink to a separate connection.

The practical lesson: use savepoints when you need partial rollback within a long transaction. They are a valuable escape hatch in complex application logic.

BEGIN;
  INSERT INTO orders VALUES (1, 'alice', 99.99);
  SAVEPOINT sp1;
  
  INSERT INTO inventory_holds VALUES (1, 'item-A');
  -- Suppose this violates a constraint:
  INSERT INTO inventory_holds VALUES (1, 'item-B');  -- ERROR
  
  ROLLBACK TO SAVEPOINT sp1;  -- undo inventory inserts, keep order
  
  -- Try alternative allocation
  INSERT INTO inventory_holds VALUES (1, 'item-C');
COMMIT;  -- order + item-C hold committed; item-A and item-B never happened

The hardest version of the ACID problem is when a single transaction spans multiple machines. A bank transfer that moves money between branches stored on different servers must still be atomic.

Two-Phase Commit (2PC) is the classical protocol for distributed atomicity:

1. Prepare phase: A coordinator sends PREPARE to all participant nodes. Each participant writes a prepare log record and replies YES (I am ready to commit) or NO (I need to abort).
2. Commit phase: If all participants said YES, the coordinator writes a commit record and sends COMMIT to all. Each participant commits and acknowledges. If any said NO, the coordinator sends ABORT.

The critical invariant: once a participant says YES, it has irrevocably promised to commit if asked. It must retain its locks and log records until it hears the final decision — even across crashes.

2PC is blocking: if the coordinator crashes after participants have said YES but before sending the decision, participants are stuck holding locks indefinitely, waiting for a coordinator that may never return. This is the fundamental vulnerability of 2PC.

Three-Phase Commit (3PC) and Paxos/Raft-based commit protocols address this at the cost of more message rounds.

Modern distributed databases like Google Spanner achieve serializable distributed transactions using TrueTime — GPS and atomic clocks to assign globally consistent timestamps — giving them external consistency stronger than traditional ACID. CockroachDB implements a similar model using hybrid logical clocks.

The distributed transactions problem remains one of the deepest in systems research. The FLP impossibility theorem (1985) proves that consensus — which underlies distributed commit — cannot be solved deterministically in an asynchronous system with even one faulty process. Real systems work around this with timeouts and probabilistic guarantees.

# Two-Phase Commit pseudocode
def two_phase_commit(coordinator, participants, transaction):
    # PHASE 1: Prepare
    votes = []
    for p in participants:
        p.write_prepare_log(transaction)
        votes.append(p.vote())  # YES or NO

    # PHASE 2: Decide
    if all(v == 'YES' for v in votes):
        coordinator.write_commit_log(transaction)
        for p in participants:
            p.commit()     # each participant commits and releases locks
    else:
        coordinator.write_abort_log(transaction)
        for p in participants:
            p.rollback()   # each participant rolls back