Two engineers each run a transaction that checks at least one other person is still on call and then removes themselves. Both run concurrently under repeatable read. Both commit and nobody is left on call. Which statement is correct?
Show the full answer Hide the answer
What is being tested
Whether you know what an isolation level actually promises. "Repeatable read" says each transaction sees a consistent snapshot; it does not say the set of transactions produces a state any single order of them could have produced. The gap between those two sentences is where this class of bug lives, and it is called write skew.
The mechanism
Both transactions take a snapshot in which two people are on call. Both read it, both conclude the invariant "at least one person on call" will still hold after their own change, and each writes a different row. Nothing conflicts: snapshot isolation detects conflicting writes to the same row, and these are different rows.
The invariant spans rows, and no row-level check can see it. The database kept every promise it made. The application assumed a promise that was never offered.
Why the other options fail
- "This is a lost update." A lost update is two transactions writing the same row where one overwrite is discarded. Snapshot isolation does prevent that, by aborting the second writer. Here nothing is overwritten - the two writes are to different rows and both are intended.
- "A unique constraint would have prevented it." Uniqueness is the wrong shape of constraint: the invariant is a minimum count, not a prohibition on duplicates. If an invariant can be expressed declaratively, do so - but a "count ≥ 1 over a filtered set" generally cannot be.
- "One must be aborted." Only under serializable isolation. Under repeatable read, reads do not create conflicts, which is precisely what makes it cheap.
The three fixes, and when each is right
| Fix | How | Right when |
|---|---|---|
| Lock the rows you read | SELECT ... FOR UPDATE on the rows the decision depends on |
the decision reads a small - known set of rows |
| Materialise the conflict | update a single counter or lock row that every such transaction touches | the invariant is global and the transaction rate is modest |
| Serializable isolation | let the engine detect the dependency and abort one transaction | invariants span rows across many code paths and you cannot enumerate them |
Postgres implements serializable with snapshot isolation plus dependency tracking, so the failure mode becomes a serialization error at commit time - which means every transaction needs retry logic, and there will be some aborts that would have been safe. That is the cost: not throughput in the usual sense, but a correctness-preserving error your application must handle everywhere.
When not to use serializable
When the invariant is local to one row - a balance that must not go negative, say - a check
constraint or a conditional update (UPDATE ... WHERE balance >= amount) is simpler, cheaper and
cannot be forgotten by a future caller. Reach for serializable when the rule spans rows and
cannot be written as a constraint, and make sure the retry loop exists before you turn it on,
because an unhandled serialization failure is a user-visible error that the previous isolation
level never produced.