MySQL死锁与等待排查：三大核心视图详解及处理方案

测试环境准备与锁场景复现

为了演示如何捕获和分析锁情况，我们需要模拟一个典型的“长事务持有锁 + 新事务请求锁”的场景。以下操作基于MySQL 5.7版本（版本检查如下）：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.38-log |
+------------+
1 row in set (0.01 sec)

第1步：建立目标表结构

创建一张包含主键的简单测试表 t，用于后续加锁实验：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

第2步：填充初始数据

向表中插入几条记录，确保有数据可供事务锁定：

INSERT INTO t (id, name) VALUES 
(1, 'a'), (2, 's'), (3, 'c'), (4, 'd'), (5, 'e');

第3步：A会话开启事务并加排他锁

在第一个终端（Session A）中，开始一个显式事务，并查询 id<5 的记录加上 for update 排他锁：

begin;
select * from t where id<5 for update;

此时，事务已成功锁定 id 为 1, 2, 3, 4 的行，且尚未提交或回滚。

第4步：B会话尝试插入引发锁等待

在第二个终端（Session B）中，尝试向同一表插入一条 id=0 的记录。由于 id=0 不在已锁范围内，通常不会直接冲突，但为了确保演示效果，我们可以修改查询条件或假设插入发生在被锁定的间隙/范围内（此处原文逻辑为 insert into t values(0,'null') 造成等待，这可能依赖于具体的索引覆盖情况或并发压力，标准解释是如果插入位置触及间隙锁或被锁范围则等待。在原文示例中，它显示产生了 LOCK WAIT 状态）：

insert into t values(0,'null');

如果在 Session B 中该语句挂起（HANG住），说明产生了锁等待。接下来我们引入排查工具。

查看锁情况的三大核心视图

MySQL 将锁相关信息存储在 information_schema 数据库下。排查思路通常是：
1. 先看谁在运行（trx_state）；
2. 再看谁等谁（lock_waits关系）；
3. 最后看锁的具体细节（locks模式）。

第1步：分析 innodb_trx 表（当前活跃事务）

此表记录了所有正在运行的事务详细信息。重点关注 trx_state（事务状态）和 trx_mysql_thread_id（对应的线程ID）。针对上面的例子，执行查询：

use information_schema;
select * from innodb_trx\G;

观察输出结果：

• trx_id: 8553, trx_state: LOCK WAIT: 表示这个事务正在等待锁（即 Session B）。
• trx_wait_started: 显示等待开始的时间。
• trx_mysql_thread_id: 22: 这是等待方 Session B 连接的 Thread ID。
• trx_query: 正在执行的 SQL 语句 insert into t values...。
• trx_id: 8552, trx_state: RUNNING: 表示正常运行的事务（即 Session A）。
• trx_rows_locked: 5: 持有锁的数据行数。
• trx_mysql_thread_id: 20: 这是持有锁方 Session A 连接的 Thread ID。

同时可结合 show full processlist; 查看更直观的线程列表，确认 Thread ID 20 处于 Sleep 状态（持有锁后未执行后续动作），而 Thread ID 22 处于 Query 状态且卡在更新操作中。

第2步：分析 innodb_lock_waits 表（锁依赖关系）

此表专门描述锁的请求者与阻塞者的对应关系。对于刚才的示例：

select * from innodb_lock_waits\G;

关键字段解析：

• requesting_trx_id: 8553: 正在申请锁的事务ID（受害者）。
• blocking_trx_id: 8552: 阻碍锁释放的事务ID（加害者）。

通过这个表可以迅速确认是哪个事务在“挡路”。

第3步：分析 innodb_locks 表（锁属性详情）

虽然在新版MySQL中该表已被废弃并合并入其他视图，但在5.7版本仍可见。它提供了锁的物理分布信息：

select * from innodb_locks\G;

关键信息：

• lock_table: 被锁定的表名 `ray`.`t`。
• lock_index: 使用的索引，这里是 PRIMARY。
• lock_mode: 锁的模式，如 X,GAP（排他锁+间隙锁）或 X（纯排他锁）。
• lock_data: 具体的锁住的数据值（如 1）。

问题解决与处理策略

综合以上信息，我们可以得出结论：Thread ID 20 (Transaction 8552) 持有了排他锁，导致 Thread ID 22 (Transaction 8553) 无法继续执行 INSERT 操作而产生阻塞。

常规处理方法：KILL 阻塞方或被阻塞方？

通常情况下，杀死持有锁且长时间不提交的长事务（Thread ID 20）是解决阻塞的最快手段，因为它能立刻释放锁资源。但如果业务允许，也可以尝试杀死发起等待的请求（Thread ID 22），使其放弃当前操作。

本例中，我们选择杀死持有锁的会话 Thread ID 20：

kill 20;

执行成功后，Session B 的 INSERT 操作通常会立即继续执行（前提是数据合法性无其他冲突），或者抛出 “Lock wait timeout exceeded” 错误（取决于具体配置和锁竞争情况，此处原因为锁被释放，若Session B还在排队则可能重新获取）。原文提示杀除后Session B恢复正常。

进阶方案：使用 pt-kill 工具

在生产环境中，手动 Kill 线程存在风险（如丢失未保存的工作、触发断连重连风暴）。推荐使用 Percona Toolkit 中的 pt-kill 工具。它可以基于用户、时间、命令类型等条件自动识别并终止异常的 MySQL 连接，更加安全可控。例如：可以通过检测睡眠超过一定时间的线程，或在特定模式下运行的线程进行批量清理。

总结

排查 MySQL 锁等待问题的标准流程是：
1. 使用 show full processlist 发现异常挂起的 Query。
2. 查询 information_schema.innodb_trx 找到对应的 Transaction ID 和 Thread ID。
3. 核对 innodb_lock_waits 确认谁是阻塞者。
4. 根据业务重要性，权衡决定是 KILL 掉阻塞方还是被阻塞方，或者优化 SQL 缩短事务持续时间。

以上就是 MySQL 锁情况及排查处理的详细内容，更多关于 SQL 性能调优的资料请关注本站其它相关文章！