InnoDB Locking Behavior for Unique Index Conflicts under READ‑COMMITTED Isolation

Preparation

Create a test table t4 with a primary key id and a unique index uniq_i1 on column i1 . Insert several rows and set the transaction isolation level to READ-COMMITTED if not already set.

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 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 transaction_isolation = 'READ-COMMITTED';
SHOW VARIABLES LIKE 'transaction_isolation';

Locking Situation

Attempt to insert a row with i1 = 12 , which already exists in the unique index. The insert fails with a duplicate‑entry error, and a query on performance_schema.data_locks shows a shared Next‑Key lock on the conflicting record.

BEGIN;
INSERT INTO t4(i1,i2) VALUES (12,2000);
-- Error: (1062, "Duplicate entry '12' for key 't4.uniq_i1'")
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 indicates lock_type = RECORD , lock_mode = S , and lock_data = 12,2 , meaning a shared Next‑Key lock on the existing (i1=12, id=2) entry.

Principle Analysis

When the insert is executed, InnoDB generates a new auto‑increment id value (7) and attempts to place the new record (id=7, i1=12, i2=2000) into the unique index. It discovers the existing (i1=12, id=2) entry, checks for NULL values (none), and then verifies whether the existing record is marked as deleted.

If the existing record is not deleted, InnoDB acquires a shared Next‑Key lock on it to prevent other transactions from modifying or inserting into the gap before the record. Because the isolation level is READ‑COMMITTED, InnoDB does not lock the supremum record of the primary key index.

After the unique‑index conflict is detected, the partially inserted row in the primary key index must be removed. The row has an implicit lock (its DB_TRX_ID belongs to the current uncommitted transaction). In READ‑COMMITTED mode, InnoDB does not convert this implicit lock to an explicit one, so the supremum record is not inherited.

Summary

The article demonstrates that under READ‑COMMITTED isolation, InnoDB places a shared Next‑Key lock on the conflicting unique‑index record, does not lock the supremum record, and cleans up the partially inserted primary‑key entry without converting its implicit lock to an explicit lock.