skip to content

Phantom Read

17 roadmaps4 questions

Re-running the same predicate query returns rows that appeared (or vanished) mid-transaction — the conflict is over a range, not a specific row. Interviewers use phantoms to probe whether I understand predicate/range locking and why REPEATABLE READ historically wasn't enough.

part ofRelational database conceptsoverview, primer and where to startread it →
on this pageshow

explore

questions

4

What is a phantom read in a database transaction, and how is it different from a non-repeatable read?

level: juniorimportance: must knowfreq 68%

answer

  1. Set membership changes, not values
  2. Can't lock a row that doesn't exist
  3. INSERT, DELETE, or update across the boundary
  4. REPEATABLE READ allows it, SERIALIZABLE doesn't
  5. Aggregate vs detail mismatch

basics

~20 s

A phantom read is when a transaction re-runs the same search condition and the set of matching rows changes, because another transaction committed inserts or deletes. A non-repeatable read is when a row you already read comes back with different values.

solid answer

~50 s

Both are re-read anomalies; they differ in *what* changes. - **Non-repeatable read**: you read row 42, someone commits an update to it, you read it again and a value differs. The membership of your result set is stable. - **Phantom read**: you evaluate a predicate such as `status = 'PENDING'` and get 10 rows; another transaction commits an INSERT (or DELETE, or an update that moves a row into or out of the predicate); you re-run the same query and get 11. The new row is the phantom. The distinction drives the fix. To stop non-repeatable reads you can hold a lock on, or a snapshot of, the rows you already saw. You cannot lock a row that does not exist yet, so stopping phantoms needs a lock on the *predicate* — a range of key space including the gaps — or one consistent snapshot for the whole transaction. That is why the SQL standard permits phantoms at REPEATABLE READ and excludes them only at SERIALIZABLE.

code

sql · 13 lines
sql
-- Transaction A
BEGIN;
SELECT count(*), sum(total) FROM orders WHERE status = 'PENDING';
--  5 | 5000

--                      -- Transaction B (concurrent)
--                      BEGIN;
--                      INSERT INTO orders(status, total) VALUES ('PENDING', 700);
--                      COMMIT;

SELECT count(*), sum(total) FROM orders WHERE status = 'PENDING';
--  6 | 5700   <- phantom row
COMMIT;

go deeper

for a junior

Give the definition crisply — the row set for the same condition changes because someone committed an insert or delete — and contrast it with a non-repeatable read (same row, different value). One example is enough.

for a middle

Add that DELETEs and boundary-crossing UPDATEs also count, and explain why the mechanism differs: you cannot lock a nonexistent row, so prevention needs range/predicate locking or a consistent snapshot.

for a senior

Tie it to real failure modes — read-check-then-insert, aggregate/detail mismatch, pagination — and name concrete prevention: single-statement reads, snapshot reads, SERIALIZABLE, or a unique/exclusion constraint that enforces the rule in the engine.

for a principal

Frame it as a class of correctness argument: any invariant that depends on 'no other row matches this predicate' needs a mechanism covering rows that do not exist yet. Discuss the concurrency cost of each option and where the invariant should live.

