Locking the spaces between rows
Beyond locking existing rows, InnoDB can lock the gaps between index values. A gap lock covers the open interval between two index records and prevents other transactions from inserting into that range. A next key lock combines a record lock on a row with a gap lock on the gap before it.
Why gaps matter
Under REPEATABLE READ, a locking read such as SELECT FOR UPDATE on a range must prevent phantom rows, new rows that would match the same WHERE clause if reread. Gap and next key locks block the inserts that would create those phantoms, giving range queries a stable result.
Behavior to remember
- Next key locking is the default under REPEATABLE READ for range scans.
- READ COMMITTED largely disables gap locks, so phantoms can appear but locking is lighter and inserts are less contended.
- Gap locks on different transactions can coexist over the same gap, since they only block inserts, not each other.
A common surprise
Two sessions taking gap locks on the same range and then both trying to insert can deadlock, because each holds a gap lock the other needs to insert.
Key idea
Gap and next key locks lock the spaces between index records so range reads under REPEATABLE READ block phantom inserts, at the cost of more contention than READ COMMITTED.