Gap locks: locking rows that don't exist

In MySQL 8.4 InnoDB, a gap lock prevents insertion into an index gap. A next-key lock combines a record lock with the preceding gap. The locking manual documents these mechanisms. They are not locks on an abstract SQL predicate independent of the access path.

An INSERT blocked by a SELECT

The scenario scans bookings.slot, its PRIMARY index, with BETWEEN 10 AND 20 FOR UPDATE. At REPEATABLE READ, insertion of 15 waits and reports X,GAP,INSERT_INTENTION. At READ COMMITTED, the later insertion of 17 completes while A's locking read remains open. These are the asserted observations, not a measurement of every interval locked.

A

B

SELECT 10–20 FOR UPDATE

INSERT slot 15→ ⏳ waits

waiting lock is X,GAP,INSERT_INTENTION

COMMIT — gap opens

⏵ INSERT slot 15→ completes

same read, READ COMMITTED

INSERT slot 17 — no wait

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 OK

Same 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.11 · Run it yourself · Scenario source

Index interval mechanism

Relevant-interval teaching model for the asserted MySQL 8.4.11 InnoDB schedule above. The shaded open interval contains no record at 15. The arrow is B's insert request, not a serialization dependency or a second transaction timeline. This view does not enumerate A's complete lock footprint.

Text equivalent: bookings.slot is the PRIMARY index, with initial keys 10, 20, and 30. A's explicit REPEATABLE READ transaction scans slot BETWEEN 10 AND 20 FOR UPDATE. B's insertion at 15 falls in the open interval (10, 20) and waits for A. The Scenario asserts the pending insertion and its X,GAP,INSERT_INTENTION mode, then resolves it after A commits. It does not assert each held lock's boundary. The MySQL 8.4 manual states: “A gap lock is a lock on a gap between index records”. The picture explains the relevant gap using that Documented contract and the known keys, rather than presenting a measured lock inventory.

After B commits 15, the later READ COMMITTED scan returns 10, 15, and 20. Insertion of 17, between 15 and 20, completes before A commits. This second observation has different initial keys and isolation; it is not an assertion that every READ COMMITTED insert avoids waiting. Replay the source with bun lesson mysql/03-locking/gap-locks; the generated Transcript and timeline above remain the execution evidence.

What this means in practice

The isolation manual distinguishes an existing-row unique lookup from a range scan: the former needs only a record lock; the latter can lock scanned index gaps and records. The index and execution plan determine which intervals are scanned, potentially beyond the returned rows. Do not infer identical lock coverage for another query or index from this transcript.

Gap locks can coexist. They inhibit inserts, so applications can still form a deadlock when insert requests conflict with locks held by other transactions. This lesson does not demonstrate that second schedule or rank gap locks as a cause of deadlocks.

At READ COMMITTED, gap locking for searches and index scans is disabled, with exceptions for foreign-key and duplicate-key checking. Changing isolation changes read semantics too; check the READ COMMITTED lesson against the business rule before using it as a repair. Use lock monitoring to identify the actual wait. Next: lock queues.

Further reading

• MySQL docs: Locks Set by Different SQL Statements

The reconstructed support assessment records the exact manual support and execution limits for this lesson.