MySQL 死锁日志分析方法

京东云开发者
• 阅读 38

作者:京东物流 张凯

引言

MySQL 死锁是线上经常遇到的现象,但是死锁分析却并不总是件容易的事情,本文介绍 MySQL 死锁日志的分析方法,帮助研发从日志中快速提取有效信息,从而提高死锁原因分析的效率。



死锁介绍

触发条件

死锁的触发条件包括四个:

•互斥

•占有且等待

•不可抢占用

•循环等待

如下图所示,两个事务加锁顺序不同导致死锁

 MySQL 死锁日志分析方法



发生死锁后只需要破坏发生死锁四个条件中的任意一个条件就可以解除死锁状态。数据库层面有两种策略用于打破死锁状态:

•被动,设置事务等待锁的超时时间,事务锁等待超时后自动回滚。默认 50 秒;

•主动,开启主动死锁检测,检测到死锁后回滚其中一个事务。默认开启。

其中默认使用第二种策略,也就是检测到死锁后立即回滚,从而解除死锁状态。因此发生死锁时业务可能报错死锁,但不会报错锁等待超时。



死锁检测

innodb_deadlock_detect 参数用于控制是否开启死锁检测,该参数是 5.7.15 中引入。

mysql>select@@innodb_deadlock_detect;
+--------------------------+
|@@innodb_deadlock_detect|
+--------------------------+
|                        1|
+--------------------------+
1rowinset(0.00 sec)

死锁检测本质上是一个搜索问题,5.7 中使用深度优先算法实现,具体是判断锁等待关系图中是否有环。

高并发场景下可以考虑关闭死锁检测,原因是如果锁等待队列很长,死锁检测成本高,会导致实例性能下降。但是前提是应用层面可以避免死锁,因此通常不建议关闭。



下面通过介绍一个死锁案例对死锁日志的格式与分析方法有一个感性认识。



死锁案例

日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-04-14 08:07:05 0x7fb6d39a6700
*** (1) TRANSACTION:
TRANSACTION 13020605130, ACTIVE 25 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 33 lock struct(s), heap size 3520, 33 row lock(s), undo log entries 34
MySQL thread id 2343498932, OS thread handle 140424015394560, query id 28769967039 x.x.x.x xwms_rw updating
UPDATE stock_occupy
        SET update_time = NOW()
        ,update_user = 'WAPS'
        ,qty_out_occupy=qty_out_occupy + 12.0000
        WHERE map_area_id = 608
        AND goods_no='EMG4418433215231'
        AND owner_no='0'
        AND lot_no='-1'
        AND product_level='100'         
            AND org_no = '10'
            AND distribute_no = '10'
            AND warehouse_no = '126'
            AND map_area_id = 608
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 127 page no 5255 n bits 272 index idx_map_goods_product_lot_owner of table `xwms`.`stock_occupy` trx id 13020605130 lock_mode X locks rec but not gap waiting
Record lock, heap no 53 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000260; asc        `;;
 1: len 16; hex 454d4734343138343333323135323331; asc EMG4418433215231;;
 2: len 3; hex 313030; asc 100;;
 3: len 2; hex 2d31; asc -1;;
 4: len 1; hex 30; asc 0;;
 5: len 8; hex 8000000000042de4; asc       - ;;

*** (2) TRANSACTION:
TRANSACTION 13020606128, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 8
MySQL thread id 2343006037, OS thread handle 140423210886912, query id 28769967052 x.x.x.x xwms_rw updating
UPDATE stock_occupy
        SET update_time = NOW()
        ,update_user = 'WAPS'
        ,qty_out_occupy=qty_out_occupy + 11.0000
        WHERE map_area_id = 608
        AND goods_no='EMG4418442253742'
        AND owner_no='0'
        AND lot_no='-1'
        AND product_level='100'
            AND org_no = '10'
            AND distribute_no = '10'
            AND warehouse_no = '126'
            AND map_area_id = 608
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 127 page no 5255 n bits 272 index idx_map_goods_product_lot_owner of table `xwms`.`stock_occupy` trx id 13020606128 lock_mode X locks rec but not gap
Record lock, heap no 53 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000260; asc        `;;
 1: len 16; hex 454d4734343138343333323135323331; asc EMG4418433215231;;
 2: len 3; hex 313030; asc 100;;
 3: len 2; hex 2d31; asc -1;;
 4: len 1; hex 30; asc 0;;
 5: len 8; hex 8000000000042de4; asc       - ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 127 page no 5276 n bits 240 index idx_map_goods_product_lot_owner of table `xwms`.`stock_occupy` trx id 13020606128 lock_mode X locks rec but not gap waiting
