MySQL Gap Locks: Protect the Interval, Not Just the Row

An InnoDB lock is not always a lock on a row that exists. A locking range query can also protect gaps in an index, preventing another transaction from inserting into part of that range. This matters when an application appears to be updating different records yet its transactions still block one another.

The useful question is not simply “which row is locked?” It is “which index interval did this statement search, under which isolation level?” Inspect both the access path and the transaction boundary before changing a global isolation setting.

The concrete problem

Use an isolated MySQL 8.4 InnoDB database with two interactive sessions. The table models available appointment slots. A unique point lookup and a range search do not necessarily acquire the same locks. Session A deliberately holds a transaction open so session B can reveal the protected interval.

Figure: Range SELECT → Index interval protected → Concurrent INSERT waits.

A worked example

CREATE TABLE slots (
  id BIGINT PRIMARY KEY,
  starts_at DATETIME NOT NULL,
  KEY by_start (starts_at)
) ENGINE=InnoDB;
INSERT INTO slots VALUES (1, '2026-11-10 10:00:00'),
                         (2, '2026-11-10 12:00:00');
-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM slots
WHERE starts_at >= '2026-11-10 10:00:00'
  AND starts_at < '2026-11-10 12:00:00'
FOR UPDATE;
-- Session B: run while A remains open
INSERT INTO slots VALUES (3, '2026-11-10 11:00:00');
-- Session A, after observing B: ROLLBACK;

Why the behavior matters

Under InnoDB's default REPEATABLE READ semantics, locking scans can use next-key locks: an index-record lock combined with a gap lock. The inserted 11:00 slot falls within the searched interval, so waiting is a plausible and intentional outcome. The actual footprint depends on the chosen index and records encountered, not just the business predicate written in SQL.

Observe the executing plan and performance_schema.data_locks/data_lock_waits rather than inferring every lock from the example. A poor access path can expand the lock footprint. Keep the transaction short, index the predicate and decide whether the application needs this range-protection behavior.

Failure modes and design tradeoffs

READ COMMITTED changes gap-lock behavior for ordinary searches, but it is not a universal “disable locks” option: duplicate-key and foreign-key checks still matter. Changing isolation can expose application assumptions about repeat reads or phantom rows. A queue or booking system needs an explicit invariant, such as a unique reservation key or a correctly modeled capacity update, rather than relying on an accidental scan lock.

A blocked INSERT is not proof of deadlock. A deadlock requires a wait cycle and is resolved differently. Do not kill arbitrary production sessions merely to make this demonstration finish.

What to verify before adoption

Record the session isolation level, chosen index, held locks and blocking relationship. Repeat with a unique primary-key lookup, then with READ COMMITTED in the disposable database. Explain observed differences instead of promising one exact lock set across all schemas. Roll back both sessions and drop the test table after the experiment.

Primary reference

• MySQL documentation. Verify the exact deployed version and driver behavior before using the example in a production runbook.