Write Skew Anomaly

Two concurrent transactions each read an overlapping set of rows, each confirms a multi-row invariant still holds, then each writes to a different row. Both commit. Individually every transaction is correct; together they break the invariant that no single one could have broken alone. Snapshot isolation (what PostgreSQL calls REPEATABLE READ) does not prevent it, because the two transactions never write the same row and so never collide. Only serializability, or an invariant enforced by the database itself, closes the gap.

Write skew is the bug you cannot see with single-row thinking. Reason about one transaction at a time and each is obviously correct: it reads the current state, checks the rule, and makes one change the rule permits. The defect lives in the invariant across a set of rows, and it only appears when two transactions each preserve that invariant locally while jointly destroying it.

The canonical example. A hospital requires at least one doctor on call. Alice and Bob are both on call. Each opens a transaction that (1) counts on-call doctors and finds 2, (2) confirms 2 is at least 1, and (3) sets themselves off call. Both commit. Zero doctors are on call. Neither transaction saw a value it was not allowed to see, and neither wrote a row the other also wrote. The rule was checked twice and broken once, by the pair.

The structural condition. Transaction T1 reads set R1 and writes set W1; T2 reads set R2 and writes set W2. The reads overlap (both consult the same constraint-relevant rows) but the writes are disjoint (each changes its own row). A database under snapshot isolation detects write-write conflicts, meaning two transactions updating the same row, and resolves them with first-updater-wins. It does not detect the read-write dependency that write skew is built on, because there is no shared write to notice. That is the whole reason lost update is preventable under snapshot isolation and write skew is not: lost update is two writers of one row, write skew is two writers of different rows whose reads overlapped.

Be precise about isolation levels, because the standard names and the engines disagree. PostgreSQL defaults to READ COMMITTED, where every statement takes a fresh snapshot, so the read-check and the write can even see different states. PostgreSQL REPEATABLE READ is not the weak thing the SQL standard describes; it is full snapshot isolation, a transaction-wide consistent snapshot that still permits write skew and phantoms. PostgreSQL SERIALIZABLE is the only level that prevents write skew. MySQL InnoDB is a different machine: its REPEATABLE READ mixes snapshot reads with next-key (gap) locks on locking statements, so its phantom behavior differs from PostgreSQL, but it does not prevent write skew either, and its SERIALIZABLE works by taking shared locks on plain reads (closer to two-phase locking) rather than the optimistic scheme PostgreSQL uses. Do not assume a mitigation that holds on one engine holds on the other.

The phantom trap, and why SELECT FOR UPDATE is not enough. Write skew often rides on a phantom: a transaction writes a row that would have changed the result of another transaction's search predicate. SELECT ... FOR UPDATE locks rows that exist; it cannot lock a row that does not exist yet. If the invariant is about the absence of a row, or a range with no rows in it (no overlapping booking for this room and time), FOR UPDATE has nothing to lock and the two inserts both succeed. FOR UPDATE is row locking, not predicate locking. It only helps when the invariant is carried by rows that are already there to be locked.

How serializability actually closes it. PostgreSQL SERIALIZABLE uses Serializable Snapshot Isolation (SSI). It runs transactions optimistically under snapshot isolation and tracks read-write dependencies (a transaction read a version that another then overwrote). When it sees the dangerous structure, a pivot transaction with both an incoming and an outgoing read-write dependency edge, it aborts one of them with SQLSTATE 40001 and the application must retry the whole transaction. Three honest caveats. First, SSI is conservative: it can abort transactions that would in fact have been serializable, so under contention you pay a retry rate, low single digits for many workloads and higher when hotspots collide. Second, the guarantee holds only among transactions that all run at SERIALIZABLE; one concurrent transaction at a lower level can reintroduce the very anomaly SSI was protecting against. Third, the retry logic is itself the correctness surface: if a single code path that performs a multi-row invariant check forgets to retry on 40001, that path is unprotected, and the failure is silent.