Data consistency
Phantom read: interview questions and how to answer them
A phantom read occurs when a repeated range query sees rows appear or disappear because another transaction inserted, deleted, or changed rows matching the predicate.
Written and reviewed by Sahil Srivastav
What it actually is
A transaction first asks for all pending jobs and later repeats the same predicate. A concurrent insert creates a new matching row, so the second result contains a phantom. The issue concerns a set predicate, not a single row value.
Preventing phantoms requires predicate or range protection, a stable serialisable snapshot, or an operation that claims rows atomically. Locking only the rows returned by the first query may not protect a row that did not yet exist.
Why it matters in production
Phantoms break checks such as “there are fewer than N active reservations” and make reports internally surprising. They also explain why a row lock is insufficient for every business rule.
The right fix is often a constraint or atomic insert rather than a long transaction that scans and then inserts.
How it works
Predicate scope
The protected object is the set described by the WHERE clause, including possible future rows.
Range locks
Some engines protect index gaps or predicate ranges; the exact behaviour depends on index and isolation level.
Snapshot
A repeatable snapshot can keep a reader’s view stable, but it does not necessarily make a later write safe.
Schema constraint
An exclusion, unique, or partial constraint can encode the invariant directly and handle concurrent inserts atomically.
Detailed boundary
range predicates and snapshots
Operational consequence
a row appearing between repeated queries
Implementing it
Prefer a database constraint when the rule can be expressed declaratively.
Index the predicate used by a range lock; otherwise the lock footprint may become unexpectedly broad.
Use serialisable retries for cross-row rules and keep the transaction short.
Use a two-sided test for this boundary: drive the normal path and the failure path concurrently, then inspect the state that survives the race. For phantom read, the useful assertion is the invariant after recovery, not merely a successful response from one caller.
Document the limit and the signal that tells an operator to change it. A production review of phantom read should name the protected resource, the caller deadline, the expected overload decision, and the evidence that would distinguish a local bug from downstream saturation.
A focused review of phantom read should separate the mechanism from its policy. Reproduce one normal request, one boundary case, and one concurrent failure; record the state transition, the resource consumed, and the signal an operator would see. Then state what the caller is allowed to retry and what must be reconciled manually. This makes phantom read testable in a repository rather than a vocabulary answer.
Interview questions and how to answer them
How is a phantom different from a non-repeatable read?
A non-repeatable read changes an existing row’s value; a phantom changes the membership of a predicate result set.
Can `FOR UPDATE` prevent all phantoms?
Only with engine-specific range or predicate locking semantics. Locking existing rows cannot lock a row that does not exist.
What is the best fix for unique booking?
Use an exclusion or equivalent database constraint if possible, rather than trusting a count check.
Why does an index matter?
The engine can lock the relevant range precisely; without it, it may scan or lock a much larger area.
What evidence would you inspect for phantom read?
Measure the boundary named in the design, compare it with the caller deadline and resource budget, and reproduce the contention or failure with more than one concurrent worker.
What is the tempting fix for this problem?
Changing a timeout, pool, or retry count alone usually moves the queue. First establish the invariant, then make the bounded mechanism and its failure outcome explicit.
Answers that lose the round
- Locking only rows returned by the first scan
- Assuming repeatable read always blocks phantoms across databases
- Counting then inserting without a constraint
- Using an unindexed predicate under high concurrency
- Treating a report snapshot as a write guarantee
- Treating the local mechanism as a complete production guarantee
- Changing the limit without measuring the resource it protects
FAQ
Are phantoms possible in read committed?
Yes. A later statement may see a newly committed matching row.
Are phantoms only inserts?
No. Deletes or updates that change predicate membership also alter the result set.
Can application mutexes prevent them?
Only if every writer shares the same process and lock; a database constraint is safer across instances.