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

原创

已于 2024-07-17 19:57:20 修改 · 1.2k 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL #主键索引 #唯一索引 #死锁 #批量插入

于 2024-07-13 13:13:21 首次发布

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,23,1) ON DUPLICATE KEY UPDATE val = VALUES(val);
INSERT INTO switch (uid, type, val) VALUES  (30001,3,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);