Understanding MySQL Next-Key Locks: Preventing Phantom Reads and Deadlocks

Transaction Isolation Levels

Serializable: transactions read and write data serially.

Repeatable Read: the same row read multiple times returns the same result (MySQL default).

Read Committed: a transaction can see data committed by other transactions.

Read Uncommitted: a transaction can read uncommitted data from other transactions.

Repeatable Read solves phantom reads using MVCC and Next-Key Lock.

Phantom read: the result set of the same query changes because other transactions insert or delete rows that satisfy the query condition.

Next-Key Lock Overview

Next-Key Lock is a combination of a gap lock and a row lock. It locks a record and the gap before it, forming a half‑open interval (previous gap open, current record closed).

Row lock: locks a specific index record.

Gap lock: locks the interval between index records; no data exists in the locked range.

Example table:

CREATE TABLE `t` (
  `id` int(10) NOT NULL,
  `a` int(10) DEFAULT NULL,
  `b` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB;

Assume the table contains four rows: (5,5,5), (10,10,10), (15,15,15), (20,20,20).

Query: SELECT * FROM t WHERE id = 7 FOR UPDATE; This locks the row with id = 10 using a Next‑Key Lock on the interval (5,10]. The row lock protects the record, and the gap lock prevents inserts into the gap (5,10).

If the query uses a non‑indexed column: SELECT * FROM t WHERE b = 6 FOR UPDATE; Because column b has no index, InnoDB must scan the whole table and applies Next‑Key Locks to all matching gaps, creating five lock ranges.

When the query targets a unique index with an equality condition that matches an existing row, the Next‑Key Lock degrades to a simple row lock: SELECT * FROM t WHERE id = 10 FOR UPDATE; In this case only the row lock is taken.

Conditions for Next‑Key Lock to Degrade to Row Lock

The query uses a unique index.

The condition is an equality match (e.g., WHERE unique_key = X).

The scanned record is known to exist.

If any of these conditions is not met, a Next‑Key Lock is applied.

Additionally, when an equality scan reaches the last matching value that does not satisfy the condition, the lock can degrade to a gap lock. SELECT * FROM t WHERE id = 8 FOR UPDATE; This results in a gap lock on the interval (5,10), while the row with id = 10 remains unlocked for other transactions.

Pros and Cons

Pros: Eliminates phantom reads under REPEATABLE READ, ensuring data consistency.

Cons: Increases lock granularity, which can reduce concurrency and raise the likelihood of deadlocks because overlapping gap locks may be requested in different orders.

Deadlock Example

Transaction A attempts to insert (8,8,8) and Transaction B attempts to insert (9,9,9). Both acquire Next‑Key Locks on the gap (5,10), leading to a circular wait and a deadlock.

Gap locks do not conflict with each other, which is why both transactions can acquire them simultaneously.

Summary

Next‑Key Lock is effective only under the REPEATABLE READ isolation level.

It locks a half‑open interval but may degrade to a row lock or a gap lock depending on query conditions.