mysql如何查看哪个SQL语句持有锁_关联innodb_locks与processlist

首页

数据库

热心网友

转载

2026-04-29

MySQL锁排查：如何精准定位“谁持锁”及其SQL语句

mysql如何查看哪个SQL语句持有锁_关联innodb_locks与processlist

在排查数据库锁等待问题时，一个常见的困惑是：明明看到了锁，却怎么也找不到是哪个具体的SQL语句持有它。这往往不是查询写错了，而是因为INNODB_LOCKS和PROCESSLIST这两个关键信息表，在设计上就没有直接的关联字段。更棘手的是，不同MySQL版本的行为差异显著，强行关联很容易导致结果遗漏或错误。

为什么不能直接 JOIN `INNODB_LOCKS` 和 `PROCESSLIST`

问题的根源在于表结构设计。INNODB_LOCKS表记录的是锁的元信息，比如锁类型、模式、对象，但它只包含持有锁的事务ID（LOCK_TRX_ID），没有线程ID。反过来，PROCESSLIST表展示的是所有连接线程的运行时状态，包含线程ID（ID），却没有事务ID。这就好比你有两把钥匙，却找不到能同时打开的两把锁——它们之间缺少一个共用的“钥匙孔”。

此外，还有一个版本演进带来的关键变化：从MySQL 8.0.24开始，INNODB_LOCKS已被官方标记为废弃（deprecated），未来的方向是统一使用performance_schema.data_locks。即使在仍可使用的5.7版本中，它也只反映“当前持有锁的事务”的一个瞬间状态，并不保证实时性，也不会包含那些已经释放但尚未被后台清理的锁残留信息。

信息割裂：INNODB_LOCKS不存储SQL文本，而PROCESSLIST的INFO字段在长事务空闲时可能为空，或被截断。
版本差异：事务ID（TRX_ID）和线程ID（THREAD_ID）的格式在不同版本中不同。5.7中是十进制整数，8.0+则变成了十六进制字符串，关联前必须进行类型转换。

MySQL 5.7 正确关联方式：用 `INNODB_TRX` 当中转表

既然两者无法直连，我们就需要一个“中间人”。在5.7环境中，INNODB_TRX表就是这个完美的桥梁。它同时包含了事务ID（TRX_ID）和对应的MySQL线程ID（TRX_MYSQL_THREAD_ID）。完整的关联路径是：INNODB_LOCKS → INNODB_TRX → PROCESSLIST。

执行前，请确保你的账号拥有PROCESS权限，否则在PROCESSLIST中只能看到自己的连接线程。

SELECT
  pl.ID AS thread_id,
  pl.USER,
  pl.HOST,
  pl.DB,
  pl.COMMAND,
  pl.TIME,
  pl.STATE,
  pl.INFO AS executing_sql,
  trx.TRX_ID,
  trx.TRX_STARTED,
  trx.TRX_STATE,
  trx.TRX_QUERY,
  locks.LOCK_TRX_ID,
  locks.LOCK_MODE,
  locks.LOCK_TYPE,
  locks.LOCK_TABLE,
  locks.LOCK_INDEX
FROM information_schema.INNODB_LOCKS locks
INNER JOIN information_schema.INNODB_TRX trx
  ON locks.LOCK_TRX_ID = trx.TRX_ID
INNER JOIN information_schema.PROCESSLIST pl
  ON trx.TRX_MYSQL_THREAD_ID = pl.ID
ORDER BY trx.TRX_STARTED;

使用这个查询时，有几点需要特别注意：

它只返回当前正持有锁且事务仍活跃的记录。如果事务已经提交，锁随之释放，就不会出现在结果中。
TRX_QUERY字段记录的是事务内最近执行的一条语句，不一定是导致加锁的那条SQL。
当PL.INFO为空而TRX_QUERY有值时，应优先参考后者；如果两者都为空，则表明该事务处于空闲状态（如Sleep），但锁尚未释放。

MySQL 8.0+ 推荐用 `performance_schema` 替代

对于MySQL 8.0+的用户，官方推荐路径已经转向performance_schema。虽然data_locks表同样不直接包含线程信息，但通过与INNODB_TRX和threads表关联，可以获得更全面、更可靠的信息。

以下是推荐的查询组合：

