Consistency & Transactions

Write Skew

Also known as: write-skew anomaly

Definition

Write skew is the anomaly where two concurrent transactions each read an overlapping set of rows, each decides an invariant still holds, and each writes a different row — leaving the invariant violated. Snapshot isolation permits it because neither transaction wrote what the other read.

Last reviewed · Part of the Architecture Glossary

In practice

The canonical example is on-call cover. The rule: at least one doctor must remain on shift.

Transaction A (Alice)Transaction B (Bob)
1SELECT count(*) WHERE on_call → 2
2SELECT count(*) WHERE on_call → 2
32 ≥ 1, so it is safe to leave2 ≥ 1, so it is safe to leave
4UPDATE ... SET on_call=false WHERE id=aliceUPDATE ... SET on_call=false WHERE id=bob
5COMMITCOMMIT

Nobody is on call. No write conflicted — A wrote Alice's row, B wrote Bob's — so first-committer-wins never fires.

The same shape shows up as: two bookings for one meeting room, two claims of the last unit of stock counted from an aggregate, two usernames passing a NOT EXISTS check.

Fixes:

  • SERIALIZABLE (Postgres SSI detects the read-write dependency cycle and aborts one).
  • Materialise the conflict: lock a row that represents the invariant — the shift, the room, the SKU — with SELECT ... FOR UPDATE.
  • A real constraint, where the invariant can be expressed as one (unique index, exclusion constraint).

When it matters

Any invariant that spans rows and is checked by reading before writing.

Common mistake

Testing for it with sequential test cases. Write skew only appears under true concurrency; a test suite that runs transactions one after another will pass forever while production quietly double-books.

See also

Go deeper