INSERT…在Mysql 5.7中，ON DUPLICATE KEY UPDATE导致死锁异常 &

Question

我们有一个mysql表如下

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

并使用插入查询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)

我们设置mysql的隔离模式为-READ COMMITTED

我有点困惑，因为2个查询试图插入不同的记录，第二个查询对student_subject表的主键有一个锁，并请求插入意图锁。我不确定为什么会发生，你知道吗?

Answer 1

我也发现了这个问题，这是innodb的一个bug, https://bugs.mysql.com/bug.php?id=98324