MySQL 5.7下INSERT ON DUPLICATE KEY UPDATE死锁及RC锁问题问询

背景与核心问题 #

表结构 #

create table test  
(`id` bigint(20) NOT NULL AUTO_INCREMENT,  
`unique_key` int(11) NOT NULL,  
`count` int(11) NOT NULL,  
PRIMARY KEY (`id`),  
UNIQUE KEY `idx_unique_key` (`unique_key`))  

场景与初始疑问 #

基于unique_key并发执行INSERT ON DUPLICATE KEY UPDATE语句，实现插入新记录或更新count字段。有观点认为该语句的死锁源于间隙锁（gap locks），而间隙锁是REPEATABLE READ（RR）隔离级别下用于防止幻读的机制。

针对以下三种优化方案，疑问如下：

• 将事务隔离级别设置为READ COMMITTED（RC）：认为无需处理幻读，因此不会产生间隙锁，能否避免死锁？
• 开启自动提交（autocommit）：每次仅执行一条INSERT ON DUPLICATE KEY UPDATE语句，能否避免死锁？
• 按unique_key分配执行逻辑：确保并发操作不会处理同一unique_key，能否避免死锁？

若上述方案仍存在极低概率的死锁，请给出示例场景。

真正关心的核心点 #

在**仅并发执行INSERT ON DUPLICATE KEY UPDATE（不考虑其他表操作）**的理论场景下，实施上述三种方案后，该语句的死锁是否仍会存在？

测试验证与新疑问 #

测试环境 #

• MySQL版本：5.7.44-log
• 隔离级别：READ COMMITTED（RC）

测试步骤 #

1. 创建表：

CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`unique_key` int(11) NOT NULL,
`count` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_unique_key` (`unique_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2. 插入初始记录：

INSERT INTO test (unique_key,count) VALUES(1, 1)

3. 启动事务1：

START TRANSACTION;
INSERT INTO test (`unique_key`, `count`)
VALUES(1, 1) ON DUPLICATE KEY UPDATE count = count + 1;

4. 启动事务2：

START TRANSACTION;
DELETE FROM test;

5. 查看InnoDB状态：

SHOW ENGINE INNODB STATUS;

测试目的是让事务2等待事务1，以便查看事务1持有的锁信息。

关键测试结果 #

事务1（事务ID 3142）持有3个锁，详情如下：

---TRANSACTION 3142, ACTIVE 18 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 33432, query id 571 localhost 127.0.0.1 root
TABLE LOCK table `ifly_ads_statis`.`test` trx id 3142 lock mode IX
RECORD LOCKS space id 46 page no 4 n bits 72 index idx_unique_key of table `ifly_ads_statis`.`test` trx id 3142 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;; 
 1: len 8; hex 8000000000000001; asc         ;; 

RECORD LOCKS space id 46 page no 3 n bits 72 index PRIMARY of table `ifly_ads_statis`.`test` trx id 3142 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;; 
 1: len 6; hex 000000000c46; asc      F;; 
 2: len 7; hex 2f0000014a0541; asc /   J A;; 
 3: len 4; hex 80000001; asc     ;; 
 4: len 4; hex 80000002; asc     ;; 

其中存在锁：index idx_unique_key of table 'ifly_ads_statis'.'test' trx id 3142 lock_mode X，有观点认为这是临键锁（next-key锁）——间隙锁与记录锁的组合，锁范围比间隙锁更大。

后续疑问 #

1. 该测试结果是否能证明RC级别下会出现临键锁？
2. 网上普遍认为临键锁用于解决幻读问题，而RC级别无需防范幻读，为何在RC级别下仍能观测到临键锁？这是否与MySQL版本有关？

内容的提问来源于stack exchange，提问作者lzwang2