# DDIA Ch. 8: two on-call doctors both leave, and "repeatable read" lets them ## 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*. ### Where to learn the prerequisites - **Write skew, phantoms, and the doctors example (#1–#3):** DDIA §"Write Skew and Phantoms", §"Characterizing write skew", §"Weak Isolation Levels". - **SSI and the real error (#4):** DDIA §"Serializable Snapshot Isolation (SSI)"; the PostgreSQL manual, §"Transaction Isolation" (Serializable Isolation Level) and §"Serialization Failure Handling" — where `40001` and the retry loop are documented. If only one is new, make it **#4** — SSI's read/write-dependency tracking is the whole reason `SERIALIZABLE` catches what `REPEATABLE READ` cannot. ## Environment The anomaly (RR → 0 on call) and the fix (`SERIALIZABLE` → `40001`, ≥1 on call) are 100% reproducible on this substrate: - PostgreSQL 16.14 (Debian, aarch64) in Docker on macOS 26.5.2 (Darwin 25.5.0), arm64; captured via `psycopg[binary]` on Python 3.12 (`uv`); deterministic. - One shared Postgres container (`ddia-ch08-pg`, trust auth); this exercise works in its own database `ex_ch08_writeskew`. ## 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`. ## Steps Interleave the two sessions in the order shown (A₁, B₁, A₂, B₂, …). Typing the statements yourself, in this order, is the lesson. ### Step 1 — both doctors check the count (REPEATABLE READ) **Predict.** Both sessions `BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ`, then each runs `SELECT count(*) FROM doctors WHERE on_call = true AND shift_id = 1`. A₁ runs first, then B₁. What count does *each* session see? ``` -- 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; ``` **Observe.** ``` -- SESSION A -- SESSION B BEGIN BEGIN count count ------- ------- 2 2 (1 row) (1 row) ``` Both see **2**. Each concludes: "the other doctor is on call, so it's safe for me to leave." Neither has written anything yet. ### Step 2 — both take *themselves* off call and commit **Predict.** A takes Alice off call, B takes Bob off call (different rows), then both `COMMIT`. Does either `UPDATE` or `COMMIT` error? After both commit, what is `count(*) WHERE on_call = true`? ``` -- 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; ``` **Observe.** ``` -- SESSION A -- SESSION B UPDATE 1 UPDATE 1 COMMIT COMMIT count name | on_call ------- -------+--------- 0 Alice | f (1 row) Bob | f (2 rows) ``` **Zero doctors on call — and not one error.** Both `UPDATE`s succeeded (they hit different rows, so there is no lost-update conflict to detect), both commits succeeded, and the invariant "≥1 on call" is now broken. This is write skew. ### Step 3 — the fix: SERIALIZABLE Reset first: `UPDATE doctors SET on_call = true WHERE shift_id = 1;` **Predict.** Run the *identical* interleaving, but begin each transaction with `ISOLATION LEVEL SERIALIZABLE`. A commits first. What happens when **B** commits? What is the final on-call count? ``` -- 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; ``` **Observe.** ``` -- SESSION A -- SESSION B BEGIN BEGIN count count ------- ------- 2 2 (1 row) (1 row) UPDATE 1 UPDATE 1 COMMIT ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. HINT: The transaction might succeed if retried. ``` **A commits; B is aborted with SQLSTATE `40001`.** Bob's transaction is rolled back — Bob is still on call. Final state: **1** on call, invariant preserved. The `HINT` says it all: the application is expected to *retry* B, and on retry B will read `count = 1` and correctly decline to leave. ### Step 4 — the other fix: SELECT … FOR UPDATE (READ COMMITTED) Reset again: `UPDATE doctors SET on_call = true WHERE shift_id = 1;` **Predict.** Back at a weaker level (`READ COMMITTED`), each session *locks* the rows it counts with `SELECT … FOR UPDATE`. A locks first; then B runs the same `FOR UPDATE`. A takes Alice off call and commits. What does B's `SELECT … FOR UPDATE` do while A holds the locks — and what rows does it return once it proceeds? ``` -- 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 ``` **Observe.** ``` -- SESSION A -- SESSION B BEGIN BEGIN name (blocks on A's row locks...) ------- Alice Bob (2 rows) UPDATE 1 COMMIT name ------ Bob (1 row) ``` **B blocked until A committed, then re-read and saw only Bob** — one doctor on call. Because `FOR UPDATE` took a lock on the rows the decision depended on, B could not proceed on a stale snapshot; when it did proceed it saw the *current* truth (1 on call) and must not leave. Final state: **1** on call. ## What you should see - **REPEATABLE READ:** both sessions read `count = 2`; both `UPDATE`s and both `COMMIT`s 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. **The boundary — write skew needs different rows and a read-to-decide.** If both transactions had updated the **same** row (say, one shared `on_call_count` counter), Postgres would have caught it as a lost update and aborted one even at `REPEATABLE READ` — no serializability needed. And if the invariant were enforceable by a *constraint on a single row* (e.g. a `CHECK` or a unique index that the database evaluates atomically), the database would reject the offending write directly and write skew couldn't arise. Write skew appears precisely in the middle: a rule that spans *multiple rows*, checked by reading them, then enforced by writing *different* ones. That shape — read a set, decide, write elsewhere — is the signature to watch for whenever you reach for anything below `SERIALIZABLE`. ### Go deeper 1. **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. 2. **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. 3. **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.