用一个极简案例带你入门MySQL死锁排查与预防

数据运维 2024-03-30 醒在深海的猫 手机阅读

背景

前不久了看了一个 mysql死锁的线上问题, 查了不少的资料看得甚是头大，不仅感慨网上关于mysql 死锁的文章太多太杂写得乱七八糟，要么就是东抄抄西抄抄格式都整不清，要么就是场景过于复杂不利于初学者复现和理解，要么就是前言不搭后语锁来锁去到底是那把锁获取到了没获取到也没说清。

这篇文章致力于用一个极简案例带你入门MySQL死锁排查与预防，真正从0开始, 从锁的分类, 从B+树的原理推广理解锁的范围, 锁的转换, 从环境部署, 到数据准备, 到并发开启事务, 触发死锁, 死锁回滚, 再到打开 InnoDB 日志，手把手教你如何复现一个死锁，排查死锁, 复现死锁的每一个步骤，到最后的如何避免死锁。 整个过程由浅入深，循序渐进, 这篇文章结合了 mysql官方文档与实操，过程非常详细, 相信你看完之后一定大有收获

前置知识

mysql 锁的分类, 这里只针对死锁中涉及到的锁进行说明, 掌握这些锁的类型, 应付一般的死锁问题足够了。我这里特意不展开，比如什么是乐观锁，什么是悲观锁，因为这些概念对分析死锁没有帮助，为了不混淆视听，这里不做讲解，大家装作不知道这些锁即可。

