分析处理遇到的Mysql死锁问题

luffylv

已于 2022-03-17 09:42:49 修改

阅读量626

点赞数 3

分类专栏： Mysql 文章标签： mysql 数据库索引 sql

于 2021-06-29 22:20:23 首次发布

本文链接：https://blog.csdn.net/m0_47503416/article/details/118306731

版权

Mysql 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

上周遇到线上死锁问题，特此总结记录一下，该情况只是某一特定场景下，遇到死锁问题还要具体问题具体分析。

场景

上周末，连续两天线上的某每小时执行一次的定时任务A在凌晨两点左右报了死锁异常（每天出现了四次死锁）。异常信息如下：

初步分析

首先，出现死锁的这个表做了分库分表，根据编号进行分片。四台服务器同时执行该定时任务，且根据某编号进行分片，也就是说不会有两个及以上该定时任务应用同时操作同一张表，正常来说应该不会出现死锁的情况。初步怀疑可能是别的也在凌晨两点执行的分片定时任务B也操作了该表，导致A与B并发操作该表产生了死锁。

分析死锁日志

通过初步分析还不能直接定位到具体是什么任务，什么sql，为什么导致的死锁。让DBA帮忙取到数据库死锁相关日志进行分析，部分日志如下：

2021-06-27T02:00:36.182010+08:00 9439 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

2021-06-27T02:00:36.182045+08:00 9439 [Note] InnoDB: 

*** (1) TRANSACTION:


TRANSACTION 71027261, ACTIVE 0 sec fetching rows

mysql tables in use 3, locked 3

LOCK WAIT 98 lock struct(s), heap size 24784, 7361 row lock(s), undo log entries 8

MySQL thread id 9969, OS thread handle 140518545692416, query id 397648075 XX.XX.XX.XX XX_XX updating

UPDATE group_rebate_11

         SET person_activity_no = 'AC000000011' 

         WHERE  group_no = 'FP00005211'

                AND order_time BETWEEN '2021-06-17 00:00:00.0'

                AND '2021-06-18 23:59:59.0'

                        AND person_activity_no = ''       

            AND income_type = 3    

                AND refund_dt < '2021-06-26 23:59:59'

2021-06-27T02:00:36.182119+08:00 9439 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:


RECORD LOCKS space id 357 page no 204 n bits 168 index PRIMARY of table `group_manage_02`.`group_rebate_11` trx id 71027261 lock_mode X locks rec but not gap waiting

Record lock, heap no 42 PHYSICAL RECORD: n_fields 29; compact format; info bits 0


2021-06-27T02:00:36.183271+08:00 9439 [Note] InnoDB: *** (2) TRANSACTION:


TRANSACTION 71027274, ACTIVE 0 sec fetching rows

mysql tables in use 1, locked 1

18 lock struct(s), heap size 1136, 617 row lock(s)

MySQL thread id 9439, OS thread handle 140518577907456, query id 397648124 XX.XX.XX.XX XX_XX updating

UPDATE group_rebate_11

         SET to_cash_out_time = '2021-06-27 02:00:36.178',
           
                income_status = 2 

         WHERE  group_no = 'FP00014711'

                AND income_status = 1
 

            AND check_time IS NOT NULL

            AND rebate_flag = 1
     
                AND DATE_ADD(check_time,INTERVAL 168 DAY_HOUR) <=

                '2021-06-27 02:00:36.178'

2021-06-27T02:00:36.183342+08:00 9439 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):


RECORD LOCKS space id 357 page no 204 n bits 168 index PRIMARY of table `group_manage_02`.`group_rebate_11` trx id 71027274 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 29; compact format; info bits 0


Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0


Record lock, heap no 4 PHYSICAL RECORD: n_fields 29; compact format; info bits 0


Record lock, heap no 5 PHYSICAL RECORD: n_fields 29; compact format; info bits 0


Record lock, heap no 100 PHYSICAL RECORD: n_fields 29; compact format; info bits 0


Record lock, heap no 101 PHYSICAL RECORD: n_fields 29; compact format; info bits 0


Record lock, heap no 102 PHYSICAL RECORD: n_fields 29; compact format; info bits 0


2021-06-27T02:00:36.263639+08:00 9439 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:


RECORD LOCKS space id 357 page no 2177 n bits 120 index PRIMARY of table `group_manage_02`.`group_rebate_11` trx id 71027274 lock_mode X locks rec but not gap waiting

Record lock, heap no 37 PHYSICAL RECORD: n_fields 29; compact format; info bits 0

2021-06-27T02:00:36.264646+08:00 9439 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

通过上面日志可以看出，确实是2ms内有两个事务同时操作同一张表导致了死锁。

先看事务1：

2021-06-27T02:00:36.182119+08:00 9439 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:


RECORD LOCKS space id 357 page no 204 n bits 168 index PRIMARY of table `group_manage_02`.`group_rebate_11` trx id 71027261 lock_mode X locks rec but not gap waiting

事务1等待`group_manage_02`.`group_rebate_11`表上主键索引的X锁，也就是行锁，没有gap锁。

再看事务2：

2021-06-27T02:00:36.183342+08:00 9439 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):


RECORD LOCKS space id 357 page no 204 n bits 168 index PRIMARY of table `group_manage_02`.`group_rebate_11` trx id 71027274 lock_mode X locks rec but not gap


2021-06-27T02:00:36.263639+08:00 9439 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:


