递归CTE不能绕过事务锁，反而可能加剧锁竞争；其真正价值在于将多次短锁压缩为一次短锁，从而减少锁持有总时长。
MySQL 8.0 的 CTE 递归本身不能绕过事务锁，也不能“优化”锁行为——它甚至可能加剧锁竞争。真正能缓解锁问题的，是用递归 CTE 替换原本在应用层或存储过程中反复执行的多轮 SELECT + INSERT 循环，从而把锁持有时间从“多次短锁”压缩为“一次短锁”。
为什么递归 CTE 在事务中反而容易触发死锁或长等待
MySQL 的递归 CTE（WITH RECURSIVE）在执行时会将整个结果集一次性物化到临时表（内存或磁盘），而该过程若涉及对基表（如 t_menu）的反复扫描和 JOIN，就会持续持有 SELECT ... FOR UPDATE 或隐式读锁（取决于隔离级别）。尤其当递归深度大、中间结果多时，InnoDB 可能升级为间隙锁（gap lock）或临键锁（next-key lock），导致阻塞其他事务。
- 递归层级每深一层，就多一次对父表的索引查找 —— 若父 ID 列无索引，会触发全表扫描并加表级读锁
-
WITH RECURSIVE不支持ORDER BY控制展开顺序，InnoDB 按内部迭代顺序加锁，不可预测 - 事务中混合使用递归 CTE 和后续
UPDATE，容易因锁顺序不一致引发死锁（例如：CTE 先锁 ID=5→10→15，UPDATE 却按 15→5→10 更新）
真正有效的写法：用 CTE 预计算路径，再原子更新
典型场景：给某个部门树节点（ID=100）及其所有子节点统一打标 status = 'archived'。别在循环里逐条 SELECT ... FOR UPDATE 再 UPDATE，改用 CTE 一次性拿到所有 ID，再单次更新。
WITH RECURSIVE dept_tree AS ( SELECT id FROM departments WHERE id = 100 UNION ALL SELECT d.id FROM departments d INNER JOIN dept_tree dt ON d.parent_id = dt.id ) UPDATE departments SET status = 'archived' WHERE id IN (SELECT id FROM dept_tree);
- 必须确保
parent_id列有索引（最好是(parent_id, id)覆盖索引） - 避免在 CTE 中
SELECT *或带复杂表达式，否则物化临时表变慢，锁持有时间拉长 - 若递归结果超 1000 行，建议加
MAXRECURSION 1000（MySQL 8.0.26+ 支持），防失控 - 该
UPDATE仍会锁住所有匹配行，但只发生一次，而非 N 次 —— 这才是锁优化的本质
替代方案：用临时表 + 显式锁控制粒度
当递归深度不确定、或需在 CTE 结果上做条件过滤（如“只更新状态为 active 的子节点”）时，CTE 直接嵌套在 UPDATE 中可能让优化器放弃使用索引。此时更稳的做法是分两步：
-- 步骤1：安全提取 ID 列表（不加锁） CREATE TEMPORARY TABLE _target_ids AS WITH RECURSIVE tree AS ( SELECT id FROM departments WHERE id = 100 UNION ALL SELECT d.id FROM departments d INNER JOIN tree t ON d.parent_id = t.id ) SELECT id FROM tree; <p>-- 步骤2：显式加锁更新（锁范围可控） UPDATE departments d INNER JOIN _target_ids t ON d.id = t.id SET d.status = 'archived' WHERE d.status = 'active';
- 第一步无锁，纯读；第二步只锁真正要改的行，且
WHERE条件可走索引 - 临时表名带下划线前缀，避免与业务表冲突
- 注意：临时表在事务结束时自动销毁，无需
DROP
递归 CTE 不是锁的解药，而是把“分散锁”收束成“集中锁”的工具。真正决定锁行为的，永远是你的访问路径（索引）、事务边界（是否包裹了无关操作）、以及是否在锁内做了耗时计算 —— 这些比语法炫技重要得多。
