# DDIA Ch. 8: a phantom double-books a room, and `FOR UPDATE` can't stop it ## Concept Two people book the same meeting room at the same time. Each transaction first *checks* that the room is free — `SELECT count(*) FROM bookings` for anything overlapping room 1, 10:00–11:00 — and both see **0**, because the table starts empty. Reassured, each `INSERT`s its booking and commits. Under **REPEATABLE READ** both commits succeed and the room is **double-booked**: two rows for one slot. This is *write skew*, and the twist is what fixes it. The classic lost-update fix — `SELECT … FOR UPDATE` to lock the row you're about to change — does **nothing** here, because there is no row to lock yet: the conflicting row is a **phantom**, one that a concurrent transaction is about to `INSERT`. You'll watch `FOR UPDATE` fail to help, then watch two real fixes work: **SERIALIZABLE** aborts one transaction with a genuine `40001`, and an **exclusion constraint** rejects the second `INSERT` with a genuine `23P01`. ## Provenance *Designing Data-Intensive Applications*, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — *Transactions*, §"Phantoms causing write skew" (and §"Materializing conflicts"): > This effect, where a write in one transaction changes the result of a search > query in another transaction, is called a *phantom*. Snapshot isolation avoids > phantoms in read-only queries, but in read-write transactions […] phantoms can > lead to […] write skew. The book's Example 8-2 is exactly this: a booking system where two transactions check availability, both find the slot free, and both insert. Because the row they conflict over does not exist at check time, row-level locking (`FOR UPDATE`) has nothing to grab. Here you reproduce that on real PostgreSQL and close it two ways the book names. ## Prerequisites The surprise is that a lock can't protect a row that isn't there yet. These help; each line notes where it shows up. 1. **Write skew** — two transactions read an overlapping set, each decides based on what it read, and each writes; the writes don't touch the same row, but together they break an invariant. It shows up as both sessions reading "0 bookings" and each inserting one. 2. **Phantoms** — a write in one transaction changes the *result set* of a search predicate in another. It shows up as the booking that "wasn't there" when you counted but exists by the time you commit. 3. **Predicate / index-range locks** — to prevent a phantom you must lock the *query* (all current and future rows matching a predicate), not a row. Plain `FOR UPDATE` can't; this is why it fails here. 4. **Serializable Snapshot Isolation (SSI)** — PostgreSQL's `SERIALIZABLE`: optimistic, it tracks the read/write dependencies (including the predicate you queried) and aborts a transaction if committing would break serializability. It shows up as the `40001` on the second commit. 5. **Materializing conflicts** — if there's no row to lock, *create* one: a lock row per bookable slot, or a constraint that makes the database refuse the overlap physically. It shows up as the `EXCLUDE USING gist` constraint raising `23P01`. ### Where to learn the prerequisites - **Write skew, phantoms, and the fixes (#1–#3, #5):** DDIA §"Write Skew and Phantoms", §"Phantoms causing write skew", §"Materializing conflicts". - **SSI (#4):** DDIA §"Serializable Snapshot Isolation (SSI)"; the PostgreSQL manual §13.2.3 "Serializable Isolation Level" and Ports & Grittner, "Serializable Snapshot Isolation in PostgreSQL" (VLDB 2012). If only one is new, make it #3 — that a row lock cannot lock the *absence* of a row is the entire reason `FOR UPDATE` fails and predicate-level protection is needed. ## Environment Deterministic — the interleaving is forced, so the outcomes reproduce exactly: - 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. - A single shared server; this exercise creates and works in its own database `ex_ch08_phantom`, table `bookings`, starting **empty** (no overlapping booking). ## Mental model Same interleaving every time — A checks, B checks, A inserts, B inserts, A commits, B commits — run under four regimes. Only the protection differs. | Regime | What it locks / checks | Stops the double-booking? | | --- | --- | --- | | REPEATABLE READ, plain check | nothing — each sees its own snapshot | **No** — both commit | | REPEATABLE READ + `SELECT … FOR UPDATE` | existing rows in the result set — and there are none | **No** — locks nothing, both commit | | SERIALIZABLE (SSI) | the read/write dependency between the two transactions, predicate included | **Yes** — aborts one with `40001` | | `EXCLUDE USING gist` constraint | a physical index entry standing in for the predicate | **Yes** — rejects one `INSERT` with `23P01` | The pivot is the second row. `FOR UPDATE` is the textbook cure for *lost updates* — it locks the row you read so a concurrent transaction can't overwrite it. But a lock is a claim on a row that **exists**. The conflict here is over a row that *doesn't exist yet*: each transaction's `INSERT` is the phantom that would have changed the other's `count(*)`. You cannot lock a row into existence to prevent its own creation. Only something that reasons about the *predicate you searched* — SSI's dependency tracking, or a constraint indexed over the slot — can see the collision. The capture harness is `code/phantom_write_skew.py`; the steps below you run by hand in two `psql` sessions. ## Setup The shared server (already running for this chapter): ``` docker run -d --name ddia-ch08-pg -p 5432:5432 \ -e POSTGRES_HOST_AUTH_METHOD=trust postgres:16 ``` Create your own database and the empty `bookings` table: ``` docker exec -i ddia-ch08-pg psql -U postgres -c "CREATE DATABASE ex_ch08_phantom" docker exec -i ddia-ch08-pg psql -U postgres -d ex_ch08_phantom -c " CREATE TABLE bookings ( id serial PRIMARY KEY, room_id int NOT NULL, start_hour int NOT NULL, end_hour int NOT NULL);" ``` Now open **two** interactive sessions — Session A and Session B — in two terminals: ``` # Session A docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_phantom # Session B (second terminal) docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_phantom ``` Do **not** read the transcripts below yet — make each prediction first. Between steps, empty the table so each starts clean: `TRUNCATE bookings;`. ## Steps ### Step 1 — REPEATABLE READ: both check, both book **Predict.** Both sessions open a `REPEATABLE READ` transaction and count overlapping bookings for room 1, 10:00–11:00 (the table is empty). Each then inserts its booking; A commits, then B commits. How many bookings exist for that slot at the end — 1 or 2? ``` -- Session A BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM bookings WHERE room_id=1 AND start_hour<11 AND end_hour>10; -- Session B BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM bookings WHERE room_id=1 AND start_hour<11 AND end_hour>10; -- Session A INSERT INTO bookings (room_id, start_hour, end_hour) VALUES (1, 10, 11); -- Session B INSERT INTO bookings (room_id, start_hour, end_hour) VALUES (1, 10, 11); -- Session A COMMIT; -- Session B COMMIT; -- either session SELECT count(*) FROM bookings WHERE room_id=1 AND start_hour<11 AND end_hour>10; ``` **Observe.** ``` === isolation level: REPEATABLE READ === Session A check: overlapping bookings for room 1, 10-11 -> 0 Session B check: overlapping bookings for room 1, 10-11 -> 0 Session A INSERT booking (room 1, 10-11) Session B INSERT booking (room 1, 10-11) Session A COMMIT -> OK Session B COMMIT -> OK result: 2 booking(s) exist for room 1, 10-11 ``` **Two bookings — double-booked.** Each transaction read its own snapshot, saw an empty slot, and inserted. The two inserts touch *different* rows, so neither blocks the other and both commit cleanly. REPEATABLE READ (snapshot isolation) prevented every *read* anomaly and still let the invariant break. ### Step 2 — add `SELECT … FOR UPDATE`: still double-booked **Predict.** Same interleaving, but each check now takes a write lock on the rows it finds: `SELECT id … FOR UPDATE`. This is the standard fix for lost updates. Does it stop the double-booking this time? ``` -- Session A BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT id FROM bookings WHERE room_id=1 AND start_hour<11 AND end_hour>10 FOR UPDATE; -- Session B BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT id FROM bookings WHERE room_id=1 AND start_hour<11 AND end_hour>10 FOR UPDATE; -- Session A INSERT INTO bookings (room_id, start_hour, end_hour) VALUES (1, 10, 11); -- Session B INSERT INTO bookings (room_id, start_hour, end_hour) VALUES (1, 10, 11); -- Session A COMMIT; -- Session B COMMIT; ``` **Observe.** ``` === isolation level: REPEATABLE READ (SELECT ... FOR UPDATE) === Session A check: overlapping bookings for room 1, 10-11 -> 0 Session B check: overlapping bookings for room 1, 10-11 -> 0 Session A INSERT booking (room 1, 10-11) Session B INSERT booking (room 1, 10-11) Session A COMMIT -> OK Session B COMMIT -> OK result: 2 booking(s) exist for room 1, 10-11 ``` **Still two.** `FOR UPDATE` locked every row the query matched — and it matched **zero** rows, so it locked nothing. B's `SELECT … FOR UPDATE` never even sees A's uncommitted row (it's not in B's snapshot, and it isn't committed), and A's insert doesn't wait on B's lock because B holds no lock. The lost-update cure is useless against a phantom: there is no existing row for either session to lock. ### Step 3 — SERIALIZABLE: one transaction is aborted **Predict.** Same interleaving again, now both transactions at `SERIALIZABLE`. A commits first. What happens to B's `COMMIT`? ``` -- Session A BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM bookings WHERE room_id=1 AND start_hour<11 AND end_hour>10; -- Session B BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM bookings WHERE room_id=1 AND start_hour<11 AND end_hour>10; -- Session A INSERT INTO bookings (room_id, start_hour, end_hour) VALUES (1, 10, 11); -- Session B INSERT INTO bookings (room_id, start_hour, end_hour) VALUES (1, 10, 11); -- Session A COMMIT; -- Session B COMMIT; ``` **Observe.** ``` === isolation level: SERIALIZABLE === Session A check: overlapping bookings for room 1, 10-11 -> 0 Session B check: overlapping bookings for room 1, 10-11 -> 0 Session A INSERT booking (room 1, 10-11) Session B INSERT booking (room 1, 10-11) Session A COMMIT -> OK Session B COMMIT -> ERROR SQLSTATE 40001 could not serialize access due to read/write dependencies among transactions result: 1 booking(s) exist for room 1, 10-11 ``` In B's `psql` session the `COMMIT` returns the real error verbatim: ``` 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. ``` **One booking — B was aborted.** SSI tracked that B *read* a predicate (bookings overlapping the slot) that A then *wrote* into, and vice versa — a dangerous read/write dependency structure. On B's commit it detected B as the "pivot" and canceled it with `40001`. The application is expected to retry B, which on a second run sees A's booking and declines. One booking survives; the invariant holds. ### Step 4 — materialize the conflict: an exclusion constraint **Predict.** Drop back to the default isolation but add a constraint that forbids two overlapping bookings of the same room physically: `EXCLUDE USING gist (room_id WITH =, int4range(start_hour, end_hour) WITH &&)` (needs the `btree_gist` extension). A inserts and commits; then B inserts. What does B's `INSERT` do? ``` docker exec -i ddia-ch08-pg psql -U postgres -d ex_ch08_phantom -c " CREATE EXTENSION IF NOT EXISTS btree_gist; TRUNCATE bookings; ALTER TABLE bookings ADD CONSTRAINT no_double_booking EXCLUDE USING gist (room_id WITH =, int4range(start_hour, end_hour) WITH &&);" ``` ``` -- Session A BEGIN; INSERT INTO bookings (room_id, start_hour, end_hour) VALUES (1, 10, 11); COMMIT; -- Session B BEGIN; INSERT INTO bookings (room_id, start_hour, end_hour) VALUES (1, 10, 11); -- rejected ``` **Observe.** ``` === EXCLUDE USING gist constraint (READ COMMITTED) === Session A check: overlapping bookings for room 1, 10-11 -> 0 Session B check: overlapping bookings for room 1, 10-11 -> 0 Session A INSERT booking (room 1, 10-11) Session A COMMIT -> OK Session B INSERT -> ERROR SQLSTATE 23P01 conflicting key value violates exclusion constraint "no_double_booking" DETAIL: Key (room_id, int4range(start_hour, end_hour))=(1, [10,11)) conflicts with existing key (room_id, int4range(start_hour, end_hour))=(1, [10,11)). result: 1 booking(s) exist for room 1, 10-11 ``` In B's `psql` session: ``` ERROR: conflicting key value violates exclusion constraint "no_double_booking" DETAIL: Key (room_id, int4range(start_hour, end_hour))=(1, [10,11)) conflicts with existing key (room_id, int4range(start_hour, end_hour))=(1, [10,11)). ``` **One booking — B's `INSERT` was refused.** The exclusion constraint is a real index entry that *is* the predicate "no two overlapping bookings of a room." If B's insert had raced A's (both uncommitted), B would *block* on A's index entry and then fail on A's commit. The phantom now has a physical home the database can lock, so the collision is caught the moment the row is written — no isolation level required. ## What you should see - **REPEATABLE READ**, plain check: both commit → **2** bookings (double-booked). - **REPEATABLE READ + `FOR UPDATE`**: both commit → **2** bookings; the lock grabbed nothing. - **SERIALIZABLE**: A commits, B's `COMMIT` fails with **`40001`** → **1** booking. - **`EXCLUDE USING gist`**: A commits, B's `INSERT` fails with **`23P01`** → **1** booking. ## Why Snapshot isolation (PostgreSQL's `REPEATABLE READ`) gives each transaction a frozen view of the database as of its first statement. That kills every *read* anomaly — dirty reads, non-repeatable reads, read skew — because a transaction never sees another's uncommitted or newer data. But correctness here isn't about what you read; it's about a rule spanning rows that *don't exist yet*: "at most one booking overlaps this slot." Each transaction reads the slot as empty (true, in its snapshot), decides to book, and inserts. The two inserts are *different rows*, so from the storage engine's view nothing conflicts — no row is written twice, no lock is contended — and both commit. That's write skew: individually legal writes that jointly break an invariant, enabled by a **phantom**, the row each transaction's insert adds to the other's search result after that other already looked. `SELECT … FOR UPDATE` is built to stop *lost updates*: it takes a write lock on the rows a query returns, so a concurrent transaction that wants to modify the same row must wait. Its power is a lock on a *row that exists*. Against a phantom that power is empty — the query returns no rows, so there is nothing to lock, and each transaction's own future `INSERT` is invisible to the other's snapshot and uncommitted besides. Locking rows can never lock the *absence* of a row. The only things that can are those that reason about the **predicate** rather than the row: PostgreSQL's `SERIALIZABLE` (SSI) tracks that B *read a range* A then *wrote*, and that the two form a cycle that no serial order can reproduce, so it aborts one with `40001` (SSI is optimistic — it lets both run and cancels a "pivot" at commit, expecting the app to retry). Materializing the conflict does the same job pessimistically: an `EXCLUDE USING gist` constraint builds a physical index over `(room, hour-range)` that *is* the predicate, giving the phantom a concrete entry the database can detect and reject with `23P01`. Both work because both protect the *query*, not the *row*. **The boundary — phantoms only bite when the invariant spans rows that don't exist yet.** If your conflict is over a row that *already exists* — two people editing the same account balance — then `SELECT … FOR UPDATE` is exactly right and cheaper than serializable: there is a real row to lock, so lock it. The moment the invariant is a predicate over a *set* ("no overlapping booking," "username not taken," "at least one doctor on call"), the conflicting row may not exist at check time, and only predicate-level protection — SSI, a materialized lock row, or a constraint indexed over the predicate — closes the gap. Reach for row locks when the thing to protect is a row; reach for predicate protection when the thing to protect is a *query*. ### Go deeper 1. **Race the constraint.** In Step 4, have both sessions `INSERT` while both are uncommitted (don't commit A first). Predict which one *blocks*, and on whose `COMMIT` the `23P01` fires — the pessimistic-lock timing SSI avoids. 2. **Retry the loser.** After Step 3's `40001`, run B's transaction again from `BEGIN`. Predict its `count(*)` now that A has committed, and confirm it correctly declines to book — the retry loop `SERIALIZABLE` assumes you wrote. 3. **Measure the abort rate.** Loosen the slots so bookings rarely overlap, then run many concurrent bookers under `SERIALIZABLE`. Predict how the `40001` rate moves with contention — SSI is cheap when conflicts are rare, costly when they aren't, which is the whole optimistic bet.