The Phantom Problem
A phantom read happens when a transaction runs the same range query twice and sees new rows the second time because another transaction inserted them. Row locks alone cannot prevent this, since the new rows did not exist to lock.
Gap Locks
To block phantoms at the serializable or repeatable read level, engines lock the gaps between existing index values, not just the rows. A gap lock on the space between two keys stops any other transaction from inserting a new key into that range.
Tradeoffs
- Gap locks prevent phantoms but reduce concurrency because they block inserts into whole ranges.
- They apply on index ranges, so good indexes keep the locked gap small.
- A combined row plus gap lock is often called a next key lock.
Key idea
Gap locks guard the spaces between indexed values so new rows cannot appear inside a queried range, which prevents phantom reads at the cost of lower insert concurrency.