Phantom Read

What It Is

A phantom read occurs when a transaction executes the same query with a `WHERE` condition twice, and the set of rows returned differs between the two executions — not because any existing row's value changed, but because another transaction inserted a new row (or deleted an existing one) that matches the condition, in between the two query executions. The "phantom" is the extra row that mysteriously appears (or the row that mysteriously vanishes) the second time.

Concrete Example — Inventory / Accounts Table

Suppose an accounts table currently has:

T1 runs a report query twice within one transaction, looking for high-value accounts:

Time  Operation                                                Result
1     T1: SELECT * FROM accounts WHERE balance > 1000;          {B: 2000}   (first execution)
2     T2: INSERT INTO accounts VALUES ('C', 'Carol', 1500);
3     T2: COMMIT;
4     T1: SELECT * FROM accounts WHERE balance > 1000;          {B: 2000, C: 1500}  (second execution)

T1's first query (step 1) returns only Bob's row. T2 then inserts and commits a brand-new row for Carol with balance 1500, which also satisfies balance > 1000. T1's second, identical query (step 4) now returns an extra row — Carol's — that simply did not exist the first time. Bob's row itself never changed at all; the "phantom" is Carol's entirely new row suddenly matching the same condition.

Distinguishing Phantom Read from Non-Repeatable Read — the Most Commonly Confused Pair

This distinction is a favorite interview/exam question, so state it precisely:

• Non-repeatable read (23.12): the same existing row is read twice, and its value changed (due to another transaction's committed UPDATE) — the row identity is the same both times, only its data differs.
• Phantom read: the same query/condition is run twice, and the set of rows matching it changed — because a new row was inserted (or an old one deleted) by another transaction, not because any existing row's value was updated. Bob's row above is completely untouched; it's Carol's newly-inserted row that's the phantom.

A useful mental shortcut: non-repeatable read = "a value I already had changed under me"; phantom read = "new data appeared (or vanished) that wasn't there before, matching my filter."

Why It's Subtle to Prevent

Locking every row a query returns (which is how REPEATABLE READ prevents non-repeatable reads) does nothing to stop phantom reads — a new row didn't exist yet at the time of the first query, so there was no row to lock! Preventing phantoms requires locking the range/condition itself (a "range lock" or "predicate lock" on balance > 1000), so that no new row can be inserted into that range until T1 finishes. This extra mechanism is exactly why phantom-read prevention is associated with the strongest isolation level, SERIALIZABLE, in the isolation-level hierarchy covered next in Chapter 25.

Edge Cases

• A phantom can also occur via deletion: if T1's first query returns 3 rows and T2 deletes one of them (matching T1's condition) and commits, T1's second identical query now returns only 2 rows — a "reverse phantom," equally covered by the same definition (the set of matching rows changed due to another transaction's committed insert/delete).
• Phantom read requires the two queries to use the same condition — if T1 runs two genuinely different queries, differing results are expected and not an anomaly at all.
• Some textbooks treat phantom read as a special case of "read skew" affecting aggregate queries (e.g., COUNT(*) WHERE balance > 1000 returning 1 then 2) — same root cause, just observed through an aggregate rather than a row list.

Key Takeaways / Interview Angle

• Q: In one sentence, what's the difference between non-repeatable read and phantom read? Non-repeatable read = an existing row's value changes between two reads; phantom read = the set of rows matching a condition changes (via insert/delete) between two identical queries.
• Q: Why can't ordinary row-level locking prevent phantom reads? Because the phantom row doesn't exist at the time of the first query, so there is nothing to lock yet — preventing phantoms requires locking the query's predicate/range itself, not just the rows already returned.
• Q: How does this set up Chapter 25? Isolation levels are formally defined by exactly which of these three anomalies (dirty read, non-repeatable read, phantom read) they permit vs. prevent — phantom read is the last and hardest anomaly, eliminated only at the strictest (SERIALIZABLE) level.