RECORD LOCKS space id 357 page no 2177 n bits 120 index PRIMARY of table `group_manage_02`.`group_rebate_11` trx id 71027274 lock_mode X locks rec but not gap waiting

事务2持有`group_manage_02`.`group_rebate_11`表上主键索引的X锁，也就是行锁，没有gap锁。

通过上面日志可以看出，事务1通过主键索引要给某个记录加X锁，但是这个记录已经被事务2锁住了，事务1等待事务2释放该锁；而事务2也通过主键索引要给另一个记录加X锁，但是这个记录已经被事务1锁住了，事务2等待事务1释放该锁。这样事务1，事务2相互等待对方释放锁，所以产生了死锁。以下图为例说明此时导致死锁的原因。

事务1和事务2并发的给group_rebate_11表中满足条件的记录加锁，但是事务1和事务2需要加锁的记录存在了交集（即记录8,9,10,11），此时事务1已经给记录1,2,3,9加了X锁，事务2给记录5,6,7,8加了X锁。接下来事务1要给记录8加锁，事务2要给记录9加锁，但这两条记录已被对方加了锁，两个事务相互等待对方释放锁，此时产生了死锁。

通过日志最后一行可知Mysql回滚了事务2，释放了事务2持有的X锁，让事务1继续执行。这与应用抛出的异常一直。

2021-06-27T02:00:36.264646+08:00 9439 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

进一步分析SQL

通过上面的分析，知道了两个事务批量更新时锁住了相同的某些记录，存在资源竞争导致了死锁，接下来看一下日志中的sql，分析下为什么会出现这种情况。

事务1的sql：

UPDATE group_rebate_11 SET person_activity_no = 'AC000000011' 
WHERE  group_no = 'FP00005211'
AND order_time BETWEEN '2021-06-17 00:00:00.0'
AND '2021-06-18 23:59:59.0'
AND person_activity_no = ''
AND income_type = 3
AND refund_dt < '2021-06-26 23:59:59';

事务2的sql：

UPDATE group_rebate_11 SET to_cash_out_time = '2021-06-27 02:00:36.178',income_status = 2 
WHERE  group_no = 'FP00014711' 
AND income_status = 1
AND check_time IS NOT NULL
AND rebate_flag = 1
AND DATE_ADD(check_time,INTERVAL 168 DAY_HOUR) <= '2021-06-27 02:00:36.178';

首先需要说明的是，以上sql的查询条件中的group_no，order_time ，person_activity_no，income_type，refund_dt，check_time字段上均建了普通索引。

Mysql的InnoDB的行锁是基于索引的。

事务2的加锁过程是先找到group_no='FP00014711' 的二级索引并加X锁，二级索引中记录的是主键索引，然后去给上一步的聚簇索引加锁。这个过程中加了两次锁。

那么事务1使用了哪些索引呢，请DBA查看事务1中的sql的执行计划，如下：

事务1走的idx_order_time索引，加锁过程是先找到order_time 在 '2021-06-17 00:00:00.0'
和'2021-06-18 23:59:59.0'之间的二级索引并加X锁，然后去给上一步的聚簇索引加锁。并没有通过group_no加锁。

问题就出在这，通过SELECT * FROM group_rebate_11 WHERE group_no='FP00014711' AND order_time BETWEEN '2021-06-17 00:00:00.0' AND '2021-06-18 23:59:59.0';发现有两条记录。也就是说，有两条group_no为FP00014711的记录的order_time在 '2021-06-17 00:00:00.0'和 '2021-06-18 23:59:59.0'之间。事务1因为是通过order_time加的锁，查找到的二级索引的记录中有group_no为FP00014711的两条记录产生了交集。从而导致的死锁，这明显不是想要的结果。

解决方式

通过以上的分析，此次死锁明显是因为索引使用不合理所致。解决当前死锁问题的方式有如下几种：

1、错峰，修改其中一个定时任务的执行时间，避免这两个定时任务同时执行。

2、创建组合索引KEY `idx_group_order` (`group_no`,`order_time`) USING BTREE。避免事务1在加锁的时候扩大范围，给其他group_no的记录加锁。

3、先通过select语句查找到到需要更新的记录的id，然后通过id去更新。缺点是更加IO次数，造成资源浪费。

4、事物1更新时强制使用索引idx_group_no，即FORCE INDEX(idx_group_no)，避免扩大锁的范围。缺点时强制索引使用是写死在sql中，索引有变化时需同步修改sql。

最后采用的是1+2解决方案。

第一次遇到并解决Mysql死锁问题，如上文中有错误之处烦请指出。互相学习，共同进步。

luffylv

关注

3
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
分析处理遇到的Mysql死锁问题

上周遇到线上死锁问题，特此总结记录一下，该情况只是某一特定场景下，遇到死锁问题还要具体问题具体分析。场景上周末，连续两天线上的某每小时执行一次的定时任务A在凌晨两点左右报了死锁异常（每天出现了四次死锁）。异常信息如下：初步分析首先，出现死锁的这个表做了分库分表，根据编号进行分片。四台服务器同时执行该定时任务，且根据某编号进行分片，也就是说不会有两个及以上该定时任务应用同时操作同一张表，正常来说应该不会出现死锁的情况。初步怀疑可能是别的也在凌晨两点执行的分片定时任务B也操作了该表，导致A.
复制链接

扫一扫