Master InnoDB Row Locking: Rules, Examples, and How Locks Are Applied

InnoDB provides three row‑lock types: Record Lock (locks a single row), Gap Lock (locks a left‑open, right‑open interval), and Next‑key Lock (locks a left‑open, right‑closed interval). The engine automatically adds write locks for DML statements ( UPDATE, DELETE, INSERT) and, under the SERIALIZABLE isolation level, read locks for SELECT. Explicit locks can be requested with SELECT ... FOR UPDATE (write lock) or SELECT ... LOCK IN SHARE MODE (read lock).

The two core principles for lock acquisition are:

Only the objects accessed during the search are locked.

The basic lock unit is the Next‑key Lock.

Consider a user table with columns id (primary key, unique index), a (non‑unique index) and b (no index). Sample rows are:

id | a | b
10 | 4 | Alice
15 | 8 | Bob
20 | 16 | Cilly
25 | 32 | Druid
30 | 64 | Erik

Case 1: Unique Index Equality Query

Record Exists

SQL: SELECT * FROM user WHERE id = 25 FOR UPDATE; Because the row with id = 25 exists, the Next‑key Lock degrades to a Record Lock, locking only that row.

Record Does Not Exist

SQL: SELECT * FROM user WHERE id = 22 FOR UPDATE; The engine searches the B+‑tree, finds id = 20 then id = 25. The accessed interval is (20, 25], which remains a Gap Lock because the target row is absent.

Case 2: Unique Index Range Query

SQL:

SELECT * FROM user WHERE id >= 20 AND id < 22 FOR UPDATE;

The left bound finds id = 20 (record lock), while the right bound scans to id = 25 and creates a Gap Lock for the interval (20, 25). The final lock set is a Record Lock on id = 20 plus a Gap Lock (20, 25).

Case 3: Non‑Unique Index Equality Query

Record Exists

SQL: SELECT * FROM user WHERE a = 16 FOR UPDATE; The engine adds a Next‑key Lock (8,16] on index a and, because the value exists, also a Gap Lock (16,32). Both locks protect the range.

Record Does Not Exist

SQL: SELECT * FROM user WHERE a = 18 FOR UPDATE; The searched interval is (16,32], which degrades to a Gap Lock (16,32) since the target row is missing.

Case 4: Non‑Unique Index Range Query

SQL:

SELECT * FROM user WHERE a >= 16 AND a < 18 FOR UPDATE;

The left bound locks (8,16] (Next‑key) and the right bound scans to id = 32</>, producing <code>(16,32]. Neither side degrades to a Record or Gap lock, so the combined lock range is (8,32].

The key difference is that non‑unique index range queries never collapse into a single Record or Gap lock; they retain the full Next‑key intervals.

Understanding these rules helps predict lock behavior during complex queries and avoid unexpected blocking in MySQL workloads.