Next Key Lock in MySQL: Explanation and Usage

In MySQL, a next key lock is a type of lock that combines a row lock with a gap lock on the next key in an index. It prevents other transactions from inserting or modifying rows in the locked range, helping to avoid phantom reads during transactions.

How It Works

A next key lock in MySQL is like putting a fence around a specific row and the gap right after it in an index. Imagine you have a row in a table, and you want to make sure no one else can add or change rows that would fit right after it while you are working. The next key lock does exactly that by locking the row itself and the space immediately following it.

This lock is a combination of two locks: a record lock on the row and a gap lock on the space between this row and the next one. This prevents other transactions from inserting new rows into that gap or changing the locked row, which helps keep your data consistent during complex operations like range queries or updates.

Example

This example shows how a next key lock works during a transaction that selects rows with a range condition using the FOR UPDATE clause.

sql

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price INT,
  KEY(price)
) ENGINE=InnoDB;

INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Pencil', 20), (3, 'Eraser', 30);

START TRANSACTION;
SELECT * FROM products WHERE price >= 10 AND price < 30 FOR UPDATE;

Output

id | name   | price
---|--------|------
1  | Pen    | 10
2  | Pencil | 20

When to Use

Use next key locks when you want to prevent other transactions from inserting or modifying rows in a range you are working on. This is especially useful in transactions that involve range queries with SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE.

For example, if you are updating prices in a range or reading rows to make decisions, next key locks help avoid phantom reads, where new rows appear in your range after you start your transaction. This ensures your transaction sees a stable set of rows.

Key Points

• Next key lock combines a row lock and a gap lock on the next index key.
• It prevents phantom reads by blocking inserts into the locked gap.
• Used automatically by InnoDB for range queries with locking clauses.
• Helps maintain data consistency in concurrent transactions.

Key Takeaways

Next key lock prevents other transactions from inserting or modifying rows in a locked range.

It combines a row lock and a gap lock to protect both the row and the gap after it.

Next key locks help avoid phantom reads during range queries in transactions.

MySQL InnoDB uses next key locks automatically with SELECT ... FOR UPDATE or LOCK IN SHARE MODE.

Understanding next key locks is important for writing safe concurrent database operations.