如何用 information_schema 找出当前阻塞的事务和被阻塞的事务？

考察要点

这道题面试官想看你是否具备在线诊断 MySQL 并发问题的能力。核心考察点包括：

1. MySQL 系统库的熟练度：是否熟悉 information_schema、performance_schema 和 sys schema 的作用，特别是与事务和锁相关的系统表。
2. 版本差异意识：是否知道 MySQL 5.7 和 8.0 在锁信息存储和查询方式上的重大变化，这是区分资深工程师和初级工程师的关键点。
3. 锁问题的分析思路：不仅仅是写出 SQL，更要理解阻塞关系是如何通过表连接（JOIN）来呈现的，即谁（blocking_pid）持有了锁，谁（blocked_pid）在等待这个锁。

核心概念

information_schema 是一个 ANSI SQL 标准的元数据数据库，提供了关于数据库、表、列、权限等信息。在 MySQL 5.7 及更早版本中，InnoDB 的锁信息也通过 information_schema 下的特定表暴露。

从 MySQL 8.0 开始，为了提升性能和标准化监控，大部分动态性能视图（包括锁信息）都迁移到了 performance_schema。information_schema 中的相关表（如 INNODB_LOCKS）被废弃，并最终在未来版本中移除。

关键系统表（视图）

阻塞关系图示

阻塞的本质是一个事务（Blocked Trx）想获取的锁资源，正在被另一个事务（Blocking Trx）持有。data_lock_waits (或 INNODB_LOCK_WAITS) 表就是这个关系的核心体现。

 1graph TD
 2    A[Blocking Trx (持有锁)] -- 持有 --> B(资源: 如某一行记录的 X 锁);
 3    C[Blocked Trx (等待锁)] -- 请求 --> B;
 4    A -- 阻塞 --> C;

构造阻塞场景示例

为了查询阻塞，我们首先需要构造一个阻塞场景。

会话 A (Blocking Session):

 1-- 创建测试表
 2CREATE TABLE lock_test (
 3  id INT PRIMARY KEY,
 4  val INT
 5) ENGINE=InnoDB;
 6INSERT INTO lock_test VALUES (1, 100);
 7
 8-- 开启事务并持有 id=1 的行锁
 9BEGIN;
10UPDATE lock_test SET val = 101 WHERE id = 1;
11-- 此时不 COMMIT

会话 B (Blocked Session):

 1-- 在另一个客户端窗口执行
 2BEGIN;
 3-- 尝试更新同一行，将会被阻塞
 4UPDATE lock_test SET val = 102 WHERE id = 1;

现在，会话 B 就会处于 Lock wait timeout exceeded 或持续等待的状态，我们就可以用下面的 SQL 来查询这个阻塞关系。

查询阻塞的 SQL

解法 1: 使用 performance_schema (✅ MySQL 8.0+)

这是 MySQL 8.0 及以上版本的标准和推荐做法。查询逻辑是：

1. 从 data_lock_waits 找到等待关系，获取阻塞方和被阻塞方的线程 ID (BLOCKING_THREAD_ID, REQUESTING_THREAD_ID)。
2. 关联 events_transactions_current 获取事务信息（如事务 ID、执行时间）。
3. 关联 threads (from performance_schema) 获取原始 SQL 语句。

 1SELECT
 2    r.trx_id AS waiting_trx_id,          -- 等待事务ID
 3    r.trx_state AS waiting_trx_state,    -- 等待事务状态
 4    TIMEDIFF(NOW(), r.trx_started) AS waiting_duration, -- 等待时长
 5    p_wait.processlist_id AS waiting_pid, -- 等待会话ID
 6    p_wait.sql_text AS waiting_query,     -- 等待的SQL
 7    b.trx_id AS blocking_trx_id,         -- 阻塞事务ID
 8    p_block.processlist_id AS blocking_pid, -- 阻塞会话ID
 9    p_block.sql_text AS blocking_query,   -- 阻塞的SQL
10    dlw.lock_type AS lock_type,           -- 锁类型
11    dlw.lock_mode AS lock_mode,           -- 锁模式
12    dlw.object_schema,                    -- 锁定的库
13    dlw.object_name,                      -- 锁定的表
14    dlw.index_name,                       -- 锁定的索引
15    dl.lock_data                          -- 锁定的具体数据（主键值）
16FROM
17    performance_schema.data_lock_waits dlw
18JOIN
19    performance_schema.events_transactions_current r ON dlw.requesting_engine_transaction_id = r.engine_transaction_id
20JOIN
21    performance_schema.threads p_wait ON r.thread_id = p_wait.thread_id
22JOIN
23    performance_schema.events_transactions_current b ON dlw.blocking_engine_transaction_id = b.engine_transaction_id
24JOIN
25    performance_schema.threads p_block ON b.thread_id = p_block.thread_id
26JOIN
27    performance_schema.data_locks dl ON dlw.blocking_engine_lock_id = dl.engine_lock_id;

