Transactions, Isolation, and MVCC

Atomicity: a transaction's writes are all-or-nothing. If it aborts mid-way, every partial write is rolled back — the database looks as if the transaction never ran.

Consistency: a transaction takes the database from one valid state to another. "Valid" is defined by application-level constraints (foreign keys, check constraints) — not the database engine itself.

Isolation: concurrent transactions see a consistent view of the database. The exact definition of "consistent" is parameterized by the isolation level (the subject of most of this lesson).

Durability: once a transaction commits, its effects survive crashes. This is what WAL and fsync are for — after the log record hits durable storage, the commit is permanent.

The tricky one is Isolation. Atomicity and Durability are binary; Isolation is a spectrum.

SQL defines isolation levels by which anomalies they prevent. Know these precisely — they appear in every database interview and every incident post-mortem:

Dirty read: transaction T2 reads a row written by T1 before T1 commits. If T1 aborts, T2 has seen data that never existed.

Non-repeatable read: T2 reads row $R$ twice; T1 commits an update to $R$ between the two reads. T2 sees different values for the same row.

Phantom read: T2 executes a predicate query (WHERE age > 30) twice; T1 inserts a new matching row between the two reads. T2 sees a different set of rows.

Write skew: T1 and T2 both read an overlapping set of rows, make decisions based on what they read, then each write different rows. Neither write conflicts with the other, but the combined result violates an invariant that either transaction alone would have maintained.

The SQL standard defines four isolation levels. Postgres implements three of them (Read Uncommitted maps to Read Committed internally):

* Postgres Repeatable Read also prevents phantoms (MVCC snapshot covers the full query), but write skew is still possible at Repeatable Read — it requires Serializable (SSI) to prevent.

-- Set isolation level for the current transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- all reads see the same snapshot from BEGIN
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

The classical approach to serializability is two-phase locking: acquire all needed locks before releasing any. Phase 1 (growing) acquires locks; phase 2 (shrinking) releases them. At no point do you release a lock and then acquire another.

Lock types:

• Shared (S) lock: multiple readers can hold it simultaneously; blocks writers.
• Exclusive (X) lock: only one holder; blocks all readers and writers.
• Predicate lock (range lock): locks a key range to prevent phantom inserts — expensive and rarely used.

2PL guarantees serializability but has two critical weaknesses:

1. Blocking: a reader blocks writers and vice versa — throughput collapses under contention.
2. Deadlock: T1 holds lock A and waits for B; T2 holds B and waits for A. The engine detects cycles and aborts one transaction.

SQL Server and MySQL InnoDB use 2PL for their default locking modes.

Multi-Version Concurrency Control (MVCC) solves the reader-writer blocking problem by keeping multiple versions of each row. A writer creates a new version; readers see an older version that was valid at their snapshot timestamp. Reads never block writes; writes never block reads.

Postgres implements MVCC by storing version metadata directly in each row (no separate version store):

• xmin: transaction ID that inserted this row version.
• xmax: transaction ID that deleted or updated this row (0 = still live).

-- Inspect row versions directly
SELECT xmin, xmax, id, email FROM users WHERE id = 42;
-- xmin: when this version was created
-- xmax: 0 means row is live; non-zero means deleted/updated by that txn

A SELECT at snapshot timestamp $T$ sees a row version if xmin committed before $T$ and (xmax = 0 or xmax started after $T$).

When a transaction begins in Postgres with Repeatable Read or Serializable isolation, it captures a snapshot: the set of transaction IDs that are currently in-progress or not yet started. Rows visible to this snapshot satisfy:

1. xmin committed and xmin < snapshot's xmin horizon.
2. xmax is 0 or xmax is not yet committed at snapshot time.

This means the transaction always sees the database as it was at BEGIN — even if other transactions commit updates mid-flight. No locking required for reads.

Write conflicts are detected at commit time, not at read time:

• First-committer-wins: if two transactions update the same row, the second commit fails with a serialization error — the application must retry.
• SSI (Serializable Snapshot Isolation): Postgres 9.1+ additionally tracks read-write dependencies between transactions to detect and abort write-skew patterns.

MVCC's cost: old row versions accumulate on disk even after they're invisible to all active transactions. These dead tuples waste space and slow sequential scans.

VACUUM reclaims dead tuple space:

-- Manual vacuum with verbose output
VACUUM VERBOSE orders;

-- Autovacuum settings (per-table override)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- trigger at 1% dead tuples
  autovacuum_analyze_scale_factor = 0.005
);

Two critical failure modes:

• Table bloat: autovacuum not keeping up with a high-delete/update workload — table grows on disk even if row count is stable.
• Transaction ID wraparound: Postgres uses 32-bit transaction IDs. After ~2 billion transactions, IDs wrap around. If VACUUM hasn't advanced the relfrozenxid horizon, the database enters emergency freeze mode and goes read-only. Monitor pg_stat_user_tables.n_dead_tup and age(relfrozenxid).

Write skew is the most subtle anomaly and the one most often overlooked in application code. Classic example — the on-call scheduling invariant (at least one doctor must be on call):

-- Doctor A and Doctor B both run this concurrently at REPEATABLE READ:
BEGIN;
-- Both read: 2 doctors currently on call (invariant satisfied)
SELECT COUNT(*) FROM shifts WHERE on_call = true;  -- returns 2
-- Each decides it's safe to remove themselves
UPDATE shifts SET on_call = false WHERE doctor_id = ?;
COMMIT;  -- Both succeed! Now 0 doctors on call -- invariant violated.

Write skew cannot happen at Serializable isolation (Postgres SSI aborts one transaction). At Repeatable Read, the application must use SELECT ... FOR UPDATE to explicitly lock the rows it reads, converting the pattern to 2PL for that critical section.