A hospital needs at least one doctor on call at all times. Tonight Alice and Bob both are. Both feel ill, and both open the app to take themselves off the rota at about the same time.
Two doctors, one rule, and two transactions that each did nothing wrong. Snapshots don’t help here, because nobody touched the same row.
Scroll to advance. Steps 1–6 are the idea, steps 7–10 go further. Switch the level, and how the check is written, at any step.
{{ warmFb }}
A hospital needs at least one doctor on call at all times. Tonight Alice and Bob both are. Both feel ill, and both open the app to take themselves off the rota at about the same time.
Each request runs in its own transaction. Alice’s counts the doctors on call and gets 2. Bob’s does the same and also gets 2. Both conclude it’s safe for one person to leave.
Alice’s transaction sets Alice off call. Bob’s sets Bob off call. They changed different rows, so there’s no lock to wait for and nothing to collide on.
{{ fb3 }}
Both commit, and nobody is on call. Neither transaction broke the rule by itself: run alone, each would have left one doctor on the rota. The bug only exists in the combination.
This is write skew: two transactions read the same data, then each writes something different based on what it read.
This is already running under REPEATABLE READ. Each transaction read a perfectly consistent snapshot, and that was the problem: both snapshots showed two doctors.
The rule that stopped lost updates, don’t overwrite a row that changed after your snapshot, never fires, because nobody wrote a row the other one had written.
{{ fb5 }}
Switch to SERIALIZABLE. Both transactions run as before and Alice’s commits. When Bob’s tries to commit, Postgres rejects it with a serialization error.
Bob’s app retries. This time it counts one doctor on call, and Bob stays on the rota.
You can stop here and you’ll recognise write skew. The next four steps are why no correct order exists, and how to fix it without SERIALIZABLE.
Why is there no correct order? Alice read Bob’s row, and Bob later changed it without Alice seeing, so Alice’s transaction has to come first. Bob read Alice’s row and Alice changed it, so Bob’s has to come first.
Each has to come before the other. Those arrows are called read-write dependencies, and when they form a loop, no serial order can explain the result.
Without SERIALIZABLE, the check itself can take locks. Postgres doesn’t allow FOR UPDATE on count(*), so select the rows and count them in the app: SELECT id FROM doctors WHERE on_call FOR UPDATE.
Alice’s check locks both rows. Bob’s check waits, then runs again against committed data, finds one doctor, and Bob stays. Every check on this rota now queues behind the others.
Switch to FOR UPDATE above. It works under READ COMMITTED; under REPEATABLE READ Bob gets a serialization error instead.
Once you know the shape, a read that decides a write to a different row, you see it in a lot of places. Two withdrawals from two accounts that must not go negative together. Two admins each removing the other’s role until no admin is left. A username checked in one table and claimed in another.
None of these collide on a single row.
FOR UPDATE worked here because the rows it needed to lock already existed. When the check is about absence, such as whether anyone has booked the room at 10:00, there’s nothing to lock.
That’s the next note.
Every idea in the series, and every step where it appears. To review, pick a term, jump to a step, and come back.
| Anomaly | Read committed | Repeatable read | Serializable | What stops it |
|---|---|---|---|---|
| Dirty read | prevented | prevented | prevented | Uncommitted versions are invisible · part 2, step 8 |
| Read skew | allowed | prevented | prevented | One snapshot per transaction · part 3, step 7 |
| Lost update (read, then write) | allowed | one fails · 40001 | one fails · 40001 | Refusing to overwrite a newer row · part 4, step 7 |
| Write skewthis note | allowed | allowed | one fails · 40001 | Tracking what was read (SSI) · part 5, step 6 |
| Phantom read | allowed | prevented | prevented | One snapshot per transaction · part 6, step 5 |
| Write skew on new rows | allowed | allowed | one fails · 40001 | Predicate locks · part 6, step 9 |
{{ q.q }}
{{ q.fb }}
{{ q.model }}