Unlocking MySQL Row-Level Locks: How InnoDB Handles Gaps, Records, and Next-Key Locks

Introduction

Hello, I am Su San. Last Friday I published an article titled "How MySQL Locks?" and later added a detailed explanation of secondary‑index gap locks after a reader requested it. The updated article now serves as a complete tutorial on MySQL row‑level locking.

Which SQL statements acquire row‑level locks?

InnoDB supports row‑level locks; MyISAM does not. Ordinary SELECT statements use MVCC snapshot reads and do not lock rows. To lock rows during a read you must use one of the two lock‑read statements inside a transaction:

SELECT ... LOCK IN SHARE MODE;
SELECT ... FOR UPDATE;

Both statements must be executed after BEGIN (or START TRANSACTION) because the locks are released when the transaction commits. In addition, UPDATE and DELETE statements always acquire exclusive (X) row‑level locks.

The two lock modes are:

Shared lock (S): allows concurrent reads but blocks writes.

Exclusive lock (X): blocks both reads and writes.

Types of row‑level locks

Depending on the isolation level, InnoDB can apply three kinds of row‑level locks:

Record Lock : locks a single index record (S or X).

Gap Lock : locks a range between index records without locking the records themselves; used to prevent phantom inserts.

Next‑Key Lock : a combination of Record Lock and Gap Lock that locks the record and the preceding gap (open‑closed interval).

Record Lock

A Record Lock protects a specific row. If a transaction holds an S‑type lock, other transactions may also acquire S‑type locks on the same row, but no X‑type lock is allowed. If a transaction holds an X‑type lock, no other S or X lock can be granted on that row.

BEGIN;
SELECT * FROM user WHERE id = 1 FOR UPDATE; -- acquires X record lock on id=1
-- other transactions trying to UPDATE or DELETE id=1 will block
COMMIT; -- releases the lock

Gap Lock

A Gap Lock exists only under the REPEATABLE READ isolation level and prevents other transactions from inserting new rows into the locked gap, thereby avoiding phantom reads. For example, a gap lock on the interval (3,5) blocks any insert of a row with id = 4.

Next‑Key Lock

A Next‑Key Lock locks both the record and the preceding gap. If a transaction holds an X‑type next‑key lock on the range (3,5], other transactions cannot insert a row with id = 4 nor update/delete the row with id = 5.

How MySQL adds row‑level locks

The lock target is always an index, and the basic unit is a next‑key lock (record + gap). In certain scenarios the next‑key lock degrades to a pure Record Lock or Gap Lock.

Below is the test table used in the experiments:

CREATE TABLE `user` (
  `id`   BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL COLLATE utf8mb4_unicode_ci,
  `age`  INT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The table contains rows with primary‑key values 1,5,10,20 and ages 19,21,22,39. The MySQL version used is 8.0.26 with the REPEATABLE READ isolation level.

Unique index equality query

If a unique index is used for an equality search, the lock behavior differs based on whether the searched row exists:

Row exists : the next‑key lock on that index entry degrades to a Record Lock.

Row does not exist : the next‑key lock on the first greater index entry degrades to a Gap Lock.

Experiments:

Record exists

BEGIN;
SELECT * FROM user WHERE id = 1 FOR UPDATE;
-- X record lock on primary key id=1
COMMIT;

Other transactions attempting to UPDATE or DELETE id=1 block because they need the same X lock.

Record does not exist

BEGIN;
SELECT * FROM user WHERE id = 2 FOR UPDATE;
-- X gap lock on the interval (1,5)
COMMIT;

The gap lock prevents other transactions from inserting rows with id = 2,3,4 while allowing inserts that would conflict with existing primary‑key values (e.g., id=1 or id=5 cause duplicate‑key errors).

Unique index range query

When a unique index is used in a range query, MySQL scans the index and adds a next‑key lock for each visited entry. The lock may degrade in the following cases:

For ">=" queries where the lower bound row exists, the next‑key lock on that row becomes a Record Lock.

For "<" or "<=" queries, the lock on the first row that fails the condition degrades to a Gap Lock.

Experiments show the exact lock sets for queries such as WHERE id > 15 FOR UPDATE and WHERE id >= 15 FOR UPDATE, illustrating the resulting next‑key locks on the matching rows and the supremum pseudo‑record.

Non‑unique index equality query

With a secondary (non‑unique) index, the process is a scan:

If matching rows exist, each scanned secondary‑index entry receives a next‑key lock; the first entry that does not satisfy the condition receives a Gap Lock. The corresponding primary‑key rows receive Record Locks.

If no matching rows exist, the first scanned entry receives a Gap Lock and no primary‑key lock is taken.

Example where age = 25 does not exist results in an X Gap Lock on the interval (22,39) of the index_age secondary index.

The lock record shows LOCK_DATA: 39,20 and LOCK_MODE: X, GAP, meaning the gap lock covers ages (22,39) and also blocks inserts of age=39 with id less than 20.

Non‑unique index range query

For range scans on a non‑unique index, every visited secondary‑index entry receives a next‑key lock; there is no degradation to Gap or Record locks. The primary‑key rows that satisfy the condition also receive Record Locks.

SELECT * FROM user WHERE age >= 22 FOR UPDATE;

Queries without index usage

If a lock‑read, UPDATE, or DELETE statement does not use an index (full‑table scan), InnoDB adds a next‑key lock on every index entry, effectively locking the whole table and blocking all concurrent modifications.

Summary of lock rules

Unique index equality : existing row → Record Lock; non‑existing row → Gap Lock.

Non‑unique index equality : scan until first non‑matching entry; scanned entries get next‑key locks, the first non‑matching entry gets a Gap Lock; matching rows also get primary‑key Record Locks.

Unique index range : next‑key locks on scanned entries; may degrade to Record or Gap locks depending on the bound and row existence.

Non‑unique index range : all scanned secondary‑index entries receive next‑key locks; no degradation.

Full‑table scans : next‑key lock on every index entry → whole‑table lock.

Understanding these rules helps prevent phantom reads and avoid accidental full‑table locking in production environments.

To analyze the locks held by a transaction you can run: <code>SELECT * FROM performance_schema.data_locks\G;</code>

That concludes the tutorial. See you next time!