Two On-Call Doctors Both Leave, and "Repeatable Read" Lets Them

Two doctors are on call; a rule says at least one must stay. Each opens a REPEATABLE READ transaction, counts 2 on call, decides it's safe to leave, and takes itself off. Both commit — zero on call, no error. That's write skew, and snapshot isolation doesn't stop it. SERIALIZABLE does: Postgres SSI aborts one transaction with a real 40001.

Concept

Two doctors, Alice and Bob, are both on call for the same shift. A rule the system must never break: at least one doctor stays on call. Each wants to go home. Each opens a REPEATABLE READ transaction, runs SELECT count(*) FROM doctors WHERE on_call = true AND shift_id = 1, sees 2, concludes "the other one is still on, so it's safe for me to leave," and takes itself off call. Both commit. Now zero doctors are on call — the invariant is violated, and no error was raised. This is write skew, and the surprise is that the isolation level literally named repeatable read does not prevent it, nor does Postgres's lost-update detection (the two transactions wrote different rows, so there is no write-write conflict to detect). You will trigger it by hand in two psql sessions, then close it two ways: SERIALIZABLE (Postgres SSI aborts one transaction with a real 40001 error) and SELECT … FOR UPDATE (which locks the rows each decision was based on).

Provenance

Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — Transactions, §"Write Skew and Phantoms" / §"Characterizing write skew":

If two transactions read the same objects, and then update some of those objects (different transactions may update different objects), a write skew anomaly can occur.

The book uses this exact on-call-doctors example: each transaction checks that enough doctors are on call, then removes itself, and snapshot isolation lets both proceed on a premise that the other transaction is busy invalidating. Here you reproduce it on a real PostgreSQL, watch the invariant break, and watch serializability close it.

Prerequisites

The surprise is about which reads an isolation level protects. One line each, where it shows up; the last one carries the fix.

1. Snapshot isolation ("repeatable read") — each transaction reads from a consistent snapshot taken at its start; it never sees another transaction's uncommitted or later-committed writes. It shows up as both sessions reading 2 even though the other is about to change that. (In Postgres, the REPEATABLE READ level is snapshot isolation.)
2. Write skew vs lost update — different rows — a lost update is two transactions writing the same row (Postgres detects it and aborts one); write skew is two transactions reading an overlapping set but writing different rows, so there is no write-write conflict to catch. It shows up as Alice's UPDATE and Bob's UPDATE touching disjoint rows and both committing.
3. Predicates and phantoms — each decision depends on a predicate (on_call = true AND shift_id = 1), not a fixed row; a concurrent write that changes which rows match the predicate is a phantom. It shows up as the count each transaction trusted becoming stale the instant the other commits.
4. Serializable Snapshot Isolation (SSI) — Postgres's SERIALIZABLE adds tracking of read/write dependencies between transactions; if committing would produce a cycle that no serial order could, it aborts one with SQLSTATE 40001. This is the prerequisite that carries the fix — it is the only thing here that watches the rows you read to decide, not just the rows you write.

Mental model

Snapshot isolation makes one promise about reads and a different promise about writes, and write skew lives in the gap between them.

• The read promise: every statement in your transaction sees the database as of one consistent instant — your snapshot. Two concurrent transactions can therefore both read count = 2, because each is reading its own frozen picture where the other's change hasn't happened.
• The write promise: if two transactions write the same row, Postgres refuses to let both win — the second gets a serialization error (lost-update prevention). But that check is keyed on the rows you write. Alice's transaction writes only Alice's row; Bob's writes only Bob's. No row is written twice, so nothing fires.

The bug is that each transaction's decision depended on rows it only read (SELECT count(*) … on_call = true), and snapshot isolation does nothing to guarantee those still hold at commit. SSI (SERIALIZABLE) closes exactly this gap: it records the predicate each transaction read, notices that each transaction wrote something the other had read to make its decision — a read/write dependency cycle — and aborts one, because no serial order of the two could have produced this outcome.

The capture harness code/write_skew_doctors.py runs all three scenarios end to end and prints the same numbers you'll produce by hand; it is the maintainer's verification path, not a substitute for typing the interleaving yourself.

Setup

The Postgres container is already running (shared across Ch. 8 exercises). For reference, it was started with:

docker run -d --name ddia-ch08-pg -p 5432:5432 \
  -e POSTGRES_HOST_AUTH_METHOD=trust postgres:16

Create this exercise's own database, table, and seed rows:

docker exec ddia-ch08-pg psql -U postgres -d study \
  -c "CREATE DATABASE ex_ch08_writeskew;"

docker exec ddia-ch08-pg psql -U postgres -d ex_ch08_writeskew -c "
  CREATE TABLE doctors (
    name text PRIMARY KEY,
    on_call boolean NOT NULL,
    shift_id int NOT NULL);
  INSERT INTO doctors VALUES ('Alice', true, 1), ('Bob', true, 1);"

