MySQL 8.0递归CTE本身不持长锁，但递归成员每轮独立执行JOIN时若遇被锁行（如FOR UPDATE未提交）即阻塞整轮迭代，叠加索引缺失导致全表扫描与锁升级，最终引发长事务锁等待；解决关键在于为manager_id/parent_id建B+Tree索引、设max_recursion_depth防雪崩、拆分事务避免递归嵌套在FOR UPDATE中。
MySQL 8.0 的 CTE 递归查询本身不主动“持有长锁”，但会因执行过程中的行访问行为触发锁等待，本质是事务边界 + JOIN 策略 + 缺失索引共同导致的内存锁竞争。解决重点不在“释放锁”，而在切断锁等待链路。
为什么递归查询会卡在锁上？
递归成员（UNION ALL 后的 SELECT）每轮都是一次独立查询，若涉及 JOIN 且被 FOR UPDATE 或 LOCK IN SHARE MODE 锁住的行，就会阻塞整轮迭代。比如：
- 锚成员查出员工 A，递归成员执行
SELECT * FROM employees e JOIN subordinates s ON e.manager_id = s.id - 此时某条
e记录正被另一事务更新未提交 → 当前轮次挂起 - 后续所有递归层级无法启动，事务保持 open，锁持续占用
这不是 CTE “设计缺陷”，而是它如实反映了底层行级锁的传播逻辑。
必须加的索引：parent_id 或 manager_id 字段
没索引时，每次递归 JOIN 都可能全表扫描，不仅慢，还会扩大锁范围（隐式锁升级）。实际中 90% 的锁竞争源于此。
- 对组织架构类表，确保
manager_id有 B+Tree 索引：CREATE INDEX idx_manager_id ON employees(manager_id) - 对分类树表，
parent_id同理：CREATE INDEX idx_parent_id ON categories(parent_id) - 避免使用函数索引或前缀索引——递归 JOIN 条件是等值匹配，需要可下推的精确索引
max_recursion_depth 不是锁开关，但能防雪崩
max_recursion_depth 是会话级变量，默认 1000，超限报错 ERROR 3636 (HY000): Recursive query aborted after 1000 iterations。它不缩短锁时间，但能阻止一个坏查询拖垮整个连接池。
- 生产环境务必显式设置：
SET SESSION max_recursion_depth = 50;（根据业务树深调整） - 不能靠它“优化性能”，只能作为安全兜底
- 应用层需在执行前设置，ORM 如 MyBatis、SQLAlchemy 均支持 session 初始化 hook
事务里跑递归查询？先想清楚隔离级别
把递归 CTE 包在长事务里，等于把所有中间扫描结果和锁都绑定到该事务生命周期。最危险的是 REPEATABLE READ 下未加 SKIP LOCKED。
- 若递归用于订单分发、库存扣减等场景，建议改用
READ COMMITTED隔离级别 - 关键路径上避免在事务内执行深度递归；可先用 CTE 查出 ID 列表，再分批
UPDATE ... WHERE id IN (...) - 绝对不要在
FOR UPDATE事务里直接WITH RECURSIVE ... SELECT ... JOIN ...—— 锁范围不可控
真正难处理的不是语法或版本问题，而是递归过程中对同一行反复读取 + 多轮 JOIN 导致的锁叠加效应。索引、深度限制、事务拆分这三件事，漏掉任何一项，都可能让一个本该秒级返回的查询变成锁风暴源头。
