Phantom read

A phantom read happens when a transaction reads the rows matching a condition, such as “bookings for room R1 that overlap 10:00 to 11:00”, and another transaction then inserts, deletes or updates rows so that a different set matches. No row you read has changed; the set has. The real danger isn’t a recount that differs. It’s two transactions that each check a slot is free, both insert, and both commit.

Picture it first: the clubhouse register

Your housing society books its clubhouse in a paper register, one line per booking, with a photocopy at each of two desks. Meera and Kabir arrive at once, one per desk. Each scans Saturday, sees nothing at 7 pm and writes a new line. That evening the merged register shows two parties in one hall.

Nobody overwrote anything; each wrote a new line on a blank part of the page. Pinning someone’s line with your thumb (a row lock) wouldn’t have helped, because the troublesome line was blank when you looked. Three things would have:

• A paperweight on Saturday’s page while you check and write: a range lock, or a lock on a parent row.
• Pre-printed lines, one per slot, so the second person finds the line already filled: a materialized conflict.
• A secretary who refuses overlapping entries, whoever writes them: a constraint.

Map it back: the register is a table, Saturday’s lines are the rows matching a WHERE clause (the predicate), the photocopies are snapshots, the new line is the phantom.

Phantom, precisely

SQL-92 defines the phantom in English: T1 reads the rows satisfying a search condition, T2 creates rows that satisfy it and commits, and T1’s repeated read returns a different set. Berenson and co-authors (1995) wrote it as histories, where r1[P] means “T1 reads every row satisfying predicate P”, and gave two readings:

• Strict, A3: r1[P] … w2[y in P] … c2 … r1[P] … c1. Only a visible re-read of P counts.
• Broad, P3: r1[P] … w2[y in P] … (c1 or a1). Once T1 has read P, any write by another transaction to a row in P before T1 finishes is the phenomenon, re-read or not.

“Write” covers insert, update and delete: a row can enter P or leave it. The standard’s English mentions only inserts.

The strict reading is too weak. In the paper’s history H3, T1 lists active employees, T2 inserts one and bumps a separate employee-count row, and T1 then reads the count. List and count disagree, yet no predicate was read twice, so A3 never fires. Forbidding the broad P3 amounts to holding predicate read locks until commit: locks on every row matching the condition, including rows that don’t exist yet. Real engines approximate that, usually by locking index ranges.

Phantom vs non-repeatable read

Both are “I read twice and got two answers”; they differ in what changed.

Why phantoms are hard: you can’t lock what isn’t there

Row locks protect rows that exist, but a predicate read’s answer also depends on rows that could exist. That gives two flavours of trouble.

Read phantoms: a report that doesn’t add up

A UPI-style settlement job counts the day’s payments, then pulls the detail rows. A late payment commits in between: the header says 1,204, the detail lists 1,205. Easy fix: read both from one snapshot (REPEATABLE READ in PostgreSQL, plain reads in MySQL).

Write phantoms: check-then-insert

The dangerous pattern has three steps:

1. Check a rule with a predicate read: no booking overlaps, the username is free, the user has fewer than 3 active sessions.
2. Decide from the result.
3. Write a row that changes the predicate’s answer: insert the booking, user or session.

Run two at once: each check is right for its own view, each write touches a different row, and together they break the rule. Berenson’s paper has a crisp case: tasks matching a predicate may total at most 8 hours; two transactions each see 7, each add a 1-hour task, and the total reaches 9. This is write skew through a predicate, which Hermitage tests as G2. Two things make it slippery:

• Snapshot isolation sees no conflict. First-committer-wins compares writes to the same rows; two inserts of new rows never collide.
• A locking read locks nothing. SELECT … FOR UPDATE locks the rows it returns, and the answer was “none”.

Deletes are easier. If on-call doctors sign off by deleting their own row after counting the shift’s rows, those rows exist at the count, so FOR UPDATE on them makes two sign-offs queue. Inserts, with nothing there yet, slip through.

The double booking, step by step

A meeting-room table in PostgreSQL, one half-open time range per booking:

CREATE TABLE bookings (
  id        bigserial PRIMARY KEY,
  room      text      NOT NULL,
  during    tstzrange NOT NULL,   -- [start, end): 10:00–11:00 and 11:00–12:00 don't overlap
  booked_by text      NOT NULL
);
CREATE INDEX ON bookings (room);

