【技术创作101训练营】认识Mysql死锁，并给它说再见

开场白

大家好，我的名字是辛国帅，辛是一把辛酸泪的辛，为了方便大家记住我的名字，大家可以反过来叫我名字：帅锅。

今天呢，我给大家分享的主题是如何分析和解决我们开发过程中遇到的数据库死锁问题。

故事是发生在看似平常的一天，座位对面的产品小姐姐看起来心情很好。突然！生产环境报警：一张库表打不开了……到底发生了什么呢，大家听我细细分解。

开启锁监控

首先，为了方便定位生产环境数据库异常，我们应该开启监控。只有这样，在发生线上环境死锁问题时，我们才能第一时间获取到数据库相关的死锁日志。那如何开启这个日志呢？大家先看一下这个命令

show engine innodb status

命令来获取死锁信息，但是它有个限制，只能拿到最近一次的死锁日志。MySQL 提供了一套 InnoDb 的监控机制，用于周期性（每隔 15 秒）输出 InnoDb 的运行状态到 mysqld 服务的标准错误输出（stderr）。

默认情况下监控是关闭的，只有当需要分析问题时再开启，并且在分析问题之后，建议将监控关闭，因为它对数据库的性能有一定影响，另外每 15 秒输出一次日志，会使日志文件变得特别大。

InnoDb 的监控主要分为四种：标准监控（Standard InnoDB Monitor）、锁监控（InnoDB Lock Monitor）、表空间监控（InnoDB Tablespace Monitor）和表监控（InnoDB Table Monitor）。后两种监控已经基本上废弃了，关于各种监控的作用可以参考 MySQL 的官方文档 Enabling InnoDB Monitors。

要获取死锁日志，我们需要开启 InnoDb 的标准监控，我推荐将锁监控也打开，它可以提供一些额外的锁信息，在分析死锁问题时会很有用。开启监控的方法有两种：

基于系统表

通过查阅官方文档，MySQL 使用了若干个特殊的表名来作为日志监控的开关。

比如我们在数据库中

创建一个名称为 innodb_monitor 的表来开启标准监控

创建一个表名为 innodb_lock_monitor 的表开启锁监控。MySQL 通过检测是否存在这个表名来决定是否开启监控，至于表的结构和表里的内容无所谓。相反的，如果要关闭监控，则将这两个表删除即可。

-- 开启标准监控
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
 
-- 关闭标准监控
DROP TABLE innodb_monitor;
 
-- 开启锁监控
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
 
-- 关闭锁监控
DROP TABLE innodb_lock_monitor;

基于系统参数

大家有没有发现上面通过表来控制日志监控是否开启的方法有点奇怪？

正因为如此，在众多开发者的舆论压力下，Mysql 5.6.16 版本之后，Mysql 改用全局系统参数的形式开启监控，如下所示：

-- 开启标准监控
set GLOBAL innodb_status_output=ON;
 
-- 关闭标准监控
set GLOBAL innodb_status_output=OFF;
 
-- 开启锁监控
set GLOBAL innodb_status_output_locks=ON;
 
-- 关闭锁监控
set GLOBAL innodb_status_output_locks=OFF;

有没有发现这种操作是不是看起来特别方便、优雅？

另外，MySQL 还提供了一个特别实用的系统参数 innodb_print_all_deadlocks 专门用于记录死锁日志。这样，当发生死锁时，死锁日志就会记录到 MySQL 的错误日志文件中。开启方式如下：

set GLOBAL innodb_print_all_deadlocks=ON;

除了 MySQL 自带的监控机制，还有一些有趣的监控工具也很有用，比如 Innotop 和 Percona Toolkit 里的小工具 pt-deadlock-logger。

读懂死锁日志

