MySQL 8.0如何查询当前表级锁等待情况？

Question

在MySQL 8.0中，如何通过系统表查询当前正在发生的表级锁等待情况？常见的方法是查询`performance_schema`中的`data_locks`和`data_lock_waits`表，但许多用户在实际操作中发现无法准确识别锁等待的持有者与等待者会话。例如，虽然`data_lock_waits`显示存在等待记录，但关联`PROCESSLIST`信息时难以定位具体SQL语句或客户端连接。此外，`SHOW OPEN TABLES WHERE In_use > 0`只能反映表被打开的使用状态，并不能准确体现锁等待。该如何结合`performance_schema`中的数据锁视图，精准查出当前表级锁的等待关系、事务ID及对应会话？

Answer 1

一、MySQL 8.0 表级锁等待的监控背景与挑战

在高并发数据库系统中，表级锁（Table-level Locks）虽然使用较少（相较于行锁），但在某些特定场景如ALTER TABLE、RENAME TABLE或使用MyISAM引擎时仍会频繁出现。当多个会话竞争同一张表的访问权限时，就可能发生锁等待甚至死锁。

传统方法如SHOW OPEN TABLES WHERE In_use > 0仅能显示当前被打开的表数量，并不能反映实际的锁冲突状态。而SHOW PROCESSLIST虽可查看运行中的线程，但无法直接关联到具体的锁等待关系。

MySQL 8.0 引入了performance_schema下的data_locks和data_lock_waits表，为深入分析锁机制提供了强大支持。然而，许多开发者在实践中发现：即使查到了data_lock_waits中的等待记录，也难以精准定位“谁在等”、“被谁阻塞”以及“对应的SQL语句是什么”。

二、核心系统表结构解析

performance_schema 提供了以下关键视图用于锁分析：

• data_locks：记录当前所有活跃的数据锁信息。
• data_lock_waits：记录正在发生的锁等待事件。
• threads：关联线程ID与会话信息（如PROCESSLIST_ID）。
• events_statements_current：获取每个线程当前正在执行的SQL语句。

字段名	来源表	说明
ENGINE	data_locks	存储引擎类型（如InnoDB）
OBJECT_SCHEMA	data_locks	数据库名
OBJECT_NAME	data_locks	表名
LOCK_TYPE	data_locks	INTENTION、RECORD、TABLE等
LOCK_MODE	data_locks	S、X、IS、IX 等模式
LOCK_STATUS	data_locks	GRANTED 或 PENDING
OWNER_THREAD_ID	data_locks	持有该锁的线程ID
WAITING_THREAD_ID	data_lock_waits	等待锁的线程ID
BLOCKING_THREAD_ID	data_lock_waits	造成阻塞的线程ID
PROCESSLIST_ID	threads	对应 SHOW PROCESSLIST 中的 ID

三、精准定位锁等待关系的SQL查询方案

通过多表联合查询，可以构建出完整的“等待者 → 阻塞者”链路。以下是推荐的标准查询语句：

SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    rl.OBJECT_SCHEMA AS waiting_schema,
    rl.OBJECT_NAME AS waiting_table,
    rl.LOCK_MODE AS waiting_lock_mode,
    rl.LOCK_TYPE AS waiting_lock_type,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    bl.OBJECT_SCHEMA AS blocking_schema,
    bl.OBJECT_NAME AS blocking_table,
    bl.LOCK_MODE AS blocking_lock_mode,
    bl.LOCK_TYPE AS blocking_lock_type,
    pl.SQL_TEXT AS blocking_sql
FROM 
    performance_schema.data_lock_waits w
JOIN performance_schema.data_locks rl ON w.REQUESTING_ENGINE_LOCK_ID = rl.ENGINE_LOCK_ID
JOIN performance_schema.data_locks bl ON w.BLOCKING_ENGINE_LOCK_ID = bl.ENGINE_LOCK_ID
JOIN information_schema.innodb_trx r ON r.trx_mysql_thread_id = rl.OWNER_THREAD_ID
JOIN information_schema.innodb_trx b ON b.trx_mysql_thread_id = bl.OWNER_THREAD_ID
LEFT JOIN performance_schema.events_statements_current pl ON pl.THREAD_ID = bl.OWNER_THREAD_ID
WHERE 
    rl.LOCK_TYPE = 'TABLE' 
    AND bl.LOCK_TYPE = 'TABLE';
    

四、分析流程图：从现象到根因的排查路径

面对表级锁等待问题，建议按照如下流程进行诊断：

graph TD
            A[发现响应延迟或长事务] --> B{是否涉及DDL操作?}
            B -- 是 --> C[检查 ALTER/RENAME 是否阻塞]
            B -- 否 --> D[查询 data_lock_waits 是否有记录]
            D -- 无等待 --> E[排除锁竞争可能]
            D -- 有等待 --> F[关联 threads 和 events_statements_current]
            F --> G[定位阻塞会话的 SQL 语句]
            G --> H[判断是否需 Kill 或优化]
            H --> I[实施干预并验证结果]

五、常见误区与调优建议

在实际运维过程中，存在以下几个典型误区：

1. 误用 SHOW OPEN TABLES 判断锁等待：该命令仅反映表打开计数，不体现锁状态。
2. 忽略 LOCK_STATUS=PENDING 的含义：PENDING 表示请求尚未获得锁，是等待的关键标志。
3. 未启用 performance_schema 相关消费者：需确保'wait/lock/sql/lock_table'和'transaction'等消费者已开启。
4. 跨引擎混淆锁类型：MyISAM 使用表锁，InnoDB 虽以行锁为主，但在全表扫描或显式LOCK TABLES时也会升级为表级意向锁。
5. 未结合 trx_wait_started 判断等待时间：可通过information_schema.innodb_trx中的该字段评估影响范围。

六、实战案例：模拟表级锁等待并定位

假设两个会话执行如下操作：

-- Session 1:
LOCK TABLES employees WRITE;

-- Session 2:
SELECT * FROM employees LIMIT 1; -- 将被阻塞
    

此时执行前述联合查询，将返回一条 WAITING 记录，其中：

• waiting_trx_id 对应 Session 2 的事务ID
• blocking_trx_id 显示 Session 1 的事务ID
• blocking_sql 可能为空（若已执行完LOCK语句），但可通过events_statements_history追溯最近语句

进一步扩展查询历史语句：

SELECT SQL_TEXT FROM performance_schema.events_statements_history 
WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = [blocking_thread_id])
ORDER BY EVENT_ID DESC LIMIT 5;