数据库死锁了，怎么办？

前言

在公司中，我反复跟我的后端开发同事们强调：尽量不要改动线上数据库的字段，因为会触发锁表影响业务，严重时还可能出现死锁！

可有同学偏偏不信，就是头铁，导致数据库真的出现了死锁，业务全挂了。

这种时候应该怎么办呢？

本文就给大家分享一下数据库死锁的排查思路，万一出了问题，也有底气去解决。

本文大纲：

• 死锁现场
• 死锁是如何产生的
  
• 排查思路
• sql模拟
• 死锁解决方案

死锁场景现场

先模拟一个业务场景：对用户数据进行迁移

类似这样：

把业务礼物表A的数据删除，然后修改用户ID后，然后插入到礼物B表。其中，A表和B表，表示同一个礼物逻辑表下的不同分表。

表结构：

CREATE TABLE `gift_send_flow_0` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sender_id` int DEFAULT NULL COMMENT '赠送者ID',
  `gift_type` varchar(50) NOT NULL COMMENT '礼物类型',
  `gift_id` varchar(50) NOT NULL COMMENT '礼物ID',
  `gift_name` varchar(100) NOT NULL COMMENT '礼物名称',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  `gift_send_time` datetime DEFAULT NULL COMMENT '礼物赠送时间',
  `quantity` int DEFAULT NULL COMMENT '礼物数量',
  `receiver_id` int DEFAULT NULL COMMENT '接收者ID',
  `message` text COMMENT '消息',
  `status` varchar(20) DEFAULT NULL COMMENT '状态',
  `expiry_time` datetime DEFAULT NULL COMMENT '过期时间',
  `channel_no` varchar(50) DEFAULT NULL COMMENT '渠道',
  `flow_no` varchar(50) NOT NULL COMMENT '流水号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unique_flow_no` (`flow_no`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

在进行礼物流水表数据迁移的过程中，出现了 死锁等待超时 的场景。

从日志可以看出，是在执行礼物赠送流水表删除的时候，阻塞等待，最后锁等待超时了。出现这种情况，一般都是因为产生了死锁。

有些小伙伴觉得很奇怪：

既然是死锁，为什么出现的却是Lock wait timeout exceeded; try restarting transaction 锁等待超时这个日志呢？这是因为在Innodb存储引擎中，当检测到死锁时，它会尝试自动解决死锁问题，通常是通过回滚(rollback)其中的一个或者多个事务来解除死锁。

死锁是如何产生的

既然是死锁问题，我们先来回顾一下死锁产生的条件。死锁是多个进程或线程因竞争有限的资源而发生的一种相互等待的状态，使得每个进程或线程都无法继续执行。死锁产生的条件包括:

• 互斥条件：至少有一个资源是独占的，即一次只能被一个进程或线程使用。
• 持有和等待条件：一个进程或线程可以持有一个资源，并等待其他进程或线程持有的资源。
• 非抢占条件：已经分配给一个进程或线程的资源不能被强制性地抢占，只能由持有资源的进程或线程显式释放。
• 循环等待条件：一系列进程或线程形成循环等待其他进程或线程持有的资源。

站在数据库的角度，死锁的表现如下：

死锁排查思路

死锁的排查思路是怎样的呢？我一般是这么排查的。

1. 用show engine innodb status，查看最近一次死锁日志。
2. 分析死锁日志，找到关键词TRANSACTION
3. 分析死锁日志，查看正在执行的SQL
4. 看它持有什么锁，等待什么锁。

顺着这个排查思路，我们先复现这个死锁案例。在删除礼物赠送流水表阻塞等待的过程，执行show engine innodb status命令，查看事务和锁的信息。

通过日志，可以看到这个事务正在执行的SQL是：

DELETE FROM gift_send_flow_0 WHERE flow_no = 'flowNo666' AND sender_id = 10000

它在等待一个idx_unique_flow_no的排他行锁。那么到底是什么SQL持有了这个锁，导致它阻塞等待呢，这时候，我们联系上下文代码，把操作这个表相关的插入或者修改、删除的SQL都梳理一下，最后发现是一条插入的SQL涉及到：

 <insert id="insertGiftSendFlow" parameterType="com.example.demo.generate.GiftSendFlowTab">
    INSERT INTO gift_send_flow (id,gift_type, gift_id, gift_name, created_time, updated_time,
                                gift_send_time, quantity, sender_id, receiver_id, message, status
                                , expiry_time, channel_no,flow_no)
    VALUES (#{id},#{giftType}, #{giftId}, #{giftName}, #{createdTime}, #{updatedTime},
            #{giftSendTime}, #{quantity}, #{senderId}, #{receiverId}, #{message}, #{status}, #{expiryTime}
            , #{channelNo},#{flowNo})
  </insert>

我们迁移的过程，涉及把原来记录删除掉，然后替换senderId，再执行插入。基本确定就是删除和插入的SQL形成的死锁。我们再来本地模拟这两条SQL的并发执行。

sql模拟死锁复现

先开启一个事务A，执行删除，插入：

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> DELETE FROM gift_send_flow_0 WHERE flow_no = 'flowNo666' AND sender_id = 10000;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO gift_send_flow_0 (id,gift_type, gift_id, gift_name, created_time, updated_time, gift_send_time, quantity, sender_id, receiver_id, message, status , expiry_time, channel_no,flow_no) VALUES (null,'虚拟', '1', '玫瑰花', '2023-11-21 22:57:28', '2023-11-21 22:57:28', '2023-11-21 22:57:28', 1,  170000, 10025, '送给女嘉宾', NULL , NULL,'1000','flowNo666');
Query OK, 1 row affected (0.01 sec)

另开一个事务，再执行删除、插入，发现在执行删除的时候，就进入了阻塞等待。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM gift_send_flow_0 WHERE flow_no = 'flowNo666' AND sender_id = 10000;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

通过show engine innodb status查看死锁日志：

发现这个跟我们代码跑的一模一样。为了进一步验证，可以通过这个命令（MySQL 8.0+）查看SQL加锁情况：SELECT * FROM performance_schema.data_locks\G;

可以得出结果，INSERT语句的时候，持有了唯一索引的排他行锁，然后DELETE的时候，也需要获取这个锁，因此形成死锁循环等待。

死锁解决方案

因为并发执行删除和插入同一个表，因此形成死锁。

死锁的方案解决方案有:

• 避免循环等待：保证资源分配的有序性，例如，定义一个全局的资源申请顺序，并要求所有进程按照这个顺序申请资源。这样可以避免循环等待的情况。
• 资源有序性：按照固定的顺序获取资源，避免多个进程在不同的顺序下请求资源，导致循环等待的情况。
• 超时机制：当一个进程无法获取所需资源时，设置一个超时机制，超过一定时间后放弃等待的资源并释放自己所持有的资源，避免长时间等待。

回到本文的案例，那就是 迁移数据的时候控制有序性，串行执行就好。

最后

本文这个例子呢，是根据工作实际模拟出来的一个案例，主要就是给大家分享死锁的排查思路。希望对大家有帮助~

👇🏻 点击下方阅读原文，获取鱼皮往期编程干货。

往期推荐