Transactions: The Write Skew Snapshot Isolation Can't Stop, and Three Roads to Serializability

· tech

#distributed-systems#book-notes#transactions

📑 Contents

I laid the foundations of transactions in the SQL series: ACID’s emphasis is on the I, the three anomalies of dirty read / non-repeatable read / phantom, the spectrum of four isolation levels, how MVCC lets reads not block writes — none of that is repeated here. DDIA Ch7’s real added value is in the second half: two insidious anomalies that even “snapshot isolation” can’t stop (lost update and write skew), and, when you truly need the strongest guarantee, the three roads to serializability. Conclusion first: most people think they’re safe once snapshot isolation is on — this chapter exists to break that sense of safety.

Lost update: two read-modify-write cycles overwrite each other

The first one is at least easy to recognise: two transactions both “read → compute → write back”, and running concurrently they can’t see each other — both read 42, both add 1, both write back 43, and one of the updates simply evaporates. You’ve seen all the fixes: atomic operations (UPDATE SET counter = counter + 1, compressing read-modify-write into one step — the same as Redis’s INCR), explicit locking (SELECT ... FOR UPDATE), or compare-and-set optimistic locking (verify the value hasn’t changed when writing back — the same as Redis’s WATCH). Lost update has mature medicine; the truly stubborn one is next.

Write skew: each one right, together wrong

Write skew is this chapter’s signature, and the most counter-intuitive of the lot. The classic scenario: a hospital requires at least two doctors on call, and Alice and Bob both want to take leave:

Invariant: doctors on call ≥ 2 (now: Alice and Bob both on) Alice's txn ① count on call → reads 2 ≥ 2 ✓ ② set "self" to on leave Bob's txn ① count on call → also reads 2 ✓ ② set "self" to on leave (snapshot isolation: both see the old snapshot's 2) (different rows → no write conflict, both commit) Result: doctors on call = 0, invariant broken 💥 Each txn is right "on its own"; the error: the condition you checked was silently changed by the other's write Same script: double-booked meeting room, two accounts claiming a username, a balance debited twice at once
Both transactions are "check first, then act": each counts the doctors on call (under snapshot isolation both read the old snapshot's 2 ✓), and each sets its own row to on leave. Because they modify different rows, there's no write conflict and snapshot isolation lets both commit — and the number on call drops to zero. Each transaction is correct on its own, but together they break the invariant — because the condition your check relied on was silently changed by the other's write. That's write skew; swap "count doctors" for "is the meeting room free" or "is this username taken" and it's the version next to you. And phantoms are its fuel: what you checked was "the result of a query on some condition", and someone else's write changed that result

Why the usual tools can’t cure it: the two transactions write different rows, so snapshot isolation’s detection of “same-row write conflicts” can’t catch it; you’d like to FOR UPDATE lock, but the thing to lock may not exist yet (checking “nobody has booked this room” — what you want to lock is “a booking that doesn’t exist”, which is the phantom at work). Only two real cures: materialise the conflict (turn the “abstract condition” into a real row you can lock, say a room table with one row per time slot), or — upgrade to true serializability.

Three roads to serializability

The definition of serializable is pure: the outcome is equivalent to “all transactions run one after another”. What’s interesting is that the three ways of implementing it have wildly different personalities:

① Actually run one at a time single-threaded serial execution no concurrency = no concurrency bugs strongest isolation, dumbest method requires: every txn fast (in-memory, no I/O waits) Redis (Lua/MULTI) / VoltDB ② Two-phase locking (pessimistic) lock reads and writes, both block "block first, ask later" safe, long history high latency, poor throughput, deadlocks classic serializable (MySQL etc.) ③ SSI (optimistic) everyone runs, validate at commit affected by someone → abort, retry few conflicts: nearly free many conflicts: abort and retry forever PostgreSQL's serializable Txns short and fast → ①; conflicts likely → ② (lock first); conflicts rare → ③ (run first, retry occasionally)
① Serial execution: simply don't run concurrently — one thread, one transaction after another, and concurrency problems don't exist by construction. Redis (MULTI/Lua) and VoltDB take this road; the precondition is that every transaction is fast (data in memory, no waiting on the outside world inside a transaction). ② 2PL (pessimistic): lock reads and writes, both block each other — "something will go wrong, so block first"; safe, but latency and throughput are ugly and it deadlocks. ③ SSI (optimistic): let everyone run, and at commit validate "did anyone change what you read" — if so, abort and retry; nearly free when conflicts are rare, endless wasted retries when they're common. Which road depends on how fast your transactions are and how often they conflict

Reflections

”Check first, then act” is the number-one red flag of the concurrent world

The biggest gift write skew gave me is a code smell you can scan for: any passage that “SELECTs to check a condition, then writes if it passes” is a suspect under concurrency — because between the check and the action, the world may have changed. Check the balance then debit, check stock then order, check the username is free then register, check the lock is yours then release it — all the same check-then-act shape. Now when I review such a passage the reflex is three questions: are these two steps atomic? If not, what guarantees nobody slips in between? If someone does, what invariant breaks? Eight times out of ten, the author hasn’t thought about the third one. That smell detector is worth more than memorising any isolation level’s definition.

The strongest isolation comes from the dumbest method — and that’s beautiful

Of the three roads to serializability, my favourite is the first: simply don’t run concurrently. For decades people invented ever-cleverer locks and ever-subtler validation, and then Redis and VoltDB said: memory is fast enough now, I’ll run one transaction at a time on a single thread and concurrency problems don’t exist by definition. That’s exactly the “trade simplicity for predictability” I admired in the Redis single-thread post — and DDIA placing it in the context of transaction theory makes it clearer: it’s not a trick, it’s a legitimate implementation of serializable, with its preconditions (short transactions, data in memory, no waiting on the outside) spelled out plainly. It reminds me: when the hardware or the scenario changes, “the dumbest method” deserves re-evaluating — a lot of clever designs are paying complexity for a bottleneck that no longer exists.

Pessimistic or optimistic — just ask the conflict rate

2PL vs SSI is, in the end, the familiar question again: pay the cost up front (lock first, everyone queues) or afterwards (run to completion, retry on collision)? The criterion is clean — the conflict rate. Lots of transactions fighting over the same hot row, and the optimists abort until they question their existence — better to lock pessimistically; conflicts rare, and the pessimists’ locks are a tax paid for nothing — optimism is nearly free. It’s the same spectrum as Redis’s WATCH and git’s merge (optimistic: everyone edits their own copy, resolve only on conflict). I later turned it into a general decision framework: for any “coordination” mechanism, estimate the conflict rate first, then decide whether to buy insurance (pessimistic) or carry the excess (optimistic). The next chapter is harsher — once transactions span machines, even “locks” and “validation” themselves become unreliable, and that’s where distributed systems get truly troublesome.