当我们一切准备就绪之后，就可以从 DBA 那里拿到死锁日志（省略版）：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-06 11:58:16 7ff35f5dd700
*** (1) TRANSACTION:
TRANSACTION 182335752, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 15
MySQL thread id 12032077, OS thread handle 0x7ff35ebf6700, query id 196418265 10.40.191.57 RW_bok_db update
INSERT INTO bok_task
                 ( order_id ...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 300 page no 5480 n bits 552 index `order_id_un` of table `bok_db`.`bok_task` 
    trx id 182335752 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 182335756, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
11 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 15
MySQL thread id 12032049, OS thread handle 0x7ff35f5dd700, query id 196418268 10.40.189.132 RW_bok_db update
INSERT INTO bok_task
                 ( order_id ...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 300 page no 5480 n bits 552 index `order_id_un` of table `bok_db`.`bok_task` 
    trx id 182335756 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 300 page no 5480 n bits 552 index `order_id_un` of table `bok_db`.`bok_task` 
    trx id 182335756 lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

从日志中主要可以得出以下几个信息：

• 死锁发生的时间
• 导致死锁的事务信息（注意：只显示两个事务，如果由多个事务导致的死锁也只显示两个）
• 显示每个事务正在执行的 SQL 语句、等待的锁以及持有的锁信息等。

下面我们就来研究下这份死锁日志，看看从这份死锁日志中能不能发现死锁的原因？

事务一的信息：

TRANSACTION:
TRANSACTION 182335752, ACTIVE 0 sec inserting

ACTIVE 0 sec 表示事务活动时间，inserting 为事务当前正在运行的状态，可能的事务状态有：fetching rows，updating，deleting，inserting 等。

mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 15

• tables in use 1 表示有一个表被使用
• locked 1 表示有一个表锁
• LOCK WAIT 表示事务正在等待锁
• 11 lock struct(s) 表示该事务的锁链表的长度为 11，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及 autoinc 锁等
• heap size 1184为事务分配的锁堆内存大小
• 2 row lock(s) 表示当前事务持有的行锁个数，通过遍历上面提到的 11 个锁结构，找出其中类型为 LOCK_REC 的记录数
• undo log entries 15 表示当前事务有 15 个 undo log 记录，因为二级索引不记 undo log，说明该事务已经更新了 15 条聚集索引记录。

MySQL thread id 12032077, OS thread handle 0x7ff35ebf6700, query id 196418265 10.40.191.57 RW_bok_db update

事务的线程信息，以及数据库 IP 地址和数据库名，对我们分析死锁用处不大。

INSERT INTO bok_task

1
( order_id ...

这里显示的是正在等待锁的 SQL 语句，死锁日志里每个事务都只显示一条 SQL 语句，这对我们分析死锁很不方便，我们必须要结合应用程序去具体分析这个 SQL 之前还执行了哪些其他的 SQL 语句，或者根据 binlog 也可以大致找到一个事务执行的 SQL 语句。

* (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 300 page no 5480 n bits 552 index `order_id_un` of table `bok_db`.`bok_task` trx id 182335752 lock_mode X insert intention waiting

这里显示的是事务正在等待什么锁。

RECORD LOCKS 表示记录锁（并且可以看出要加锁的索引为 order_id_un），space id 为 300，page no 为 5480，n bits 552表示这个记录锁结构上留有 552 个 bit 位（该 page 上的记录数 + 64）。

lock_mode X 表示该记录锁为排他锁

insert intention waiting 表示要加的锁为插入意向锁，并处于锁等待状态。

在上面有提到 innodb_status_output_locks 这个系统变量可以开启 InnoDb 的锁监控，如果开启了，这个地方还会显示出锁的一些额外信息，包括索引记录的 info bits 和数据信息等：

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000001; asc     ;;

其实Mysql中一共有四种类型的行锁：记录锁，间隙锁，Next-key锁和插入意向锁。这四种锁对应的死锁日志各不相同，如下所示：

• 记录锁（LOCK_REC_NOT_GAP）: lock_mode X locks rec but not gap
• 间隙锁（LOCK_GAP）: lock_mode X locks gap before rec
• Next-key 锁（LOCK_ORNIDARY）: lock_mode X
• 插入意向锁（LOCK_INSERT_INTENTION）: lock_mode X locks gap before rec insert intention

这里有一点要注意的是，并不是在日志里看到 lock_mode X 就认为这是 Next-key 锁，因为还有一个例外：如果在 supremum record 上加锁，locks gap before rec 会省略掉，间隙锁会显示成 lock_mode X，插入意向锁会显示成 lock_mode X insert intention。譬如下面这个：

RECORD LOCKS space id 0 page no 307 n bits 72 index `PRIMARY` of table `test`.`test` trx id 50F lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

看起来像是 Next-key 锁，但是看下面的heap no 1 表示这个记录是 supremum record（另外，infimum record 的 heap no 为 0），所以这个锁应该看作是一个间隙锁。

事务二的消息：

* (2) TRANSACTION:

TRANSACTION 182335756, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
11 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 15
MySQL thread id 12032049, OS thread handle 0x7ff35f5dd700, query id 196418268 10.40.189.132 RW_bok_db update
INSERT INTO bok_task

( order_id ...
* (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 300 page no 5480 n bits 552 index `order_id_un` of table `bok_db`.`bok_task` trx id 182335756 lock_mode X
* (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 300 page no 5480 n bits 552 index `order_id_un` of table `bok_db`.`bok_task` trx id 182335756 lock_mode X insert intention waiting

事务二和事务一的日志基本类似，不过它多了一部分 HOLDS THE LOCK(S)，表示事务二持有什么锁，这个锁往往就是事务一处于锁等待的原因。这里可以看到事务二正在等待索引 order_id_un 上的插入意向锁，并且它已经持有了一个 X 锁（Next-key 锁，也有可能是 supremum 上的间隙锁）。

说到这里为止，我们已经得到了很多关键信息，那此时我们可以逆推出死锁发生的原因吗？

这是每个开发人员和 DBA 最关心的问题，那如何通过死锁日志来诊断死锁的成因？实际上这是非常困难的。

如果每个事务都只有一条 SQL 语句，这种情况的死锁成因还算比较好分析，因为我们可以从死锁日志里找到每个事务执行的 SQL 语句，只要对这两条 SQL 语句的加锁过程有一定的了解，死锁原因一般不难定位。但也有可能死锁的成因非常隐蔽，这时需要我们对这两条 SQL 语句的加锁流程做非常深入的研究才有可能分析出死锁的根源。

不过大多数情况下，每个事务都不止一条 SQL 语句，譬如上面的死锁日志里显示的 undo log entries 15，说明执行 INSERT 语句之前肯定还执行了其他的 SQL 语句，但是具体是什么，我们不得而知，我们只能根据 HOLDS THE LOCK(S) 部分知道有某个 SQL 语句对 order_id_un 索引加了 Next-key 锁（或间隙锁）。另外事务二在 WAITING FOR 插入意向锁，至于它和事务一的哪个锁冲突也不得而知，因为事务一的死锁日志里并没有 HOLDS THE LOCK(S) 部分。

所以，对死锁的诊断不能仅仅靠死锁日志，还应该结合应用程序的代码来进行分析，如果实在接触不到应用代码，还可以通过数据库的 binlog 来分析（只要你的死锁不是 100% 必现，那么 binlog 日志里肯定能找到一份完整的事务一和事务二的 SQL 语句）。通过应用代码或 binlog 理出每个事务的 SQL 执行顺序，这样分析死锁时就会容易很多。

常见死锁分析

尽管上面说通过死锁日志来推断死锁原因非常困难，但我想也不是完全不可能。我将这些死锁按事务执行的语句和正在等待或已持有的锁进行分类汇总（目前已经收集了十余种死锁场景）：

表中的语句虽然只列出了 delete 和 insert，但实际上绝大多数的 delete 语句和 update 或 select ... for update 加锁机制是一样的，所以为了避免重复，对于 update 语句就不在一起汇总了（当然也有例外，譬如使用 update 对索引进行更新时加锁机制和 delete 是有区别的，这种情况我会单独列出）。

对每一个死锁场景，我都会定义一个死锁名称（实际上就是事务等待和持有的锁），每一篇分析，我都分成了 死锁特征、死锁日志、表结构、重现步骤、分析和参考 这几个部分。

下面我们介绍几种常见的死锁场景，还是以前面提到的 students 表为例：

其中，id 为主键，no（学号）为二级唯一索引，name（姓名）和 age（年龄）为二级非唯一索引，score（学分）无索引。数据库隔离级别为 RR。

死锁案例一

dead-lock-1.jpg

死锁的根本原因是有两个或多个事务之间加锁顺序的不一致导致的，这个死锁案例其实是最经典的死锁场景。

首先，事务 A 获取 id = 20 的锁（lock_mode X locks rec but not gap），事务 B 获取 id = 30 的锁；然后，事务 A 试图获取 id = 30 的锁，而该锁已经被事务 B 持有，所以事务 A 等待事务 B 释放该锁，然后事务 B 又试图获取 id = 20 的锁，这个锁被事务 A 占有，于是两个事务之间相互等待，导致死锁。

死锁案例二

dead-lock-2.jpg

首先事务 A 和事务 B 执行了两条 UPDATE 语句，但是由于 id = 25 和 id = 26 记录都不存在，事务 A 和 事务 B 并没有更新任何记录，但是由于数据库隔离级别为 RR，所以会在 (20, 30) 之间加上间隙锁（lock_mode X locks gap before rec），间隙锁和间隙锁并不冲突。之后事务 A 和事务 B 分别执行 INSERT 语句要插入记录 id = 25 和 id = 26，需要在 (20, 30) 之间加插入意向锁（lock_mode X locks gap before rec insert intention），插入意向锁和间隙锁冲突，所以两个事务互相等待，最后形成死锁。

要解决这个死锁很简单，显然，前面两条 UPDATE 语句是无效的，将其删除即可。另外也可以将数据库隔离级别改成 RC，这样在 UPDATE 的时候就不会有间隙锁了。这个案例正是文章开头提到的死锁日志中的死锁场景，别看这个 UPDATE 语句是无效的，看起来很傻，但是确实是真实的场景，因为在真实的项目中代码会非常复杂，比如采用了 ORM 框架，应用层和数据层代码分离，一般开发人员写代码时都不知道会生成什么样的 SQL 语句，我也是从 DBA 那里拿到了 binlog，然后从里面找到了事务执行的所有 SQL 语句，发现其中竟然有一行无效的 UPDATE 语句，最后追本溯源，找到对应的应用代码，将其删除，从而修复了这个死锁。

死锁案例三

别看这个案例里每个事务都只有一条 SQL 语句，但是却实实在在可能会导致死锁问题，其实说起来，这个死锁和案例一并没有什么区别，只不过理解起来要更深入一点。要知道在范围查询时，加锁是一条记录一条记录挨个加锁的，所以虽然只有一条 SQL 语句，如果两条 SQL 语句的加锁顺序不一样，也会导致死锁。

在案例一中，事务 A 的加锁顺序为： id = 20 -> 30，事务 B 的加锁顺序为：id = 30 -> 20，正好相反，所以会导致死锁。这里的情景也是一样，事务 A 的范围条件为 id < 30，加锁顺序为：id = 15 -> 18 -> 20，事务 B 走的是二级索引 age，加锁顺序为：(age, id) = (24, 18) -> (24, 20) -> (25, 15) -> (25, 49)，其中，对 id 的加锁顺序为 id = 18 -> 20 -> 15 -> 49。可以看到事务 A 先锁 15，再锁 18，而事务 B 先锁 18，再锁 15，从而形成死锁。

如何避免死锁

在工作过程中偶尔会遇到死锁问题，虽然这种问题遇到的概率不大，但每次遇到的时候要想彻底弄懂其原理并找到解决方案却并不容易。其实，对于 MySQL 的 InnoDb 存储引擎来说，死锁问题是避免不了的，没有哪种解决方案可以说完全解决死锁问题，但是我们可以通过一些可控的手段，降低出现死锁的概率。

如上面的案例一和案例三所示，对索引加锁顺序的不一致很可能会导致死锁，所以如果可以，尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；

如上面的案例二所示，Gap 锁往往是程序中导致死锁的真凶，由于默认情况下 MySQL 的隔离级别是 RR，所以如果能确定幻读和不可重复读对应用的影响不大，可以考虑将隔离级别改成 RC，可以避免 Gap 锁导致的死锁；

为表添加合理的索引，如果不走索引将会为表的每一行记录加锁，死锁的概率就会大大增大；

我们知道 MyISAM 只支持表锁，它采用一次封锁技术来保证事务之间不会发生死锁，所以，我们也可以使用同样的思想，在事务中一次锁定所需要的所有资源，减少死锁概率；

避免大事务，尽量将大事务拆成多个小事务来处理；因为大事务占用资源多，耗时长，与其他事务冲突的概率也会变高；

避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句；我们经常会有一些定时脚本，避免它们在同一时间点运行；

设置锁等待超时参数：innodb_lock_wait_timeout，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

总结

就因为这次生产环境遇到了死锁问题，使原本平常的一天，变得非同寻常。彼时的我就是一个小白，对死锁仍是百思不得其解，但是惨痛的教训鞭策我慢慢地从困惑到感兴趣。甚至为了彻底搞懂死锁，我查阅了大量的文档、资料，阅读了 MySQL 官方文档，花钱买 MySQL 书籍，硬着头皮去读 MySQL 源码，得出解决死锁问题的结论：需要从事务、隔离级别、索引、加锁机制多个维度去分析。在探索的过程中发现死锁场景各式各样，有些死的很荒谬，有些死的很精妙，还有些死的不明不白，大家有机会一定要亲自实验一下。因为对死锁的研究前前后后烧了不少的脑细胞，趁此机会分享给大家，希望大家和死锁说再见的这一天越来越近。