其他系列文章导航
文章目录
前言
为避免影响业务，应尽可能避免修改线上数据库的字段，因为这可能导致锁表并可能导致死锁。
但是如果数据库确实出现了死锁，就需要采取相应的排查思路来解决问题，以恢复业务的正常运行。
本文将分享一些有关数据库死锁排查的思路和方法，以便在出现问题时能够有足够的把握解决它们。
一、死锁场景现场
模拟场景：对用户数据进行迁移。
把业务礼物表A的数据删除，然后修改用户ID后，然后插入到礼物B表。其中，A表和B表，表示同一个礼物逻辑表下的不同分表。
CREATE TABLE `gift` ( `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`) )
在进行礼物流水表数据迁移的过程中，出现了 死锁等待超时 的场景。
编辑
从日志可以看出，是在执行礼物赠送流水表删除的时候，阻塞等待，最后锁等待超时了。出现这种情况，一般都是因为产生了死锁。
既然是死锁，为什么出现的却是
Lock wait timeout exceeded; try restarting transaction锁等待超时这个日志呢？这是因为在Innodb存储引擎中，当检测到死锁时，它会尝试自动解决死锁问题，通常是通过回滚(rollback)其中的一个或者多个事务来解除死锁。
二、死锁是如何产生的
死锁产生的条件包括:
- 互斥条件：至少有一个资源是独占的，即一次只能被一个进程或线程使用。
- 持有和等待条件：一个进程或线程可以持有一个资源，并等待其他进程或线程持有的资源。
- 非抢占条件：已经分配给一个进程或线程的资源不能被强制性地抢占，只能由持有资源的进程或线程显式释放。
- 循环等待条件：一系列进程或线程形成循环等待其他进程或线程持有的资源。
三、死锁排查思路
死锁的排查思路：
- 用
show engine innodb status，查看最近一次死锁日志。 - 分析死锁日志，找到关键词
TRANSACTION。 - 分析死锁日志，查看正在执行的SQL。
- 看它持有什么锁，等待什么锁。
顺着这个排查思路，我们先复现这个死锁案例。在删除礼物赠送流水表阻塞等待的过程，执行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的时候，也需要获取这个锁，因此形成死锁循环等待。
五、死锁的解决方案
因为并发执行删除和插入同一个表，因此形成死锁。
死锁的方案解决方案有:
- 避免循环等待：保证资源分配的有序性，例如，定义一个全局的资源申请顺序，并要求所有进程按照这个顺序申请资源。这样可以避免循环等待的情况。
- 资源有序性：按照固定的顺序获取资源，避免多个进程在不同的顺序下请求资源，导致循环等待的情况。
- 超时机制：当一个进程无法获取所需资源时，设置一个超时机制，超过一定时间后放弃等待的资源并释放自己所持有的资源，避免长时间等待。
回到本文的案例，那就是 迁移数据的时候控制有序性，串行执行就好。
