CONCEPT Cited by 1 source

Gap locking¶

Gap locking is an InnoDB-specific lock mechanism that locks the space between indexed rows (rather than the rows themselves), to prevent concurrent transactions from inserting new rows into that space. It is the substrate InnoDB uses to make REPEATABLE READ isolation-level transactions phantom-read-free on locking reads — a MySQL-specific strengthening of the SQL-standard REPEATABLE READ semantics.

What a gap is¶

In an indexed table, a gap is the space between two consecutive index entries. For an integer-keyed table with rows at primary keys 10, 20, 30, the gaps are:

Before 10 (negative infinity → 10).
Between 10 and 20.
Between 20 and 30.
After 30 (30 → positive infinity).

A gap lock makes one or more of these gaps un-insertable for any transaction except the lock holder, for the duration of the holder's transaction.

Canonical worked example¶

From PlanetScale's Brian Morrison II (Source: sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work):

-- Transaction A
START TRANSACTION;
SELECT * FROM products WHERE release_year = 1999 FOR UPDATE;
UPDATE products SET cost = 800 WHERE release_year = 1999;
COMMIT;

-- Transaction B, concurrent
INSERT INTO products (album, artist, release_year, cost)
  VALUES ('The Battle Of Los Angeles', 'Rage Against The Machine', 1999, 1500);

Transaction A's SELECT ... FOR UPDATE under the default REPEATABLE READ isolation triggers InnoDB to lock:

Each existing row where release_year = 1999 (the "record" locks).
The gaps around those rows on the release_year index — specifically, the ranges in the index that would contain a newly inserted row with release_year = 1999.

Transaction B's INSERT tries to place a row at release_ year = 1999, which falls into a gap Transaction A has locked. Transaction B is blocked until Transaction A commits. Verbatim from the post: "MySQL would make that transaction wait until the previous one had completed before proceeding. This prevents the second transaction from inserting a new row that would alter the results of the first transaction."

Why gap locks are needed¶

Without gap locks, a transaction could read a set of rows matching a predicate, take action based on the set's contents, and then find that a concurrent transaction had inserted new matching rows between reads — a phantom read. Plain row locks on the existing rows don't prevent this, because the phantom rows don't yet exist to lock.

Gap locking solves the problem structurally: lock the space where matching rows could be inserted, not just the existing matching rows. An INSERT targeting a locked gap waits for the gap's holder to release.

This is how InnoDB achieves the MySQL-specific strengthening of REPEATABLE READ beyond the SQL-standard version. In the SQL standard (per Berenson et al. 1995), REPEATABLE READ permits phantom reads; only SERIALIZABLE forbids them. InnoDB's gap locking closes the gap at REPEATABLE READ.

Next-key locks (the actual default)¶

The lock InnoDB actually takes on locking reads under REPEATABLE READ is usually a next-key lock: a combination of a record lock (on the indexed row itself) + a gap lock (on the gap immediately preceding that row in the index). This protects both the row and its surroundings.

SHOW ENGINE INNODB STATUS\G output distinguishes:

lock_mode X — next-key lock (record + gap before) — the default.
lock_mode X locks rec but not gap — record-only lock, no gap protection. Set explicitly with unique-index point lookups or when innodb_locks_unsafe_for_binlog was set (deprecated).
lock_mode X locks gap before rec — gap-only lock, no record.

The pedagogical framing in the PlanetScale post treats "gap lock" as a single concept; the internal InnoDB taxonomy has more granularity.

Scope of gap locking¶

Gap locking is only active:

Under REPEATABLE READ and SERIALIZABLE isolation levels. Under READ COMMITTED, gap locks are largely disabled for performance (only used for foreign-key- constraint checking and duplicate-key detection).
On locking reads (SELECT … FOR UPDATE / FOR SHARE), UPDATE, and DELETE. Plain non-locking SELECTs don't take gap locks — they rely on MVCC snapshots to hide concurrent inserts.
When a range scan or a scan without a unique single-row hit is performed. Unique-index point lookups don't need gap locks because there's no range to protect.

Index coverage and lock scope¶

An important InnoDB subtlety named in the PlanetScale post: if a locking read under REPEATABLE READ can be served by an index matching the WHERE predicate, the gap locks cover just the relevant index range. If no usable index exists and InnoDB has to do a table scan, it:

Locks every row it scans (not just matching rows).
Takes gap locks to prevent any inserts that could affect the predicate's result if the query were re-run.

Canonical operational implication: missing indexes explode lock scope under REPEATABLE READ locking reads. This is one of the sharper reasons to index every column appearing in WHERE predicates under locking read workloads — the cost isn't just latency, it's contention.

See concepts/mysql-transaction-isolation-levels and concepts/row-level-lock-contention.

Trade-offs¶

Benefits:

Phantom-read prevention without falling back to SERIALIZABLE's implicit-shared-lock-on-every-SELECT cost.
Developer doesn't have to think about phantom-read anomalies when using FOR UPDATE with a predicate — InnoDB does the right thing automatically.

Costs:

Gap locks reduce concurrency on INSERT-heavy workloads — any insert into a locked gap blocks.
INSERT-INSERT deadlocks become possible when two transactions each try to insert into gaps the other has locked.
Debugging SHOW ENGINE INNODB STATUS output is harder: a lock on "a gap" is less tangible than a lock on "row id 123".
Applications porting from READ COMMITTED to REPEATABLE READ may unexpectedly find their insert rates drop from the new gap-lock serialisation.

Seen in¶

sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work — Brian Morrison II's PlanetScale pedagogical post canonicalises gap locking as the InnoDB-specific mechanism that prevents phantom reads under REPEATABLE READ on locking reads. Worked example: SELECT * FROM products WHERE release_year = 1999 FOR UPDATE blocks a concurrent INSERT INTO products (... release_year = 1999 ...) for the duration of the reader's transaction. Canonical framing verbatim: "Gap locking is a special type of lock that is used by MySQL to prevent phantom reads. Gap locking will use criteria in where clauses to lock the space around the read data, preventing rows from being inserted that may alter the query if its run a second time." Also canonicalises the critical index-scope corollary: without a usable index, REPEATABLE READ locking reads lock every scanned row plus gap-lock to prevent phantom-affecting inserts.