What is a Phantom Read in Database Transactions?
Learn what a phantom read is, how it differs from non-repeatable reads, and which isolation levels prevent it.
Expected Interview Answer
A phantom read is a concurrency anomaly where a transaction re-runs a range query and finds a different set of rows than the first time, because another transaction inserted or deleted rows matching that range and committed in between.
Unlike a non-repeatable read, which is about an existing row’s values changing, a phantom read is about the row count or membership of a set changing — new rows "appear like phantoms" or previously matching rows vanish. This happens because a row-level lock or snapshot taken on the rows a query already returned cannot protect against entirely new rows being inserted that would also match the same WHERE clause. Preventing phantom reads generally requires range locks, predicate locks, or true Serializable isolation, since Repeatable Read’s per-row locking does not cover rows that do not exist yet.
- Naming the anomaly precisely helps choose the right isolation level
- Understanding phantoms clarifies why range queries need extra protection
- Distinguishes phantom reads from dirty and non-repeatable reads in interviews
- Guides correct use of Serializable or explicit range locking for aggregate logic
AI Mentor Explanation
Imagine querying "how many centuries were scored this series" and getting five, but before you finish your report, another match concludes and a batter scores a sixth century that gets added to the same series list. Re-running your query now returns six, even though you never asked about that specific new innings — a new row simply appeared inside your existing range. That is exactly a phantom read: the set matching your condition grew because of a commit that happened in between your two identical queries.
Step-by-Step Explanation
Step 1
Run an initial range query
A transaction issues a query with a WHERE clause matching a set of rows, like a date range or status filter.
Step 2
Another transaction inserts a matching row
A concurrent transaction inserts (or deletes) a row that would also satisfy the same range condition, then commits.
Step 3
Re-run the identical range query
The original transaction runs the same query again later in its lifetime.
Step 4
Observe the phantom
The result set now includes (or excludes) the newly committed row, even though existing rows already returned did not change values.
What Interviewer Expects
- A precise distinction between phantom reads and non-repeatable reads
- Understanding that phantoms involve new/removed rows, not changed values in existing rows
- Knowledge of which isolation levels prevent phantoms and how (range/predicate locks)
- A concrete range-query example demonstrating the anomaly
Common Mistakes
- Conflating a phantom read with a non-repeatable read
- Assuming Repeatable Read always prevents phantom reads on every database
- Not mentioning that phantoms specifically involve range or set-membership queries
- Failing to name Serializable or range locking as the fix
Best Answer (HR Friendly)
“A phantom read happens when you run the same search twice inside one transaction, like "count all orders over one hundred dollars," and get a different number the second time because someone else added or removed a matching row in between. It is different from a normal changed value — it is about new rows sneaking into or out of your result set, and it is why the strictest isolation levels add extra protection for range queries.”
Code Example
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT COUNT(*) FROM Orders WHERE total > 1000; -- returns 12
-- Meanwhile, another transaction commits a brand-new order
-- with total = 1500, which also matches the WHERE clause
SELECT COUNT(*) FROM Orders WHERE total > 1000; -- may now return 13
-- This new, previously nonexistent row is the "phantom" --
-- SERIALIZABLE isolation (or explicit range locks) is required
-- to prevent this from happening.
COMMIT;Follow-up Questions
- How is a phantom read different from a non-repeatable read?
- Which isolation level is guaranteed to prevent phantom reads under the SQL standard?
- How does MySQL InnoDB’s gap locking prevent phantom reads at Repeatable Read?
- How would you demonstrate a phantom read with two concurrent SQL sessions?
MCQ Practice
1. A phantom read specifically refers to what kind of change between two identical queries in a transaction?
A phantom read is about new rows appearing or existing rows disappearing from a range query’s result set, not values changing within existing rows.
2. Which SQL standard isolation level is guaranteed to prevent phantom reads?
Only Serializable is formally guaranteed by the SQL standard to prevent phantom reads; Repeatable Read technically still permits them.
3. How does a phantom read differ from a non-repeatable read?
Non-repeatable reads change an existing row’s value between reads; phantom reads change which rows match a range query between reads.
Flash Cards
What is a phantom read? — When a re-run range query returns a different set of rows because another transaction inserted or deleted matching rows and committed.
Phantom read vs non-repeatable read? — Phantom reads change row membership in a range; non-repeatable reads change a value in an existing row.
Which isolation level prevents phantom reads per the SQL standard? — Serializable.
How does MySQL InnoDB handle phantoms at Repeatable Read? — It uses gap locks to lock the space between rows, blocking inserts that would create phantoms.