MYSQL死锁-更新插入导致死锁

见贤思齐

1问题背景2线上问题2.1线上异常日志2.2数据准备2.3问题复现2.3.3死锁日志分析3分析原因4解决方法5总结1问题背景前段时间，领导说我们业务量大涨，部门新增HC，让我们赶紧招人。领导：经过大家的共同努力和不懈奋斗，我们的业务量实现了显著的大涨，这是对我们团队能力和工作成果的最好证明。为了更好地应对业务量的增长，满足客户的需求，我们决定在部门内新增HC，大家行动起来吧。。。。面试官：你好，今天想和你聊聊MySQL数据库中的死锁问题。首先，你能解释一下什么是死锁吗？应聘者：死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，这些事务都将无法向前推进。在MySQL中，这通常发生在多个事务尝试以不同顺序访问相同的资源（如表或行）时。面试官：很好，那么MySQL中死锁发生的常见原因有哪些？应聘者：MySQL中死锁的常见原因包括：不同事务交叉锁定资源：当两个或多个事务相互等待对方释放锁定的资源时，就可能发生死锁。索引使用不当：不恰当的索引使用可能导致查询锁定更多行，增加死锁的风险。大量数据的修改：在处理大量数据时，尤其是同时修改多个表或行时，更容易发生死锁。锁升级：在某些情况下，低级锁（如行锁）可能会升级为更高级别的锁（如表锁），这也会增加死锁的可能性。面试官：如何分析一个SQL都加了哪些锁呢？你需要哪些前置信息呢？应聘者：好的，我先说一下我的理解。加锁规则：两个原则、两个优化、一个bug原则1：加锁的基本单位是next-keylock，前开后闭区间原则2：查找过程中访问到的对象才会加锁优化1：索引上的等值查询，给唯一索引加锁的时候，匹配上数据，next-keylock退化为行锁优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-keylock退化为间隙锁一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止面试官：那我有个案例，你分析分析都加了哪些锁？是否会产生死锁。我有一个回收单表，回收单id+回收类型是唯一索引，我先根据回收单idA更新回收单A状态，（如果数据不存在）再插入回收单A数据。我再根据回收单idB更新回收单B状态，（如果数据不存在）再插入回收单B数据。应聘者：。。。上面是国内开发者在找工作过程中常被问到的问题，大家吐槽是八股文，过度依赖背诵，加剧应试教育的倾向，使得应聘者更加注重面试通过率而非实际技能的提升。其实有些八股文是实际遇到问题的经验总结。这个问题是我们在线上每日错误日志清零时发现排查的死锁问题。在这里介绍一下，给大家遇到类似问题时提供一个排查思路。2线上问题2.1线上异常日志线上错误日志，从日志第2行可以发现是发生了死锁，从第6行可以发现是插入了数据时发生了异常，从20行可以看到异常的方法。根据日志找到业务代码，发现业务代码逻辑是：先把回收单id对应历史的回收单都更新为失效，然后再插入回收单id对应的新的回收单数据。2.2数据准备首先在测试库里建表，并准备相关的原数据。1、使用的mysql版本：线上5.7.21，测试8.0.322、配置的隔离级别：REPEATABLE-READ创建个checkout_detail表，分别插入三条数据。CREATE TABLE `checkout_detail` (  `id` bigint(20) NOT NULL COMMENT '主键id',  `recycle_order_id` bigint(20) NOT NULL COMMENT '回收单ID',  `confirm_recycle_time` datetime NOT NULL COMMENT '确认回收时间',  `contrast_type` int(4) NOT NULL COMMENT '对比类型：1:售前、2:后验、3:售后',  `remark` varchar(255) DEFAULT '' COMMENT '备注',  RIMARY KEY (`id`),  UNIQUE KEY `uniq_idx_recycle_order_id_contrast_type` (`recycle_order_id`,`contrast_type`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后验表'; INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )VALUES ( 1, 1, '2024-07-15 19:56:01', 1, "回收单1" );#模拟线上数据 INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark );VALUES ( 2, 10, '2024-07-15 19:56:01', 2, "回收单10" );#模拟线上数据 INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark );VALUES ( 3, 20, '2024-07-15 19:56:01', 3, "回收单20" ); #模拟线上数据2.3问题复现2.3.1执行流程执行时间顺序事务A事务BSTARTTRANSACTION;STARTTRANSACTION;1updatecheckout_detailSETremark='更新状态'WHERErecycle_order_id=30;2updatecheckout_detailSETremark='更新状态'WHERErecycle_order_id=40;3INSERTINTOcheckout_detail(id,recycle_order_id,confirm_recycle_time,contrast_type,remark)VALUES(30,30,'2024-07-1519:56:01',1,"插入回收单30");4INSERTINTOcheckout_detail(id,recycle_order_id,confirm_recycle_time,contrast_type,remark)VALUES(40,40,'2024-07-1519:56:01',1,"插入回收单40");大家可以思考一下，这个执行过程中都会加哪些锁？会发生锁等待吗？会发生死锁吗？2.3.2死锁排查上面执行第3步会锁等待，执行第4步会死锁。执行如下SQLSHOW ENGINE INNODB STATUS;它是MySQL中一个非常有用的命令，它用于显示InnoDB存储引擎的当前状态信息。这个命令对于诊断InnoDB存储引擎的问题、监控性能以及理解内部操作非常有帮助。输出的内容非常多，我们只关注锁信息就行，找到LATESTDETECTEDDEADLOCK最近一次死锁信息如下：2.3.3死锁日志分析现在让我们来分析这个死锁日志，我只会分析我们需要的信息。2.3.3.1事务A23087信息*** (1) "TRANSACTION": TRANSACTION 23087, ACTIVE 22 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 1 MySQL thread id 9, OS thread handle 123145459134464, query id 1039 localhost 127.0.0.1 root update INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )VALUES( 30, 30, '2024-07-15 "19":56:01', 1, "插入回收单30" ) 事务状态事务ID：23087操作：正在进行插入（INSERT）操作。锁等待情况锁结构数量：3个锁结构，这表明MySQL为该事务准备了多个锁来管理对数据的访问。行锁数量：2个行锁，说明事务试图在checkout_detail表中的两行数据上设置锁。事务阻塞LOCKWAIT：这表明事务正在等待其他事务释放锁。在当前情况下，它正在等待能够插入或更新它试图操作的两行数据。2.3.3.2事务23087持有锁*** (1) HOLDS THE "LOCK(S)": RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23087 lock_mode X  Record lock, heap no 1 HYSICAL "RECORD": n_fields 1; compact format; info bits 0  "0": len 8; hex 73757072656d756d; asc supremum;; 事务23087持有的锁是一个针对uniq_idx_recycle_order_id_contrast_type索引的X（排他）锁，但它实际上锁定的是索引中的“supremum”伪记录。这通常意味着事务并没有锁定任何具体的、存在的数据行，而是锁定了一个表示索引末尾的虚拟记录。2.3.3.3事务23087等待锁*** (1) WAITING FOR THIS LOCK TO BE "GRANTED": RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23087 lock_mode X insert intention waiting Record lock, heap no 1 HYSICAL "RECORD": n_fields 1; compact format; info bits 0  "0": len 8; hex 73757072656d756d; asc supremum;; 事务23087正在等待一个插入意向锁（lock_modeXinsertintentionwaiting）被授予2.3.3.4事务23088信息** (2) "TRANSACTION":TRANSACTION 23088, ACTIVE 14 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1MySQL thread id 10, OS thread handle 123145460199424, query id 1043 localhost 127.0.0.1 root updateINSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )VALUES( 40, 40, '2024-07-15 "19":56:01', 1, "插入回收单40" )事务状态事务ID：23088操作：正在进行插入（INSERT）操作。锁等待情况锁结构数量：3个锁结构，这表明MySQL为该事务准备了多个锁来管理对数据的访问。行锁数量：2个行锁，说明事务试图在checkout_detail表中的两行数据上设置锁。事务阻塞LOCKWAIT：这表明事务正在等待其他事务释放锁。在当前情况下，它正在等待能够插入或更新它试图操作的两行数据。2.3.3.5事务23088持有锁*** (2) HOLDS THE "LOCK(S)":RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23088 lock_mode XRecord lock, heap no 1 HYSICAL "RECORD": n_fields 1; compact format; info bits 0 "0": len 8; hex 73757072656d756d; asc supremum;;事务23088持有的锁是一个针对uniq_idx_recycle_order_id_contrast_type索引的X（排他）锁，但它实际上锁定的是索引中的“supremum”伪记录。这通常意味着事务并没有锁定任何具体的、存在的数据行，而是锁定了一个表示索引末尾的虚拟记录。2.3.3.6事务23088等待锁*** (2) WAITING FOR THIS LOCK TO BE "GRANTED":RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23088 lock_mode X insert intention waitingRecord lock, heap no 1 HYSICAL "RECORD": n_fields 1; compact format; info bits 0 "0": len 8; hex 73757072656d756d; asc supremum;;事务23088正在等待一个插入意向锁（lock_modeXinsertintentionwaiting）被授予3分析原因参考MySQL的官方文档。间隙锁（GapLocks）是一种特殊的锁机制，用于锁定索引记录之间的间隙，或者第一个索引记录之前的间隙以及最后一个索引记录之后的间隙。这种锁的主要目的是防止其他事务在这些间隙中插入新的记录，从而维护数据的一致性和隔离性。插入意向锁（InsertIntentionLocks）是InnoDB存储引擎在插入操作前设置的一种间隙锁（GapLocks）。这种锁的目的是在多个事务尝试向同一个索引间隙中插入不同位置的数据时，能够并行执行而不需要相互等待。可以得到索引如下加锁示意图索引上添加锁锁总是锁定索引记录。如果要锁定的是最后一条记录之后的区间，防止有人在这个区间插入数据，那么mysql就会锁定隐藏的最大记录索引记录关联的锁4解决方法1、查看死锁日志时，先看一下发生死锁的事务等待获取锁的语句，都有哪些语句发生死锁。2、根据死锁语句，找到相关到业务代码（如果有日志，直接根据日志找到业务代码也行）。3、根据业务代码执行流程，来分析死锁发生过程。（注意分析数据存在，数据不存在时的加锁区别）发现了问题原因，那么解决方案就很简单了。在这个场景下是：先查询数据是否存在，如果数据存在则更新，如果数据不存在再插入。5总结两个事务即使生成的间隙锁的范围是一样的，也不会发生冲突，因为间隙锁目的是为了防止其他事务插入数据，因此间隙锁与间隙锁之间是相互兼容的。在执行插入语句时，如果插入的记录在其他事务持有间隙锁范围内，插入语句就会被阻塞，因为插入语句在碰到间隙锁时，会生成一个插入意向锁，然后插入意向锁和间隙锁之间是互斥的关系。关于作者 黄培祖，采货侠JAVA开发工程师