Understanding InnoDB Locking Mechanisms: Record Lock, Gap Lock, and Next-Key Lock

In MySQL databases, various lock mechanisms are introduced to solve concurrency problems, and many of these locks are added automatically during database operations, which often leads developers to overlook their underlying principles.

A common interview question is: "Do you know what MySQL InnoDB locks actually lock?" Answering this well requires understanding isolation levels, indexes, and lock types.

Record Lock (record lock) is applied to index records. For example: SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; This statement locks the row where c1 = 10, preventing other transactions from inserting, updating, or deleting rows with that value. Even if a table has no explicit index, InnoDB creates a hidden clustered index and uses it for record locking.

Gap Lock (gap lock) locks the gaps between index records, or the gap before the first or after the last record. The “gap” refers to positions where new index values could be inserted. Gap locks are only effective under the Repeatable Read isolation level.

Under Repeatable Read , the following locking behavior occurs:

For unique indexes with a unique search condition, InnoDB locks only the found index record (no gap lock).

For other search conditions, InnoDB locks the scanned index range using gap lock or next‑key lock to block other transactions from inserting into the covered gap.

Next-Key Lock combines a record lock on an index record with a gap lock on the preceding gap. It is the basic locking unit in InnoDB’s RR level and solves phantom reads.

Example of next‑key lock intervals (left‑open, right‑closed):

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, ∞ )

Because next‑key locks also lock the gaps, they prevent other transactions from inserting new rows that would satisfy a range condition, thereby eliminating phantom reads.

MySQL’s RR isolation level differs from the standard: InnoDB uses next‑key locks to prevent phantom reads, whereas the standard RR would still suffer from them.

Locking Principles :

Principle 1: The basic locking unit is the next‑key lock (a left‑open, right‑closed interval).

Principle 2: Only objects accessed during the search are locked.

Optimization 1: For equality queries on a unique index, the next‑key lock degrades to a row lock.

Optimization 2: For equality queries that traverse rightward and the last value does not satisfy the condition, the next‑key lock degrades to a gap lock.

Bug: Range queries on a unique index may lock up to the first value that does not satisfy the condition.

Illustrative scenarios:

1. UPDATE t SET d = d + 1 WHERE id = 7; – No row with id = 7 exists, so session A locks the range (5,10] (principle 1) which then degrades to a gap lock (5,10) (optimization 2).

2. SELECT * FROM t WHERE id >= 10 AND id < 11 FOR UPDATE; – The unique index on id causes a row lock on id = 10 (optimization 1).

3. SELECT * FROM t WHERE id > 10 AND id <= 15 FOR UPDATE; – InnoDB scans forward to

id = 20</>, locking (10,15] as a next‑key lock in addition to the row lock on <code>id = 10

.

4. SELECT id FROM t WHERE c = 5 LOCK IN SHARE MODE; – On a non‑unique index c, InnoDB locks (0,5] as a next‑key lock, then (5,10] as another next‑key lock, with the final gap lock (5,10) after optimization 2.

5. SELECT * FROM t WHERE c >= 10 AND c < 11 FOR UPDATE; – Two next‑key locks are acquired: (5,10] and (10,15] on the non‑unique index c.

Summary

InnoDB provides three lock types: Record Lock (locks index records), Gap Lock (locks gaps between index records), and Next‑Key Lock (combines both). The RR isolation level uses next‑key locks as the basic unit, ensuring that any scanned data is locked, which prevents phantom reads but can reduce concurrency and increase the risk of deadlocks.

Two key optimizations improve performance:

Equality queries on a unique index downgrade next‑key locks to row locks.

Equality queries that traverse rightward and stop at a non‑matching value downgrade next‑key locks to gap locks.