Understanding MySQL Gap Locks and Next‑Key Locks with Practical Examples

In InnoDB, exclusive locks include row (record) locks, gap locks, and next‑key locks, which together prevent other transactions from inserting, updating, or deleting data within specific intervals.

The article first clarifies that a row lock simply locks a specific record, while a gap lock protects the empty space between records, illustrated with a real‑life queue analogy.

It then shows how MySQL determines gap‑lock intervals based on the indexed column number , dividing the key space into ranges such as (‑∞,2), (2,4), (4,5), (5,5), (5,11), (11,+∞). Any insert that would fall into a locked gap is blocked.

Two conditions must be met for InnoDB to automatically use gap locks: the transaction must run under the REPEATABLE READ isolation level, and the search condition must use an index.

Example 1 demonstrates a SELECT … FOR UPDATE on number=4 that locks the gaps (2,4) and (4,5). Subsequent INSERT statements targeting values within those gaps block, while inserts outside the locked ranges succeed.

session 1:
start transaction;
select * from news where number=4 for update;

session 2:
start transaction;
insert into news value(2,4);   # blocked
insert into news value(2,2);   # blocked
insert into news value(4,4);   # blocked
insert into news value(4,5);   # blocked
insert into news value(7,5);   # succeeds
insert into news value(9,5);   # succeeds
insert into news value(11,5);  # succeeds

The lock range for this case is (2,4) and (4,5), preventing inserts of records whose number would fall between the existing keys.

Example 2 uses a SELECT … FOR UPDATE on number=13 . Even though no record with number=13 exists, InnoDB still locks the surrounding gap (11,+∞), causing inserts after the last matching record to block while updates that place a row before the gap succeed.

session 1:
start transaction;
select * from news where number=13 for update;

session 2:
start transaction;
insert into news value(11,5);   # succeeds
insert into news value(12,11);  # succeeds
insert into news value(14,11);  # blocked
update news set id=14 where number=11; # blocked
update news set id=11 where number=11; # succeeds

Example 3 selects rows with number=5 for update, locking gaps (4,5) and (5,11). Inserts that would fall inside these gaps block, while inserts beyond the locked interval succeed.

session 1:
start transaction;
select * from news where number=5 for update;

session 2:
start transaction;
insert into news value(4,4);   # blocked
insert into news value(4,5);   # blocked
insert into news value(5,5);   # blocked
insert into news value(7,11); # blocked
insert into news value(9,12);  # succeeds

Example 4 runs a SELECT … FOR UPDATE with the condition number>4 , resulting in a gap lock covering (4,+∞). Some updates succeed because they affect rows outside the locked gap, while others block.

session 1:
start transaction;
select * from news where number>4 for update;

session 2:
start transaction;
update news set id=2 where number=4;   # succeeds
update news set id=4 where number=4;   # blocked
update news set id=5 where number=5;   # blocked
insert into news value(2,3);            # succeeds
insert into news value(null,13);       # blocked

The article concludes by defining the next‑key lock as the combination of a record lock and its surrounding gap lock; InnoDB uses next‑key locks by default to protect both the row and the adjacent gaps.