MySQL 8.0中如何通过CTE递归查询解决复杂的层级锁问题？-mysql教程-PHP中文网

mysql 8.0 中无“层级锁”概念，所谓等待链需用 with recursive 手动建模：以 data_lock_waits 或 innodb_lock_waits 为边、innodb_trx 为节点构建递归图，但受限于瞬态快照、事务消亡、字段类型不一致及默认递归深度限制，实际常只能捕获部分链路。

mysql 8.0中如何通过cte递归查询解决复杂的层级锁问题？

MySQL 8.0 中没有“层级锁”这个概念，CTE 递归查询也不能解决锁问题——它处理的是树形数据遍历，不是并发控制或锁等待分析。如果你实际想查的是「被某事务阻塞的完整等待链（比如 A → B → C 这样的锁等待传递关系）」，那确实可以用 WITH RECURSIVE 配合 performance_schema.data_lock_waits 或 information_schema.INNODB_TRX + INNODB_LOCK_WAITS 来建模，但必须清楚：这不是 MySQL 原生支持的“层级锁查询”，而是你用递归 CTE 对锁等待图做一次手动展开。

为什么不能直接查“层级锁”？

MySQL 不提供类似 PostgreSQL 的 pg_blocking_pids() 或 Oracle 的 v$lock 层级视图。它的锁等待信息分散在：performance_schema.data_lock_waits（8.0.30+）、INNODB_LOCK_WAITS（旧版）、INNODB_TRX 和 INNODB_LOCKS（已弃用）。这些表本身不带层级结构，需要你主动建模等待关系。

INNODB_LOCK_WAITS 只存两两等待对（blocking_trx_id → requested_trx_id），没有深度信息
等待链可能成环（如 A→B→C→A），必须靠 level 和路径记录防死循环
事务可能已提交或回滚，blocking_trx_id 在 INNODB_TRX 中查不到，导致 JOIN 失败或空结果

如何用 `WITH RECURSIVE` 构建等待链？

以 INNODB_LOCK_WAITS + INNODB_TRX 为例，目标是找出某个事务 ID（如 '12345'）引发的全部下游等待者，并标记层级和路径：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

锚点部分：从 INNODB_LOCK_WAITS 找出所有直接等待 blocking_trx_id = '12345' 的 requested_trx_id
递归部分：用上一轮的 requested_trx_id 作为新 blocking_trx_id，继续找它的等待者
必须显式加 WHERE level 防止无限递归；同时用 <code>CONCAT(path, '→', requested_trx_id) 记录路径防环
字段必须严格对齐：blocking_trx_id、requested_trx_id、level、path 四列，类型统一为 CHAR(18) 或 VARCHAR(32)

WITH RECURSIVE wait_chain AS (
  SELECT 
    blocking_trx_id,
    requested_trx_id,
    1 AS level,
    CAST(requested_trx_id AS CHAR(32)) AS path
  FROM information_schema.INNODB_LOCK_WAITS
  WHERE blocking_trx_id = '12345'
<p>UNION ALL</p><p>SELECT 
w.blocking_trx_id,
w.requested_trx_id,
wc.level + 1,
CONCAT(wc.path, '→', w.requested_trx_id)
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN wait_chain wc ON w.blocking_trx_id = wc.requested_trx_id
WHERE wc.level </p><h3>为什么 JOIN 顺序和索引这么关键？</h3><p>递归部分的 <code>FROM INNODB_LOCK_WAITS w JOIN wait_chain wc</code> 必须把 CTE 放右边，否则报错 <code>ERROR 3641 (HY000): Recursive reference to CTE 'wait_chain' is not allowed in this context</code>。这是因为 MySQL 要求每轮迭代以上一轮 CTE 输出为驱动表去查基础表。</p>

确保 INNODB_LOCK_WAITS.blocking_trx_id 有索引（默认有，但检查是否失效）
如果等锁链很长（>100 层），默认 cte_max_recursion_depth=1000 可能不够，需临时调高：SET SESSION cte_max_recursion_depth = 2000
INNODB_LOCK_WAITS 是内存表，内容随事务变化实时刷新，执行时看到的是快照，不是历史记录

容易被忽略的三个现实限制

真实环境里，这条语句大概率跑不出完整链路，因为：

INNODB_LOCK_WAITS 只存当前活跃等待，一旦事务提交/回滚，对应行就消失 —— 你查到的只是“此刻还卡着”的那一截
MySQL 8.0.30 之前，performance_schema.data_lock_waits 才是推荐路径，但需提前开启：UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'
无法区分锁类型（行锁 vs 表锁 vs MDL），也不能关联到具体 SQL 或会话 ID，要补全上下文还得 JOIN INNODB_TRX 和 PROCESSLIST，字段类型不一致时必须用 CAST 对齐