解法 2: 使用 information_schema (⚠️ MySQL 5.7 及更早版本)

这是老版本的方法，在新版本中已不推荐。逻辑类似，但表名不同。

 1SELECT
 2    r.trx_id AS waiting_trx_id,
 3    r.trx_mysql_thread_id AS waiting_pid,
 4    r.trx_query AS waiting_query,
 5    b.trx_id AS blocking_trx_id,
 6    b.trx_mysql_thread_id AS blocking_pid,
 7    (SELECT info FROM information_schema.processlist WHERE id = b.trx_mysql_thread_id) AS blocking_query
 8FROM
 9    information_schema.INNODB_LOCK_WAITS w
10JOIN
11    information_schema.INNODB_TRX b ON w.blocking_trx_id = b.trx_id
12JOIN
13    information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id;

注意: 在 5.7 中，直接从 INNODB_TRX 获取阻塞方的 trx_query 通常显示为 NULL，因为事务可能处于空闲状态。需要额外关联 information_schema.processlist 来获取其最后执行的命令。

常见低分回答

1. 混淆版本：在 MySQL 8.0 环境下，仍然尝试查询 information_schema.INNODB_LOCK_WAITS，导致查不出结果或报错，这表明知识体系没有更新。
2. 只会 SHOW PROCESSLIST：回答 SHOW PROCESSLIST 或查询 information_schema.PROCESSLIST。虽然能看到某个会话的状态是 updating 并且 State 可能是 waiting for table metadata lock 或其他等待状态，但这无法直接、清晰地展示出 InnoDB 层面的行锁阻塞关系（谁阻塞了谁）。
3. 只会 SHOW ENGINE INNODB STATUS：这个命令输出信息非常全面，在 LATEST DETECTED DEADLOCK 和 TRANSACTIONS 部分包含了锁信息。但它的缺点是输出为非结构化的文本，难以程序化解析和精确查询，不符合题目中“用 SQL 找出”的要求。
4. 查询不完整：只查询了 data_lock_waits 或 INNODB_LOCK_WAITS，但没有 JOIN 其他表。这只能看到事务 ID 之间的等待关系，无法获取到具体的 SQL 语句、会话 ID (pid) 和事务持续时间等关键上下文信息，不利于问题排查。
5. 不了解 sys schema：完全不知道 sys schema 的存在。sys schema 提供了更友好的视图，是排查问题的捷径，不知道它会显得实践经验不足。

工程实践与优化

• 首选 sys schema：在生产环境中，排查锁问题最快捷、最推荐的方式是使用 sys schema 提供的视图。它封装了 performance_schema 的复杂 JOIN，提供了即用型结果。
  sql

  ⧉⛶

   1-- 查询锁等待（MySQL 8.0+）
   2-- 这一个视图就等价于上面 performance_schema 的复杂 JOIN
   3SELECT * FROM sys.innodb_lock_waits;
   4
   5-- 查询包含空闲事务的阻塞情况（更全面）
   6SELECT * FROM sys.session_blocking_others;

• 性能开销：频繁查询 performance_schema 和 information_schema 会带来一定的性能开销，因为它们是基于内存数据动态生成的视图。在超高并发的系统上，应谨慎使用，或通过监控系统（如 Percona PMM, Prometheus+mysqld_exporter）在后台定期采集。
• 找到阻塞后的操作：
  1. 分析：通过查询结果，快速定位 blocking_pid 和 blocking_query。分析该SQL为何长时间持有锁（例如，事务未提交、查询扫描行数过多、无索引等）。
  2. 决策：评估业务影响。如果阻塞导致核心业务不可用，且阻塞事务本身价值不高或可以重试，应果断处理。
  3. 处理：使用 KILL <blocking_pid> 命令终止阻塞的会话。操作前务必确认影响并获得授权。
• 根本性优化：KILL 只是临时手段。长期的解决方案是优化代码和SQL：
  • 缩短事务：遵循“小事务”原则，避免在事务中包含用户交互或耗时操作。
  • 优化索引：确保 WHERE 条件和 JOIN 条件的列都有合适的索引，减少锁定的记录范围。
  • 合理使用锁：使用更低粒度的锁（如行锁代替表锁），或在非核心路径考虑使用乐观锁。

