Understanding INSERT … ON DUPLICATE KEY UPDATE Locking in MySQL InnoDB (Repeatable‑Read vs Read‑Committed)

Author introduces MySQL 8.0.32 InnoDB example to demonstrate how INSERT ... ON DUPLICATE KEY UPDATE behaves under different isolation levels.

1. Preparation

Creates table t4 and inserts sample rows.

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

Sets transaction_isolation to REPEATABLE‑READ , starts a transaction, runs an INSERT that conflicts on the primary key and uses ON DUPLICATE KEY UPDATE , then queries performance_schema.data_locks to show the lock details.

SET transaction_isolation='REPEATABLE-READ';

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

The result shows an X,REC_NOT_GAP lock on record id=2 , indicating an exclusive record lock.

The article explains why the INSERT acquires an exclusive lock: the ON DUPLICATE KEY UPDATE clause requires an update, which needs an exclusive lock on the conflicting row.

3. Read‑Committed

Repeats the same steps after setting transaction_isolation to READ‑COMMITTED . The lock mode and lock data are identical to the REPEATABLE‑READ case, confirming that the locking behavior does not change between these two isolation levels for this statement.

4. Summary

Both REPEATABLE‑READ and READ‑COMMITTED isolation levels produce the same locking process and result for INSERT … ON DUPLICATE KEY UPDATE when a primary‑key conflict occurs.

Question: If the conflicting row has been marked deleted but the deleting transaction has not yet committed, how would the lock state differ?