What Lock Is Acquired When a Primary Key Index Conflict Occurs During INSERT

1. Preparation

We create a test table with a primary key and a unique index on column i1 :

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

Insert some initial rows:

INSERT INTO `t4` (`id`, `i1`, `i2`) VALUES
(1, 11, 21),
(2, 12, 22),
(3, 13, 23),
(4, 14, 24),
(5, 15, 25),
(6, 16, 26);

Set the transaction isolation level to REPEATABLE-READ (skip if already set):

SET transaction_isolation = 'REPEATABLE-READ';
SHOW VARIABLES LIKE 'transaction_isolation';

2. Lock Situation

The table t4 has a primary key index and a unique index uniq_i1 . There is already a row where i1 = 12 .

We start a transaction and try to insert another row with i1 = 12 :

BEGIN;
INSERT INTO t4(i1, i2) VALUES (12, 2000);

The insert fails with a duplicate‑key error:

(1062, "Duplicate entry '12' for key 't4.uniq_i1'")

We then query the lock information:

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

The result shows two row‑level locks:

On the unique index uniq_i1 : lock mode S (shared) on the record (12, 2) .

On the primary key index: lock mode X (exclusive) on the supremum pseudo‑record.

3. Principle Analysis

3.1 Primary Key Index

When the INSERT is executed, InnoDB first inserts the new row into the primary‑key index, generating an auto‑increment id value (7 in this example). The primary‑key page then contains the new record before the unique‑index check is performed.

If the unique‑index check later discovers a conflict, the primary‑key entry must be removed. The record is initially protected by an implicit lock; because the transaction has not committed, InnoDB converts this implicit lock to an explicit lock ( X,REC_NOT_GAP ).

When the record is deleted, the lock is transferred to the next record (the supremum record). The next record inherits only the lock mode X , and then adds the GAP flag, resulting in an X,GAP lock. InnoDB’s rule for supremum records upgrades any lock to a Next‑Key lock, which is why we see an exclusive Next‑Key lock on the supremum record.

3.2 Unique Index

Before inserting into the unique index uniq_i1 , InnoDB locates the target position ( (i1=12, id=2) ) and acquires a shared Next‑Key lock on that existing record to prevent concurrent modifications. Because the new row would duplicate the existing i1 value, InnoDB aborts the insert and reports the duplicate‑key error. The shared lock observed on uniq_i1 is exactly this preventive lock.

4. Summary

The article demonstrates that an INSERT causing a primary‑key index conflict results in two locks: a shared Next‑Key lock on the conflicting unique‑index record and an exclusive Next‑Key lock on the supremum record of the primary‑key index, which originates from the implicit‑to‑explicit lock conversion and lock inheritance mechanisms in InnoDB.