Mysql 之排查死锁及阻塞

Java菜鸟在北京

已于 2025-01-21 23:30:19 修改

阅读量1.2k

点赞数 21

文章标签： mysql 排查死锁或阻塞

于 2025-01-17 20:47:16 首次发布

本文链接：https://blog.youkuaiyun.com/u012246511/article/details/145193909

版权

既然要排查，就得有死锁：

① 本文先来个死锁现场，更有助于没接触过死锁的朋友切身实践下；

② 然后通过命令 SHOW ENGINE INNODB STATUS 分析事务和锁的状态；

③ 最后使用 INFORMATION_SCHEMA 下三张表(MEMORY引擎)更简单的监控问题；

④ 命令show full processlist 也会提及。

理论是基石、实战是产物，相信看完定有所得 😊。

1、死锁现场

2. show engine innodb status 命令

3. 巧用 INFORMATION_SCHEMA

3.1 INNODB_TRX 表

3.2 INNODB_LOCKS 表

3.3 INNODB_LOCK_WAITS 表

4. show full processlist 命令

5. 总结

1、死锁现场

还是基于Mysql 5.7 ，在Navicat中打开两个查询窗口，会话A、B，然后依次执行，代码如下：

-- 会话A 
begin;

select * from wuzhen.t_news where id=5 for update;
SELECT SLEEP(7);
update wuzhen.t_news set remark='测试' where id=4 ;

commit;


-- 会话B

begin;

select * from wuzhen.t_news where id =4 for update ;
SELECT SLEEP(5);
select * from wuzhen.t_news where id =5 for update ;

commit;

会话A、B中存在经典的AB-BA死锁。会话A被回滚，而B得以正常执行，如图：

题外话：

InnoDB在检测到死锁时，将会基于成本评估策略(本文3.1小节有提及)选择回滚谁；

如果我们在会话A的update前再加俩不存在行锁竞争的update，大概率会回滚会话B，上图

2. show engine innodb status 命令

该命令诚如中文直译：展示InnoDB存储引擎状态。

它不仅有事务和锁的相关信息，还包含后台线程、日志、缓冲池及内存、行操作等关键信息，可以基于这些指标值进行评估和优化。

简单说下该日志的格式，两个分隔线中间是标题，其下就是具体详情。

------------------------
LATEST DETECTED DEADLOCK # 标题
------------------------

XXXXXX # 具体详情

完整日志附在下面，我们分析下该命令有关死锁(最近检测到的死锁)的信息:

检测时间：2025-01-17 13:07:44 0x580：此为死锁被检测到的时间戳。
事务信息

事务 (1)：

基本信息：

TRANSACTION 139617, ACTIVE 6 sec starting index read：事务编号为 139617，已经处于活动状态 6 秒，正在开始索引读取操作。
mysql tables in use 1, locked 1：该事务正在使用 1 个 MySQL 表，并且锁定了 1 个表。
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)：处于锁等待状态，包含 3 个锁结构，堆大小为 1136，有 2 个行锁。
MySQL thread id 106, OS thread handle 10868, query id 11590 localhost 127.0.0.1 root statistics：事务运行在 MySQL 线程 106 上，操作系统线程句柄是 10868，查询 ID 为 11590，由本地的 root 用户发起，该事务正在进行统计操作。

操作及锁等待信息：

select * from wuzhen.t_news where id =5 for update：该事务正在执行一个 SELECT 查询操作，并且使用了 FOR UPDATE 子句，这意味着它会对查询结果集的行进行加锁，以防止其他事务修改这些行。
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:：显示该事务正在等待锁的授予。
RECORD LOCKS space id 339 page no 16 n bits 80 index PRIMARY of table 'wuzhen'.'t_news' trx id 139617 lock_mode X locks rec but not gap waiting：具体说明该事务正在等待 wuzhen.t_news 表 PRIMARY 索引上的锁，锁模式是 X locks rec but not gap waiting，即排它锁，锁定记录但不锁定间隙，并且处于等待状态。同时提供了该记录的物理记录信息，包括多个字段的长度、十六进制数据及部分可解析的字符内容，这些信息可用于进一步的深入分析。

事务 (2)：

基本信息：

TRANSACTION 139616, ACTIVE 7 sec starting index read事务编号为 139616，已经处于活动状态 7 秒，正在开始索引读取操作。
mysql tables in use 1, locked 1：使用 1 个 MySQL 表并锁定 1 个表。
3 lock struct(s), heap size 1136, 2 row lock(s)：具有 3 个锁结构，堆大小为 1136，有 2 个行锁。
MySQL thread id 105, OS thread handle 1408, query id 11591 localhost 127.0.0.1 root updating：该事务运行在 MySQL 线程 105 上，操作系统线程句柄是 1408，查询 ID 为 11591，由本地的 root 用户发起，正在进行更新操作。

操作及锁信息：

update wuzhen.t_news set remark='测试' where id=4：该事务正在执行一个更新操作，将 wuzhen.t_news 表中 id = 4 的行的 remark 字段更新为 测试。
*** (2) HOLDS THE LOCK(S):：显示该事务持有的锁信息。
RECORD LOCKS space id 339 page no 16 n bits 80 index PRIMARY of table 'wuzhen'.'t_news' trx id 139616 lock_mode X locks rec but not gap：持有 wuzhen.t_news 表 PRIMARY 索引上的锁，锁模式是 X locks rec but not gap，即排它锁，锁定记录但不锁定间隙。同样给出了持有锁的记录的物理记录信息，部分与事务 (1) 等待锁的记录信息重叠，说明二者涉及对相同部分数据的操作。
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:：显示该事务也在等待另一个锁。
RECORD LOCKS space id 339 page no 16 n bits 80 index PRIMARY of table 'wuzhen'.'t_news' trx id 139616 lock_mode X locks rec but not gap waiting：正在等待 wuzhen.t_news 表 PRIMARY 索引上的另一个锁，锁模式是 X locks rec but not gap waiting，处于等待状态，并提供了相应记录的详细物理记录信息。

Mysql检测到死锁并解决

*** WE ROLL BACK TRANSACTION (2)：系统检测到死锁后，决定回滚事务 (2) 【也就是死锁现场会话A中 1213异常】来解决死锁问题，以打破两个事务之间的相互等待状态。

InnoDB		
=====================================
2025-01-17 13:07:48 0x1744 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 868 srv_active, 0 srv_shutdown, 58250 srv_idle
srv_master_thread log flush and writes: 59118
----------
SEMAPHORES