InnoDB Lock Analysis for INSERT … ON DUPLICATE KEY UPDATE (Repeatable‑Read and Read‑Committed)

Preparation

A test table t4 is created with an id primary key, two integer columns i1 and i2 , a unique index on i1 , and InnoDB engine. Sample rows are inserted to provide data for the lock tests.

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

1. REPEATABLE‑READ

The transaction isolation level is set to REPEATABLE‑READ . An INSERT … ON DUPLICATE KEY UPDATE statement attempts to insert a row with id=7, i1=12, i2=220 . Because i1=12 already exists, the duplicate‑key clause updates the primary‑key field.

SET transaction_isolation='REPEATABLE-READ';
BEGIN;
INSERT INTO t4 (id,i1,i2) VALUES (7,12,220)
ON DUPLICATE KEY UPDATE id=VALUES(id), i2=VALUES(i2);

Lock information is retrieved from performance_schema.data_locks . The result shows a mixture of exclusive next‑key locks on the unique index, exclusive record locks on the primary key, and gap locks on the supremum record. The article explains each lock row (1‑row to 6‑row) in detail, describing why InnoDB locks the conflicting unique‑index entry, how it converts implicit locks to explicit ones during rollback, and why the supremum record receives a special next‑key lock.

2. READ‑COMMITTED

The isolation level is switched to READ-COMMITTED and the same INSERT statement is executed. The same set of locks appears, but the article notes that READ‑COMMITTED normally would not acquire next‑key locks; however, because the unique index permits multiple NULL values, InnoDB still uses next‑key locks to prevent phantom inserts.

SET transaction_isolation='READ-COMMITTED';
BEGIN;
INSERT INTO t4 (id,i1,i2) VALUES (7,12,220)
ON DUPLICATE KEY UPDATE id=VALUES(id), i2=VALUES(i2);

The lock rows are examined in the same order as in the REPEATABLE‑READ case, confirming that the locking logic is identical for the conflicting unique‑index entry.

3. Summary

No additional summary content is provided in the original article.

4. Prize Interaction

Readers are invited to comment on why the gap lock is applied only to the gap before the newly inserted primary‑key record rather than the record itself, with a promise of book prizes for selected comments.