Mastering MySQL Row‑Level Locks: Rules, Types, and How to Inspect Them

SQL Statements That Acquire Row‑Level Locks

InnoDB is the only MySQL storage engine that supports row‑level locking. Ordinary SELECT statements use MVCC snapshot reads and do not lock rows. To acquire row‑level locks during a read you must use a locking read inside a transaction:

// shared (S) lock
SELECT ... LOCK IN SHARE MODE;

// exclusive (X) lock
SELECT ... FOR UPDATE;

Both UPDATE and DELETE acquire exclusive row‑level locks automatically. The locks are held until the transaction commits (or rolls back).

Types of Row‑Level Locks

Depending on the isolation level MySQL uses three lock types:

Record lock – locks a single row (S or X).

Gap lock – locks a range between rows, not the rows themselves. Used only under REPEATABLE READ to prevent phantom reads.

Next‑key lock – a combination of record lock and gap lock; it locks the index record and the preceding gap (closed‑open interval).

1. Record Lock

Example:

BEGIN;
SELECT * FROM user WHERE id = 1 FOR UPDATE;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飞   |  19 |
+----+--------+-----+

The row with id=1 is locked with an X‑type record lock. Other transactions attempting to UPDATE or DELETE that row will block. Inserting a new row with the same primary‑key value fails because of the unique constraint, not because of the lock.

2. Gap Lock

Gap locks exist only under REPEATABLE READ. If a transaction holds a gap lock on the interval (3,5), no other transaction can insert a row with id=4:

Gap locks are compatible with each other; multiple transactions may hold the same gap lock because their purpose is only to block inserts that would create phantom rows.

3. Next‑Key Lock

A next‑key lock = record lock + gap lock. For a range (3,5] the lock prevents inserts of id=4 and also prevents updates or deletes of the record id=5:

If a transaction holds an X‑type next‑key lock on a range, another transaction requesting the same range will block.

How MySQL Applies Row‑Level Locks

Locks are applied to index entries; the basic unit is the next‑key lock. In certain scenarios a next‑key lock degrades to a record lock or a gap lock.

Unique Index Equality Queries

If the searched row exists, the next‑key lock on that index entry degrades to a record lock .

If the row does not exist, the next‑key lock on the first greater index entry degrades to a gap lock .

Existing record example:

BEGIN;
SELECT * FROM user WHERE id = 1 FOR UPDATE;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飞   |  19 |
+----+--------+-----+

The transaction holds an X‑type record lock on id=1. Any concurrent UPDATE or DELETE of that row blocks.

Non‑existing record example:

BEGIN;
SELECT * FROM user WHERE id = 2 FOR UPDATE;
Empty set

Performance schema shows an X intention lock on the table and an X‑type gap lock on the primary‑key range (1,5). Inserts of id=2,3,4 block; inserts of id=1 or id=5 fail with a primary‑key conflict.

Unique Index Range Queries

Range scans on a unique index also start with next‑key locks, but they may degrade depending on the boundary condition:

Greater / Greater‑Equal : if the boundary row exists, its next‑key lock degrades to a record lock.

Less / Less‑Equal : if the boundary row does not exist, the lock on the terminating row degrades to a gap lock; if the boundary row exists, < degrades to a gap lock while <= stays a next‑key lock.

Experiments with SELECT ... FOR UPDATE under REPEATABLE READ confirm these rules.

Non‑Unique Index Equality Queries

When a secondary (non‑unique) index is used, MySQL scans the index entries:

If matching rows exist, each scanned secondary‑index entry receives a next‑key lock; the first non‑matching entry’s lock degrades to a gap lock, and the corresponding primary‑key rows receive record locks.

If no matching rows exist, the first scanned entry’s next‑key lock degrades directly to a gap lock; no primary‑key rows are locked.

Example (no age=25 rows):

BEGIN;
SELECT * FROM user WHERE age = 25 FOR UPDATE;
Empty set

The transaction holds an X‑type gap lock on the secondary index range (22,39) for age, preventing inserts of ages 23‑38.

Non‑Unique Index Range Queries

For range scans on a non‑unique index, every scanned secondary‑index entry receives a next‑key lock; there is no degradation to record or gap locks.

BEGIN;
SELECT * FROM user WHERE age >= 22 FOR UPDATE;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 10 | 山治      |  22 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+

Locks applied:

Primary‑key rows id=10 and id=20 receive record locks.

Secondary‑index entries age=22 and age=39 receive next‑key locks covering (21,22] and (22,39] respectively, plus a next‑key lock on the supremum pseudo‑record (39,+∞].

Analyzing Acquired Locks

During a transaction you can inspect the lock set with: SELECT * FROM performance_schema.data_locks\G; Typical output shows:

Table lock : X intention lock (I_X).

Row lock : X lock on the index entry. The LOCK_MODE column indicates the lock type: X – next‑key lock. X, REC_NOT_GAP – record lock. X, GAP – gap lock.

For example, a unique‑index equality query that finds a row shows a record lock (REC_NOT_GAP) on the primary‑key entry; a query that finds no row shows a GAP lock on the surrounding range.

Importance of Indexes

If a locking read (or UPDATE/DELETE) cannot use an index, MySQL performs a full‑table scan. In that case every index entry receives a next‑key lock, effectively locking the whole table and causing severe concurrency problems. Always ensure that the WHERE clause can use an appropriate index.

Key Takeaways

Unique‑index equality queries lock existing rows with record locks and missing rows with gap locks.

Non‑unique‑index equality queries lock scanned secondary‑index entries with next‑key locks; the first non‑matching entry becomes a gap lock, and matching rows’ primary keys get record locks.

Unique‑index range queries may cause next‑key locks to degrade to record or gap locks depending on the boundary condition; non‑unique‑index range queries always keep next‑key locks.

All locking reads ( SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE) and UPDATE/DELETE statements must be executed inside a transaction; otherwise the locks are released immediately.

Running these statements without an index leads to a full‑table next‑key lock, which is effectively a table‑wide lock and should be avoided in production.