数据库死锁排查思路分享

站长

2024年03月19日 21:21 · 阅读数 5

前言

大家好，我是田螺。本文跟大家讲讲数据库死锁的排查思路。耐心看完哦，非常有用的。

死锁现场
排查思路
sql模拟
死锁解决方案

公众号：捡田螺的小男孩 （有田螺精心原创的面试PDF）
github地址，感谢每颗star：github

死锁场景现场

业务场景类似就是这样：做用户的数据迁移，酱紫：

把业务礼物表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查看死锁日志：

数据库死锁排查思路分享

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

数据库死锁排查思路分享

可以发现，当执行删除SQL的时候，会给唯一索引 idx_unique_flow_no加一个排他锁。那就奇怪了，我们从刚才日志可以发现，插入SQL等待的是一个idx_unique_flow_no的读共享锁。为啥会冲突呢？其实是因为读共享锁跟排他锁是冲突的：

数据库死锁排查思路分享

可以得出结果，delete语句的时候，持有了唯一索引的排他行锁，然后insert的时候，也需要获取这个索引的读共享锁，因此形成死锁。

死锁解决方案

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

死锁的方案解决方案有:

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

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

最后

本文这个例子呢，是我模拟的一个案例出来，主要就是给大家分享死锁的排查思路。希望对大家有帮助哈。感兴趣的伙伴，可以关注我的公众号:捡田螺的小男孩