What are gap locks and next-key locks, and why do they surprise developers?

Summary

A gap lock locks the space between index records so other transactions cannot insert into it; a next-key lock is a record lock plus the gap before it. InnoDB uses them under REPEATABLE READ for locking reads and writes, to prevent phantom rows.

Longer Answer

SELECT * FROM orders WHERE customer_id = 7 FOR UPDATE locks the matching index records and the gaps around them, so another transaction inserting an order for customer 7 (or a neighbouring value) waits. If the condition is not on an index, InnoDB may lock every row it scans, which is effectively the whole table.

Surprises: inserts blocked by a range nobody seems to touch; deadlocks between transactions that insert into the same gap; UPDATE ... WHERE non_indexed_col = ? locking far more than expected. Mitigations: index the columns in locking conditions, keep transactions short, and consider READ COMMITTED (gap locking is mostly disabled there, except for foreign-key and duplicate-key checks).

Key Points

• Gap lock: blocks inserts into a range.
• Next-key = record + preceding gap.
• Unindexed conditions lock everything scanned.
• READ COMMITTED reduces gap locks.

Deep dive: Transactions, Locking & Concurrency in Code

What interviewers look for

Understanding why locking depends on indexes. A strong signal for backend roles that debug lock waits.