Both users run the same code; only the time range and the name differ:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM bookings
 WHERE room = 'R1'
   AND during && tstzrange('2026-10-27 10:00+05:30', '2026-10-27 11:00+05:30');
-- the app inserts only if the count is 0
INSERT INTO bookings (room, during, booked_by)
VALUES ('R1', tstzrange('2026-10-27 10:00+05:30', '2026-10-27 11:00+05:30'), 'Asha');
COMMIT;

Asha wants 10:00 to 11:00, Ravi wants 10:30 to 11:30, and their requests interleave:

On PostgreSQL both commit, leaving two overlapping R1 bookings. The read phantom is prevented: a re-count by A after Ravi’s commit still returns 0, because A’s snapshot predates his insert. READ COMMITTED double-books too. At SERIALIZABLE, PostgreSQL sees that each session read what the other wrote and rolls one of them back with SQLSTATE 40001, “could not serialize access due to read/write dependencies among transactions”, at the latest when it tries to commit. Only one booking remains.

What each engine actually does

PostgreSQL

At READ COMMITTED, each statement takes a fresh snapshot, so a re-run query sees new rows. At REPEATABLE READ, one snapshot serves the whole transaction: read phantoms disappear, write phantoms remain.

At SERIALIZABLE it runs Serializable Snapshot Isolation (SSI): each read also leaves a non-blocking predicate lock, shown in pg_locks as SIReadLock. When another serializable transaction writes into something you read, PostgreSQL records a read-write dependency, and when dependencies form a pattern no serial order could produce, it aborts one transaction with 40001.

Ports and Grittner describe SIRead locks on tuples, pages, whole relations and index “gaps”, so an insert into a scanned range is noticed. In 2012, B-tree gap locks were page-sized and other index types locked the whole index; PostgreSQL 11 added predicate locking for hash, GiST and GIN. Fine locks get promoted to coarse ones when lock memory runs short, and a sequential scan always needs a lock on the whole relation. So a count that uses the room index leaves SIRead locks on the index pages and rows it touched; the same count as a sequential scan locks all of bookings, and every concurrent serializable writer to the table now looks like a conflict.

MySQL InnoDB

InnoDB has two kinds of read, and they disagree:

• Consistent reads (plain SELECT) read a snapshot taken at the transaction’s first such read, at the default REPEATABLE READ. No read phantoms, no locks.
• Locking reads (FOR SHARE, FOR UPDATE) and the searches inside UPDATE and DELETE read the latest committed rows and lock what they scan, with next-key locks that also block inserts into the scanned range.

So one transaction can get two answers to one question:

-- MySQL 8.4, REPEATABLE READ, Session A
START TRANSACTION;
SELECT COUNT(*) FROM bookings
 WHERE room = 'R1' AND start_at < '2026-10-27 11:00' AND end_at > '2026-10-27 10:00';   -- 0
-- Session B inserts R1 10:30–11:30 and commits
SELECT COUNT(*) FROM bookings WHERE …;              -- 0  (snapshot)
SELECT COUNT(*) FROM bookings WHERE … FOR SHARE;    -- 1  (latest rows, now locked)
SELECT COUNT(*) FROM bookings WHERE …;              -- 0  (snapshot again)
COMMIT;

Hence Hermitage’s verdict that MySQL’s REPEATABLE READ prevents predicate anomalies only for read-only transactions. A plain SELECT check double-books, as in PostgreSQL; a FOR UPDATE check makes the second session wait or, on an empty range, deadlock.

At READ COMMITTED, InnoDB disables gap locking for searches and index scans, so a FOR UPDATE check on an empty range locks nothing and both bookings commit. At SERIALIZABLE, plain SELECTs inside a transaction become FOR SHARE reads with shared next-key locks; both checks succeed, each insert then waits for the other’s gap lock, and InnoDB rolls one back with error 1213, “Deadlock found”.

SQL Server

REPEATABLE READ locks only rows already read, so new rows can land in the range. SNAPSHOT behaves like PostgreSQL’s REPEATABLE READ. SERIALIZABLE takes key-range locks on index entries, covering the gaps between keys: n + 1 RangeS-S locks for a scan matching n rows, and only if an index covers the predicate column. Two RangeS-S locks are compatible but an insert’s RangeI-N conflicts with them, so two serializable check-then-inserts end in a deadlock victim (error 1205), as in InnoDB.

