hard Write Skew, Lost Update & Snapshot Isolation

Anomalies the ANSI triad never mentions

The textbook isolation ladder is defined by three anomalies it forbids — dirty read, non-repeatable read, phantom. But two of the most dangerous anomalies are not on that list: lost update and write skew. Both survive under snapshot isolation (SI), the level most people are actually running when they select “REPEATABLE READ” in PostgreSQL (Oracle has no REPEATABLE READ level — it exposes the same snapshot isolation under the name “SERIALIZABLE”). The mechanism: SI gives each transaction a consistent snapshot as of its start and lets readers never block writers — but a decision made against a stale-but-consistent snapshot can be wrong by the time it commits.

Worked example: the on-call doctors (write skew)

Invariant: at least one doctor must be on call. Alice and Bob are both on call and both feel unwell.

Both commit. Now zero doctors are on call. Each transaction read a snapshot where the invariant held, and each wrote a different row — so there is no write-write conflict for SI to detect. This is write skew: the writes don’t overlap, but the premise each read is invalidated by the other’s write.

Its simpler cousin: lost update (same row)

Two read-modify-write cycles on the same row. balance = 100:

T1: read balance (100)         T2: read balance (100)
T1: balance = 100 + 50         T2: balance = 100 + 20
T1: write 150                  T2: write 120   // overwrites 150; +50 is lost

The correct result is 170; you get 120. The naive balance = balance + x read-then-write is the bug — an atomic UPDATE ... SET balance = balance + 50 (a single statement) avoids it. Crucially, unlike write skew, lost update touches the same row, so it is a write-write conflict — and SI in PostgreSQL/Oracle detects it and aborts the second committer (first-committer-wins). Plain READ COMMITTED does not.

The correctness fact people get wrong

PostgreSQL’s “REPEATABLE READ” is really SNAPSHOT ISOLATION, which permits write skew. Oracle has no REPEATABLE READ level at all — it exposes SI under the name “SERIALIZABLE”, and, crucially, Oracle’s “SERIALIZABLE” is not truly serializable: it still permits write skew. Only PostgreSQL’s SERIALIZABLE, backed by SSI (Serializable Snapshot Isolation) — which tracks read/write dependencies and aborts a transaction that would complete a dangerous cycle — actually prevents it.

So the doctors bug is invisible under SI (PostgreSQL REPEATABLE READ / Oracle “SERIALIZABLE”) and caught only by PostgreSQL’s SSI-backed SERIALIZABLE (one transaction aborts with a serialization failure; the app retries).

MVCC visibility — the detail to keep straight

• READ COMMITTED takes a fresh snapshot per statement: each statement sees everything committed before that statement began. Two statements in one transaction can see different data.
• REPEATABLE READ / SI takes one snapshot per transaction (in PostgreSQL, fixed at the first query, not literally at BEGIN) and reuses it for every read.

Do not over-generalize “snapshot at transaction start” to READ COMMITTED — under READ COMMITTED the snapshot advances with every statement, which is exactly why a second read can return a newer value.

Pitfalls

• Assuming REPEATABLE READ is serializable. It isn’t — it is SI, and write skew slips through.
• Phantom-driven write skew. When the invariant depends on rows that don’t exist yet (e.g. “no overlapping booking”), SELECT ... FOR UPDATE has nothing to lock. You must materialize the conflict (lock a proxy row) or use SERIALIZABLE.
• MySQL/InnoDB REPEATABLE READ does not auto-detect lost update the way PostgreSQL/Oracle SI does — behavior differs across engines.

The judgment layer

Snapshot Isolation vs Serializable. SI is fast — readers and writers never block each other — and correct for the vast majority of transactions; its blind spot is multi-row invariants (write skew). SERIALIZABLE (SSI) closes that hole but pays for it with extra bookkeeping and, under contention, serialization-failure aborts that the application must catch and retry. Choose SERIALIZABLE when an invariant spans rows and correctness is non-negotiable (scheduling, double-spend, inventory); stay on SI where transactions are independent and throughput matters.

Named alternatives short of full serializable. SELECT ... FOR UPDATE explicitly locks the exact rows the invariant reads, turning the silent read into a real write-lock conflict — cheap and surgical when the rows already exist. When they don’t (phantoms), materialize the conflict: insert/lock a dedicated row representing the constraint (e.g. one row per on-call shift) so contenders serialize on it. These target one query; SERIALIZABLE protects the whole transaction blanket-wise at higher cost.

Takeaways

• Write skew & lost update are real anomalies outside the ANSI dirty/non-repeatable/phantom triad.
• PostgreSQL “REPEATABLE READ” (and Oracle’s misleadingly-named “SERIALIZABLE”) = snapshot isolation, which permits write skew; only PostgreSQL’s SSI-backed SERIALIZABLE prevents it.
• READ COMMITTED = snapshot per statement; REPEATABLE READ/SI = snapshot per transaction — don’t conflate them.
• Fix without going fully serializable via SELECT FOR UPDATE or by materializing the conflict.

Synthesized from Kleppmann, Designing Data-Intensive Applications (Ch. 7), Berenson et al. “A Critique of ANSI SQL Isolation Levels,” and the PostgreSQL documentation on transaction isolation and SSI (Ports & Grittner). Re-authored/Deepened for this guide.