Record lock, heap no 38 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000260; asc        `;;
 1: len 16; hex 454d4734343138343432323533373432; asc EMG4418442253742;;
 2: len 3; hex 313030; asc 100;;
 3: len 2; hex 2d31; asc -1;;
 4: len 1; hex 30; asc 0;;
 5: len 8; hex 8000000000044335; asc       C5;;

*** WE ROLL BACK TRANSACTION (2)

其中:

•加锁索引相同,都是二级索引;

•两个事务中三个锁对应两个主键,包括 8000000000044335(279349)/ 8000000000042de4(273892);

•binlog 中显示提交事务也就是事务 1 中先后 update 279349 与 273892,因此判断死锁原因是交叉更新。



表结构

  `id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'自增id',
  `map_area_id`bigint(20)NOTNULLCOMMENT'地图区域ID',
  `goods_no`varchar(50)NOTNULLCOMMENT'商品编号',
  `product_level`varchar(50)NOTNULLCOMMENT'商品等级',
  `lot_no`varchar(50)NOTNULLCOMMENT'批次号',
  `owner_no`varchar(50)NOTNULLCOMMENT'货主编号',
  PRIMARYKEY(`id`),
  UNIQUEKEY`idx_map_goods_product_lot_owner`(`map_area_id`,`goods_no`,`product_level`,`lot_no`,`owner_no`)

其中:

•加锁索引是二级联合唯一索引;

•update 根据二级唯一索引更新非索引字段,因此执行时具体原地更新主键索引,二级索引不变,且加锁类型是 X 型 record lock;

•综合以上信息,判断死锁原因是两个事务交叉更新同一张表的两行数据导致死锁。



下面介绍如何从死锁日志中获取有效信息,并分析其中最重要的信息-锁,包括锁的类型、不同类型锁的兼容性、常见加锁规则。



死锁分析方法

日志格式

简化后的死锁日志格式如下所示。

InnoDB:***(1)TRANSACTION:
InnoDB:***(1) WAITING FOR THIS LOCKTO BE GRANTED:
InnoDB:***(2)TRANSACTION:
InnoDB:***(2) HOLDS THE LOCK(S):
InnoDB:***(2) WAITING FOR THIS LOCKTO BE GRANTED:
InnoDB:*** WE ROLL BACK TRANSACTION(1)

其中主要信息包括:

•两个事务

•两条 SQL

•三部分锁信息

其中存在的问题包括:

•两个事务有等锁 SQL,没有可能存在的持锁 SQL;

•事务 1 缺少持锁类型,8.0 中已提供;

•SQL 超长时自动截断;

•加锁行数据是十六进制,因此需要根据字段的数据类型转换成对应格式,比如十进制或字符串。

其中前两种信息的缺失直接导致死锁分析的难度增大,因此死锁原因分析通常需要反推来处理,也就是从等锁类型判断持锁类型。



缺少部分可以参考以下分析方法:

•binlog,可以获取提交事务中已执行的 SQL 以及可能存在的更新前的记录;

•general log,可以获取提交事务与回滚事务中已执行的 SQL,包括已执行无更新的操作,比如删除不存在的记录。



锁信息

MySQL 中锁的粒度包括实例、表、行,其中后两种都可能导致死锁,本文假设都是行粒度,也就是行锁。

注意行锁是给表的索引的记录加锁,且是给访问过的对象加锁。

死锁日志中与锁相关的信息包括:

•锁所属表,比如分区表与非分区表的加锁规则不同;

•锁所属索引,比如唯一键与非唯一键的加锁规则不同;

•锁类型,其中不同类型锁的兼容性不同;

•锁定数据行,其中:

◦不同行的加锁类型可能不同,比如右边界记录(supremum pseudo-record)的 next-key lock 无法退化;

◦数据行是否标记删除可能影响到后续加锁,一个字节中的第六位表示是否标记删除(info bits),因此十进制 32 表示标记删除。比如二级唯一索引的唯一性检查时如果发现冲突行已标记删除,将循环给下一行加锁直到数据不冲突。