Inside InnoDB: gap, next-key and insert intention locks

InnoDB locks index records. Four kinds matter here:

• A record lock locks one index entry.
• A gap lock locks the space between two entries (or beyond the first or last). It only stops inserts, and gap locks from different transactions coexist.
• A next-key lock is a record lock plus the gap before that record. For keys 80, 95, 130 and 170 they are (−∞, 80], (80, 95], (95, 130], (130, 170] and (170, +∞), the last via a “supremum” pseudo-record.
• An insert intention lock is a gap lock an INSERT sets first. Inserts at different positions in one gap don’t block each other, but they wait for any gap or next-key lock covering it.

Together these let InnoDB “lock the nonexistence” of a row, in the manual’s phrase: a locking read that finds nothing keeps the range empty until you commit.

Three consequences follow, all spelled out in the manual’s locking pages:

Locks follow the index scan, not your WHERE clause. InnoDB “only knows which index ranges were scanned”. A booking check that scans (room, start_at) can also block an insert for R1 at 15:00, which overlaps nothing, or one for the neighbouring room, because the gaps at both ends of the scanned range are locked. Over-locking costs concurrency, not correctness.

No usable index means locking everything. Without one, “every row of the table becomes locked”. With no index on room, the check scans the whole clustered index, so it blocks inserts for every room and updates to unrelated rows. The plan decides, not the predicate: on a tiny table the optimizer may choose a full scan even for id > 100, and then every record is locked. Run EXPLAIN on locking reads.

Two empty-range locks don’t exclude each other. Gap locks are compatible, so two FOR UPDATE checks on one empty range both succeed; each INSERT then waits for the other, and InnoDB kills one with error 1213. Nobody double-books, but you need a retry.

Fixing it: the menu

Every fix gives the two transactions something concrete to collide on; they differ in what, and in who pays.

SERIALIZABLE and a retry loop

Run the booking at SERIALIZABLE and retry on 40001. A transaction that is correct alone stays correct in any mix of serializable transactions, or doesn’t commit, which covers rules no constraint can express, such as “at most 3 sessions”. You pay with retry code around the whole transaction, some needless aborts, and a fragile assumption: only serializable transactions are tracked, so a READ COMMITTED admin script escapes. See serializable isolation.

A locking read on an indexed range (MySQL, SQL Server)

On MySQL at REPEATABLE READ, make the check a locking read over an index that bounds the scan:

-- MySQL 8.4, REPEATABLE READ (the default)
CREATE INDEX idx_room_start ON bookings (room, start_at);

START TRANSACTION;
SELECT id FROM bookings
 WHERE room = 'R1'
   AND start_at < '2026-10-27 11:00'
   AND start_at > '2026-10-27 06:00'   -- bookings last at most 4 hours: bounds the scan
   AND end_at   > '2026-10-27 10:00'
 FOR UPDATE;                           -- next-key locks over the scanned range
-- no rows: INSERT the booking, then COMMIT

Without the lower start_at bound, the locks cover every R1 booking ever made. Retry the occasional 1213 deadlock. On SQL Server, WITH (UPDLOCK, HOLDLOCK) on the check gives update-mode key-range locks, which conflict with each other, so the second checker waits at its SELECT instead of deadlocking at its INSERT.

Lock a parent row as a mutex

Give the rule an owner row and lock it first: the room for bookings, the shift for sign-offs, the user for sessions.

BEGIN;  -- PostgreSQL, READ COMMITTED
SELECT 1 FROM rooms WHERE room = 'R1' FOR UPDATE;     -- the queue forms here
SELECT count(*) FROM bookings
 WHERE room = 'R1'
   AND during && tstzrange('2026-10-27 10:30+05:30', '2026-10-27 11:30+05:30');
-- count = 0: INSERT the booking; otherwise ROLLBACK
COMMIT;

Ravi waits on the first statement until Asha commits; his count then takes a fresh snapshot and sees her row. It works on any SQL database, but it serializes all bookings for the room, overlapping or not: fine for a meeting room, a bottleneck for a hot parent like a flash-sale event (contention and double booking).

Materialize the conflict

