← DDIA

Chapter 07 · Transactions

Transactions

Two people booked the same seat. And two on-call doctors both went off duty.

A transaction is a promise: all-or-nothing, isolated from the neighbors. Every weakening of that promise is a race waiting to happen.

ACID: atomic · consistent · isolated · durable. This chapter is the I.

Three acts: 1. races 2. snapshots 3. serialize

Three ways transactions collide

Same data, two clients, wrong answer.

txn A txn B time → leaked write BEGIN WRITE bal=800 READ bal → 800 acts on 800 ABORT B acted on an uncommitted ghost

A never committed. B already read the write, and already used it.

txn A txn B time → READ ctr → 42 READ ctr → 42 WRITE ctr = 42+1 WRITE ctr = 42+1 COMMIT COMMIT ctr = 43, should be 44 · one +1 lost

Both read 42. Both increment. Both write 43. Two +1s collapsed into one.

alice bob time → COUNT on_call → 2 ✓ COUNT on_call → 2 ✓ UPDATE alice off · 2→1 ok UPDATE bob off · 2→1 ok COMMIT COMMIT 0 doctors on call · invariant broken

Rule: ≥1 doctor must stay on call. Alice checks: 2 on call, going off leaves 1, safe. Bob checks the same and thinks the same. Neither sees the other going off first.

Every anomaly is a promise the database didn't make. Isolation levels are the rungs of stronger promises.

The isolation ladder each row lists what that level newly prevents. protections are cumulative up the ladder. Read Committed dirty read · dirty write Snapshot Iso non-repeatable read · lost updates (concurrent write conflicts)* Serializable write skew · phantoms† * Lost-update detection is implementation-dependent. PostgreSQL (40001), SQL Server (3960), and Oracle (ORA-08177) abort the later writer on a same-row conflict. MySQL/InnoDB's snapshot covers reads only, not writes, so read-modify-write silently loses updates unless you SELECT ... FOR UPDATE. † SI blocks phantom reads via snapshots. But write skew is a separate anomaly, and snapshots alone can't catch it (the doctors above). Vendor names for SI: PostgreSQL → REPEATABLE READ · SQL Server → SNAPSHOT · Oracle → SERIALIZABLE (SI-based; write skew still possible).

Snapshot isolation

Reads the past reliably. Cannot stop two futures from colliding.

balance · versioned rows visible if created ≤ snap 900 @ t=12 750 @ t=7 800 @ t=1 too new too new ✓ visible Txn A · reader began at t=5 snap-ts = 5 Txn B · writer commit @ t=7 → 750 commit @ t=12 → 900 read 1 → 800 read 2 → 800 readers never block writers · writers never block readers

MVCC (multi-version concurrency control) keeps every past row. Newest wins only if it committed before your snapshot; otherwise, walk down.

alice · snap=5 bob · snap=6 time → snap=5 · on_call → 2 ✓ snap=6 · on_call → 2 ✓ UPDATE alice off UPDATE bob off COMMIT COMMIT SI ✕ write skew · same snapshot, disjoint writes, invariant broken

Same snapshot, disjoint writes. What SI does well, read the past reliably, is what makes this fail.

Takeaway

Snapshot isolation blocks nearly every race. Write skew is the exception, and it is the whole reason Act 3 exists.

Three ways to actually be serializable

01

Serial execution

One thread per partition. Every txn is a stored procedure that runs to completion, no interactive round-trips.

WHEN TO PICK working set fits in RAM and every txn is fast. Redis, VoltDB.
WATCH OUT no cross-partition scaling. one slow txn stalls the queue.
02

Two-phase locking

Grab a shared lock on read, upgrade to exclusive on write. Hold until commit.

WHEN TO PICK you need strong guarantees on a mature engine. MySQL/InnoDB, SQL Server.
WATCH OUT deadlocks. Throughput collapses under contention.
03

Serializable Snapshot Iso

Run at SI. Track read-write dependencies. Abort losers at commit.

WHEN TO PICK reads far outnumber writes; conflicts are rare. PostgreSQL 9.1+.
WATCH OUT performance cliffs on hot rows. Aborts cost work.

PostgreSQL 9.1+ defaults to SSI. It is the modern answer.

── Reference ──

Six anomalies · three sources of confusion · five things to remember.

All the anomalies, one table
# Anomaly Definition Example First level that prevents it Mechanism
1 Dirty read Reading another txn's uncommitted write. A reader sees balance=600 mid-transfer; the writer aborts, leaving a ghost value. Read Committed Only committed row versions visible.
2 Dirty write Overwriting another txn's uncommitted write. Two txns update listing + invoice; interleave sells to Alice, invoices Bob. Read Committed Row-level write locks held until commit.
3 Read skew Two reads in one txn see different committed states. Read acc1=$500 before transfer, acc2=$400 after: $900 sum that never existed. Snapshot Iso One snapshot per txn, not per statement.
4 Lost update Two RMW cycles on the same row; later write overwrites earlier. Both read counter=42, both write 43: one +1 lost. Snapshot Iso* First-committer-wins: abort with 40001 / 3960 / ORA-08177.
5 Write skew Two txns read a shared premise, write disjoint rows; combined result violates invariant. Both doctors see count=2, each removes themselves: 0 on call. Serializable SSI tracks read→write deps, aborts one.
6 Phantom A row matching a predicate appears or disappears due to a concurrent write. Check "slot free" returns empty, insert; concurrent insert wins the slot. Read-side: SI. Write-decision: Serializable. SSI read-write dependency tracking.

* At each engine's SI level: PostgreSQL REPEATABLE READ (40001), SQL Server SNAPSHOT (3960), and Oracle SERIALIZABLE (ORA-08177) abort the conflicting writer. MySQL/InnoDB REPEATABLE READ silently loses updates; use SELECT ... FOR UPDATE or an atomic UPDATE.

How to tell lost update, write skew, and phantom-driven skew apart
Question Lost update Write skew Phantom-driven skew
Both txns write the same row? Yes No; disjoint No; one writes a row the other's query would have matched
What's shared? The row itself A premise both read A predicate
Does the conflicting row exist at read time? Yes Yes No. The defining feature.
Can SI catch it? Yes (same-row w-w) No No
Cheapest fix Atomic UPDATE or FOR UPDATE Serializable, or materialize the conflict UNIQUE / exclusion constraint if expressible; else Serializable

Dirty read/write → touching uncommitted data.

Read skew → my reads disagree with each other.

Lost update → our writes collide on one row.

Write skew → our writes don't collide, but our premises do.

Phantom → the collision is with a row that didn't exist yet.