按照粒度分:

  • 库级别锁, 对整个数据库进行操作时使用, 比如数据库备份时使用， 官方标识 DATABASE
  • 表级锁，对整张表进行操作时使用, 比如 alter table 对表结构进行修改时使用， 官方标识TABLE
  • 行级锁，对某行/某些行操作时使用, 一般的增删改查用到的最多的锁类型， 官方标识RECORD

    • 按照兼容性分

  • 共享锁, 在读操作时触发, 官方名称 lock_mode S, 简称 S
  • 排他锁, 在写操作时触发, 官方名称 lock_mode X , 简称 X

    • 按照是否有意向分:

    意向锁, 又可细分 意向共享锁 意向排他锁:

  • 意向共享锁 , 官方标识 lock_mode S intention, 简称 IS
  • 意向排他锁 官方名称 lock_mode X insert intention, 简称 IX

    • 为什么要有意向锁这个东西, 我来举个场景:

  • 事务A对某行进行update操作, 尚未提交, 这个过程只用了行锁锁住了某一行

  • 事务B对表进行聚合操作，比如select * 这种需要对整个表进行上锁，需要上一个表锁。显然是会被阻塞的，因为事务A对某行正在进行操作
    上面的1，2都很好理解，那么现在问题来了, 事务B怎么知道自己会被阻塞? 答案很简单，事务B需要暴力遍历意向每一行，如果所有和都没用上行锁，那么就可以继续select了。 这是不是听起来很笨?
    于是意向锁就诞生了，在写操作之前，事务A需要获取两把锁, 一把是修改行记录的X锁, 一把是表的IX锁。当B事务获取不到IX锁的时候就会被阻塞，二无需遍历所有行。

    • 非意向锁
    非意向锁是相对意向锁来的, 并不是真的有一种锁叫做非意向锁。比如前面的 S 和 X 就是属于非意向锁, 这是相对 IS 和 IX 来看的。

    意向锁定协议如下：

    • 在事务可以获取表中行的共享锁之前，它必须首先获取IS表上的锁或更强的锁。
    • 在事务可以获取表中行的排他锁之前，它必须首先获取IX 表上的锁。

    按照锁的区间范围分:

  • 行锁, 官方标识 RECORD LOCK
  • 间隙锁, 官方标识 RECORD LOCK
  • 区间锁, 官方标识 NEXT KEY LOCK

    • 这三种类型的锁在分析死锁的时候特重要, 需要单独领出来详细说明

    RECORD LOCK

    行锁指的是精确对某一行进行上锁， 比如 select * from table_x where id =xxx ; 其中 id 为主键。 这时锁的对象就是主键。 行锁的对象永远是一个 索引 index。 那如果 where 条件不是主键呢 ? 如果不是主键而是其他索引，那么锁最终仍会落实到主键上面去。

    GAP LOCK

    间隙锁指的是对一个范围进行上锁, 或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁定。

  • 比如 SELECT id FROM t WHERE id BETWEEN 10 and 20 FOR UPDATE; 将会对 id 在 (10,20) 范围进行上锁。
  • 那如果 是 SELECT id FROM t WHERE id =10，且 这条记录存在, 则这个间隙锁就变成了一把行锁。
  • 接上面, 如果是 SELECT id FROM t WHERE id =10, 但是 id = 10记录不存在，只存在 9，11，21 这几个值, 此时间隙锁的范围就是 (9,11）, 会找到比10小的第一条记录和比10大的第一条记录，并在这个上下界之间上锁。

    • NEXT KEY LOCK

    区间锁指的是对一个区间进行加锁，它等于间隙锁+行锁。比如 表中存在 id =1，2，3 三条记录，随后执行 update t set a=x where id =3, 这会在 (-∞, 3] 的范围内加上一个区间锁，这可以看作是 (-∞,3）的间隙锁 加上 id =3 这把行锁

    加锁原则

    相信你看完3上面三个锁，肯定是一知半解，到底什么时候加 RECORD LOCK，什么时候加 NEXT KEY LOCK，什么时候加 NEXT KEY LOCK，怎么一把锁又退化成另一把锁了是吧。

    我总结了一下，要想搞清楚上面这三类型的锁，就需要理解下面这两个问题:

  • 为什么会有这么多锁
  • 为什么锁之间是可以退化的

    • 为什么会有 RECORD LOCK, GAP LOCK, NEXT KEY LOCK

    一句话，为了解决幻读问题

    所谓幻读就是下面这种情况:

    时刻事务A事务B
    t1START TRANSACTION;
    t2select count(1） from tabele t; 查询结果为1START TRANSACTION;
    t3insert 一条记录 进 table
    t4commit
    t5select count(1） from tabele t; 查询结果为2, 同一事物内插叙结果不一致

    为了解决这种问题怎么办，你想到的直观方法就是给表上锁，上锁的范围就是 [−∞,最后一个索引+1][-∞, 最后一个索引+1][−∞,最后一个索引+1] ，这样另外一个事务想要 insert 进来就无法执行了。最后一个索引+1 就是 NEXT KEY 的意思，所以这把锁就教做 NEXT KEY LOCK。 由于最后一个索引+1 还不存在，第一个索引左边还有"间隙", 所以实际上锁的范围是 （第一个索引−1,最后一个索引)（第一个索引-1, 最后一个索引)（第一个索引−1,最后一个索引), 在 mysql 中记作 (infimum,supremum)( infimum,supremum)(infimum,supremum)

    RECORD LOCK, GAP LOCK, NEXT KEY LOCK 之间的转换关系

    其实到底加了什么锁并不用死记硬背, 只有一句话 : 能影响查询记录都要加锁

    怎么理解这句话呢? 我放下面这张B + 树的图你就知道了， 明白了 B+ 树是如何索引到节点(也就是主键索引)，那么访问到的所有记录你就明白是什么意思了。

    1711765133388.png

    B+树只有叶子节点存储数据, 中间节点不存储数据。 假设有如下几种情况:

  • insert 一条记录, 为了解决幻读, 则需要上一把 NEXT KEY LOCK, 保障已有的所有记录和下一条记录 id =10(如果id自增)都会被锁住, 范围是 (infimum,supremum)( infimum,supremum)(infimum,supremum)， 此时实际上是一把 GAP LOCK
  • update 一条记录 id=2, 刚好有这条记录, 我只要保障 id=2 这条记录不被删除就不会产生幻读, 所以我只需要一把RECORD LOCK 锁住 id =2 这条记录即可, 这就是 行锁的来由
  • select between 6 and 8， 此时也是一把 GAP LOCK, 范围是（6，8）， 其他事务不能操作这个间隙内的数据
  • select between 2 and 10, id=2 刚好存在，所以会在叶子节点顺着双向链表向下查找, 因为id =10不存在, 最后一个节点为 id =9，所以最终的 GAP LOCK 是[2,supremum)

    • 其他操作上锁的原则也是类似，遵循的就是一个最小范围，最大保护的原则

    至此你已经了解了MYSQL中常见锁的分类，掌握以上概念，对于理解一般的死锁问题就足够了

    死锁现场复现

    部署准备

    docker 简单部署一个mysql环境

    创建 docker 容器

    docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:8.3

    查看 docker 容器

    CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS          PORTS                    NAMES
6cc4a834104a   mysql:8.3      "docker-entrypoint.s…"   16 seconds ago   Up 12 seconds   3306/tcp, 33060/tcp      some-mysql

    进入 docker 容器

    docker exec -it some-mysql  bash

    进入 mysql

    mysql -p 
 
my-secret-pw

    数据准备

    创建数据库, 表, 插入数据

    create database test_db ;
 
use  database test_db ;
 
CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `user` ( `name`) VALUES ( 'Tom');
 
INSERT INTO `user` ( `name`) VALUES ( 'John');

    随后查询一下

    mysql> select * from user ;
+----+------+
| id | name |
+----+------+
|  1 | Tom  |
|  2 | John |
+----+------+
2 rows in set (0.00 sec)

    mysql 设置

    查看隔离级别, 去饿不隔离级别处于RR级别

     SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

    关闭事务自动提交, 以方便我们在命令行中手动操作事务

    mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.04 sec)

    触发死锁

    打开两个窗口，按照下图的顺序，执行sql语句:

    时间事务1857事务1858
    t0START TRANSACTION;
    t1select * from user where id=3 for update ; empty set
    t2START TRANSACTION;
    t3select * from user where id=4 for update ; empty set
    t4INSERT INTO user (id, name) VALUES (3, 'Peter'); 被阻塞
    t5INSERT INTO user (id, name) VALUES (4, 'Jack'); 死锁
    t6触发自动 ROLLBACK
    t7得到获取锁, 写入成功

    操作完成后, 数据库应该有3条记录

    mysql> select * from user ;