Turn “no overlapping row exists” into “this row is still free”. Pre-create one row per room per 30-minute slot (or per seat per show, per berth per train and date) and book with a conditional update:

UPDATE room_slots
   SET booked_by = 'Ravi', booking_id = 9172
 WHERE room = 'R1'
   AND slot_start IN ('2026-10-27 10:30+05:30', '2026-10-27 11:00+05:30')
   AND booked_by IS NULL;
-- 2 rows updated: booked. Fewer: ROLLBACK, the slot is taken.

Ravi’s update waits on the 10:30 row Asha locked. When she commits, PostgreSQL’s READ COMMITTED re-checks the WHERE clause on the new version and skips the row, so Ravi updates 1 row, not 2, and rolls back; at REPEATABLE READ he gets 40001. For 50 rooms with half-hour slots from 9:00 to 18:00, 90 days ahead, that’s 50 × 18 × 90 = 81,000 rows: trivial to store, but a job must roll the calendar forward. Counted limits work too: number each session’s slot with CHECK (slot BETWEEN 1 AND 3) and UNIQUE (user_id, slot), and a fourth login finds no free number.

Let a constraint say no

A constraint binds every writer at every isolation level. Usernames get CREATE UNIQUE INDEX ON users (lower(username)); one confirmed booking per berth gets a partial unique index with WHERE status = 'CONFIRMED'; non-overlapping ranges get an exclusion constraint:

CREATE EXTENSION IF NOT EXISTS btree_gist;   -- lets GiST compare text with =
ALTER TABLE bookings
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room WITH =, during WITH &&);

Ravi’s INSERT then waits for Asha’s uncommitted row and, once she commits, fails with SQLSTATE 23P01 (exclusion violation); map it to “slot taken”. A constraint compares two rows at a time, so it can say “no two overlap” but not “at most 3”, and EXCLUDE is PostgreSQL-only. More in database constraints.

Advisory locks

A parent row without the row: SELECT pg_advisory_xact_lock(4211) locks an application-chosen 64-bit key (a room id, or a hash of room:R1) until the transaction ends. PostgreSQL doesn’t enforce their use, so a code path that skips the call skips the protection.

Going deeper: phantoms beyond one database

Predicate locks, gap locks and SSI live inside one database’s lock manager. Shard bookings by booking id, or move them to a key-value store, and two shards can each confirm “no overlap here” at once. The fix is to make the conflict a single key that one node owns:

• Conditional writes on a deterministic key. In DynamoDB, put ROOM#R1#SLOT#2026-10-27T10:00 with attribute_not_exists(pk). The condition is evaluated against the one item that key identifies, so the second writer fails. A multi-slot booking wraps its puts in TransactWriteItems, all-or-nothing for up to 100 writes: materialized slots without SQL.
• Uniqueness tables. For unique emails across sharded users, conditionally write an item keyed by the email first. The key is the predicate.
• Short holds in Redis. SET hold:R1:2026-10-27T10:00 bk_9172 NX PX 600000 succeeds for the first caller only and expires in 10 minutes: a tatkal-style hold during payment, with the database still the source of truth.
• Distributed locks, last. A distributed lock needs leases and fencing tokens, because a paused holder can wake after its lease expired.

Going deeper: how the fixes fail

• Random-looking 40001s. SSI aborts follow what you scan: sequential scans and lock promotion lock whole relations. Index the predicate columns and keep transactions short.
• 23505 instead of 40001. PostgreSQL warns that serializable inserts can hit unique violations when other writers insert without checking first. Treat it as “taken”, or retry.
• Gap-lock deadlocks and waits. Empty-range checks deadlock on insert, and next-key locks stall inserts that merely sort nearby.
• A writer at a weaker level. SSI, locking reads and advisory locks protect only transactions that use them; a READ COMMITTED batch import bypasses all three. Constraints and slot rows bind everyone.

Cheat sheet

How to say it in the interview

“A phantom is when the set of rows matching a WHERE clause changes under a transaction because someone inserted, deleted or updated rows into or out of it. A read phantom just makes a recount differ, and a snapshot fixes it. The dangerous kind is check-then-insert: two bookings both count zero overlaps and both insert. There’s no row to lock, so FOR UPDATE does nothing in PostgreSQL, and snapshot isolation sees no conflict. For a meeting room I’d add an EXCLUDE constraint on room and time range. If the rule doesn’t fit a constraint, I’d lock the room row, pre-create slot rows, or use SERIALIZABLE with retries.”

