Under REPEATABLE READ, what is a phantom read, and how does InnoDB next-key locking relate?
PICTURE THIS: DATABASE INDEX
Simple meaning
A phantom is a new row that matches your previous range query when you re-query in the same transaction.
WHY — SQL instead of guessing?
Why interviewers care about SQL:
who only read docs from people who shipped.
and tied to Backend work.
Name the idea, why it exists, then one short example.
End with when you use it and one common pitfall.
STEPS — What happens step by step?
Before you speak the answer, walk the interviewer through these steps:
- 1A phantom is a
new row that matches your previous range query when you re-query in the same transaction.
- 2InnoDB uses gap or
next-key locks to block inserts into that range at REPEATABLE READ.
- 3PostgreSQL's snapshot isolation avoids
seeing phantoms in a single statement snapshot but write skew is a different anomaly you should still name.
- 4Give an example
One tiny concrete case you can say aloud.
- 5Common mistake
What juniors usually get wrong.
- 6Close
When you pick this over the alternative.
EXAMPLE — See it in action
Here's a short line you can speak, broken into clear beats:
Note: Adapt this scaffold to your own project — keep it under 60–90 seconds.
Key takeaway
A phantom is a new row that matches your previous range query when you re-query in the same transaction. InnoDB uses gap or next-key locks to block inserts into that range at REPEATABLE READ.