mysql 8.0递归cte本身不持长锁，但事务包裹、join无索引及每轮独立扫描会导致锁等待加剧；max_recursion_depth仅防无限递归，不缩短锁时长；真正优化需控制事务边界、补全索引并避免长事务中执行。
MySQL 8.0 的递归 CTE 查询本身不生成“临时表”锁，但其执行过程会反复触发 JOIN 和扫描，若未控制事务边界或索引缺失，就会让事务长时间持锁——这不是 CTE 的锅，而是你没管住它背后的执行逻辑。
为什么递归 CTE 会让事务变长？
递归 CTE 分为锚成员（只跑一次）和递归成员（可能跑 N 次），每次递归都是一次独立的 SELECT 执行。关键点在于：
- 每轮递归成员都会重新走一遍 WHERE + JOIN，如果
parent_id列没索引，就会全表扫描并加大量行锁 - 整个 CTE 被包裹在事务中时，所有中间扫描行为都受事务隔离级别约束，锁不会提前释放
-
max_recursion_depth只是防止无限递归报错，不控制锁生命周期；设成 50 并不能让第 50 层的锁更快释放 - 常见卡点：某轮 JOIN 的右表某行正被另一事务
UPDATE ... FOR UPDATE占着 → 当前递归停住 → 事务持续 open → 锁“变长”
如何快速定位 CTE 正在卡在哪一层？
别只看 SHOW PROCESSLIST，它常显示 Sleep 状态却掩盖了真实阻塞。要查底层锁等待链：
- 运行
SELECT * FROM performance_schema.data_lock_waits;—— 直接看到谁在等谁、等哪行 - 结合
SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_started DESC LIMIT 5;，找trx_state = 'RUNNING'且trx_query为空的线程 ID - 用该线程 ID 查
performance_schema.events_statements_current，确认最后执行的是否是你的 CTE 查询 - 若发现
waiting_event_id对应某次 JOIN 的lock_time很长，基本可断定是那个 JOIN 条件列缺索引
怎么改写才能真正缩短锁持有时间？
核心思路是：把“递归过程”从长事务里剥出来，让它变成短平快的只读操作。实操建议如下：
- 显式设置会话级限制：
SET SESSION max_recursion_depth = 50;（根据业务树深定，比如组织架构最多 6 层，设 10 就够） - CTE 查询**不要放在 BEGIN...COMMIT 块里**；如果必须嵌入事务，确保前面没有其他 DML，且 CTE 后立即 COMMIT
- 对递归涉及的关联字段（如
parent_id、id）补上联合索引：ALTER TABLE t ADD INDEX idx_pid_id (parent_id, id); - 避免在 CTE 中做复杂计算或子查询；如需聚合，先用非递归方式预计算好结果存入临时表，再 JOIN 进去
- 紧急止血时，用
KILL [blocking_thread_id];干掉卡住的递归线程，注意别误杀上游业务事务
容易被忽略的兼容性细节
MySQL 8.0 的递归 CTE 在不同隔离级别下表现差异很大：
- 在
REPEATABLE READ下，递归过程中多次读同一张表会复用快照，但每轮 JOIN 仍可能因间隙锁（Gap Lock）阻塞插入 - 改用
READ COMMITTED可减少 Gap Lock，但要注意幻读风险——尤其当递归依赖新插入的父节点时 -
tmp_table_size和max_heap_table_size对 CTE 无直接影响；CTE 不走内存临时表路径，它用的是引擎层迭代器 - 不要试图通过调大
binlog_cache_size来“优化” CTE 性能，它只影响事务内 binlog 缓存，跟 CTE 执行无关
最麻烦的不是语法写错，而是把 CTE 当成普通查询放进一个开了半小时的事务里——锁不会因为你写了 WITH RECURSIVE 就自动变短，它只认你什么时候 COMMIT。