Does REPEATABLE READ prevent phantoms? Depends on the engine and the read. PostgreSQL’s is a snapshot: no phantoms on re-read, but check-then-insert still double-books. In MySQL, plain SELECTs read a snapshot while locking reads see the latest rows and take next-key locks, so only a locking check is safe. SQL Server’s shows phantoms, as the standard allows.

Why doesn’t SELECT FOR UPDATE stop double booking when there are no rows yet? It locks the rows it returns, and there are none, so in PostgreSQL both sessions insert. InnoDB at REPEATABLE READ locks the empty gap, but gap locks are compatible, so the two inserts deadlock and one rolls back; at READ COMMITTED it double-books.

What is a gap lock? An InnoDB lock on the space between index entries that only stops inserts. A next-key lock is a record lock plus the gap before it, taken by locking reads at REPEATABLE READ and above; an INSERT’s insert intention lock waits for it. With no usable index, the whole table is locked.

How would you prevent double booking of a meeting room? In PostgreSQL, EXCLUDE USING gist (room WITH =, during WITH &&), which rejects the overlap with 23P01 at any isolation level. Elsewhere, slot rows booked with UPDATE … WHERE booked_by IS NULL and a row-count check, or a lock on the room row. On DynamoDB, conditional puts per slot key in one transaction.

Where to go next

• Transaction isolation levels: every anomaly and every engine’s levels on one page.
• Serializable isolation: write skew in general, two-phase locking and SSI internals.
• Database constraints: UNIQUE, partial unique indexes and EXCLUDE in depth.
• Read committed vs repeatable read: the two common defaults, head to head.
• Optimistic vs pessimistic locking: FOR UPDATE, NOWAIT, version columns and deadlocks.
• Contention and double booking: seats, slots and flash-sale stock at scale.
• Design a hotel booking system and design Ticketmaster: these fixes inside full designs.

References and further reading

1. A Critique of ANSI SQL Isolation Levels (Berenson, Bernstein, Gray, Melton, O’Neil and O’Neil, SIGMOD 1995; MSR-TR-95-51)microsoft.comThe broad P3 and strict A3 phantom definitions, predicate locks, history H3, and the job-hours example of a phantom that snapshot isolation allows.
2. Serializable Snapshot Isolation in PostgreSQL (Ports and Grittner, VLDB 2012)drkp.netHow PostgreSQL’s SIREAD locks cover tuples, pages, relations and index gaps, and why they never block.
3. PostgreSQL documentation: Transaction Isolationpostgresql.orgPhantom read defined, “allowed, but not in PG” at REPEATABLE READ, SSI predicate locking with SIReadLock, relation-level locks for sequential scans, and SQLSTATE 40001.
4. PostgreSQL documentation: Range Types (constraints on ranges)postgresql.orgHalf-open ranges, the && overlap operator, and EXCLUDE USING GIST with btree_gist for per-room non-overlapping reservations.
5. MySQL 8.4 Reference Manual: Phantom Rowsdev.mysql.comHow next-key locking blocks inserts into a scanned range, including the gap after the last record, and how it can “lock the nonexistence” of a row.
6. MySQL 8.4 Reference Manual: InnoDB Lockingdev.mysql.comRecord, gap, next-key and insert intention locks; gap locks being purely inhibitive and compatible; READ COMMITTED disabling gap locks for searches.
7. MySQL 8.4 Reference Manual: Locks Set by Different SQL Statements in InnoDBdev.mysql.comLocks follow the scanned index range, not the WHERE clause; without a suitable index every row of the table is locked.
8. MySQL 8.4 Reference Manual: Locking Readsdev.mysql.comSELECT … FOR SHARE and FOR UPDATE read the latest data and lock what they read, unlike consistent reads.
9. SQL Server documentation: Transaction locking and row versioning guidelearn.microsoft.comKey-range locks under SERIALIZABLE, their modes and compatibility matrix, n + 1 range locks, and the requirement for an index on the predicate column.
10. Hermitage: testing transaction isolation levels (Kleppmann)github.comHand-run tests of predicate anomalies (PMP and G2) per database and level, including MySQL’s read-only-only phantom protection.