Transactions: ACID, Isolation Levels & MVCC

Module Overview

When thousands of concurrent transactions interact with shared database records simultaneously, uncoordinated interleaving creates catastrophic concurrency anomalies. In this eighth installment, we unpack ACID Guarantees, examine the four standard ANSI SQL Isolation Levels alongside their respective anomalies, analyze Two-Phase Locking (2PL), and dive into Multi-Version Concurrency Control (MVCC)—the non-blocking snapshot architecture powering modern PostgreSQL and MySQL InnoDB engines.

1. Deconstructing the ACID Properties

Atomicity (All-or-Nothing)

A transaction's operations are treated as an indivisible atomic unit. If any statement fails or the server crashes mid-flight, all modifications are completely rolled back using undo logs.

Consistency (State Validity)

A transaction transitions the database from one valid state to another, strictly satisfying all schema invariants, primary keys, foreign keys, and CHECK constraints.

Isolation (Concurrency Boundary)

Concurrent execution of transactions produces the same persistent database state as if the transactions had been executed sequentially one after another.

Durability (Persistence Guarantee)

Once a transaction commits, its modifications are permanently recorded on non-volatile storage and survive any subsequent power failure or operating system crash.

2. Concurrency Anomalies & ANSI SQL Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Write Skew
Read Uncommitted	Allowed	Allowed	Allowed	Allowed
Read Committed	Prevented	Allowed	Allowed	Allowed
Repeatable Read	Prevented	Prevented	Prevented (in MVCC)	Allowed
Serializable	Prevented	Prevented	Prevented	Prevented

3. Pessimistic Concurrency Control: Two-Phase Locking (2PL)

Under Two-Phase Locking (2PL), transactions acquire locks in two distinct chronological phases:

Growing Phase: The transaction may acquire shared (read) or exclusive (write) locks, but cannot release any lock.
Shrinking Phase: The transaction may release acquired locks, but cannot acquire any new locks.

Strict 2PL (S2PL): Releasing write locks early can lead to cascading aborts if a transaction rolls back after another transaction reads its uncommitted data. Strict 2PL mandates that all exclusive locks must be held until the transaction explicitly commits or terminates.

4. Multi-Version Concurrency Control (MVCC)

Traditional locking forces read queries to wait for active writes, drastically degrading performance on read-heavy workloads. MVCC resolves this through a fundamental paradigm: "Readers never block writers, and writers never block readers."

How MVCC Operates Internally

Instead of overwriting rows in place on disk:

Row Header Metadata: Every row stores transaction timestamps or identifiers: creation transaction (xmin in Postgres, DB_TRX_ID in InnoDB) and deletion transaction (xmax / roll pointer).
Consistent Read Views: When a read transaction begins, it captures a snapshot of currently active uncommitted transactions. It sees only row versions committed prior to its snapshot timestamp, ignoring later modifications.
Garbage Collection: Obsolete row versions no longer visible to any active transaction snapshot are periodically purged by background threads (PostgreSQL VACUUM or InnoDB master purge threads).

Next in Curriculum

In Part 9: Database Recovery: Write-Ahead Logging (WAL) & The ARIES Protocol, we analyze how database engines guarantee durability through Write-Ahead Logging and the three-pass ARIES recovery algorithm.