MySQL 5.7中INSERT ... ON DUPLICATE KEY UPDATE引发死锁异常

问题背景 #

MySQL 5.7环境，隔离级别为READ COMMITTED，高并发写入场景下执行INSERT ... ON DUPLICATE KEY UPDATE语句频繁触发死锁，相关信息如下：

业务表结构 #

CREATE TABLE student_subject (
  student_subject_id int(11) NOT NULL AUTO_INCREMENT,
  student_id int(11) NOT NULL,
  subject_id int(11) NOT NULL,
  version int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (student_subject_id),
  UNIQUE KEY student_subject_uniq (student_id, subject_id),
  CONSTRAINT student_subject_fk1 FOREIGN KEY (student_id) REFERENCES student (student_id) ,
  CONSTRAINT student_subject_fk2 FOREIGN KEY (subject_id) REFERENCES subject (subject_id)
)

业务写入SQL #

insert into student_subject (student_id, subject_id) values (101, 201) ON DUPLICATE KEY UPDATE student_id = values(student_id), subject_id = values(subject_id), version = version + 1;

死锁日志 #

mysql tables in use 1, locked 1
LOCK WAIT 31 lock struct(s), heap size 3520, 17 row lock(s), undo log entries 16
MySQL thread id 2690963, OS thread handle 47277357205248, query id 147266531743 10.8.83.115 sub update
insert into student_subject (student_id, subject_id) values (101, 201) ON DUPLICATE KEY UPDATE student_id = values(student_id), subject_id = values(subject_id), version = version + 1
2022-06-01T20:57:19.434319Z 2691930 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 8263 page no 28625 n bits 232 index PRIMARY of table `test`.`student_subject` trx id 22794090198 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

2022-06-01T20:57:19.434455Z 2691930 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 22794089938, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
28 lock struct(s), heap size 3520, 16 row lock(s), undo log entries 16
MySQL thread id 2691930, OS thread handle 47273083741952, query id 147266531747 10.8.84.91 sub update
insert into student_subject (student_id, subject_id) values (102, 201) ON DUPLICATE KEY UPDATE student_id = values(student_id), subject_id = values(subject_id), version = version + 1
2022-06-01T20:57:19.434505Z 2691930 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 8263 page no 28625 n bits 232 index PRIMARY of table `test`.`student_subject` trx id 22794089938 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

2022-06-01T20:57:19.434616Z 2691930 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 8263 page no 28625 n bits 232 index PRIMARY of table `test`.`student_subject` trx id 22794089938 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

2022-06-01T20:57:19.434731Z 2691930 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

核心疑问 #

• 两个待提交事务插入的业务键分别为(101,201)和(102,201)，属于不同业务记录，为什么会触发死锁
• 日志显示事务2已经持有主键索引的X锁，为什么还会申请同位置的插入意向锁

死锁产生原理 #

首先明确日志中反复出现的supremum记录：这是InnoDB每个索引页内置的虚拟边界记录，代表当前页内比所有真实存在的索引值都大的位置，不是实际存储的业务行。
该死锁和业务键是否重复无关，本质是自增主键+INSERT ... ON DUPLICATE KEY UPDATE的锁机制在高并发下的必然结果，具体逻辑如下：

1. 锁特性基础：INSERT ... ON DUPLICATE KEY UPDATE做唯一性校验时，无论是否命中冲突，都会先对检查到的索引位置加next-key锁（行锁+gap锁的组合）。即使是READ COMMITTED隔离级别，为了避免唯一键写入冲突，这类语句也不会完全释放校验时加的gap锁。
2. 插入位置重合：该表主键是自增字段，所有新插入记录的主键值都是连续递增的，所有新行都会插入到主键索引B+树同一页的最末尾，也就是supremum记录之前的gap区间，高并发下所有插入事务都会争抢同一个位置的锁。
3. 等待环路形成：
   • 事务2在批量插入前16条记录时，已经拿到了主键索引supremum位置的X型next-key锁，这个锁会阻塞其他事务在对应gap区间的插入意向锁申请。
   • 事务1执行插入时，先通过唯一键校验，随后申请主键页尾的插入意向锁，被事务2持有的X锁阻塞，进入锁等待队列。
   • 事务2插入第17条记录时，需要单独申请该位置的插入意向锁——这里要注意InnoDB的锁规则：插入意向锁和gap锁本身是互斥的，哪怕gap锁是当前事务自己持有，插入新行时也必须单独申请插入意向锁；同时锁申请遵循先进先出的排队规则，不能跳过队列中更早等待的事务1直接授予锁。
   • 此时形成环路：事务1等待事务2释放持有的X锁，事务2等待事务1退出等待队列才能拿到插入意向锁，最终触发死锁，InnoDB选择回滚开销更小的事务2。

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