Phantom Read

Hook: A phantom read is like checking the same shelf twice and finding a new book that was not there a minute ago — interviewers love it because it proves you understand how transactions protect a moving target.

Question: What is a phantom read in SQL transactions?

Answer: A phantom read happens when a transaction runs the same WHERE query twice and gets a different set of rows the second time because another committed transaction inserted, deleted, or changed rows so they now match the filter. It is not about one row changing value; it is about the result set itself changing. This usually shows up at weaker isolation levels such as READ COMMITTED.

Interview-Ready Answer: I would say a phantom read is when I repeat the same predicate query inside one transaction and the returned row set changes because another transaction committed rows that now match that predicate. For example, I might count all open orders, and on the second read I see extra orders appear. To prevent it, I use stronger isolation, especially SERIALIZABLE; and in some databases like PostgreSQL, REPEATABLE READ also gives a stable snapshot for repeated reads.

What it means

A phantom read is a range anomaly. A range anomaly means the problem is not one specific row; it is the set of rows that match a predicate such as status = 'OPEN' or amount > 100. You read the same condition twice inside one transaction, and the second read sees extra rows, missing rows, or both.

How it happens under the hood

1. Transaction A reads a predicate, for example, all open orders.
2. Transaction B inserts, deletes, or updates a row so that it now matches that same predicate, then commits.
3. Transaction A runs the exact same query again.
4. At a weak isolation level, A sees a different row set. That new row set is the phantom.
5. At stronger isolation, the database either gives A a stable snapshot or uses locks for the whole range so the new row cannot sneak in unnoticed.

Think of a WHERE clause as a fence around a field. A phantom is a sheep that slips into the fenced area after your first count.

Compare the classic anomalies

How databases prevent it

There are two common defenses. The first is snapshot isolation, which means a transaction reads a consistent picture of the database taken at the start of the transaction. The second is range locking, such as gap locks or next-key locks, where the database protects not just rows you read, but the space between them so new matching rows cannot appear.

Here is the important interview detail: behavior depends on the engine. PostgreSQL default isolation is READ COMMITTED; MySQL InnoDB default is REPEATABLE READ; SQL Server default is READ COMMITTED. In PostgreSQL, REPEATABLE READ gives a stable snapshot for repeated reads, while SQL Server needs SERIALIZABLE to block phantoms. MySQL InnoDB often uses next-key locks to protect indexed ranges.

Why stronger isolation has a cost

Normal indexed range reads are still about O(log n + k), where k is the number of matched rows. The extra cost comes from coordination: stronger isolation can add lock waits, deadlocks, or retries under contention. In a busy checkout table, one hot range can turn a fast read into a blocked read for tens or hundreds of milliseconds, and sometimes longer if many sessions keep touching the same key range.

So the practical rule is simple: use the weakest isolation that is safe, but move to SERIALIZABLE or explicit locking when business rules depend on the exact set of rows, not just the values inside them.

One subtle gotcha

A row that was updated so it now matches your filter also counts as a phantom. It is not only about brand-new inserts. If your transaction first sees no rows with balance < 0, and another transaction changes a row so it becomes negative, that new match is still a phantom from your point of view.

Real-World Example: Imagine a checkout service for a concert ticket app. One transaction checks how many seats are still available in section A, then reserves a seat if the count is above zero. If two buyers do this at the same time under weak isolation, both can see the same available count before either commit is visible to the other. One of them creates the phantom: a new matching row appears in the seat-reservation range, and the system accidentally oversells the last seat.

What goes wrong: the user sees a success message, then support gets a wave of complaints because two confirmations were sent for one seat. Logs often show odd flips like available_seats=1 on two different requests followed by duplicate reservation inserts. In the worst case, downstream systems such as payment capture or seat assignment disagree, and the team has to refund one order and manually fix inventory.

Why this matters: phantom reads are one of those bugs that look rare in testing but show up under load, exactly when money and trust are on the line.

Follow-up & Tricky Questions:

• How is a phantom read different from a non-repeatable read? A non-repeatable read is the same row changing value between reads. A phantom read is the set of rows matching the predicate changing, usually because rows were inserted, deleted, or updated into the range.
• Which isolation level prevents phantom reads? SERIALIZABLE is the safest general answer. Some engines also make REPEATABLE READ behave like a stable snapshot for repeated reads, but that is database-specific.
• Does MVCC prevent phantoms? MVCC, or multiversion concurrency control, gives each transaction a consistent snapshot, which often prevents phantoms for plain reads. But it does not solve every business-rule conflict; write skew can still happen unless you use the right locks or true serializable isolation.
• Do indexes help with phantom reads? Yes, especially for range protection. Indexed predicates let the database lock or reason about a narrower range; without an index, it may need broader locking or scanning, which can hurt concurrency.
• Can a delete cause a phantom? Yes. If the second read returns fewer matching rows because another transaction deleted one or updated it so it no longer matches, the result set changed, so you still have a phantom-style anomaly.
• Does SELECT ... FOR UPDATE always stop phantoms? No. It locks the rows you actually found, but it does not automatically protect every possible new row that could appear in the predicate range. Some databases need range locks or serializable isolation for that.
• Gotcha: does REPEATABLE READ always prevent phantoms? No. That is true in some engines and not in others. In an interview, always mention the database engine if you bring up isolation guarantees.
• Gotcha: can a phantom happen without an insert? Yes. An update that moves a row into your filter range can create the same effect, because your second query sees a new match.

Common Mistakes:

• Mixing it up with dirty reads. Correction: dirty reads are about uncommitted data; phantom reads are about committed rows changing the result set of a predicate query.
• Saying only inserts can cause it. Correction: deletes and updates that move rows into or out of the range can also create a phantom.
• Assuming every database treats REPEATABLE READ the same way. Correction: always name the engine, because PostgreSQL, MySQL, and SQL Server differ.
• Forgetting the business rule. Correction: you care about phantoms when the exact membership of a set matters, such as inventory, quotas, or uniqueness-by-range checks.

Memory Hook: Same fence, new sheep. The WHERE clause is the fence; a phantom is a row that sneaks into the fenced area between two looks.

Cheat Sheet:

• Phantom read = same predicate, different row set.
• Usually caused by inserts, deletes, or updates that change which rows match.
• Dirty read = uncommitted data; non-repeatable read = same row changed; phantom read = matching set changed.
• SERIALIZABLE is the universal prevention answer.
• DB behavior differs: PostgreSQL default READ COMMITTED, MySQL InnoDB default REPEATABLE READ, SQL Server default READ COMMITTED.
• Performance trade-off: stronger isolation means more blocking, retries, and possible deadlocks.

Practice Tasks:

• Build a small bookings table and run the same count query twice while inserting a matching row in another session.
• Repeat the experiment at READ COMMITTED and then at SERIALIZABLE; note the different behavior.
• Add an index on the filtered column and think about why range protection becomes more precise.