CodePark/ questions / Isolation levels & anomalies
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Isolation levels & anomalies

Two doctors go off call at the same moment, each in their own transaction running this code. What anomaly can occur, why does Repeatable Read not stop it in PostgreSQL, and how do you fix it?

-- rule: at least one doctor must stay on call
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;  -- both see 2
-- app checks count > 1, then:
UPDATE doctors SET on_call = false WHERE id = $1;
COMMIT;

This is write skew. Here is what happens:

  • Both transactions read the same snapshot (the same view of the data).
  • Both see two doctors on call.
  • Each one updates a different row.
  • Neither writes a row the other wrote, so there is no write conflict.
  • Both commit, and nobody is on call.

Why Repeatable Read does not stop it: PostgreSQL's Repeatable Read is snapshot isolation. It only detects a conflict when two transactions change the same row. A decision based on rows you read, but did not write, is not protected.

Fixes:

  • Run it at SERIALIZABLE. PostgreSQL detects the read/write dependency and aborts one transaction with a serialization failure. You then retry that transaction.
  • Or lock the rows the decision depends on, for example SELECT id FROM doctors WHERE on_call FOR UPDATE, and count them in the application. The second transaction then waits for the first.
  • Or turn the rule into something a constraint or a single row update can enforce.
  • Write skew happens when decisions use read data but the writes go to different rows.
  • Snapshot isolation only catches write conflicts on the same row.
  • SERIALIZABLE detects write skew and aborts one transaction.
  • Another fix is to lock the rows you read with FOR UPDATE.
  • Retry the transaction on a serialization failure.