Open two terminals — one psql session per doctor:

# terminal 1 — SESSION A (Alice):
docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_writeskew

# terminal 2 — SESSION B (Bob):
docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_writeskew

Between scenarios, put both doctors back on call from either session:

UPDATE doctors SET on_call = true WHERE shift_id = 1;

Do not read ahead — make each prediction before you run the interleaving. The whole exercise is captured by code/write_skew_doctors.py.

Interleave the two sessions in the order shown (A₁, B₁, A₂, B₂, …). Typing the statements yourself, in this order, is the lesson.

-- SESSION A:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true AND shift_id = 1;
-- SESSION B:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true AND shift_id = 1;

-- SESSION A:
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;
-- SESSION B:
UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;
-- then, from either session:
SELECT count(*) FROM doctors WHERE on_call = true AND shift_id = 1;
SELECT name, on_call FROM doctors WHERE shift_id = 1 ORDER BY name;

-- SESSION A:                    -- SESSION B:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) ... ;            BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
                                 SELECT count(*) ... ;
UPDATE doctors SET on_call = false WHERE name = 'Alice';
                                 UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;                          COMMIT;

-- SESSION A:                    -- SESSION B:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT name FROM doctors
  WHERE on_call = true AND shift_id = 1 FOR UPDATE;
                                 BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
                                 SELECT name FROM doctors
                                   WHERE on_call = true AND shift_id = 1 FOR UPDATE;   -- blocks
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;                          -- B unblocks here

What you should see

• REPEATABLE READ: both sessions read count = 2; both UPDATEs and both COMMITs succeed with no error; final on-call count is 0 — invariant violated.
• SERIALIZABLE: same interleaving; A commits, B's COMMIT fails with ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001); final on-call count is 1.
• FOR UPDATE (READ COMMITTED): B's SELECT … FOR UPDATE blocks until A commits, then returns only Bob (1 row); final on-call count is 1.

Why

Every transaction here makes a decision — "it's safe for me to leave" — and that decision rests on a premise: "at least two doctors are on call." Under snapshot isolation, each transaction reads that premise from its own snapshot, taken at BEGIN, and the premise is true in that snapshot. The catastrophe is that the premise is about rows the transaction only read, and the action it then takes (UPDATE … SET on_call = false) is precisely what makes the other transaction's identical premise false. Snapshot isolation guarantees your reads are consistent with each other; it does not guarantee they are still true when you commit. And Postgres's lost-update protection can't save you either, because that mechanism watches for two transactions writing the same row — here they write disjoint rows (Alice, Bob), so there is no collision to detect. Two transactions, each correct in isolation, combine into an outcome (0 on call) that no serial execution — A-then-B or B-then-A — could ever produce: run them one at a time and the second reads count = 1 and stops.

SERIALIZABLE is the level that forbids exactly this. Postgres implements it as Serializable Snapshot Isolation: it keeps running on snapshots (so readers still don't block writers) but additionally tracks read/write dependencies — "transaction B read a predicate that transaction A then wrote into." When both transactions have such a dependency on each other, they form a cycle: A must come "before" B (A didn't see B's write) and B must come "before" A. No serial order satisfies both, so at commit Postgres identifies one transaction as the pivot and aborts it with 40001 — the exact DETAIL: … Canceled on identification as a pivot you saw. The application's contract with a serializable database is to catch 40001 and retry; on retry the survivor reads the now-true count and behaves. SELECT … FOR UPDATE reaches the same safety a heavier way: by locking the counted rows it turns the read-to-decide into a write lock, so the two transactions can no longer both proceed on the same premise — the second blocks, then re-reads reality.

Go deeper

• Materialize the conflict. Add a shifts(shift_id, min_on_call) row and have each transaction SELECT … FOR UPDATE that single shift row before deciding. Predict whether the anomaly vanishes at REPEATABLE READ now (it does) — you've turned an implicit predicate into an explicit lockable object, the "materializing conflicts" technique from the chapter.
• The meeting-room phantom variant. Model bookings(room_id, time_range) and two transactions each checking "no overlapping booking exists" then inserting their own. There's no row to lock (the conflicting row doesn't exist yet) — a true phantom. Watch SERIALIZABLE still catch it via SSI's predicate tracking, and see why FOR UPDATE alone can't.
• Measure the retry. Wrap session B in a client-side retry loop that catches 40001 and re-runs the transaction. Confirm that on the second attempt B reads count = 1 and declines to leave — turning the abort into correct behavior, which is how serializable applications are actually written.

Sources: DDIA 2e, Ch. 8, §"Write Skew and Phantoms", §"Characterizing write skew", §"Serializable Snapshot Isolation (SSI)" · PostgreSQL 16 manual, §"Transaction Isolation" and §"Serialization Failure Handling".