System Design · Consistency & Replication · 2026-06-29

Phantom Reads & Predicate Locks: Why Row Locks Aren't Enough for Serializable Transactions

A blank line, then:

Core concept

When a transaction reads a set of rows matching some condition — say, "all bookings between noon and 1 pm" — locking the individual rows you found doesn't protect you from a concurrent transaction inserting a new row that also matches that condition. The second read of the same query can return a different, larger set: a "phantom" row appeared. This is the phantom read anomaly, and it sits one level above dirty reads and non-repeatable reads in the classic isolation hierarchy. To block it you need a predicate lock (a lock on the condition itself, not on individual rows), which says "no one may insert, update, or delete any row that would satisfy this query's WHERE clause while I hold this lock."

flowchart TD
    A[Transaction T1: read bookings noon-1pm] --> B[Finds rows R1 R2]
    C[Transaction T2: insert new booking 12:30pm] --> D[Commits new row R3]
    B --> E[T1 re-reads same query]
    D --> E
    E --> F[Now sees R1 R2 R3 - phantom!]

Concrete real-world example

Imagine a hotel booking system. Transaction T1 checks whether a room is free from noon to 1 pm, finds no conflicts, and is about to insert a reservation. Meanwhile T2 does the same check, also finds no conflicts, and inserts first. When T1 commits its own insert, both reservations now overlap — a double-booking. Neither transaction touched a row the other had locked, so plain row locks let both through. A predicate lock on the range "noon to 1 pm for room 101" would have forced T2 to wait until T1 finished, serializing them correctly.

One trade-off / gotcha

True predicate locks are expensive to implement efficiently because the database must check every new insert or update against every active predicate lock held by any transaction. In practice, most databases use index-range locks (locks on gaps between existing index entries, also called gap locks) as an approximation. Gap locks are cheaper but coarser: they lock a range of the index rather than an arbitrary condition, so they can block transactions that wouldn't actually conflict. MySQL InnoDB (a popular transactional database engine) does exactly this at its REPEATABLE READ isolation level, which is why simply upgrading to SERIALIZABLE in InnoDB tightens the gaps further but still relies on this same mechanism internally.

An interview-style question to ponder

A candidate says: "I'll prevent double-bookings by first doing a SELECT to check for conflicts, and if none are found, I'll immediately do an INSERT — all inside a single transaction at the REPEATABLE READ isolation level." Why does this fail, and what is the minimum change needed to fix it?

Stuck? Show a hint

Think about what REPEATABLE READ actually guarantees versus what it deliberately doesn't cover — and ask whether the protection needed here is about rows that exist or rows that don't exist yet.

Show answer

REPEATABLE READ prevents a re-read of an existing row from changing, but it does not prevent a phantom: a new row inserted by a concurrent transaction that satisfies the same WHERE condition.

The SELECT sees a snapshot of existing rows and finds no overlap — correct so far. But REPEATABLE READ doesn't lock the gap in the schedule where a new booking could land. A concurrent transaction can insert a conflicting row and commit before this transaction's INSERT runs.
The two transactions both pass the "no conflict" check independently, then both insert — classic check-then-act race condition, also called a write skew (two transactions each read a consistent but incompatible state and both write based on it).
The minimum fix is to escalate to SERIALIZABLE isolation level, which causes the database to apply predicate/gap locks automatically on the range scanned by the SELECT, blocking any concurrent insert that would land in that range until the transaction commits.
Alternatively, at REPEATABLE READ you can use a SELECT ... FOR UPDATE (a locking read that explicitly acquires row-level write locks) on a canonical "lock row" for that room and time slot — but this requires a pre-existing row or a sentinel row to lock, which is awkward; SERIALIZABLE is cleaner.
But why not just use SELECT ... FOR UPDATE on the conflicting rows found? Because if no rows are found, there is nothing to lock — the phantom inserts into the empty gap, which is exactly the scenario that needs predicate/gap locking, not row locking.
Watch out: many ORMs (libraries that generate SQL automatically) default to REPEATABLE READ, so developers often assume they are safe when they are not.