+----+-------+
| id | name  |
+----+-------+
|  1 | Tom   |
|  2 | John  |
|  3 | Peter |
+----+-------+
3 rows in set (0.00 sec)

    innodb status 分析

    使用下面这条命令查看 innodb 引擎最近的 死锁日志

    show engine innodb status

    我截取了和死锁强烈相关的部分:

    ------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-28 17:02:01 139628245939968
*** (1) TRANSACTION:
TRANSACTION 1857, ACTIVE 42 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 20, OS thread handle 139628315199232, query id 83 localhost root update
INSERT INTO `user` (`id`, `name`) VALUES (3, 'Peter')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test_db`.`user` trx id 1857 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test_db`.`user` trx id 1857 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 1858, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 21, OS thread handle 139628314142464, query id 84 localhost root update
INSERT INTO `user` (`id`, `name`) VALUES (4, 'Jack')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test_db`.`user` trx id 1858 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test_db`.`user` trx id 1858 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

    TRANSACTION 1857 分析

    事务分析

    *** (2) TRANSACTION: TRANSACTION 1858, ACTIVE 24 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s) MySQL thread id 21, OS thread handle 139628314142464, query id 84 localhost root update INSERT INTO `user` (`id`, `name`) VALUES (4, 'Jack')

    transaction id = 1857 的事务, 存活 42 秒, 插入数据，
    涉及mysql 的一张表, 锁住了一张表，
    这个事务等待3把锁, 其中两把是行锁，
    发生死锁的SQL语句是 sql 语句是 INSERT INTO user (id, name) VALUES (3, 'Peter')

    持有锁分析

    *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test_db`.`user` trx id 1857 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;

    拥有一把 排他锁, 位于 test_db 库， user 表 的 主键 , 锁的范围是 (-无穷,asc supremum).

    等待锁分析

    *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test_db`.`user` trx id 1857 lock_mode X insert intention waiting 
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 
0: len 8; hex 73757072656d756d; asc supremum;;

    拥有一把 排他锁, 位于 test_db 库， user 表, , 锁的范围是 (-∞,asc supremum)， 也就是 (-∞,+∞)

    TRANSACTION 1858 分析

    事务分析

    *** (2) TRANSACTION: TRANSACTION 1858, ACTIVE 24 sec inserting 
