Write Skew: The Anomaly Snapshot Isolation Does Not Catch

Snapshot isolation is the isolation level most production databases actually run. Each transaction reads a consistent snapshot of committed data as of the moment it started, and it commits only if no other transaction has written the same rows it wrote. Readers never block writers. Writers never block readers. Dirty reads, fuzzy reads, and the ANSI-catalogued anomalies disappear. It feels serializable.

It is not.

The hole has a name: write skew. Two transactions each read an overlapping set of rows, each write a different row the other only read, and both commit. There is no write–write conflict, so snapshot isolation is happy. The combined effect is a state that no serial execution would have produced, and an invariant you thought the database was protecting is gone.

This is the follow-up the Percolator post flags in one sentence. It is worth a post of its own, because the failure mode is quiet, the naming in vendor docs is actively misleading, and the fixes are small once you can see the shape.

What snapshot isolation actually guarantees

A transaction under snapshot isolation operates on a private view of the database, taken at start. Writes are staged. At commit the engine checks one thing: did anyone else commit a write to a row this transaction also wrote, after the snapshot was taken? If yes, abort (first-committer-wins). If no, the writes become visible at a new commit timestamp.

That check is a write-set intersection, not a read-set intersection. Two transactions that read the same row and write different rows sail through. MVCC makes this cheap: the engine already keeps versions, so a snapshot is a timestamp, not a lock, and the commit check is a scan of the transaction's own writes. That is why SI won. It is also why SI is not serializable.

The bank that goes negative

The textbook example, used since Berenson, Bernstein, Gray, Melton, and the O'Neils' 1995 SIGMOD paper A Critique of ANSI SQL Isolation Levels, is a two-account constraint.

Phil has checking V1 = 100 and savings V2 = 100. The rule is V1 + V2 ≥ 0 — either account may go negative if the other covers it. Two withdrawals start at the same time:

T1: read V1, V2          (sees 100, 100)
    V1 := V1 - 200       (now -100)
    check V1 + V2 ≥ 0    (uses snapshotted V2 = 100) → ok
    commit

T2: read V1, V2          (sees 100, 100)
    V2 := V2 - 200       (now -100)
    check V1 + V2 ≥ 0    (uses snapshotted V1 = 100) → ok
    commit

No row was written by both transactions. Snapshot isolation commits both. The database now holds V1 = V2 = -100. The constraint is broken. In any serial order the second withdrawal would have seen the first and aborted.

The doctors-on-call variant is the same diagram with different nouns: a table of who is on duty, a constraint that at least one doctor stays on, two people who each see the other on duty and both go off. Same disjoint writes, same dead invariant.

Why ANSI SQL missed it

ANSI SQL-92 defined isolation levels by forbidding a short list of phenomena — dirty write, dirty read, non-repeatable read, phantom — that lock-based engines produce. Snapshot isolation exhibits none of them and still is not serializable. That is the point of the 1995 critique: the standard's anomaly list is not a definition of serializability, and an isolation level can look "clean" on that list while allowing histories no serial schedule would.

Vendors then made the vocabulary worse. Oracle's SERIALIZABLE isolation is snapshot isolation. PostgreSQL, before 9.1, used the same name for the same thing. If you set the isolation level to serializable and assumed the database would refuse the two withdrawals above, you were wrong — you had SI, and SI is fine with write skew.

How to close the hole

You have three honest options. They are not equally convenient.

Raise the isolation level to real serializability. PostgreSQL 9.1 shipped serializable snapshot isolation (SSI), from Cahill, Röhm, and Fekete's 2008 SIGMOD paper. SSI keeps SI's non-blocking reads and adds a runtime check for "dangerous" structures of concurrent transactions — the rw-antidependency patterns that produce write skew. When it sees one, it aborts a participant with a serialization failure (SQLSTATE 40001). The application retries. Ports and Grittner later described the PostgreSQL implementation in VLDB 2012. You pay in extra aborts, not in lock convoys. That is a good trade if your writers already know how to retry.

Materialize the conflict. Give the hidden constraint a row both transactions must update. A account_totals row for Phil, or an on_call_count row for the ward: each withdrawal subtracts from the total. Now the two transactions write the same row, SI's first-committer-wins check fires, and one aborts. You have turned a predicate invariant into a write–write conflict. The cost is a denormalized column and a hotter row.

Promote a read to a write. SELECT … FOR UPDATE (Oracle and PostgreSQL) or an equivalent "update a row to itself" forces the read into the write set. The second transaction now conflicts with the first. Use this when the constraint is local to a small, known set of rows and you do not want a new table.

What does not work: hoping REPEATABLE READ is enough. In PostgreSQL, REPEATABLE READ is snapshot isolation. It will not catch write skew. Only SERIALIZABLE (SSI, 9.1+) will — and only if every transaction that participates in the invariant runs at that level. Mixed isolation is how the hole reopens.

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT balance FROM accounts WHERE id IN ('v1', 'v2');
-- application checks v1 + v2 - 200 >= 0

UPDATE accounts SET balance = balance - 200 WHERE id = 'v1';

COMMIT;
-- on SQLSTATE 40001: retry the whole transaction

When you actually have to care

Most rows in most apps do not sit under a multi-row invariant. A user editing their own profile, an order inserting a line, a counter that is allowed to race — write skew is not your problem, and SI is the right default. Reach for SSI or a materialized conflict when the business rule spans rows that different transactions can write independently:

• "At least N of these flags stay true."
• "The sum of these balances stays non-negative."
• "Exactly one of these mutually exclusive states is set."
• "This inventory pool minus all open reservations stays ≥ 0," if reservations are separate rows.

Those rules look like they belong in the application. Under SI, they do belong in the application — or in a lock, or in a conflict row, or in SSI. The database will not infer them from a CHECK constraint on a single row, because each transaction's check ran against a snapshot that was true at the time.

Spanner's external consistency closes this class of bug a different way: true serializability plus real-time order, paid for with TrueTime and consensus. Most of us are not running Spanner. Most of us are running SI and calling it good enough, which it is, until the invariant is multi-row and the writes are disjoint.

The takeaway

Snapshot isolation is a write-set isolation level wearing a serializable costume. It prevents the anomalies ANSI named and permits the one ANSI did not: two transactions that each preserve an invariant on a snapshot, and together break it. Name the invariant, decide whether it is local enough to lock or important enough to serialize, and stop trusting the word SERIALIZABLE on the vendor's label. The isolation level you asked for and the isolation level you got are not always the same thing.