如何通过sys库和performance_schema定位长时间持有锁的SQL语句？

本文共计1079个文字，预计阅读时间需要5分钟。

直接查询 `performance_schema.data_locks` 是最基础的起点，它记录了当前所有活跃的数据锁（包括行锁、区间锁、表锁），但请注意，默认情况下该表不开启采集，需要确认配置是否生效。

执行前检查：SELECT VARIABLE_VALUE FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME = 'performance_schema' 必须为 ON；同时确保 innodb_status_output_locks 已启用（5.7+）或 8.0+ 的 performance_schema.data_locks 表已加载。

常用查询：

SELECT ENGINE_TRANSACTION_ID AS trx_id, OBJECT_SCHEMA AS db, OBJECT_NAME AS table_name, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA -- 行锁时显示被锁的索引值（如主键ID），NULL表示表级锁 FROM performance_schema.data_locks WHERE LOCK_TRX_ID IS NOT NULL;

⚠️ 容易踩的坑：

• LOCK_DATA 在唯一索引行锁下才稳定可读；非唯一索引或间隙锁时可能为 NULL 或显示范围值，不能直接用于定位具体行
• 该表只反映“当前快照”，事务一提交锁就消失，需配合 data_lock_waits 看阻塞链
• MySQL 5.7 不支持 data_locks，得退回到 SHOW ENGINE INNODB STATUS 解析输出

查谁在等锁、谁在堵谁

锁冲突时，等待方会卡住，持有方可能还在运行（比如长事务没提交）。关键看 performance_schema.data_lock_waits —— 它明确记录了 BLOCKING_TRX_ID → BLOCKED_TRX_ID 的阻塞关系。

典型查询：

SELECT b.OBJECT_SCHEMA, b.OBJECT_NAME, b.LOCK_MODE, b.LOCK_TYPE, w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx, w.BLOCKED_ENGINE_TRANSACTION_ID AS blocked_trx FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks b ON w.BLOCKING_ENGINE_LOCK_ID = b.ENGINE_LOCK_ID;

再关联 information_schema.INNODB_TRX 就能拿到事务的 SQL 和开始时间：

SELECT t.TRX_ID, t.TRX_MYSQL_THREAD_ID, t.TRX_QUERY, t.TRX_STARTED, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(t.TRX_STARTED) AS trx_seconds FROM information_schema.INNODB_TRX t WHERE t.TRX_ID IN (SELECT BLOCKING_ENGINE_TRANSACTION_ID FROM performance_schema.data_lock_waits);

⚠️ 注意：

• data_lock_waits 只有发生真实等待时才写入，如果锁已被释放、或事务已回滚，这条记录就没了
• 某些隐式锁（如插入唯一冲突触发的锁）不会出现在 data_locks，但会体现在 INNODB_STATUS 的 LATEST DETECTED DEADLOCK 段

用 sys.innodb_lock_waits 快速定位阻塞源头

sys 库是 performance_schema 的封装视图，sys.innodb_lock_waits 把锁等待、事务、SQL、线程信息全拼好了，适合快速排查。

执行：

SELECT * FROM sys.innodb_lock_waits\G

返回字段里重点关注：

• waiting_trx_id / blocking_trx_id：事务 ID 对应关系
• waiting_pid / blocking_pid：对应 PROCESSLIST.ID，可直接 KILL
• waiting_query：卡住的 SQL（常为空，因为还没执行到语句层面）
• blocking_query：持有锁的那条 SQL（若为 NULL，说明锁来自未提交事务，但当前没在执行语句）
• sql_kill_blocking_connection：生成好的 KILL 命令，复制执行即可

⚠️ 限制：

• 依赖 performance_schema 全量开启，且 sys 视图本身需 MySQL 5.7.9+ 或 8.0 默认安装
• 如果持有锁的事务来自应用连接池空闲连接（比如 Spring 的 @Transactional 未正确关闭），blocking_query 往往为空，只能靠 TRX_STARTED 和 TRX_STATE 判断是否“sleeping in transaction”

为什么查不到锁？常见配置遗漏点

很多 DBA 查不到锁，不是 SQL 写错，而是基础开关没开。必须逐项确认：

• performance_schema=ON（my.cnf 中设置，重启生效）
• innodb_monitor_enable='all' 或至少 'lock_monitor'（动态生效，但 8.0+ 推荐用 setup_instruments）
• 8.0+ 需启用对应 instruments：UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE '%wait/lock%';
• information_schema.INNODB_TRX 要求 innodb_status_output 和 innodb_status_output_locks 均为 ON（5.7+ 动态变量）

没有这些，data_locks、data_lock_waits 就是空的，sys.innodb_lock_waits 也查不到任何内容——不是没锁，是根本没采集。

真正难的不是写 SQL，是让 MySQL 先愿意把锁信息吐出来。