当然也有其他因素影响加锁的类型,主要包括:

•数据库版本,比如 5.7.26 中针对 replace / insert duplicate 语句的加锁进行优化,唯一键不冲突时不加间隙锁;

•事务隔离级别,比如 RC 中没有间隙锁;

这些信息都可以认为是死锁案例的特征,其中锁类型是最重要的特征



锁类型

锁类型(type_mode)主要包括以下三部分信息:

•lock_mode,表示锁的模式,包括 IS、IX、S、X、AUTO_INC;

•lock_type,表示锁的粒度,包括 RECORD 与 TABLE,对应行锁与表锁;

•rec_lock_type,表示行锁的类型,包括 record lock、gap lock、next-key lock、insert intention lock。其中:

◦gap lock 是事务隔离级别 RR 中为解决幻读引入的锁类型;

◦insert intention lock 是一种特殊的 gap lock,表示插入的意向,用于在插入操作存在 gap lock 时表示等待状态。



比如死锁日志中锁类型显示 lock_mode X locks rec but not gap waiting,其中:

•lock_mode = X

•lock_type = RECORD

•rec_lock_type = record lock

•lock_status = WAITING

注意锁的状态分两种,包括已获取到(GRANTED)与等待中(WAITING)。



死锁由两组锁等待组成,锁等待发生在锁冲突时,锁冲突根据锁兼容矩阵判断,下面介绍锁兼容矩阵。



锁兼容矩阵

不同类型行锁的兼容性见下表,其中第一行表示已有的锁,第一列表示要加的锁,❌ 表示锁冲突。

锁类型 record gap next-key insert intention
record
gap
next-key
insert intention

其中:

•insert intention 不影响其他事务加任何类型的锁;

•gap lock 只和 insert intention 冲突,用于防止其他事务在间隙中插入记录导致幻读,与其他锁不冲突;

如果已有的锁是等待状态,要加的锁与该锁冲突,要加的锁同样会发生锁等待。



常见加锁规则

加锁场景:

•查询(数据定位),不是 MVCC,加锁读,包括回表加锁;

•更新,下面是部分场景与对应加锁类型:

◦为防止脏写,record lock;

◦为防止幻读,gap lock;

◦为防止唯一键冲突,next-key lock。

加锁类型:

•显式锁;

•隐式锁,比如 insert、update、delete 语句在没有锁冲突时不加显式锁,必要时转换成显式锁。



加锁的单位是 next-key lock,部分场景下会发生退化,其中:

•退化为 record lock:

◦唯一索引上的等值查询;

•退化为 gap lock:

◦非唯一索引的等值查询向右遍历到第一个不满足等值条件的记录;

不退化的场景:

•supremum pseudo-record;

•insert duplicate / replace 语句中根据唯一键定位数据;

•分区表,低于 5.7.23 版本中存在一个 bug,具体是唯一索引的等值查询遍历到第一个不满足等值条件的记录时加锁 next-key lock。



下面介绍一种高频锁冲突,那就是插入时唯一键冲突加锁,注意加锁类型与事务隔离级别无关,这也是少见的 RC 中使用 gap lock 的场景。

当事务与未提交事务的唯一键冲突时:

•未提交事务,如果存在隐式锁,将其转换成显式锁,具体类型是 X 型 record lock;

•冲突事务,等待 S 型 next-key lock;

insert 与 insert duplicate / replace 语句中唯一性检查时加锁模式不同:

•insert,S 型锁

•insert duplcate / replace,X 型锁



常见解决方案

常见的解决方案包括:

•修改事务隔离级别,其中:

◦有效场景,比如更新不存在的场景时加锁 gap lock,从 RR 改为 RC 时不加锁;

◦无效场景,比如插入唯一键前的唯一性检查依然加间隙锁;

•修改 SQL,比如将 insert duplicate 改写为 insert,唯一键不冲突时前者加锁 gap lock,后者不加锁;

•数据库版本升级,比如 5.7.26 中删除 insert duplicate / replace 唯一键不冲突时的加锁 gap lock;



日志分析工具

如下所示,自动分析死锁日志并将提取出来的特征显示在表格中,这里分析的是另一个死锁案例。

MySQL 死锁日志分析方法



