Gap locks: locking rows that don't exist
PostgreSQL locks rows. InnoDB at REPEATABLE READ also locks the spaces between rows — gap locks — and the combination of a row lock plus the gap before it, a next-key lock. It's how InnoDB keeps phantoms out of locking reads without PostgreSQL-style predicate tracking: if nobody can insert into the range you scanned, nobody can create a phantom in it.
The price: INSERTs into a scanned range wait for transactions that never touched any existing row they conflict with.
An INSERT blocked by a SELECT
A runs the classic "hold the range while I decide" query. Slots 10 and 20 exist; 11–19 is a gap — nothing there to lock. Or is there?
A> BEGIN;
Query OK
A> SELECT slot FROM bookings WHERE slot BETWEEN 10 AND 20 FOR UPDATE;
slot
------
10
20
(2 rows)B books slot 15. No such row exists — yet the INSERT blocks.
B> BEGIN;
Query OK
B> INSERT INTO bookings VALUES (15, 'mallory');
⏳ B is waiting for a lock…
A> SELECT lock_mode FROM performance_schema.data_locks WHERE object_name = 'bookings' AND lock_status = 'WAITING'; -- B waits for an insert-intention lock on the gap A's range scan locked
lock_mode
------------------------
X,GAP,INSERT_INTENTION
(1 row)
A> COMMIT; -- the gap opens
Query OK
⏵ B resumes:
Query OK, 1 row affected
B> COMMIT;
Query OKSame story at READ COMMITTED: the locking read locks only the three rows it found — the gaps stay free.
A> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT slot FROM bookings WHERE slot BETWEEN 10 AND 20 FOR UPDATE;
slot
------
10
15
20
(3 rows)
B> INSERT INTO bookings VALUES (17, 'trent'); -- straight through — no gap lock to wait on
Query OK, 1 row affected
A> COMMIT;
Query OK
A> SELECT slot FROM bookings ORDER BY slot;
slot
------
10
15
17
20
30
(5 rows)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
What this means in practice
At REPEATABLE READ a locking read on a range is a reservation on the whole range, not only the rows it returns. WHERE slot BETWEEN 10 AND 20 FOR UPDATE locks the rows it finds and every gap between them, plus the gap running up to the next key above the range, so nothing new can appear in that window until you commit.
That permissiveness among readers is exactly why gap locks are a top deadlock source. Gap locks don't conflict with each other — only with inserts — so two transactions can gap-lock the same range at the same time, and then both try to INSERT into it. Each now waits for the other's gap lock, and that's a deadlock, errno 1213.
READ COMMITTED switches most of this off: gap locking there is used only for foreign-key and duplicate-key checks (the manual). That's the usual first fix when gap-lock waits or deadlocks dominate, paid for with everything READ COMMITTED allows. When an INSERT is stuck with no visible row conflict, the tell is in performance_schema.data_locks: a waiting lock tagged X,GAP,INSERT_INTENTION, the insert-intention lock (monitoring locks).
Gap locking is why InnoDB's REPEATABLE READ is stronger than the SQL standard asks for, and why it deadlocks more than PostgreSQL — those two facts are the same fact. When the waits pile up instead of deadlocking, the next thing to understand is how the queue itself behaves.