mysql tables in use 1, locked 1 
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s) 
MySQL thread id 21, OS thread handle 139628314142464, query id 84 localhost root update 
INSERT INTO `user` (`id`, `name`) VALUES (4, 'Jack')

    transaction id = 1858 的事务, 存活 42 秒, 插入数据，
    涉及mysql 的一张表, 锁住了一张表，
    这个事务等待3把锁, 其中两把是行锁，
    发生死锁的SQL语句是 INSERT INTO user (id, name) VALUES (4, 'Jack')

    持有锁分析

    *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test_db`.`user` trx id 1858 lock_mode X 
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 
0: len 8; hex 73757072656d756d; asc supremum;;

    拥有一把 排他锁, 位于 test_db 库， user 表 的 主键 , 锁的范围是 (-无穷,asc supremum).

    等待锁分析

    *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 4 page no 4 n bits 72 index 
PRIMARY of table `test_db`.`user` trx id 1857 lock_mode X insert intention waiting 
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 
0: len 8; hex 73757072656d756d; asc supremum;;

    拥有一把 排他锁, 位于 test_db 库， user 表, , 锁的范围是 (-∞,asc supremum)， 也就是 (-∞,+∞)

    小结一下

    我们梳理一下目前得到的有用信息:

  • 定位到了发生死锁的现场位于: 事务1858 执行 INSERT INTO user (id, name) VALUES (4, 'Jack'); 发生死锁
  • 死锁的原因是 事务 1857 在执行 INSERT INTO user (id, name) VALUES (3, 'Peter') 时在等待一把锁, 这把锁肯定是依赖于 1858 事务的释放。 事务1858 执行 INSERT INTO user (id, name) VALUES (4, 'Jack') 也在等待一把锁，这把锁需要 1857 事务的释放。两个事务形成了环路等待
  • 事务 1858 检测到了死锁，主动触发回滚。 事务 1857 得到资源，成功插入一条数据

    • 相信你看完 innodb 的日志，也只是云里雾里。 的确，到目前为止，的确不能完全分析出死锁的全过程。 innodbstatus 只能记录在在死锁发生的时候的现场, 至于这个现场的前后是怎么来的，并不能完全洞悉。

    performance_schema.data_locks 表 分析

    下面我们更进一步, 来看看 performance_schema.data_locks ，这张表记录的 事务获取到锁的记录

    SELECT * FROM performance_schema.data_locks  where ENGINE_TRANSACTION_ID = 1857 or ENGINE_TRANSACTION_ID = 1858 G;

    *************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:1070:139628715770544
ENGINE_TRANSACTION_ID: 1857
            THREAD_ID: 59
             EVENT_ID: 27
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139628715770544
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:4:4:1:139628715767632
ENGINE_TRANSACTION_ID: 1857
            THREAD_ID: 59
             EVENT_ID: 27
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767632
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:4:4:1:139628715767976
ENGINE_TRANSACTION_ID: 1857
            THREAD_ID: 59
             EVENT_ID: 28
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767976
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:4:4:4:139628715768320
ENGINE_TRANSACTION_ID: 1857
            THREAD_ID: 59
             EVENT_ID: 28
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715768320
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
4 rows in set (0.00 sec)

    聪明的你会发现，为什么只有 1857 事务获取到的锁, 事务 1858 去哪里了？ 这是因为 1858 回滚了，所以并不能获取到 1858 的锁记录。

    但是有没有关系，我们来看看 1857 事务 都获取到了什么锁:

  • LOCK MODE IX 表示排他意向锁, LOCK_TYPE TABLE 表锁, 对象是整张表, LOCK_DATA NULL 表示并没有实际对表的某一行进行操作, LOCK_STATUS: GRANTED 表示已获得
  • LOCK MODE X 锁 表示排他锁代表即将对某一行进行写操作, LOCK_STATUS: GRANTED 表示已获得, 重要的是 LOCK_TYPE: RECORD 和 LOCK_DATA: supremum pseudo-record, supremum 代表锁的一个上界 类似于正无穷, pseudo-record 表示 伪行锁， 表示并不是真的锁了某一行， 因为要锁的 id =3 不存在。
  • LOCK MODE IX 排他意向锁, 代表事务即将 往 supremum 插入记录了
  • LOCK MODE X GAP 锁, 范围 [3, +无穷)

    • 解释一下为什么需要四把锁 , 这4把锁分别负责:

    • IX TABLE 锁防止表被别的事务对这张表进行写操作,保护事务可能的后续写操作
    • X RECORD 锁防止表被别的事务改非锁定读操作,比如当前事务 修改 id =3 这一行, 另一个事务在修改前后都读取这一行造成读不一致
    • IX RECORD 锁定 supremum 位置，防止在此过程中其他事务也进行 insert操作
    • X GAP 锁，支持范围查询， 比如 另一个事务查询 select * from user where id>=3 需要无法获取 [3,+无穷) 的锁会被阻塞，防止其他事务读到这个还未提交的事务。

    分析到这里，是不是又比只看 innodb status 更近一步了。接下来，我们彻底还原一下死锁的形成过程。

    刨根问底，一探究竟

    关闭死锁检测和自动回滚

    为了验证我们上面的猜测，彻底搞懂两个事务在每一个时刻到底获取到了哪一把锁, 我们直接开大招, 把死锁自动回滚机制给关掉，这样即使有死锁也不会自动回滚，就让两个事务一直死锁, 保留死锁案发现场, 再去performance_schema.data_locks里面查看每一时刻的锁获取情况。

    SET GLOBAL innodb_deadlock_detect = OFF;
Query OK, 0 rows affected (0.00 sec)


SHOW VARIABLES LIKE 'innodb_deadlock_detect%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | OFF   |
+------------------------+-------+
1 row in set (0.03 sec)

    手动设置超长等待锁时间

    不光关闭死锁检测和自动回滚，我们为了避免长时间锁等待超时触发事务关闭，我们手动把等待锁的超时时间设置长一点, 方便死锁的时长不会因为超时而取消，这样方便鼠标移来移去操作打印日志，我们直接设置成3分钟超时。 默认时间是50秒

    SET GLOBAL innodb_lock_wait_timeout = 180

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 180   |
+--------------------------+-------+
1 row in set (0.01 sec)

    经过这两个设置以后，死锁的形成已经完全暴露出来了。

    注意, 以上的配置只用于测试环境, 生产环境切勿如此操作！！！

    再次触发死锁

    我们把 tabel user 删掉, 重新准备环境:

    use  database test_db ;

drop table user ;

CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `user` ( `name`) VALUES ( 'Tom');
 
INSERT INTO `user` ( `name`) VALUES ( 'John');

    还是像之前一样把事务操作记录表给出来， 方便起见我把 事务 ID 直接标注上去:

    时间事务 tansaction ID = 2034事务 tansaction ID = 2035
    t0START TRANSACTION;
    t1select * from user where id=3 for update ; empty set
    t2START TRANSACTION;
    t3select * from user where id=4 for update ; empty set
    t4INSERT INTO user (id, name) VALUES (3, 'Peter'); 被阻塞
    t5INSERT INTO user (id, name) VALUES (4, 'Jack'); 触发死锁
    t6ctrl+C, 然后 输入 ROLLBACK ; 手动回滚事务
    t7获得锁, insert 成功, commit

    这里因为关闭了死锁时自动回滚, 所以事务最终都会因为超时而被取消，而不是遇到死锁的后一个事务回滚，前一个事务自动提交。在 t6 时候，我们在执行sql的命令行键入 CTRL+C退出，然后输入 ROLLBACK 手动回滚事务。 t7时刻检测到死锁条件被破坏，获取到了锁资源, insert 成功。

    分析每一时刻的锁情况

    我们辛苦一个窗口，专门用例监视每一个时刻的锁情况

    SELECT * FROM performance_schema.data_locks  G;

    t1 时刻

    *************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:1078:139628715770544
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139628715770544
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:12:4:1:139628715767632
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767632
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
2 rows in set (0.00 sec)

    在 t1 时刻, 事务 2034 获取到了两把锁:

  • LOCK MODE IX 表示排他意向锁, LOCK_TYPE TABLE 表锁, 对象是整张表, LOCK_DATA NULL 表示并没有实际对表的某一行进行操作, LOCK_STATUS: GRANTED 表示已获得
  • LOCK MODE X 锁 表示排他锁代表即将对某一行进行写操作, LOCK_STATUS: GRANTED 表示已获得, 重要的是 LOCK_TYPE: RECORD 和 LOCK_DATA: supremum pseudo-record, supremum 代表锁的一个上界 类似于正无穷, pseudo-record 表示 伪行锁， 表示并不是真的锁了某一行， 因为要锁的 id =3 不存在。

    • 回顾一下, 意向锁定协议如下：

  • 在事务可以获取表中行的共享锁之前，它必须首先获取IS表上的锁或更强的锁。
  • 在事务可以获取表中行的排他锁之前，它必须首先获取IX 表上的锁。

    • 规则2解释了为什么 获取 X supremum 行锁 之前需要获取 IX 表锁

    t3 时刻

    *************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792457592:1078:139628715782800
ENGINE_TRANSACTION_ID: 2035
            THREAD_ID: 62
             EVENT_ID: 71
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139628715782800
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792457592:12:4:1:139628715779888
ENGINE_TRANSACTION_ID: 2035
            THREAD_ID: 62
             EVENT_ID: 71
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715779888
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:1078:139628715770544
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139628715770544
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:12:4:1:139628715767632
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767632
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
4 rows in set (0.00 sec)

    在 t3 时刻 , 事务 2034 获取到的两把锁不变。
    事务 2035 也获取到了两把锁 :

  • LOCK MODE IX 表示排他意向锁, LOCK_TYPE TABLE 表锁, 对象是整张表, LOCK_DATA NULL 表示并没有实际对表的某一行进行操作, LOCK_STATUS: GRANTED 表示已获得

  • LOCK MODE X 锁 表示排他锁代表即将对某一行进行写操作, LOCK_STATUS: GRANTED 表示已获得, 重要的是 LOCK_TYPE: RECORD 和 LOCK_DATA: supremum pseudo-record, supremum 代表锁的一个上界 类似于正无穷, pseudo-record 表示 伪行锁， 表示并不是真的锁了某一行， 因为要锁的 id =4 不存在。

    • t4 时刻

    *************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792457592:1078:139628715782800
ENGINE_TRANSACTION_ID: 2035
            THREAD_ID: 62
             EVENT_ID: 71
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139628715782800
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792457592:12:4:1:139628715779888
ENGINE_TRANSACTION_ID: 2035
            THREAD_ID: 62
             EVENT_ID: 71
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715779888
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:1078:139628715770544
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139628715770544
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:12:4:1:139628715767632
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767632
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:12:4:1:139628715767976
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 149
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767976
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: supremum pseudo-record
5 rows in set (0.00 sec)

    t4时刻, 2034 事务 准备insert id=3 这条记录了, 准备获取 一把 IX行锁, 因为 id =3 还不存在， 所以也是一把 supremum pseudo-record 类型。这把 IX supremum pseudo-record 行锁 被阻塞掉了。原因就是它 被 2035 事务的 IX 表锁 阻塞了。

    2034 想要获取 IX 行锁, 需要获取 IX 表锁

    t5 时刻

    *************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792457592:1078:139628715782800
ENGINE_TRANSACTION_ID: 2035
            THREAD_ID: 62
             EVENT_ID: 71
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139628715782800
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792457592:12:4:1:139628715779888
ENGINE_TRANSACTION_ID: 2035
            THREAD_ID: 62
             EVENT_ID: 71
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715779888
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792457592:12:4:1:139628715780232
ENGINE_TRANSACTION_ID: 2035
            THREAD_ID: 62
             EVENT_ID: 72
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715780232
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: supremum pseudo-record
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:1078:139628715770544
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139628715770544
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:12:4:1:139628715767632
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767632
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:12:4:1:139628715767976
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 149
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767976
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: supremum pseudo-record
6 rows in set (0.00 sec)

    t5 时刻 2035 事务也在等待一把IX 行锁, 而这把行锁被 2034 的IX 表锁给阻塞了。至此已经破案了，两个事务的循环等待条件成立， 如下图所示:

    1711774374516.png

    t7 时刻

    t6 时刻我们手动 ROLLBACK了2035 事务， 所以 t7 时刻和上面第一次查询 performance_schema.data_locks 表的记录一致，符合预期

    *************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:1078:139628715770544
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139628715770544
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:12:4:1:139628715767632
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 148
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767632
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:12:4:1:139628715767976
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 149
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715767976
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139628792455976:12:4:4:139628715768320
ENGINE_TRANSACTION_ID: 2034
            THREAD_ID: 59
             EVENT_ID: 149
        OBJECT_SCHEMA: test_db
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139628715768320
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
4 rows in set (0.00 sec)

    总结一下

    总结一下如何分析死锁, 排查死锁:

  • 对锁定分类要清楚, 不同锁的存在意义, 锁的范围, B+树是如何进行索引的，知识点要能够串联起来，单纯地死记硬背是很难理解的
  • 对一些常见的操作, 比如 增删改查过程如何获取锁要有个大概的了解，看完这篇文章，相信大家都可以自己去摸索，我就不举例了
  • 对事务的的隔离级别要有清楚的认知, 隔离级别和锁是密切相关的。 退一步说 如果完全不考虑一致性，根本就不需要锁
  • innodb status 只保留了案发现场, 但是是如何来的并不能推测，但如果你有经验，可以基于此进行猜测，多少能得出一些有用信息
  • 更进一步, 我们可以查看 performance_schema.data_locks 这张表，看看事务获取到了哪些锁
  • 想要完整复现死锁的每一步，我们可以把锁等待时间手动设置的特别长, 而且需要手动关闭事务的自动提交，关闭死锁检测和死锁的自动回滚，但生产环境不用如此操作

    • 下面是一些预防死锁的建议：

  • 优化事务，避免什么操作都一股脑的上事务，事务的存留时间是越短越好
  • 打开SQL的死锁检测，死锁时自动回滚
  • 将SQL的等待锁时间降低，默认是50秒，避免长时间等待锁
  • 优化查询语句，比如减少在一个select语句里面查询过多的表，这样会导致同时锁定多张表
  • 优化索引，尽量选择区分度高的字段作为索引，避免多个查询命中相同的索引(形成行锁)或者索引范围(形成间隙锁)

    • 巨人的肩膀:

  • dev.mysql.com/doc/refman/…
  • dev.mysql.com/doc/refman/…