Understanding InnoDB Record Locking When Inserting Duplicate Primary Keys

The article, authored by MySQL and OceanBase expert Cao Shengchun, analyzes the locking behavior of InnoDB when an INSERT statement attempts to add a row with a primary‑key value that already exists.

1. Preparation

First, a test table t1 is created and populated with sample rows.

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

Insert test data:

INSERT INTO `t1` (`id`, `i1`) VALUES
(10, 101), (20, 201), (30, 301), (40, 401);

2. Lock Situation

Attempting to insert another row with id = 10 triggers a duplicate‑key error:

BEGIN;
INSERT INTO t1(id, i1) VALUES (10, 1010);

The error returned is:

(1062, "Duplicate entry '10' for key 't1.PRIMARY'")

Querying performance_schema.data_locks shows a shared record lock on the existing row:

SELECT engine_transaction_id, object_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 't1' AND lock_type = 'RECORD'\G

Result (excerpt):

engine_transaction_id | 247910
object_name           | t1
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 10

The lock mode S,REC_NOT_GAP indicates a shared ordinary record lock on the row with id = 10 .

3. Principle Analysis

During an INSERT, InnoDB first locates the target position in the primary‑key index, which is the last record whose key is less than or equal to the new key. For id = 10 , it finds the existing row <id = 10, i1 = 101> and attempts to insert after it.

If the existing row is a valid, committed record, the duplicate‑key conflict is detected. InnoDB then acquires a shared record lock on that row to prevent other transactions from modifying or deleting it while the check is performed.

If the existing row had been marked for deletion by an uncommitted transaction, it would not be considered a valid record, and the INSERT could succeed.

To illustrate the interaction with a concurrent DELETE, two sessions are used:

-- Session 1 (Transaction 1)
BEGIN;
DELETE FROM t1 WHERE id = 10;

-- Session 2 (Transaction 2)
BEGIN;
INSERT INTO t1(id, i1) VALUES (10, 1010);

After running the same lock‑query, the lock view shows:

engine_transaction_id | 247916
object_name           | t1
lock_type             | RECORD
lock_mode             | S,REC_NOT_GAP
lock_status           | WAITING
lock_data             | 10

engine_transaction_id | 247911
object_name           | t1
lock_type             | RECORD
lock_mode             | X,REC_NOT_GAP
lock_status           | GRANTED
lock_data             | 10

Transaction 247911 (the DELETE) holds an exclusive record lock, while transaction 247916 (the INSERT) waits for a shared lock, demonstrating the blocking behavior.

4. Summary

The article concludes without a formal summary but raises two open questions about the lock state after transaction 247911 commits or rolls back, inviting readers to discuss.