Understanding InnoDB Row-Level Locks and Locking Rules in MySQL

0. Preface

InnoDB locks are generally divided into exclusive and shared locks at the lock level, and into table‑level and row‑level locks at the resource granularity. When multiple transactions access the same resource, they may block each other or cause deadlocks; therefore MySQL introduces intention locks to coordinate different granularity locks.

1. Definition of Row-Level Locks

Row‑level locks can be further classified as:

Record Lock Gap Lock – locks the gaps between index records Next‑Key Lock – combination of record lock and gap lock, with a left‑open right‑closed range

1. Record Lock

A record lock locks the index record, not the data row itself. If the table lacks a primary key, InnoDB creates a hidden clustered index; if there is a unique non‑null index, it is used; otherwise a hidden primary key is generated.

select C1 from T where C1 = 10 for update;

2. Gap Lock

Gap lock locks the gaps between index entries, i.e., the positions where new values could be inserted.

update T set C2 = 1 where C1 > 10 and C1 < 20;

3. Next‑Key Lock

Next‑Key Lock is the combination of a record lock and a gap lock.

(‑∞,10] (10,11] (11,13] (13,20] (20,+∞]

Thus, statements such as SELECT ... FOR UPDATE , LOCK IN SHARE MODE , UPDATE , and DELETE acquire gap or next‑key locks for the scanned range, which only become effective under the REPEATABLE READ isolation level.

2. Row-Level Lock Rules

After understanding the lock types, the following two principles, two optimizations, and one “bug” govern how locks are actually applied:

Principle 1: The basic locking unit is a Next‑Key Lock with a left‑open right‑closed range. Principle 2: Only the objects accessed during the search are locked. Optimization 1: For equality searches on a unique index, the Next‑Key Lock may degrade to a record lock. Optimization 2: When traversing a unique index to the right and the last value does not satisfy the equality condition, the Next‑Key Lock degrades to a gap lock. Bug: A range query on a unique index may read up to the first value that does not satisfy the condition.

1. Primary‑Key Index Cases

Example index layout:

(1) UPDATE T SET d = d+1 WHERE id = 7; No row with id = 7 exists.

According to Principle 1, the lock range is (5,10]. Principle 2 finds no row to lock, so Optimization 1 does not apply. Optimization 2 treats this as an equality query (id = 7) where the next key (id = 10) does not satisfy the condition, so the Next‑Key Lock degrades to a gap lock (5,10).

Resulting lock: (5,10).

(2) SELECT * FROM T WHERE id >=10 AND id <11 FOR UPDATE;

Principle 1 gives a possible lock range (5,10]; the unique index stops at id = 10. The “bug” causes the engine to read the first non‑matching value (id = 15). Principle 2 locks all accessed objects, so the final lock intervals are (5,10] and (10,15]. Optimization 1 degrades the lock on id = 10 to a record lock.

Resulting lock: (10,15] and id = 10.

(3) SELECT * FROM T WHERE id >10 AND id <=15 FOR UPDATE;

Principle 1 yields (10,15]; the unique index stops at id = 15. The “bug” makes the engine read up to id = 20. Principle 2 locks (10,15] and (15,20]. Optimization 1 does not apply because the equality condition is on id = 15, not a unique‑index equality. Optimization 2 does not trigger because the last value satisfies the condition.

Resulting lock: (10,15] and (15,20].

2. Ordinary Index Cases

Example ordinary index layout:

(1) SELECT id FROM T WHERE c = 5 LOCK IN SHARE MODE;

Principle 1 gives lock interval (0,5]; because c is non‑unique, the engine must also read up to c = 10. Principle 2 therefore locks (5,10] as well. Optimization 1 does not apply (only unique indexes can degrade to record lock). Optimization 2 degrades the lock on the rightmost non‑matching value to a gap lock (5,10). The query uses index covering, so the primary‑key index is not locked.

Resulting lock: (0,5] and (5,10).

(2) SELECT id FROM T WHERE c >=10 AND c < 11 LOCK IN SHARE MODE;

Principle 1 yields possible intervals (5,10] and (10,15]. Principle 2 locks both intervals. Optimization 1 does not apply (non‑unique index). Optimization 2 does not degrade to a gap lock because the last value satisfies c = 10.

Resulting lock: (5,10] and (10,15].

3. Summary

In InnoDB’s REPEATABLE READ level, the basic locking unit is a Next‑Key Lock; any scanned data is locked. Range queries on unique indexes read up to the first non‑matching value. Two performance‑related optimizations exist: (1) equality queries on unique indexes can degrade Next‑Key Locks to record locks, and (2) when traversing rightward and the last value fails the equality test, the lock degrades to a gap lock.