A Phantom Double-Books a Room, and FOR UPDATE Can't Stop It

Two REPEATABLE READ transactions each check that a meeting room is free — both see 0 overlapping bookings — then each INSERT the same slot and commit. The room ends up double-booked. Adding SELECT … FOR UPDATE, the lost-update fix, changes nothing: there is no row to lock. SERIALIZABLE aborts one with a real 40001; an exclusion constraint rejects one with a real 23P01.

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 INSERTs 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.

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.

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;.

-- 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;

-- 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;

-- 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;

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

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.

Go deeper

• 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.
• 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.
• 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.

Sources: DDIA 2e, Ch. 8, §"Write Skew and Phantoms", §"Phantoms causing write skew", §"Materializing conflicts", §"Serializable Snapshot Isolation (SSI)" · PostgreSQL manual §13.2.3 · Ports & Grittner, "Serializable Snapshot Isolation in PostgreSQL" (VLDB 2012).