InnoDB Locking Analysis for INSERT … ON DUPLICATE KEY under REPEATABLE‑READ and READ‑COMMITTED

Author: Cao Shengchun, a technical expert at iKangsheng, focuses on MySQL and OceanBase source code analysis.

1. Preparation

Create a test table and insert sample data:

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);

2. REPEATABLE‑READ

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

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

Execute an INSERT with a duplicate‑key conflict (the primary key is not updated):

BEGIN;
INSERT INTO t4 (id,i1,i2) VALUES (7,12,220)
ON DUPLICATE KEY UPDATE i2 = VALUES(i2);

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 a X (exclusive) Next‑Key lock on the unique index entry <i1 = 12, id = 2> and X,REC_NOT_GAP locks on the primary‑key record <id = 2> . The article explains why InnoDB first inserts the row into the primary index, then attempts to insert into the secondary unique index, discovers the conflict, rolls back the primary‑key insert, and finally updates the conflicting row via the ON DUPLICATE KEY UPDATE clause. It also details how implicit locks are converted to explicit locks (LOCK_X, LOCK_REC_NOT_GAP) during rollback and how the supremum pseudo‑record receives a Next‑Key lock.

3. READ‑COMMITTED

Switch the isolation level to READ‑COMMITTED:

SET transaction_isolation = 'READ-COMMITTED';
-- Verify
SHOW VARIABLES LIKE 'transaction_isolation';

Run the same INSERT statement and inspect locks:

BEGIN;
INSERT INTO t4 (id,i1,i2) VALUES (7,12,220)
ON DUPLICATE KEY UPDATE i2 = VALUES(i2);
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

Even under READ‑COMMITTED, InnoDB still acquires an X Next‑Key lock on the unique index entry <i1 = 12, id = 2> . The article notes that this is necessary because unique indexes allow multiple NULL values; the lock prevents other transactions from inserting a conflicting NULL row during the duplicate‑key check.

4. Summary

No additional summary content is provided.

For further reading, the author lists links to previous issues covering related topics such as lock types, deadlock analysis, savepoints, and two‑phase commit.