记一次 MySQL 死锁排查过程

转载自 | kissyu.org

背景

以前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁。借着这个机会又重新学习了一下 mysql 的死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多。虽然是后端程序员,我们不需要像 DBA 一样深入地去分析与锁相关的源码,但是如果我们能够掌握基本的死锁排查方法,对我们的日常开发还是大有裨益的。

PS:本文不会介绍死锁的基本知识,mysql 的加锁原理可以参考本文的参考资料提供的链接。

死锁起因

先介绍一下数据库和表情况,因为涉及到公司内部真是的数据,所以以下都做了模拟,不会影响具体的分析。

我们采用的是 5.5 版本的 mysql 数据库,事务隔离级别是默认的 RR(Repeatable-Read),采用 innodb 引擎。假设存在 test 表:

表的结构很简单,一个主键 id,另一个唯一索引 a。表里的数据如下:

出现死锁的操作如下:

然后我们可以通过 SHOW ENGINE INNODB STATUS; 来查看死锁日志:

分析

阅读死锁日志

遇到死锁,第一步就是阅读死锁日志。死锁日志通常分为两部分,上半部分说明了事务 1 在等待什么锁:

从日志里我们可以看到事务 1 当前正在执行 delete from test where a = 2,该条语句正在申请索引 a 的 X 锁,所以提示 lock_mode X waiting。

然后日志的下半部分说明了事务 2 当前持有的锁以及等待的锁:

从日志的 HOLDS THE LOCKS(S) 块中我们可以看到事务 2 持有索引 a 的 X 锁,并且是记录锁(Record Lock)。该锁是通过事务 2 在步骤 2 执行的 delete 语句申请的。由于是 RR 隔离模式下的基于唯一索引的等值查询(Where a = 2),所以会申请一个记录锁,而非 next-key 锁。

从日志的 WAITING FOR THIS LOCK TO BE GRANTED 块中我们可以看到事务 2 正在申请 S 锁,也就是共享锁。该锁是 insert into test (id,a) values (10,2) 语句申请的。insert 语句在普通情况下是会申请排他锁,也就是 X 锁,但是这里出现了 S 锁。这是因为 a 字段是一个唯一索引,所以 insert 语句会在插入前进行一次 duplicate key 的检查,为了使这次检查成功,需要申请 S 锁防止其他事务对 a 字段进行修改。

那么为什么该 S 锁会失败呢?这是对同一个字段的锁的申请是需要排队的。S 锁前面还有一个未申请成功的 X 锁,所以 S 锁必须等待,所以形成了循环等待,死锁出现了。

通过阅读死锁日志,我们可以清楚地知道两个事务形成了怎样的循环等待,再加以分析,就可以逆向推断出循环等待的成因,也就是死锁形成的原因。

死锁形成流程图

为了让大家更好地理解死锁形成的原因,我们再通过表格的形式阐述死锁形成的流程:

拓展

在排查死锁的过程中,有个同事还发现了上述场景会产生另一种死锁,该场景无法通过手工复现,只有高并发场景下才有可能复现。

该死锁对应的日志这里就不贴出了,与上一个死锁的核心差别是事务 2 等待的锁从 S 锁换成了 X 锁,也就是 lock_mode X locks gap before rec insert intention waiting。我们还是通过表格来详细说明该死锁产生的流程:

总结

排查死锁时,首先需要根据死锁日志分析循环等待的场景,然后根据当前各个事务执行的 SQL 分析出加锁类型以及顺序,逆向推断出如何形成循环等待,这样就能找到死锁产生的原因了。

PS:上述分析都是基于经验的推断,希望其他小伙伴们能够指出当中的错误以及不足指出,谢谢!

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20171219B07SFH00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券