Why Some MySQL Inserts Are Blocked: Decoding Next‑Key Lock Ranges

MySQL's NEXT-KEY LOCK is used to prevent phantom reads in REPEATABLE READ (RR) isolation, effectively locking the current row plus an adjacent interval. The article explores how large that interval actually is and whether it corresponds to a simple closed range on a secondary index column.

Test Setup

A table test is created with a primary key a and a secondary index on column b:

CREATE TABLE `test` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Five rows are inserted and committed:

INSERT INTO test VALUES(10,2);
INSERT INTO test VALUES(15,2);
INSERT INTO test VALUES(20,4);
INSERT INTO test VALUES(25,6);
INSERT INTO test VALUES(99,8);
COMMIT;

Concurrent Sessions

Session A locks the row where b=4:

BEGIN;
SELECT * FROM test WHERE b=4 FOR UPDATE;

Session B locks rows where b=2 and b=6:

SELECT * FROM test WHERE b=2 FOR UPDATE;
SELECT * FROM test WHERE b=6 FOR UPDATE;

Both sessions succeed, indicating that the columns are not locked with exclusive (X) locks, suggesting a lock range rather than a single row.

Insert Experiments

Attempts to insert values that fall within the inferred lock range are blocked (the queries are killed):

INSERT INTO test VALUES(16,2);  -- blocked
INSERT INTO test VALUES(16,6);  -- blocked

Inserts that lie outside the range succeed:

INSERT INTO test VALUES(14,2);  -- succeeds
INSERT INTO test VALUES(26,6);  -- succeeds

Lock Range Analysis

Examining the original records and the FOR UPDATE on b=4, InnoDB defines the lock range as follows:

For b=2, the range starts at the primary key value 15 and extends to positive infinity.

For b=4, the range covers all rows with b=4.

For b=6, the range starts at negative infinity and ends at primary key value 25.

Visually, the B+‑tree leaf nodes are ordered by the secondary index b and, for equal b, by the primary key a. This ordering creates a continuous interval that can be illustrated as:

From this diagram, inserting (16,2) falls between the existing rows (2,15) and (4,20), so it is blocked; similarly, (16,6) falls between (4,20) and (6,25) and is blocked. Inserts like (14,2) and (26,6) lie outside the locked intervals and therefore succeed.

Boundary Cases

When the lock is on a boundary record (e.g., b=2 FOR UPDATE or b=8 FOR UPDATE), the range expands: b=2 FOR UPDATE locks from negative infinity up to the b=4 range (negative infinity to primary key 20). b=8 FOR UPDATE locks from the b=6 range (primary key 25 to positive infinity) up to positive infinity.

These cases are illustrated with additional diagrams:

Conclusion

The next‑key lock on a secondary index does not correspond to a simple closed interval on that column alone; it also depends on the primary key values. The effective lock range can be described as: b=2: primary key from 15 to +∞ b=4: all rows with

b=4

b=6

: primary key from -∞ to 25

Insertions that fall inside these ranges are blocked, while those outside are allowed. This behavior minimizes lock scope to improve concurrency, and the gap lock on a secondary index is determined by both the secondary column and the primary key ordering.