CTE递归查询本身不持长锁，但置于显式事务中时，每轮迭代均为独立SELECT并受隔离级别约束，某轮因行锁阻塞则整个事务挂起，导致锁“变长”；max_recursion_depth仅限制迭代轮次，不控制锁行为，需通过剥离事务、补全联合索引、显式设深度来优化。
CTE递归查询本身不扩大锁范围，但放在长事务里会把多轮扫描的锁“捆”在一起；真正优化锁范围的关键是剥离事务、补索引、设深度限制。
为什么CTE递归会让锁看起来“变长”
递归 CTE 的锚点只执行一次，但递归成员可能跑几十轮——每轮都是独立 SELECT，都会触发 WHERE + JOIN 扫描。如果整个 CTE 被包在 BEGIN…COMMIT 里：
- 某一轮因
parent_id行被其他事务UPDATE ... FOR UPDATE占着而阻塞 → 整个事务卡住 → 所有已扫描行的锁不释放 -
max_recursion_depth只是轮次计数器，设成 50 不会让第 50 层的锁提前释放 - 没索引时，每轮都全表扫描并加大量行锁，不是“锁范围大”，而是“锁得又多又久”
必须做的三件事：事务剥离、索引补全、深度显式控制
别指望靠改写 CTE 语法缩锁，重点是收束执行上下文：
- CTE 查询**绝对不要**放在已有 DML 的事务中；如必须嵌入，确保它是事务里唯一语句，且执行后立即
COMMIT - 对所有 JOIN 条件列建联合索引，例如
ALTER TABLE departments ADD INDEX idx_parent_id_id (parent_id, id)—— 单独parent_id索引不够，因为还要回表取id - 连接前先执行
SET SESSION max_recursion_depth = 50（根据业务树深定，比如组织架构最多 6 层，设 10 就够），避免默认 1000 轮兜底导致意外长耗时
递归成员的位置和写法错误会直接报错
MySQL 强制要求递归成员只能作被驱动表，否则解析失败：
- ✅ 正确：
FROM departments d INNER JOIN dept_tree dt ON d.parent_id = dt.id（CTE 在右） - ❌ 报错：
FROM dept_tree dt INNER JOIN departments d ON d.parent_id = dt.id（CTE 在左） - ❌ 报错：
FROM departments d LEFT JOIN dept_tree dt ON ...（LEFT JOIN 右侧也不允许） - 终止条件必须写在递归分支的
WHERE里，比如WHERE dt.level ，不能靠外部 <code>LIMIT或最终SELECT过滤
紧急排查：怎么确认是不是 CTE 卡在某轮 JOIN 上
别只看 SHOW PROCESSLIST，它常显示 Sleep 掩盖真实阻塞。要查底层锁链：
- 运行
SELECT * FROM performance_schema.data_lock_waits，看BLOCKING_TRX_ID和REQUESTED_TRX_ID对应关系 - 用
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 突然撞上一个正在被修改的热点行——这时候锁等待会静默积累，直到超时或被 KILL。所以索引和事务边界，比怎么写递归逻辑重要得多。
