MySQL-原理篇-④锁（死锁）

学习核心

• 死锁（以特定案例拆解死锁场景）
  • 死锁是怎么发生的？
  • 发生死锁的条件？
  • 如何排查死锁问题？
  • 如何避免死锁问题？

学习资料

• 加了什么锁导致死锁？
• 解决死锁之路

todo：案例截图

加了什么锁导致死锁

案例准备（MySQL8.0.21 可重复读隔离级别）

​ 构建t_student表，除了id字段其他都是普通字段，插入相关数据。

CREATE TABLE `t_student` (
  `id` int NOT NULL,
  `no` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t_student(id,no,name,age,score) values
 (15,'S0001','Bob',25,34),(18,'S0002','Alice',24,77),(20,'S0003','Jim',24,5),
 (30,'S0004','Eric',23,91),(37,'S0005','Tom',22,22),(49,'S0006','Tom',25,83),
 (50,'S0007','Rose',23,89);
 
 # 事务A执行
 time1:update t_student set score = 100 where id =25;
 time3:insert into t_student(id,no,name,age,score) values(25,'S0025','sony',28,90);
 
 # 事务B执行
 time2:update t_student set score = 100 where id =26;
 time4:insert into t_student(id,no,name,age,score) values(26,'S0026','ace',28,90);
 
 # 检索事务占用锁状态
 select * from performance_schema.data_locks;

SQL 执行顺序：

1.为什么会发生死锁？

​ 根据上述SQL执行步骤，拆解每个time时刻SQL执行过程中加了什么锁

​ 此处根据id检索，因此对照的是主键索引（也可理解为唯一索引）的等值检索场景，可以拆分为指定记录是否存在来进行分析

​ 根据事务ID（ENGINE_TRANSACTION_ID）来定位事务关联的锁状态

time1

# 事务A执行
begin;
update t_student set score = 100 where id =25;

 # 查看锁状态
select * from performance_schema.data_locks;

• 加锁说明：
  • 表级锁：X类型的意向锁
  • 行级锁：X类型的间隙锁（加锁范围：（20,30））

问题：行级锁的加锁范围如何确定？

​ 结合结果分析，加锁类型为X类型的间隙锁，因此加锁范围的右边界值为LOAD_DATA（30）。表中按照ID排序（主键索引）（因此此处是根据id进行检索，相应表数据也要根据相应的索引值进行排序），可以跟踪到id为30的上一条记录的id值为20，因此此处加锁范围的左边界值为20。因此最终确认间隙锁的加锁范围为（20,30）

time2

# 事务B执行
begin;
update t_student set score = 100 where id =26;

# 查看锁状态
select * from performance_schema.data_locks;

• 加锁说明：
  • 表级锁：X类型的意向锁
  • 行级锁：X类型的间隙锁（加锁范围：（20,30））

​ 结合结果可知，此处事务B的间隙锁加锁范围和事务A的间隙锁加锁范围都是一样的

问题：事务A和事务B的间隙锁范围都是一样的，为什么不会冲突？

​ 两个事务的间隙锁之间是相互兼容的，不会产生冲突。参考MySQL官网给出的描述：间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享（S型）和排他（X型）的间隙锁是没有区别的，两者相互不冲突，且功能相同

​

time3

# 事务A继续执行
insert into t_student(id,no,name,age,score) values(25,'S0025','sony',28,90); # 受到阻塞，进入了等待状态

# 查看锁状态
select * from performance_schema.data_locks;

• 加锁说明：

  • 表级锁：X类型的意向锁

  • 行级锁：插入意向锁（LOCK_MODE：INSERT_INTENTION）、X类型的间隙锁（加锁范围：（20,30））

问题：何为插入意向锁？

​ 插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。

​ 如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

​ 插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的

​ 插入意向锁的生成时机：每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），现象就是 insert 语句会被阻塞

问题：time3执行语句为何受到阻塞？

​ 待插记录的记录ID是25，其下一条记录id为30，在事务B中被上了间隙锁，因此此处会生成一个插入意向锁

​ 根据上述加锁状态分析，间隙锁虽然范围重叠但本质上并不冲突。此处则是因为事务A加了一个插入意向锁（锁的记录点是25），而这个锁点恰好在事务B的间隙锁范围内，因此导致冲突。因此此处锁的状态是等待状态，现象体现在insert语句会被阻塞

time4

# 事务B继续执行
insert into t_student(id,no,name,age,score) values(26,'S0026','ace',28,90); # 受到阻塞

# 查看锁状态
select * from performance_schema.data_locks;

• 加锁说明：

  • 表级锁：X类型的意向锁

  • 行级锁：插入意向锁（LOCK_MODE：INSERT_INTENTION）、X类型的间隙锁（加锁范围：（20,30））

​ 事务 B 在生成插入意向锁时而导致被阻塞，这是因为事务 B 向事务 A 生成的范围为 (20, 30) 的间隙锁插入了一条记录，而插入意向锁和间隙锁是冲突的，所以事务 B 在获取插入意向锁时就陷入了等待状态

2.死锁发生的条件

综合上述场景分析，则可理解为什么会发生死锁？

​ 事务 A 和事务 B 在执行完后 update 语句后都持有范围为(20, 30）的间隙锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁

​ 综合上述场景分析，有如下总结：

• 两个事务即使生成的间隙锁的范围是一样的，也不会发生冲突，因为间隙锁目的是为了防止其他事务插入数据，因此间隙锁与间隙锁之间是相互兼容的
• 在执行插入语句时，如果插入的记录在其他事务持有间隙锁范围内，插入语句就会被阻塞，因为插入语句在碰到间隙锁时，会生成一个插入意向锁，然后插入意向锁和间隙锁之间是互斥的关系
• 如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁

3.如何排查死锁问题

​ 上述步骤是通过一步步定位每个步骤中事务的执行过程中的加锁情况，进而拆解死锁是如何产生的。但是现实场景中往往可能并不明确每个SQL执行的流程，所以没法准确定位死锁的源头，因此需要通过死锁日志来进行排查。

​ 在没有开启“主动死锁检测”（一种数据库死锁的解除机制）的前提下，可通过死锁日志（存在限制，只能拿到最近一次的死锁日志）进行跟踪：SHOW ENGINE INNODB STATUS;（以上述案例为参考，查看死锁日志进行跟踪）

• 分析死锁日志：https://www.toberoot.com/database/mysql/dba_mysql/tec/mysql_5.7_%E6%AD%BB%E9%94%81%E5%88%86%E6%9E%90.html

MySQL监控机制

​ MySQL 提供了一套 InnoDb 的监控机制，用于周期性（每隔 15 秒）输出 InnoDb 的运行状态到 mysqld 服务的标准错误输出（stderr）。默认情况下监控是关闭的，只有当需要分析问题时再开启，并且在分析问题之后，建议将监控关闭，因为它对数据库的性能有一定影响，另外每 15 秒输出一次日志，会使日志文件变得特别大。InnoDb 的监控主要分为四种：标准监控（Standard InnoDB Monitor）、锁监控（InnoDB Lock Monitor）、表空间监控（InnoDB Tablespace Monitor）和表监控（InnoDB Table Monitor），后两种监控已经基本上废弃了

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

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

-- 开启标准监控
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 之后，可以通过设置系统参数来开启锁监控

-- 开启标准监控
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

4.如何避免死锁问题

​ 死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒

  当发生超时后，提示错误：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启

  当检测到死锁后，提示错误：ERROR 1213 (48001): Deadlock found when trying to get lock; try restarting transaction

​ 可通过上述策略来避免死锁，也可以回归业务的角度来预防死锁，对订单做幂等性校验的目的是为了保证不会出现重复的订单，可以直接将 order_no 字段设置为唯一索引列，利用它的唯一性来保证订单表不会出现重复的订单，不过有一点不好的地方就是在插入一个已经存在的订单记录时就会抛出异常。