Gap Locking
In REPEATABLE READ, InnoDB uses gap locks to prevent phantom reads — they lock the gap between existing index values, not just the rows.
MySQL
Advanced
5 min read
Gap Locks and Next-Key Locks
Example
-- Gap lock example
-- Table: orders with id 1, 5, 10
START TRANSACTION;
SELECT * FROM orders WHERE id BETWEEN 3 AND 7 FOR UPDATE;
-- Locks the gap between 1 and 5, rows 5, and gap between 5 and 10
-- Another session cannot INSERT id=4 or id=6 until first commits
-- To reduce gap locking: use READ COMMITTED isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Pro Tip
Gap locks prevent phantom rows — if you see unexpected lock waits, check for gap locks in SHOW ENGINE INNODB STATUS.