Phantom Reads Problem
What you'll learn: You'll understand how range queries can return inconsistent result sets within the same transaction when other transactions insert or delete rows.
The Core Problem
A phantom read occurs when you execute the same range query twice in a transaction, but get different result sets because another transaction inserted or deleted rows that match your query conditions between your two reads.
Unlike nonrepeatable reads (where existing rows change values), phantom reads involve rows that appear or disappear entirely—like ghosts materializing or vanishing.
How It Happens
Imagine you're running a report on all orders over $1,000:
Your transaction:
- First query:
SELECT COUNT(*) FROM orders WHERE amount > 1000→ returns 10 rows - (Meanwhile, another transaction inserts a new $1,500 order and commits)
- Second query:
SELECT COUNT(*) FROM orders WHERE amount > 1000→ returns 11 rows
You never modified any data, yet the result changed. The new row is a "phantom"—it wasn't there before but now appears in your range.
The Restaurant Analogy
Think of counting empty tables in a restaurant. You count 5 empty tables, then walk to get your notepad. When you return and count again, there are now 4 empty tables—someone sat down while you were away. The occupied table is a "phantom change" to your original count.
Prevention
Phantom reads are allowed in the Repeatable Read isolation level because it only locks rows you've actually read, not the entire range. To prevent phantoms, you need the Serializable isolation level, which uses range locks or other mechanisms to prevent insertions/deletions in your query's range.
Key Takeaway: Phantom reads occur when concurrent inserts or deletes change the result set of range queries within your transaction; Repeatable Read doesn't prevent them, but Serializable does.