数据库死锁排查思路分享（纠正篇）

最新推荐文章于 2024-07-31 09:08:05 发布

Mr小林

最新推荐文章于 2024-07-31 09:08:05 发布

阅读量425

点赞数 1

文章标签： java 数据库

原文链接：https://mp.weixin.qq.com/s/gKC9dyfyuORbAhqMjR0lqg

版权

来源：捡田螺的小男孩

前言

大家好，我是田螺。昨天发了一篇死锁思路排查的文章，发现我举的代码例子，跟实际的业务场景有一点出入。因为我个人做事情是比较严谨的，所以今天纠正一下，再发一次。

这篇文章，主要给大家讲讲数据库死锁的排查思路。

死锁现场
排查思路
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)其中的一个或者多个事务来解除死锁。

死锁是如何产生的

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

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

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

死锁排查思路

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

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

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

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

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-26 19:10:45', '2023-11-26 19:10:45', '2023-11-26 19:10:45', 1, 20000, 10025, '送给女嘉宾', null, null, '1000', 'flowNo666')

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

  <delete id="delByFLowNo">
    DELETE FROM gift_send_flow WHERE flow_no = #{flowNo}
    AND sender_id = #{sendId}
  </delete>

我们迁移的过程，涉及把原来记录删除掉，然后替换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> begin;
Query OK, 0 rows 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');

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