PostgreSQL：在事务中使用SELECT FOR UPDATE时发现死锁

Question

我将要翻译的内容如下：

我有以下数据库表结构：

ID (PK)| REF_ID | ACTIVE | STATUS

ID - 主键

我正在使用以下查询语句进行选择和更新操作：

BEGIN;    
select * from table where ref_id = $1 and is_active is true for update;
UPDATE table set status = $1 where id =$2;
END;

上述内容的解释

1）选择查询结果将用于锁定所有具有提供的引用ID的行，并且该结果将用于某些业务逻辑。

2）更新查询以更新与同一引用ID的某一行相关联的状态。

问题

postgres@machine ERROR:  deadlock detected
postgres@machine DETAIL:  Process 28297 waits for ShareLock on transaction 4809510; blocked by process 28296.
        Process 28296 waits for ShareLock on transaction 4809502; blocked by process 28297.
        Process 28297: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update
        Process 28296: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update 
postgres@machine ERROR:  deadlock detected
postgres@machine DETAIL:  Process 28454 waits for ShareLock on transaction 4810111; blocked by process 28384.
        Process 28384 waits for ShareLock on transaction 4810092; blocked by process 28297.
        Process 28297 waits for AccessExclusiveLock on tuple (113628,5) of relation 16817 of database 16384; blocked by process 28454.
        Process 28454: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update
        Process 28384: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update
        Process 28297: select * from jobs where ref_id ='a840a8bd-b8a7-45b2-a474-47e2f68e702d' and is_active is true for update

这个表用于高并发和分布式应用程序（100个进程同时使用相同的ref_id），因此我希望通过在同一事务中进行选择和更新来避免分布式锁。但是，我遇到了死锁错误，我不知道为什么显式锁定没有起作用。

期望的行为是，如果其他任何具有相同引用ID的作业已获取锁，则任何其他具有相同引用ID的作业必须等待。

请帮助我找出我错过了什么或另一个解决方法。即使进行了显式锁定并且在事务内部，我仍然不清楚为什么会发生死锁。

- Abhishek Soni

---

将 ORDER BY 放入 SELECT ... FOR UPDATE 中。除此之外，在高并发应用程序中，当许多行在一个事务中被锁定时，死锁是无法避免的。 - Laurenz Albe

@LaurenzAlbe 感谢您的快速回复。如果您能解释一下应该按什么进行“ORDER BY”，甚至如何帮助，那将会很有帮助。其次，我可以限制并行性，因此想要进行基准测试，因为这对我来说不是使命关键（解决方案对我有效），这就是为什么想知道为什么它不能在高并发应用程序中工作的原因；谢谢。 - Abhishek Soni

如果行按照特定顺序被锁定，死锁的出现可能性就会降低。 - Laurenz Albe

谢谢 @LaurenzAlbe，确实有所帮助。但是这超出了我对锁定的理解范畴。 - Abhishek Soni

Answer 1

如Laurenz所说，在这种简单的情况下，您可以在锁定查询中使用ORDER BY来消除死锁的可能性。

当出现死锁时，例如：

• 进程A获取了行1的锁
• 进程B获取了行2的锁
• 进程A请求对行2进行锁定（并等待B释放它）
• 进程B请求对行1进行锁定（并等待A释放它）

此时，进程将永远相互等待（或者直到服务器注意到并杀死其中一个）。

但是，如果两个进程预先同意锁定行1和行2，则不会发生这种情况；一个进程仍在等待另一个进程，但另一个进程可以继续进行。

更一般地说，只要所有进程都同意在获取锁时遵循相同的顺序，就保证至少有一个进程始终在取得进展； 如果您尝试获取的锁仅高于您已经持有的锁，则持有“最高”锁的人永远不会等待任何人。

排序必须明确，并且随时间稳定，因此生成的主键是理想的（即应该ORDER BY id）。

- Nick Barnes

---

谢谢。我太懒了，没打全拼。 - Laurenz Albe

Answer 2

我对这个问题来得有点晚，但我遇到了一个类似的问题，当我在同一个事务中执行select ... for update和update时，会创建一个ShareLock。在我的情况下，可能也是在上面的情况下，这是由于Postgres不喜欢将非主键列用作select ... for update的条件。即使该列上有唯一约束，对该列的选择似乎也会锁定表。我找到了两种解决方法：

1. 将被选择的列设置为主键 - 在我的情况下，它是一个唯一的文本参考代码。我从表中删除了id bigint primary key列，并将reference列设置为主键，死锁问题就解决了。

2. 在上面的情况下，您可能需要在事务开始之前找到要选择的行的ID。伪代码/ SQL示例如下：

$3 = (select * from table where ref_id = $1 and is_active is true)

begin transaction

select * from table where id = $3 for update;
UPDATE table set status = $1 where id =$2;

commit transaction

- Nicko