唯一索引插入引发的死锁问题

MySQL 8.4.0

RR隔离级别

场景复现

分析下面SQL死锁的场景

对于switch表，有主键索引id和唯一索引(uid、type)。

 CREATE TABLE `switch` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for each switch',
  `uid` int NOT NULL COMMENT 'User ID associated with the switch',
  `type` tinyint NOT NULL COMMENT 'Type of the switch',
  `val` tinyint(1) NOT NULL COMMENT 'Value of the switch, TRUE for on and FALSE for off',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Table to store switch information for different users'

业务中有这么一个需求，以事务的方式批量插入固定两个开关；

注意，虽然是批量插入，但顺序是固定的！！！

INSERT INTO switch (uid, type, val) VALUES  (30001,3,1) ON DUPLICATE KEY UPDATE val = VALUES(val);
INSERT INTO switch (uid, type, val) VALUES  (30001,23,0) ON DUPLICATE KEY UPDATE val = VALUES(val);

然后业务上线后一段时间，告警发现事务出现了死锁Deadlock found when trying to get lock; try restarting transaction。

死锁排查

排除乱序插入；当时以为是事务中批量插入的顺序不一致，但重新梳理了下业务，能确定事务插入顺序都是固定的【23、3】；

INSERT INTO switch (uid, type, val) VALUES  (30001,23,0) ON DUPLICATE KEY UPDATE val = VALUES(val);
INSERT INTO switch (uid, type, val) VALUES  (30001,3,0) ON DUPLICATE KEY UPDATE val = VALUES(val);

唯一索引顺序不一致：后面想到使用了唯一索引，唯一索引的顺序是按照uid、type递增排序的，但我插入的时候是先插入type=23、再插入type=3的记录，因此怀疑是插入的时候，唯一索引上加了间隙锁，锁住了(0~23)的间隙，两个事务都保持间隙锁不释放，就产生了死锁。

定位到问题后，尝试把插入顺序调整为唯一索引的顺序，再尝试，就没有死锁问题了。

锁分析

导出死锁日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-07-13 03:56:02 281472325639936
*** (1) TRANSACTION:
TRANSACTION 6770, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 281472766701312, query id 100 localhost root update
INSERT INTO switch (uid, type, val) VALUES  (30001,23,0) ON DUPLICATE KEY UPDATE val = VALUES(val)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 5 n bits 72 index uid of table `mydatabase`.`switch` trx id 6770 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80007531; asc   u1;;
 1: len 1; hex 97; asc  ;;
 2: len 4; hex 80000001; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 5 n bits 72 index uid of table `mydatabase`.`switch` trx id 6770 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80007531; asc   u1;;
 1: len 1; hex 97; asc  ;;
 2: len 4; hex 80000001; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 6769, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2
MySQL thread id 10, OS thread handle 281472765583104, query id 101 localhost root update
INSERT INTO switch (uid, type, val) VALUES  (30001,3,0) ON DUPLICATE KEY UPDATE val = VALUES(val)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 5 n bits 72 index uid of table `mydatabase`.`switch` trx id 6769 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80007531; asc   u1;;
 1: len 1; hex 97; asc  ;;
 2: len 4; hex 80000001; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 5 n bits 72 index uid of table `mydatabase`.`switch` trx id 6769 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80007531; asc   u1;;
 1: len 1; hex 97; asc  ;;
 2: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

使用GPT分析，

1. 死锁的原因：
   • 两个事务都试图在mydatabase.switch表的uid索引上插入或更新具有相同uid（在这里是u1，即十六进制的80007531）的记录。
   • 事务6770（Thread ID 9）正在等待一个它当前也持有的锁（这是一个死锁的经典标志）。
   • 事务6769（Thread ID 10）正在等待一个间隙锁（gap lock）以插入一个新的记录，但同时它也持有一个对已有记录的记录锁（record lock）。
2. 详细分析：
   • 事务6770正在尝试插入一个记录（或更新一个已存在的记录），其uid为u1，并且它正在等待一个X（排他）锁来允许这个操作。然而，它已经在等待该锁，因为它也被事务6769持有。
   • 事务6769也在尝试插入或更新同一个uid为u1的记录。它已经持有一个对u1记录的X锁，但它现在还需要一个间隙锁（gap lock）来确保在u1之前或之后的间隙中没有其他记录被插入，这样它就可以安全地插入一个新的uid为u1的记录（即使它实际上并不打算插入一个新的uid，但由于使用了INSERT ... ON DUPLICATE KEY UPDATE语法，MySQL仍然需要确保间隙的锁）。
   • 因为两个事务都在等待对方释放锁，所以形成了一个死锁。

解决方案

那为什么让插入顺序和唯一索引的顺序一致后，就没有死锁产生了呢？

那是因为，如果插入操作不会导致间隙中出现新的索引值，MySQL可以优化并使用行级锁，而不会产生额外的间隙锁。

继续分析上面解决方案中事务的死锁报告：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-07-13 04:10:58 281472325639936
*** (1) TRANSACTION:
TRANSACTION 6835, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 281472766701312, query id 126 localhost root update
INSERT INTO switch (uid, type, val) VALUES  (30001,23,0)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 5 n bits 72 index uid of table `mydatabase`.`switch` trx id 6835 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80007531; asc   u1;;
 1: len 1; hex 97; asc  ;;
 2: len 4; hex 80000001; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 5 n bits 72 index uid of table `mydatabase`.`switch` trx id 6835 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80007531; asc   u1;;
 1: len 1; hex 97; asc  ;;
 2: len 4; hex 80000001; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 6834, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2
MySQL thread id 10, OS thread handle 281472765583104, query id 127 localhost root update
INSERT INTO switch (uid, type, val) VALUES  (30001,3,0)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 5 n bits 72 index uid of table `mydatabase`.`switch` trx id 6834 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80007531; asc   u1;;
 1: len 1; hex 97; asc  ;;
 2: len 4; hex 80000001; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 5 n bits 72 index uid of table `mydatabase`.`switch` trx id 6834 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80007531; asc   u1;;
 1: len 1; hex 97; asc  ;;
 2: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

从您提供的死锁报告来看，虽然两个事务（Transaction 6835 和 Transaction 6834）都在尝试操作相同的uid（u1，即十六进制的80007531）在mydatabase.switch表的uid索引上，但是实际上并没有形成经典的死锁情况。

这里的关键点在于：

1. Transaction 6835（Thread ID 9）正在等待一个S（共享）锁，但它已经显示为waiting状态，这意味着它并没有实际持有任何锁，而只是在等待。在MySQL的InnoDB存储引擎中，共享锁通常允许多个事务同时读取同一行，但不允许其他事务修改它。然而，由于Transaction 6835处于等待状态，它实际上并没有持有任何锁。
2. Transaction 6834（Thread ID 10）已经持有一个X（排他）锁在uid为u1的记录上，并正在等待一个间隙锁（gap lock）以允许它插入一个可能的新记录（尽管实际上由于ON DUPLICATE KEY UPDATE，它可能只是更新现有的记录）。间隙锁用于锁定一个范围，但不包括记录本身，以确保没有其他事务在该范围内插入新记录。

由于Transaction 6835没有实际持有任何锁，它只是在等待一个它还没有获得的锁，因此它不能与Transaction 6834形成死锁。死锁通常发生在两个或更多的事务互相等待对方释放资源时。

可以发现，事务6835因为是顺序插入type=3、23的记录，并不需要间隙锁，所以不会和事务6834持有的X锁形成循环等待关系。

总结

在批量插入的过程中，尽量保证插入顺序与索引的顺序一致，不仅是主键索引，也包括唯一索引。