Mysql 之 阻塞与死锁详解
本文基于MySQL 5.7.11的默认引擎InnoDB 1.2.x ，从复现问题(模拟阻塞与死锁)、产生原因、解决办法、编码建议等角度全面解析阻塞和死锁；
第四节也会提及使用命令和INFORMATION_SCHEMA下的表来查看事务&锁的情况。
目录
1. 阻塞
1.1 阻塞配置
1.2 模拟阻塞
1.3 阻塞超时带来的问题
1.4 划重点
2 . 死锁
2.1 基本概念
2.2 等待图（Wait - for Graph）
2.3 AB-BA死锁示例
2.4 X锁&S锁互斥 死锁示例
3. 编码建议
4. 查看运行中的事务&锁
5. 总结
1. 阻塞
首先明确一点，阻塞与死锁是不同的。但它俩发生的场景往往相同：通常都在最糟糕的时候发生，比如产线业务最繁忙、高并发的场景下 🤭。
阻塞是 InnoDB 引擎的正常行为，它为了确保事务可以正常并发，保证数据一致性等；
但会影响性能，尤其在高并发情况下。两者差异如下图
| 特性 | 阻塞 | 死锁 |
|---|---|---|
| 定义 | 事务等待资源释放，才能继续执行 | 两个或多个事务互相等待对方持有的资源，形成循环等待。 |
| 发生条件 | 事务请求锁时，资源已被其他事务锁定。 | 事务之间相互等待对方的锁，并且没有办法继续执行。 |
| 处理方式 | 事务会在锁释放后继续执行，或者等待超时抛出异常 | MySQL 自动检测并回滚一个事务，以解决死锁问题。 |
| 性能影响 | 会导致事务等待，延迟响应，可能影响整体系统性能。 | 死锁回滚需要额外的时间和资源，可能影响事务的响应时间。 |
| 发生概率 | 常见，特别是在高并发情况下。 | 较少发生，但一旦发生，通常需要手动优化事务的执行顺序。 |
| 影响范围 | 一般影响单个事务的执行，但不会导致系统崩溃。 | 死锁可能导致多个事务的失败，影响系统稳定性。 |
1.1 阻塞配置
InnoDB中有俩参数来控制阻塞的时间和策略。
- innodb_lock_wait_timeout 用来控制等待时间，默认是50秒，可动态设置Session | Global级；Global在重启后将丢失；Global修改时在这期间存活的Session也是不生效的；也可通过配置文件静态修改。
- innodb_rollback_on_timeout 控制等待超时的事务是否回滚，默认是OFF 不回滚；该参数为只读，不支持动态设置；需要在 MySQL 的配置文件（通常是
my.cnf或my.ini）中进行修改，然后重启服务才生效。
备注：有些文章甚至GPT 有关innodb_rollback_on_timeout 都说支持动态设置。其实官网对这俩参数的配置和作用说的很清楚，附上
innodb_lock_wait_timeout
innodb_rollback_on_timeout
用以下命令动态设置Session级等待时间，设置global级只需换成 set @@global.X = Y
-- 查看等待时间
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 设置等待时间
SET @@session.innodb_lock_wait_timeout = 10;
再来看看回滚策略，默认是OFF，如果尝试动态修改将报错。须在配置文件中配置后重启MySQL才能生效。
-- 查看回滚策略
SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';
-- 不支持动态设置
SET @@innodb_rollback_on_timeout = on;
1.2 模拟阻塞
这里我们用Navicat打开俩查询窗口来当不同的会话用，有张t_news新闻表里有10条记录，主键id从1-10，如图：
会话A中开始事务，在id<5上加排他锁，模拟业务逻辑（休眠Sleep 20秒），最后提交。
-- 会话A
begin;
select * from wuzhen.t_news where id<5 for update;
SELECT SLEEP(20);
commit;会话B先修改Session级等待超时为5秒，开启事务然后插入两条数据id分别为22、23，接着尝试获取id=5的排他锁，最后提交。
-- 会话B
-- 修改会话B的等待超时
SET @@innodb_lock_wait_timeout = 5;
-- 开启事务
begin;
-- 插入两条数据
insert into wuzhen.t_news (id) values (22);
insert into wuzhen.t_news (id) values (23);
select * from wuzhen.t_news where id =5 for update;
Commit;先运行会话A紧接着会话B，发现会话A获取排他锁成功后进入20秒休眠，最后正常结束；
会话B在插入两条数据后，尝试获取id=5的排他锁，但是5秒后超时了，上图：
会话B此时抛出了1205错误，这就是阻塞在超时后的报错信息。
1.3 阻塞超时带来的问题
其实依次执行上面会话A、B 除了性能的降低，还存在更为严重的问题。
问1：
会话B 插入的两条数据为什么没有阻塞？为什么id=5时阻塞了？(会话A中明明是id<5)
答1：
因为会话A中 id<5 采用了 Next-Key Lock算法，它锁定了小于5的所有记录，并且也锁住了5本身。插入的两条id不在这个范围，但是id=5在。
问2：
会话B 中插入的两条数据是提交入库了还是回滚了？
答2：
其实会话B 中两条数据既没有提交，也没有回滚，而是一直暂存在当前事务中，这种状态非常危险。
在会话B的事务中select * from wuzhen.t_news 是可以看到新增的2条数据，由于隔离性其他事务不可见，这会带来隐藏很深的阻塞问题。
我们在会话A中执行以下代码，将会发生莫名奇妙的阻塞，如图
1.4 划重点
1、默认情况下InnoDB 不会回滚阻塞超时引发的错误 ，牢记这一点非常重要。
这种设计让开发者有更多的控制权，可以根据业务需求进行回滚、提交、重试、忽略错误继续执行等。试想下如果你前面有一百万条数据插入都正常，却在最后当前读上阻塞报错了，而这个读跟前面的插入毫无关系，此可都回滚的话，岂不是很不合理(当然，代码这么设计本身就不合理🙂)。
2、抛出1205 异常后既没 commit也没rollback，这种状态非常危险，因此我们在程序中必须选择提交或回滚。
有关减少阻塞提高并发性能的方式，不能一概而论(没有银弹)。基本思路是 首先需要清楚的知道阻塞产生的条件，然后分析业务实现层面是否有减少或完全避免资源竞争的可能性。
比如上面会话A、B的代码，业务允许的话，我们完全可以去掉声明式的排他锁，根据Next-Key Lock算法原理规避掉资源竞争，再比如把那模拟业务的sleep 20秒拆分出来等等....
2 . 死锁
2.1 基本概念
死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的互相等待。这种互相等待如果无外力作用，俩事务都将无法执行下去，形成死锁。
死锁是不正常的，通常由于不合理编码、滥用锁、事务边界模糊等造成。
2.2 等待图（Wait - for Graph）
解决死锁最简单的一种办法就是超时，事务互相等待时其中某个因先超时而回滚，其他事务得以继续进行；但回滚的如果是一个权重比较大的(undo log很多)，显然很不合理。
InnoDB采用的是 等待图的方式来主动检测死锁，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁。InnoDB引擎通常会选择回滚undo log 量较小的事务。
请牢记：InnoDB并不会回滚大部分的错误异常，但死锁除外。 因此当我们捕获了1213死锁异常时，其实并不需要对其进行回滚。
等待图要求数据库保存两种信息，锁的信息链表和事务等待链表；通过这俩链表可以构造出一张图，如果这个图中存在回路，就代表存在死锁；如图，事务1和事务3存在回路即存在死锁。
2.3 AB-BA死锁示例
这是典型的资源竞争顺序不一致造成的死锁，当事务B 抛出1213异常后自动回滚，事务A得以正常执行下去。
大多数死锁都会被InnoDB自动侦测到，我们需要做的是找到死锁发生的条件，进而优化的。
| 时间 | 事务 A | 事务 B |
|---|---|---|
| 1 | BEGIN; | |
| 2 | mysql>SELECT * FROM t WHERE a = 1 FOR UPDATE; ********* 1. row *********** a: 1 1 row in set (0.00 sec) | BEGIN |
| 3 | mysql>SELECT * FROM t WHERE a = 2 FOR UPDATE; ********* 1. row *********** a: 2 1 row in set (0.00 sec) | |
| 4 | mysql>SELECT * FROM t WHERE a = 2 FOR UPDATE; #等待 | |
| 5 | mysql>SELECT * FROM t WHERE a = 1 FOR UPDATE; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
2.4 X锁&S锁互斥 死锁示例
事务A 持有待插入记录的下一个记录的X锁(排他锁)，但事务B的在等待队列中还存在一个S锁(共享锁)请求，则可能发生死锁。InnoDB选择回滚 undo log 大的事务，这与AB-BA的处理方式有所不同。
| 时间 | 事务 A | 事务 B |
|---|---|---|
| 1 | BEGIN; | |
| 2 | BEGIN; | |
| 3 | SELECT * FROM t WHERE a = 4 FOR UPDATE; | |
| 4 | SELECT * FROM t WHERE a <= 4 LOCK IN SHARE MODE; -- 等待 | |
| 5 | INSERT INTO t VALUES(3); -- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | |
| 6 | 事务获得锁，正常运行 |
3. 编码建议
- 事务隔离级别选择：使用合适的事务隔离级别，如
READ COMMITTED，降低锁的持有时间和范围，减少锁冲突概率。 - 锁粒度细化：尽量使用行级锁，避免表级锁、间隙锁等。
- 事务操作顺序：保证事务操作数据的顺序一致，避免形成回路造成死锁。
- 避免执行时间长的大事务：尽量使事务简短(拆成小事务)，减少执行时间尽快释放锁。
- 索引优化：确保表上有合适的索引，避免全表扫描，降低锁冲突。
- 超时设置：设置合理的超时时间，避免长时间等待锁。
- 避免隐式提交：确保事务操作的显式开始和结束，防止意外提交导致的锁问题。
-
定期审视日志：打开慢查询日志（MySQL默认为关闭）、错误日志，定期分析和优化这些SQL可以减少阻塞和死锁的发生。
4. 查看运行中的事务&锁
（本章内容打算找时间单独详解，到时候附上链接）
在InnoDB 1.0 之前，我们只能通过 SHOW FULL PROCESSLIST ，SHOW ENGINE INNODB STATUS 这样的命令查看当前数据库中事务和锁的情况。
从InnoDB1.0 开始在 INFORMATION_SCHEMA 下添加了多张表，可以更简单的监控和分析当前事务&锁的各种问题。
5. 总结
编码中如果偶尔写出慢SQL且能及时优化掉是成长，如果写的全是慢SQL肯定是不能接受的；
如果偶尔导致死锁并解决了那是成长，如果能经常码出死锁那就要好好反思下了...🙂
如何避免阻塞减少死锁，其中涉及到InnoDB底层原理，包括但不限于事务、锁、索引、算法策略等方面的知识。 有兴趣可以读一读 《高性能MySQL》《MySQL技术内幕》等书，当然直接去看官方文档也是极好的。