SELECT
  t.PROCESSLIST_ID AS thread_id,
  t.PROCESSLIST_USER AS user,
  t.PROCESSLIST_HOST AS host,
  t.PROCESSLIST_DB AS db,
  t.PROCESSLIST_COMMAND AS command,
  t.PROCESSLIST_TIME AS time,
  t.PROCESSLIST_STATE AS state,
  t.PROCESSLIST_INFO AS executing_sql,
  trx.TRX_ID,
  trx.TRX_STARTED,
  trx.TRX_STATE,
  trx.TRX_QUERY,
  dl.ENGINE_TRANSACTION_ID,
  dl.OBJECT_SCHEMA,
  dl.OBJECT_NAME,
  dl.INDEX_NAME,
  dl.LOCK_TYPE,
  dl.LOCK_MODE,
  dl.LOCK_DATA
FROM performance_schema.data_locks dl
INNER JOIN information_schema.INNODB_TRX trx
  ON dl.ENGINE_TRANSACTION_ID = trx.TRX_ID
INNER JOIN performance_schema.threads t
  ON trx.TRX_MYSQL_THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID IS NOT NULL
ORDER BY trx.TRX_STARTED;

字段注意：ENGINE_TRANSACTION_ID是8.0+的新字段，对应TRX_ID，类型为BIGINT UNSIGNED，关联时无需进行十六进制转换。
过滤内部线程：threads表比PROCESSLIST更底层。PROCESSLIST_ID为空的记录通常代表内部后台线程（如purge线程），可以直接过滤掉。
前置检查：如果查询结果为空，先别急着下结论。首先检查performance_schema是否启用（SELECT @@performance_schema;应为1），再确认具体的锁采集器是否打开（SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'wait/lock/innodb%';的ENABLED列需为YES）。

真正卡住时，别只盯“持锁者”，先看“等待者”

很多时候，问题的直接表现是一个会话被“卡住”了。此时，我们的目标往往是找到并终止这个等待锁的会话，而不是去追溯那个可能早已执行完毕的持锁者。这时，直接查询锁等待关系表会更高效。

例如，使用performance_schema.data_lock_waits可以清晰地看到谁在等、等谁：

SELECT
  rpl.PROCESSLIST_ID AS waiting_thread,
  rpl.PROCESSLIST_INFO AS waiting_sql,
  bpl.PROCESSLIST_ID AS blocking_thread,
  bpl.PROCESSLIST_INFO AS blocking_sql,
  dlw.BLOCKING_ENGINE_TRANSACTION_ID,
  dlw.REQUESTING_ENGINE_TRANSACTION_ID
FROM performance_schema.data_lock_waits dlw
INNER JOIN performance_schema.threads rpl
  ON dlw.REQUESTING_ENGINE_TRANSACTION_ID = rpl.THREAD_ID
INNER JOIN performance_schema.threads bpl
  ON dlw.BLOCKING_ENGINE_TRANSACTION_ID = bpl.THREAD_ID
INNER JOIN information_schema.INNODB_TRX rtrx
  ON dlw.REQUESTING_ENGINE_TRANSACTION_ID = rtrx.TRX_ID
INNER JOIN information_schema.INNODB_TRX btrx
  ON dlw.BLOCKING_ENGINE_TRANSACTION_ID = btrx.TRX_ID;

目标明确：查询结果中的waiting_thread通常就是你最终需要KILL的目标会话。需要警惕的是，blocking_thread对应的持锁会话可能已经结束，杀它并无意义。
瞬时性：data_lock_waits只记录“正在发生”的等待关系。一旦等待超时回滚或被手动终止，记录就会消失，因此查询动作要快。
信息互补：如果blocking_sql为NULL，不代表持锁事务没有执行过SQL，很可能它当前处于空闲状态但锁未释放。此时应结合btrx.TRX_QUERY字段（事务最近查询）来综合判断。

在实际排查中，最常踩的坑其实是配置和版本兼容性。在8.0中，开启了performance_schema不等于自动开启了锁信息采集；在5.7中，INNODB_LOCKS查不到记录，也不一定代表没有锁，可能是锁已被释放或采集未触发。因此，在决定KILL之前，务必先确认锁是否真实存在、是否仍在生效，避免误操作。

来源:https://www.php.cn/faq/2388492.html

免责声明：游乐网为非赢利性网站，所展示的游戏/软件/文章内容均来自于互联网或第三方用户上传分享，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系youleyoucom@outlook.com。

上一篇：Navicat能否实现脚本运行失败后自动重试_通过批处理脚本结合命令行控制下一篇：mysql如何实现冷备份_停机状态下拷贝data目录与配置文件