The Decoherence Trap: When AI slop drowns out the signal in an endless sea of noise
Concept of the day · databases
Write Skew
Two transactions that are individually correct can break an invariant together.
The mechanism. Under snapshot isolation, each transaction reads a consistent snapshot and writes rows no one else wrote. No write-write conflict occurs, so both commit. But each made its decision based on rows the other then changed. The invariant spanned both row sets, and nothing checked it.
The canonical example. Hospital rule: at least one doctor on call. Alice and Bob are both on call. Each opens a transaction, reads "two doctors on call, fine", and takes themselves off. Both commit. Zero doctors on call. Neither transaction wrote a row the other read-locked, because snapshot isolation locks nothing on read.
Recognising it in your own code. Look for: read a set of rows, make a decision from an aggregate over them, then write. Booking the last seat, enforcing a unique username without a unique index, enforcing a balance floor across several accounts, claiming a job from a queue.
The fixes, in order of preference. A database constraint (unique index, check, exclusion constraint) — the database checks it for you. Then SERIALIZABLE isolation (PostgreSQL's SSI will abort one transaction; you must retry). Then materialising the conflict: lock an explicit row that represents the invariant, so the two transactions do conflict.
Takeaway. "We use transactions" does not mean "our invariants hold". Isolation level is part of the invariant.