## The definition A **predicate** is the search condition of a query: `WHERE status = 'PENDING'`, `WHERE room_id = 7 AND day = '2026-05-01'`, `WHERE amount > 1000`. A **phantom read** happens when one transaction evaluates a predicate, later evaluates the same predicate again, and the *set of rows that satisfy it* has changed because another transaction committed in between. The rows that appear (or vanish) are the phantoms. Three things can produce a phantom, not just INSERT: - an INSERT of a row that matches the predicate, - a DELETE of a row that matched it, - an UPDATE that moves a row across the predicate boundary (an order whose `amount` goes from 900 to 1100 becomes a phantom for `amount > 1000`). ## A worked example Transaction A is producing an invoice for pending orders: 1. A: `SELECT SUM(total) FROM orders WHERE status = 'PENDING'` → 5,000. 2. B: inserts a new pending order for 700 and commits. 3. A: `SELECT count(*) , SUM(total) FROM orders WHERE status = 'PENDING'` → 6 rows, 5,700. A's own invoice now contains an internally inconsistent total and line-item list. Nothing A read was *stale* in the dirty-read sense — every value B wrote was committed. The problem is that A saw two different worlds within one transaction. ## Why it is a separate category from a non-repeatable read A non-repeatable read is *value instability on a known row identity*. A phantom is *membership instability of a set*. The reason the standard bothers to separate them is entirely about the prevention mechanism: - Value instability is fixable by remembering identities: hold a shared lock on each row you read until commit, or serve all reads from a snapshot taken at transaction start. - Membership instability is not fixable that way, because the offending row has no identity yet at the time you would need to lock it. A lock manager can only lock things that exist. Preventing phantoms therefore requires either **predicate locking** (lock the logical condition), its practical approximation **index-range / next-key / gap locking** (lock a contiguous stretch of index key space, including the empty gaps between existing keys), or a **consistent snapshot** so re-reads never observe later commits at all. ## Where the standard puts it The classic ANSI/ISO SQL isolation table defines levels by which of three phenomena they permit: | Level | Dirty read | Non-repeatable read | Phantom | |---|---|---|---| | READ UNCOMMITTED | yes | yes | yes | | READ COMMITTED | no | yes | yes | | REPEATABLE READ | no | no | yes | | SERIALIZABLE | no | no | no | Note the shape of the table: REPEATABLE READ exists precisely as "row-stable but not set-stable". SERIALIZABLE is the only standard level that forbids phantoms. ## Where it actually bites in production - **Aggregate vs. detail mismatch**: a report that runs `COUNT(*)` and then fetches the rows and finds a different number. - **Read-check-then-write**: "is there already a row for this key/range? no → insert" is unsafe under phantoms because the check and the insert straddle another transaction's commit. This is why uniqueness is enforced by a unique index rather than by a SELECT. - **Pagination and cursors**: rows appearing or disappearing between pages of the same logical scan. - **Batch jobs** that scan a queue predicate twice (claim, then process) and are surprised by a changed working set. ## Practical prevention, in order of preference 1. Read everything you need in **one statement** — a single statement is atomic with respect to other transactions on essentially every engine, so no phantom can slip in mid-predicate. 2. Run the transaction at an isolation level whose reads come from **one snapshot**, so re-reads are stable. 3. If the transaction must *act* on the absence of rows, snapshot stability is not enough — use SERIALIZABLE, explicit range locking, or a database constraint that enforces the rule directly. The general lesson: phantoms are about *sets*, and any correctness argument that depends on "nothing else matches my predicate" needs a mechanism that covers rows that do not exist yet.

  • Can a DELETE cause a phantom read, or only an INSERT?
    A DELETE can too. The anomaly is defined as the result set of a predicate changing between evaluations inside one transaction, so a committed DELETE that removes a matching row is a phantom just as much as an INSERT that adds one. An UPDATE also counts when it moves a row across the predicate boundary — for example changing an order's status so it starts or stops matching `status = 'PENDING'`.
  • Why can't the engine just take a shared lock on every row it returns and be done with it?
    Because the phantom row is not in the result set at lock time — it does not exist yet, or does not yet match. A lock manager can only lock objects that exist, so locking the returned rows leaves the gaps between them unprotected. Preventing phantoms requires locking the predicate itself, approximated in practice by locking ranges of index key space including the empty gaps.

Non-repeatable read is a guest at the party changing clothes between your two headcounts. A phantom read is a new guest walking in. You can watch the guests you already met; you can't watch someone who is not there yet.

saying these in an interview costs you the question

  • Saying a phantom read is just a non-repeatable read on a different row
  • Claiming only INSERTs cause phantoms, never DELETEs or boundary-crossing UPDATEs
  • Confusing it with a dirty read — phantoms come from committed data, not uncommitted
  • Believing READ COMMITTED prevents phantoms because it hides uncommitted data
  • Thinking locking all rows returned by the query is enough

context

resolving links…

open as a page

Your transaction takes a lock on every row a range query returns, yet re-running that same range query still returns extra rows. Explain why row-level locking cannot prevent phantoms, and what locking mechanisms do.

level: middleimportance: must knowfreq 52%

basics

~20 s

Row locks only cover rows that exist; a phantom is a row inserted into the gap between them. Prevention needs predicate locking, approximated in practice by index range locks — gap locks and next-key locks that lock the empty key space too.

open as a page

The SQL standard lists phantom reads as permitted at REPEATABLE READ, yet on several multi-version (MVCC) engines a repeated range query inside a REPEATABLE READ transaction never shows newly committed rows. Explain the discrepancy and what it does and does not guarantee.

level: seniorimportance: should knowfreq 42%

basics

~20 s

The standard's levels were defined by which anomalies a lock-based implementation exhibits. MVCC engines serve a transaction's reads from one snapshot, so re-reads are stable and read phantoms never appear — but that only stabilizes what you see, it does not make writes based on that view safe.

open as a page

A hot table takes thousands of writes per second, and one code path must be able to trust that no other row matches its search condition before it inserts. Compare the ways to eliminate the phantom in that check — SERIALIZABLE with retries, explicit range locking, and pushing the rule into an index constraint — and explain how you would choose.

level: principalimportance: nice to knowfreq 28%

basics

~20 s

Three options: SERIALIZABLE (no blocking, but aborts you must retry, and abort rate grows with contention), range/next-key locking (deterministic but serializes the hot range and invites deadlocks), or a unique/exclusion constraint that makes the index enforce the rule with no extra locking. Prefer the constraint when the rule fits an index.

open as a page