Postgres死锁

Question

我在Postgres数据库中看到一些无法解释的死锁。为了简化相关查询，死锁中涉及的事务之一是：

BEGIN;
UPDATE A SET CHUNK_ID=1, STATUS='PROCESSING' WHERE ID IN (
    SELECT ID FROM A
    WHERE CHUNK_ID IS NULL
    ORDER BY O_ID
    LIMIT 1000
    FOR UPDATE 
);
COMMIT;

另一个是：

BEGIN;
UPDATE A SET STATUS='SENT' WHERE ID = 1; 
UPDATE A SET STATUS='SENT' WHERE ID = 2;
UPDATE A SET STATUS='SENT' WHERE ID = 3;
...
COMMIT; 

我的问题是，这里怎么可能出现死锁呢？我想不出第一个事务可能导致死锁的任何场景，而不管同时运行的任何其他查询。

是否存在这样的情况，即使用嵌套SELECT ...FOR UPDATE可以是死锁的一部分吗？

谢谢

Answer 1

(这只是一个猜测，但希望这是一个有根据的猜测。)

一切都取决于SELECT锁定行的顺序……按O_ID排序...用于更新。如果O_ID的顺序与ID的顺序不同，则完全有可能出现类似的情况：

ID    O_ID
--    ----
1     2
2     1

• 事务A使用ID=2.
• Transaction B锁定行使用ID=1.
• Transaction A锁定行尝试使用ID=1锁定行，但被强制等待事务B。
• 事务B尝试使用ID=2锁定行，但被强制等待事务A。

警告:即使O_ID order与ID order相同，ORDER BY子句也可能实际上并不保证锁定的顺序(它只保证返回结果的顺序)。不幸的是，这似乎缺乏记录。无论如何，看起来Oracle在锁定时并不(总是)遵守ORDER BY，所以在PostgreSQL下也要小心。

通常，死锁的解决方案是始终以相同的顺序锁定。假设ORDER BY实际上保证了锁定的顺序，您可以简单地包含SELECT ...按O_ID排序...用于在第二个事务中进行更新。或者，在第一个事务中使用ORDER BY ID。

顺便说一句，为什么你首先要显式锁定呢？你到底想用它来达到什么目的？