PostgreSQL 9.3自表左连接更新如何避免无限锁等待？

嘿，针对你遇到的这个自表LEFT JOIN UPDATE偶尔锁等待的问题，我给你几个实用的方案，既能保持LEFT JOIN的高效性，又能避免无限锁等待：

1. 显式锁定目标行（推荐） #

你的问题核心是自表更新时，查询内部可能出现锁的顺序冲突——哪怕是单用户场景也可能触发。可以在子查询里只锁定需要更新的目标行，避免尝试锁定LEFT JOIN的nullable side（也就是src表的行）。

修改你的子查询，使用FOR UPDATE OF trg来指定只锁定trg表（也就是要更新的t_territories中id_scenario=1的行）：

UPDATE t_territories AS upt
SET id_owner = diff.id_owner
FROM (
 SELECT trg.id_place, src.id_owner
 FROM t_territories AS trg
 LEFT JOIN t_territories AS src
 ON (src.id_scenario = 0) AND (src.id_place = trg.id_place)
 WHERE (trg.id_scenario = 1)
 AND (trg.id_owner IS DISTINCT FROM src.id_owner)
 FOR UPDATE OF trg; -- 仅锁定要更新的目标行，避免nullable side的锁错误
) AS diff
WHERE (upt.id_scenario = 1) AND (upt.id_place = diff.id_place)

这样做的好处是：

• 提前锁定所有需要更新的行，确保后续UPDATE操作不会遇到锁等待
• 不会触发FOR UPDATE cannot be applied to the nullable side of an outer join的错误，因为我们只锁定非nullable的trg表

2. 提前单独锁定目标行 #

如果上面的写法你觉得不够直观，也可以在执行UPDATE之前，先单独锁定目标行：

-- 先锁定所有要更新的行
SELECT 1 FROM t_territories 
WHERE id_scenario = 1 
FOR UPDATE;

-- 再执行原来的UPDATE语句
UPDATE t_territories AS upt
SET id_owner = diff.id_owner
FROM (
 SELECT trg.id_place, src.id_owner
 FROM t_territories AS trg
 LEFT JOIN t_territories AS src
 ON (src.id_scenario = 0) AND (src.id_place = trg.id_place)
 WHERE (trg.id_scenario = 1)
 AND (trg.id_owner IS DISTINCT FROM src.id_owner)
) AS diff
WHERE (upt.id_scenario = 1) AND (upt.id_place = diff.id_place)

这个方法更直白，适合你的单元测试场景——因为你是顺序执行，提前锁定不会有并发问题，反而能确保后续UPDATE顺利执行。

3. 设置锁等待超时（兜底方案） #

如果前两种方法还是偶尔出现问题，可以设置锁等待超时，避免无限等待。在单元测试开始前执行：

SET lock_timeout = '5s'; -- 等待5秒后自动报错，不会无限挂起

这个方法是兜底，虽然不能从根源解决锁冲突，但能保证你的单元测试不会无限卡住，方便调试。

为什么会出现这个问题？ #

哪怕是单用户操作，PostgreSQL的查询执行引擎在处理自表LEFT JOIN UPDATE时，可能会因为执行计划的原因，先扫描并锁定一些行，后续更新时又需要访问这些已被锁定的行，导致自我锁等待。通过显式提前锁定目标行，就能让锁的顺序变得可控，避免这种情况。

内容的提问来源于stack exchange，提问作者Skrol29