MYSQL出现死锁的排查过程

MYSQL出现死锁如何进行排查？

问题描述
线上异常报错通知
显示一条插入语句遇见了事务回滚异常

回滚原因显示是死锁，从而造成mysql自动回滚了其中一个事务
被回滚的事务永远是开销相对最小，占据的锁资源相对最少的一个

回顾死锁发生的条件
MySQL死锁的发生需同时满足以下四个必要条件，这些条件源自数据库事务对资源的竞争与调度机制：

互斥条件（Mutual Exclusion）
事务A锁定了表A的某一行，事务B无法同时锁定该行，必须等待事务A释放锁。
占有且等待（Hold and Wait）
事务A锁定表A的行1，随后尝试锁定表B的行1（被事务B持有），事务A阻塞但保持对行1的锁。
事务B锁定表B的行1，随后尝试锁定表A的行1（被事务A持有），事务B阻塞但保持对行1的锁。
此时形成循环等待，导致死锁。
不剥夺条件（No Preemption）
事务A持有的行锁不会被MySQL内核强制移除，即使事务B因等待超时或系统资源不足，也只能等待事务A提交或回滚。
循环等待条件（Circular Wait）
事务1：锁定表A → 请求锁定表B（被事务2持有）。
事务2：锁定表B → 请求锁定表A（被事务1持有）。
此时事务1和事务2互相等待，形成死锁。

找到死锁发生在哪
找到死锁发生的具体sql，是哪两个SQL之间出现了锁竞争？
利用MYSQL的SHOW ENGINE INNODB STATUS命令
这个命令会查询出最近的一次死锁的详细信息

mysql -u用户名 -p密码 -e "SHOW ENGINE INNODB STATUS\\G" > 死锁.txt

命令行执行以上命令，即可导出最后一次死锁详情日志

阅读死锁.txt
找到LATEST DETECTED DEADLOCK字段下的内容

事务1（INSERT操作）
可以看到第一个sql，insert的sql，也就是最开始报错通知里的sql

这个日志中，得知
事务一已持有锁：
主键索引（PRIMARY）：X锁（排他锁），锁定记录物理位置space id 1953 page no 2713 n bits 192，具体记录包含user_id=940619、friend_id=221846等字段值。
行锁数量：2个行锁（2 row lock(s)）。
等待锁：
索引im_user_friend_index_userId：插入意向锁（lock_mode X insert intention），等待space id 1953 page no 2356的锁资源。

事务2（delete操作）
紧接着往下翻阅出现第二个delete的SQL

事务2已持有锁：
索引im_user_friend_index_userId：S锁（共享锁），锁定page no 2356的多个记录（如heap no 2到heap no 40），涉及user_id字段值（如80000000000e576f、80000000000e5772等）。
行锁数量：701,503个行锁（701503 row lock(s)），显示大规模数据操作。
锁结构：7,736个锁结构（7736 lock struct(s)），占用堆大小约876KB（heap size 876752）。
原来是这个sql占据了大量锁，导致死锁也不意外了。
可能原因：
MySQL在处理包含子查询的DELETE语句时，可能会将子查询结果集物化为临时表。在这个过程中，为了确保数据的一致性，MySQL可能会对源表（如im_user_friend）中的行加锁，即使这些行最终不会被删除，并且在可重复读隔离级别下，MySQL为了防止幻读，在执行删除语句时，不仅会对符合条件的数据行加锁，还会对索引扫描过程中经过的间隙加锁。即使最终没有删除任何行，这些间隙锁仍然会被持有直到事务结束。所以删除操作持有了整个表所有的行锁，此时再插入就会出现锁资源竞争

解决办法
将子查询删除语句拆分
原语句：

 delete from im_user_friend where  user_id in (select  t.user_id from (select f.user_id from im_user_friend f left join im_user u on f.user_id=u.id where u.id is NULL) t)

拆分后：

 -- 第一步：查询
SELECT t.user_id FROM (
    SELECT f.user_id FROM im_user_friend f 
    LEFT JOIN im_user u ON f.user_id = u.id 
    WHERE u.id IS NULL
) t;

-- 第二步：根据查询结果删除 (假设结果为空，所以执行下面这句)
DELETE FROM im_user_friend WHERE user_id IN (); -- 实际上是空的

原语句（单条DELETE）：

执行时，会对子查询中扫描到的行加共享锁（S锁），并且还会加上间隙锁。

可能由于子查询物化临时表时，会扫描并且锁定整个表（或者大部分索引），导致锁住了很多间隙和行。
间隙锁里就包含了即将要插入的行！！

此时，如果另一个事务也要插入数据，并且插入的位置被这些间隙锁阻塞，那么就会发生等待。如果两个事务互相等待，就会死锁。

原语句执行时，会对子查询扫描的行加共享锁和间隙锁。由于子查询扫描了整个表或大部分索引，它锁定了许多间隙，包括最后一个记录之后的间隙（通过supremum锁）。
新插入的行的id是递增的，因此通常会插入到索引的末尾，这正是被supremum锁锁定的区域，所以更容易被阻塞。这意味着任何试图插入新行的事务都会被阻塞，因为插入需要获取插入意向锁，而插入意向锁与已有的间隙锁冲突。

如果两个事务一个执行DELETE另一个执行INSERT，它们互相等待对方的锁释放，从而导致死锁。

拆分后：

第一步SELECT是快照读，不会加锁。

第二步DELETE时，因为IN列表是空的，所以不会扫描任何行，因此不会加任何锁（包括行锁和间隙锁）。即使有数据，也不会全表加锁，间隙锁只会很小概率的锁住即将插入的行

所以，不会阻塞其他事务的插入操作，从而避免了死锁。

MYSQL出现死锁的排查过程

评论 (0)