自连接易触发锁等待，因多事务对同一表行锁定顺序不一致，导致隐式锁竞争甚至死锁；常见于UPDATE/DELETE混用自连接、CTE或递归查询中。
为什么自连接容易触发锁等待
PostgreSQL 14 中的自连接（SELECT ... FROM table t1 JOIN table t2 ON ...）本身不直接加锁，但一旦混入 UPDATE 或 DELETE，尤其是用子查询或 CTE 引用同一张表多次，就极易形成隐式锁竞争。根本原因在于：自连接语句在执行时可能按不同顺序扫描、锁定同一张表的行，而多个并发事务若锁定顺序不一致，就会快速滑向锁等待甚至死锁。常见于分页更新、树形结构遍历、去重清洗等场景。
查出正在阻塞的自连接会话
先定位是不是自连接 SQL 在卡住别人。重点看 wait_event_type = 'Lock' 且 query 包含多次引用同一表名（如 orders o1 JOIN orders o2）的活跃会话：
SELECT pid, usename, application_name, state,
now() - xact_start AS xact_duration,
wait_event_type, wait_event,
substring(query FROM 1 FOR 120) AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
AND wait_event_type = 'Lock'
AND query ~* '\s+[a-z_][a-z0-9_]*\s+JOIN\s+[a-z_][a-z0-9_]*\s+ON'
ORDER BY xact_start;- 如果
state = 'idle in transaction'，说明事务已空转但没提交，大概率是自连接逻辑里漏了COMMIT或异常跳出了事务块 -
query_snippet里出现两个别名指向同一张表（如users u1 JOIN users u2），就是高危自连接信号 - 注意区分：纯
SELECT自连接不会阻塞写，但带FOR UPDATE或嵌套在UPDATE ... FROM里的就会
避免自连接锁等待的实操手段
不靠加索引硬扛，而是从执行路径上切断锁冲突源头：
- 把
UPDATE ... FROM (SELECT ... FROM t t1 JOIN t t2)拆成两步：先用CREATE TEMP TABLE落实匹配结果，再用单表UPDATE ... WHERE id IN (SELECT id FROM temp)—— 避免执行器在一次计划中对同一表反复加锁 - 强制统一锁顺序：在自连接条件中显式用
t1.id < t2.id或t1.created_at < t2.created_at，确保所有事务按相同逻辑遍历，大幅降低死锁概率 - 改用
LATERAL替代部分自连接：例如SELECT * FROM orders o1, LATERAL (SELECT * FROM orders o2 WHERE o2.user_id = o1.user_id AND o2.id > o1.id) o2，它天然按外层顺序驱动，锁更可控 - 对高频自连接操作，设
statement_timeout = '30s'（会话级），防止一个慢自连接拖垮整条连接池
PostgreSQL 14 特有的坑：WITH RECURSIVE + FOR UPDATE
这是个隐蔽雷区。在 14 中，WITH RECURSIVE 查询若结尾跟 FOR UPDATE，递归过程中每层都可能对中间结果加行锁，且锁不会提前释放——导致后续事务一碰就等。现象是 pg_stat_activity 显示 wait_event = 'Lock' ，但阻塞源头的 query 看起来只是个普通 CTE。
- 临时解法：用
pg_terminate_backend(blocking_pid)干掉源头，但治标不治本 - 根治办法：把递归逻辑移到应用层做，或改用物化临时表分层处理，避开
FOR UPDATE直接作用于递归结果集 - 验证是否中招：执行
SELECT * FROM pg_locks WHERE pid = blocking_pid AND mode LIKE '%RowExclusive%';，若返回多行且relation::regclass是同一张表，基本可确认
自连接锁等待最难缠的地方不在语法本身，而在它常和长事务、未提交的 DML、以及应用层连接复用耦合在一起——单独优化 SQL 往往不够，得同步检查事务边界是否清晰、连接是否及时释放。