功能入口在【易维-SRE开放平台-MySQL死锁分析】中,欢迎使用。

MySQL 死锁日志分析方法





结论

MySQL 死锁日志中的主要信息包括两个事务、两条 SQL、三部分锁信息。

其中锁信息又包括表、索引、锁类型、数据行。

此外,还包括实例级别的信息,包括数据库版本与事务隔离级别。

上述因素都会影响加锁规则,因此提供日志分析工具用于信息的自动提取。



参考教程

•MySQL的死锁系列- 锁的类型以及加锁原理

点赞
收藏
评论区
推荐文章
Jacquelyn38 Jacquelyn38
3年前
解决进程死锁——银行家算法透析
死锁是指两个或两个以上的进程在执行过程中,由于竞争资源或者由于彼此通信而造成的一种阻塞的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。避免死锁算法中最有代表性的算法是DijkstraE.W于1968年提出的银行家算法:下面我们将从例题中一点一点的分析:解题:第一步:
Wesley13 Wesley13
3年前
java死锁分析
1编写死锁案列,运行packagecom.thread.thread.deadLock;publicclassDeadLockTest{publicstaticStringobjA"strA";publicstaticStringobjB"strB";
Wesley13 Wesley13
3年前
MySQL 5.6.35 索引优化导致的死锁案例解析
一、背景随着公司业务的发展,商品库存从商品中心独立出来成为一个独立的系统,承接主站商品库存校验、订单库存扣减、售后库存释放等业务。在上线之前我们对于核心接口进行了压测,压测过程中出现了MySQL5.6.35死锁现象,通过日志发现引发死锁的只是一条简单的sql,死锁是怎么产生的?发扬技术人员刨根问底的优良传统,对于这次死锁原因进行了细致的排
Wesley13 Wesley13
3年前
Mysql 死锁的详细分析方法
 用数据库的时候,偶尔会出现死锁,针对我们的业务系统,出现死锁的直接结果就是系统卡顿、客户找事儿,所以我们也在想尽全力的消除掉数据库的死锁。_出现死锁的时候,如果只是想解锁,用showfullprocesslist看下kill掉就好了,__如果想查找到详细的问题,一个办法是用showengineinnodbstatus来查看简略
Wesley13 Wesley13
3年前
mysql死锁问题分析
mysql死锁问题分析(https://www.oschina.net/action/GoToLink?urlhttps%3A%2F%2Fwww.cnblogs.com%2FLBSer%2Fp%2F5183300.html)
Wesley13 Wesley13
3年前
Java多线程之死锁编码及定位分析
目录死锁是什么代码实现死锁解决办法1\.死锁是什么死锁是指两个或两个以上的进程在执行过程中因争夺资而造成的一种互相等待的现象,若无外力干涉那它们都将无法推进下去,如果系统资源允是,进程的资源请求都能够得到满是,死锁出现的可能性就很低,否则就会因争夺有限的资源而陷入死锁。2.代码实现代码实现import
Wesley13 Wesley13
3年前
MySQL 是怎么死锁的?
!(https://oscimg.oschina.net/oscnet/883b0e7e326d42db82f62128ad1517d8.jpg)Java技术栈www.javastack.cn关注阅读更多优质文章(https://www.oschina.net/action/GoToLink?urlhttp
MySQL事务死锁问题排查 | 京东云技术团队
一、背景在预发环境中,由消息驱动最终触发执行事务来写库存,但是导致MySQL发生死锁,写库存失败。com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:rpcerror:code
MySQL innoDB 间隙锁产生的死锁问题 | 京东云技术团队
背景线上经常偶发死锁问题,当时处理一张表,也没有联表处理,但是有两个mq入口,并且消息体存在一样的情况,频率还不是很低,这么一个背景,我非常容易怀疑到,两个消息同时近到这一个事务里面导致的,但是是偶发的,又模拟不出来什么场景会导致死锁,只能进行代码分析,问
MySQL的index merge(索引合并)导致数据库死锁分析与解决方案 | 京东云技术团队
背景在DBS集群列表更多连接查询死锁中,看到9月22日有数据库死锁日志,后排查发现是因为mysql的优化indexmerge(索引合并)导致数据库死锁。定义indexmerge(索引合并):该数据库查询优化的一种技术,在mysql5.1之后进行引入,它可以