常见追问与延伸题目

1. 追问：如果 sys.innodb_lock_waits 查询为空，但业务明显卡顿，怀疑是锁问题，还有哪些可能的原因和排查方向？

   • 回答要点 1 (元数据锁)：可能是元数据锁（Metadata Lock, MDL）。当一个会话对表结构进行修改（如 ALTER TABLE），或某些查询需要获取表结构信息时，会持有 MDL。此时其他会话对该表的 DML/DDL 操作会被阻塞。可以通过查询 performance_schema.metadata_locks 和 performance_schema.threads 来定位。
   • 回答要点 2 (Gap Lock / Next-Key Lock)：在 REPEATABLE READ 隔离级别下，范围查询或非唯一索引查询可能会引入间隙锁（Gap Lock）或临键锁（Next-Key Lock）。这可能导致“锁住了不存在的记录”，使得 INSERT 操作被阻塞。这种情况在 data_locks 中 lock_type 为 GAP 或 RECORD,GAP，需要检查阻塞事务的查询范围。
   • 回答要点 3 (其他等待)：性能问题不一定是锁。也可能是 CPU、I/O 瓶颈，或者等待其他资源。可以通过 performance_schema.events_waits_summary_global_by_event_name 查看系统主要的等待事件，判断瓶颈是在 wait/io/file 还是 wait/synch/mutex。

2. 追问：你刚才提到了 performance_schema，默认情况下它是开启的吗？如果关闭了，对诊断有什么影响？如何开启？

   • 回答要点 1 (默认状态)：在 MySQL 5.7 中，performance_schema 默认是开启的，但很多 instrument（监控项）是关闭的。在 MySQL 8.0 中，performance_schema 不仅默认开启，而且大部分关键的 instrument（如 statements, transactions）也默认开启，大大方便了开箱即用的诊断。
   • 回答要点 2 (影响)：如果 performance_schema 被禁用 (performance_schema=OFF)，那么上述所有基于 performance_schema 和 sys schema 的查询都将失效，诊断能力会严重受限，只能退回到使用 SHOW ENGINE INNODB STATUS 等原始工具。
   • 回答要点 3 (开启方法)：performance_schema 的开启/关闭需要在 MySQL 配置文件 (my.cnf 或 my.ini) 中设置 performance_schema = ON，然后重启 MySQL 服务。对于其内部的 consumers 和 instruments，可以在线通过 UPDATE performance_schema.setup_* 表来动态启用或禁用，无需重启。例如，UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';。

3. 追问：如果换成 PostgreSQL，你应该如何查询类似的锁等待信息？

   • 回答要点 1 (核心视图)：PostgreSQL 的锁信息主要通过 pg_locks 和 pg_stat_activity 这两个系统视图获取。pg_locks 记录了所有已授予和等待中的锁，pg_stat_activity 记录了所有活动后端进程（会话）的信息，包括进程ID（pid）、当前执行的查询等。
   • 回答要点 2 (查询逻辑)：查询思路是，在 pg_locks 中找到一个 granted = false 的锁（被阻塞的），然后根据其 transactionid, relation (表Oid), page, tuple 等信息，在 pg_locks 中找到另一个 granted = true 且与前者资源冲突的锁（阻塞的）。最后，将这两个锁对应的 pid 关联到 pg_stat_activity 以获取详细的会话和查询信息。
   • 回答要点 3 (示例SQL)：可以给出一个简化的查询示例，展示对这两个核心视图的连接使用。
     sql

     ⧉⛶

      1SELECT
      2    blocked_locks.pid     AS blocked_pid,
      3    blocked_activity.usename  AS blocked_user,
      4    blocked_activity.query    AS blocked_query,
      5    blocking_locks.pid     AS blocking_pid,
      6    blocking_activity.usename AS blocking_user,
      7    blocking_activity.query   AS blocking_query
      8FROM
      9    pg_catalog.pg_locks         blocked_locks
     10JOIN
     11    pg_catalog.pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
     12JOIN
     13    pg_catalog.pg_locks         blocking_locks
     14        ON blocking_locks.locktype = blocked_locks.locktype
     15        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
     16        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
     17        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
     18        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
     19        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
     20        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
     21        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
     22        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
     23        AND blocking_locks.pid != blocked_locks.pid
     24JOIN
     25    pg_catalog.pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
     26WHERE
     27    NOT blocked_locks.granted;