使用SELECT FOR UPDATE加JOIN时的行锁顺序与死锁风险

用户提供的事务代码 #

BEGIN;

SELECT *
FROM A
JOIN B ON A.id = B.a_id
WHERE A.id = 123
ORDER BY A.id, B.id
FOR UPDATE;

-- more ops...

COMMIT;

用户的问题 #

• 在使用JOIN的情况下，我对ORDER BY子句的使用是否正确？
• 行被锁定的具体顺序是什么？我原本想先按id顺序锁定表A的所有行，再按id顺序锁定表B的所有行，但我猜测这种方式并不正确。

问题解答 #

1. JOIN场景下ORDER BY的有效性 #

你的ORDER BY子句无法实现预期的跨表锁定顺序。ORDER BY仅作用于查询返回的结果集排序，不会改变数据库执行查询时访问和锁定行的顺序。PostgreSQL处理FOR UPDATE时，锁定顺序由查询执行计划决定（比如嵌套循环、哈希连接的访问策略），和结果集的排序逻辑完全独立。

2. 实际锁定顺序 #

锁定顺序取决于查询执行计划：

• 如果计划是先扫描A表找到id=123的行，再关联B表匹配行，那么会先锁定A表的该行，再按B表的访问顺序（如索引扫描按B.id排序、全表扫描按存储顺序）锁定B表的匹配行。
• 如果计划是先扫描B表筛选a_id=123的行，再关联A表，那么会先锁定B表的匹配行，再锁定A表的id=123行。

你预期的“先锁A表所有行、再锁B表所有行”的逻辑，当前写法完全无法实现——哪怕A表涉及多行，ORDER BY也无法控制跨表的锁定顺序。

正确的死锁规避方案 #

要严格控制锁定顺序，需拆分查询，明确按顺序锁定不同表的行：

BEGIN;

-- 先按id顺序锁定A表目标行
SELECT * FROM A WHERE id = 123 ORDER BY id FOR UPDATE;

-- 再按id顺序锁定B表目标行
SELECT * FROM B WHERE a_id = 123 ORDER BY id FOR UPDATE;

-- more ops...

COMMIT;

所有并发事务遵循这一锁定顺序，就能从根源上避免死锁。

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