mysql 8.0递归cte本身不持长锁，但事务范围、join策略及缺失终止条件会导致锁等待加剧；其递归成员每轮执行均可能因索引缺失或行锁冲突而阻塞，max_recursion_depth仅防无限递归而非缩短锁时长。
MySQL 8.0 的递归 CTE 查询本身不持长锁，但不当使用会间接导致锁等待加剧——根本原因在事务范围、JOIN 策略和未设终止条件
递归 CTE 执行期间到底锁了什么
递归 CTE 不是“自己加锁”，而是其内部执行的每一轮 SELECT（尤其是递归成员中的 JOIN）都会按需访问数据行。如果这些行已被其他事务用 FOR UPDATE 或 LOCK IN SHARE MODE 锁住，当前递归查询就会卡在那一步等待。
常见现象：SELECT ... FROM t1 JOIN t2 ON ... 在递归层反复执行，而 t2 上某条记录正被另一事务更新且未提交 → 整个递归过程停在该轮，后续层级无法推进，事务持续 open，锁自然“变长”。
- 锚成员（initial query）只执行一次，影响小
- 递归成员（recursive member）可能执行 N 次，每次都是独立的扫描+JOIN，锁行为叠加
- 若没走索引（比如
parent_id无索引），每次 JOIN 都可能触发全表扫描+隐式锁升级
max_recursion_depth 不是锁开关，但能防雪崩
max_recursion_depth 是会话级变量，默认值 1000。它不控制锁，只控制递归最多跑多少层。一旦超限，MySQL 直接报错 ERROR 3636 (HY000): Recursive query aborted after 1000 iterations，强制终止查询。
这看似是“优化”，实则是兜底：避免因循环引用（如 A→B→C→A）或数据异常导致无限递归，把连接拖死、锁堆满、线程卡住。
- 生产环境建议显式设置：
SET SESSION max_recursion_depth = 50;（根据业务树深调整） - 不能靠它缩短锁持有时间，但能防止一个坏查询拖垮整个连接池
- 注意：该变量只对当前会话生效，应用层需在执行前设置
真正影响锁时长的是事务边界和 JOIN 方式
递归 CTE 被包裹在事务中时，它的所有中间结果和扫描行为都受事务隔离级别约束。最典型陷阱是：在长事务里执行递归查询，又没加 SKIP LOCKED。
例如这个订单分发场景：
START TRANSACTION;
SELECT * FROM orders
WHERE status = 'pending'
AND id IN (
WITH RECURSIVE pending_tree AS (
SELECT id FROM orders WHERE status = 'pending' AND priority = 1
UNION ALL
SELECT o.id FROM orders o INNER JOIN pending_tree pt ON o.parent_id = pt.id
)
SELECT id FROM pending_tree
)
FOR UPDATE;
-- 如果某条 o.parent_id 对应的父订单正被其他事务锁定，这里就卡住
改进关键点：
- 把递归逻辑拆出来，先查 ID 列表（只读），再用
SELECT ... FOR UPDATE SKIP LOCKED批量取可用记录 - 确保
parent_id和status有联合索引，避免递归层全表扫 - 避免在递归 CTE 内部写
FOR UPDATE—— CTE 本身是只读上下文，加锁必须放在最终SELECT层
路径跟踪比深度限制更能防循环和锁滞留
仅靠 max_recursion_depth 无法识别逻辑环（比如员工 A 管理 B，B 管理 C，C 又被 A 直接管理）。这类环会导致重复扫描同一行多次，放大锁冲突概率。
更稳妥的做法是在 CTE 中维护路径字段，主动跳过已出现过的节点：
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id, CAST(id AS CHAR(1000)) AS path
FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id, CONCAT(s.path, ',', e.id)
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.id
WHERE LOCATE(CONCAT(',', e.id, ','), CONCAT(',', s.path, ',')) = 0
)
SELECT * FROM subordinates;
这种写法增加了字符串操作开销，但换来的是确定性终止和更低的锁重试率。尤其适合组织架构、权限树等高一致性要求场景。
真正容易被忽略的点：递归 CTE 的性能和锁表现，90% 取决于底层表的索引设计与事务粒度，而不是 CTE 语法本身有多“高级”。别迷信 WITH RECURSIVE 能自动优化锁——它只是把原来要写 5 层 JOIN 的逻辑收进一句 SQL，该扫的表、该等的锁，一点没少。
