• 解决死锁之路4（终结篇）

    转：https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html

    在前面的几篇博客中，我们学习了事务，隔离级别，各种锁类型，以及各种 SQL 语句的加锁原理，这一系列的知识学习下来，断断续续，竟然已经过去了大半年的时间。随着对数据库原理的学习越来越深入，越发现一发不可收拾，这里面的水实在是太深了。这是这个系列的最后一篇，也让我们回到这个系列的初衷，如何通过死锁日志来诊断死锁的成因，如何分析和解决我们开发过程中遇到的数据库死锁问题。

    这是《解决死锁之路》系列博文中的最后一篇，你还可以阅读其他几篇：

    1. 学习事务与隔离级别
    2. 了解常见的锁类型
    3. 掌握常见 SQL 语句的加锁分析
    4. 死锁问题的分析和解决

    一、开启锁监控

    在遇到线上死锁问题时，我们应该第一时间获取相关的死锁日志。我们可以通过 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 的标准监控，我推荐将锁监控也打开，它可以提供一些额外的锁信息，在分析死锁问题时会很有用。开启监控的方法有两种：

    1. 基于系统表

    MySQL 使用了几个特殊的表名来作为监控的开关，比如在数据库中创建一个表名为 innodb_monitor 的表开启标准监控，创建一个表名为 innodb_lock_monitor 的表开启锁监控。MySQL 通过检测是否存在这个表名来决定是否开启监控，至于表的结构和表里的内容无所谓。相反的，如果要关闭监控，则将这两个表删除即可。这种方法有点奇怪，在 5.6.16 版本之后，推荐使用系统参数的形式开启监控。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 开启标准监控
    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;

    2. 基于系统参数

    在 MySQL 5.6.16 之后，可以通过设置系统参数来开启锁监控，如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 开启标准监控
    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 的错误日志文件中。

    1
    set GLOBAL innodb_print_all_deadlocks=ON;

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

    二、读懂死锁日志

    一切准备就绪之后，我们从 DBA 那里拿到了死锁日志（其中的SQL语句做了省略）：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    ------------------------
    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 语句、等待的锁以及持有的锁信息等。下面我们就来研究下这份死锁日志，看看从这份死锁日志中能不能发现死锁的原因？

    首先看事务一的信息：

    * (1) 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 和数据信息等：

    1
    2
    3
    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     ;;

    在 《了解常见的锁类型》 中我们说过，一共有四种类型的行锁：记录锁，间隙锁，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。譬如下面这个：

    1
    2
    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

    1
    ( 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 执行顺序，这样分析死锁时就会容易很多。

    三、常见死锁分析

    尽管上面说通过死锁日志来推断死锁原因非常困难，但我想也不是完全不可能。我在 Github 上新建了一个项目 mysql-deadlocks，这个项目收集了一些常见的 MySQL 死锁案例，大多数案例都来源于网络，并对它们进行分类汇总，试图通过死锁日志分析出每种死锁的原因，还原出死锁现场。这虽然有点痴人说梦的感觉，但还是希望能给后面的开发人员在定位死锁问题时带来一些便利。

    我将这些死锁按事务执行的语句和正在等待或已持有的锁进行分类汇总（目前已经收集了十余种死锁场景）：

    dead-lock-collections.jpg

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

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

    对于这种分类方法我感觉并不是很好，但也想不出什么其他更好的方案，如果你有更好的建议，欢迎讨论。另外，如果你有新的死锁案例，或者对某个死锁的解释有异议，欢迎 给我提 Issue 或 PR。

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

    students-table.png

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

    3.1 死锁案例一

    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 占有，于是两个事务之间相互等待，导致死锁。

    3.2 死锁案例二

    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 语句，最后追本溯源，找到对应的应用代码，将其删除，从而修复了这个死锁。

    3.3 死锁案例三

    dead-lock-3.jpg

    别看这个案例里每个事务都只有一条 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，从而形成死锁。

    3.4 如何避免死锁

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

    1. 如上面的案例一和案例三所示，对索引加锁顺序的不一致很可能会导致死锁，所以如果可以，尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；
    2. 如上面的案例二所示，Gap 锁往往是程序中导致死锁的真凶，由于默认情况下 MySQL 的隔离级别是 RR，所以如果能确定幻读和不可重复读对应用的影响不大，可以考虑将隔离级别改成 RC，可以避免 Gap 锁导致的死锁；
    3. 为表添加合理的索引，如果不走索引将会为表的每一行记录加锁，死锁的概率就会大大增大；
    4. 我们知道 MyISAM 只支持表锁，它采用一次封锁技术来保证事务之间不会发生死锁，所以，我们也可以使用同样的思想，在事务中一次锁定所需要的所有资源，减少死锁概率；
    5. 避免大事务，尽量将大事务拆成多个小事务来处理；因为大事务占用资源多，耗时长，与其他事务冲突的概率也会变高；
    6. 避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句；我们经常会有一些定时脚本，避免它们在同一时间点运行；
    7. 设置锁等待超时参数：innodb_lock_wait_timeout，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

    总结

    终于写完了这个系列的博客。

    一开始是去年 9 月份的时候，线上某个系统遇到了一个死锁问题，当时对这个死锁百思不得其解，慢慢的从困惑到感兴趣，虽然那时花了大概一个礼拜的时间研究后就已经把这个死锁问题解决了，但是对死锁的执念却一直没有放下，开始翻阅大量的文档和资料，看 MySQL 官方文档，买 MySQL 书籍，甚至去读 MySQL 源码，从事务、隔离级别、索引一直看到加锁机制、死锁分析等等。再到后来，突然觉得没意思想放弃，感觉就算知道了什么语句加什么锁也没有多大意义，这个都是死记硬背的东西，花时间把这些规则背下来没什么价值，还不如需要的时候自己去实验一把也就知道了。中间歇了有两个多月时间，后来想想不能半途而废，逼自己一定要把这个系列写完，最后一篇死锁问题的分析肯定需要收集然后对这些死锁问题进行大量的分析，于是去网上找了各种各样的死锁日志，然后一次一次的做实验，这不收集还好，一收集便停不下来，这应该就是死锁收藏癖吧。

    对死锁的研究前前后后烧了不少的脑细胞，特别是后期收集死锁日志的时候，才发现死锁场景各式各样，有些死的很荒谬，有些死的很精妙，还有些死的不明不白，直到现在我还没搞懂为什么。所有的这些死锁案例都收集在 这里，如果你感兴趣，欢迎来和我一起添砖加瓦，希望有一天，真的可以和死锁说再见。

    参考

    1. 开启InnoDB监控
    2. Enabling InnoDB Monitors
    3. 命令行监控工具Innotop
    4. MySQL中用Percona Toolkit监视死锁
    5. [MySQL 学习] Innodb锁系统(1)之如何阅读死锁日志
    6. 【MySQL】如何阅读死锁日志
    7. InnoDB locking
    8. MySQL中的事务与锁
    9. mysql死锁问题分析
    10. MySQL · 引擎特性 · InnoDB 事务锁系统简介
    11. MySQL · 答疑解惑 · MySQL 锁问题最佳实践
     
  • 相关阅读:
    Linux命令比较文件内容
    Linux命令jobs小记
    权限控制框架
    Log4j2源码分析系列：（一）配置加载
    Callable和Supplier的区别
    排序算法之归并排序
    Spring boot整合Mybatis
    排序算法之堆排序
    排序算法之希尔排序
    排序算法之快速排序
  • 原文地址：https://www.cnblogs.com/guanbin-529/p/14221873.html
Copyright © 2020-2023  润新知