两个“选择更新”是否可以同一张表上的语句会导致死锁？ [英] Can two "SELECT FOR UPDATE" statements on the same table cause a deadlock?

问题描述

假设两个同时进行的事务在Postgresql DB上执行以下查询：

Suppose that two simultaneous transactions execute the following queries on a Postgresql DB:

事务A：

SELECT * FROM mytable WHERE id IN (1, 2, 3, 4) FOR UPDATE

交易B：

SELECT * FROM mytable WHERE id IN (6, 3, 2, 1) FOR UPDATE

是否有可能出现死锁发生由于Postgresql以不一致的顺序获取行锁？例如。如果Postgresql按照本例中给出的ID顺序获取行锁，则存在死锁的可能性。

Is it possible for a deadlock to occur due to Postgresql acquiring row locks in an inconsistent order? E.g. if Postgresql were to acquire row locks in the order the ids are given in this example, then there is a potential for deadlock.

或者说Postgresql内部足够智能，可以始终获取行锁的方式使得同一张表上的同时，离散的 SELECT FOR UPDATE 语句不能彼此死锁（例如，始终通过按主键顺序获取行锁）？

Or is Postgresql internally intelligent enough to always acquire row locks in a way that simultaneous, discrete SELECT FOR UPDATE statements on the same table cannot deadlock each other (e.g. by always acquiring row locks in order of primary key)?

如果Postgresql 不自动防止此类死锁的发生，有没有办法修改查询以防止这种情况发生（例如，实际上PostgreSQL按照给定ID的顺序获取行锁，然后对ID进行连续排序应该可以防止死锁）？

If Postgresql doesn't automatically prevent such deadlocks from occurring, is there a way to modify the queries to prevent such a situation (e.g. if in fact Postgresql acquires row locks in the order the ids are given, then consistently sorting the ids should prevent deadlock)?

感谢您的帮助！

推荐答案

对不起，我还有另一个答案，但这是错误的。

Sorry, I had another answer but it was wrong.

文档指出，ORDER BY子句在FOR UPDATE子句之前应用。因此，将以选择行的任何顺序来获取锁（我已经通过测试确认了这一点）。如果需要按其他顺序选择它们，则可以使用：

The documentation states that an ORDER BY clause is applied before the FOR UPDATE clause. So the locks are acquired in whatever order the rows are selected (I have confirmed as such by testing). If you need to select them in a different order, you can use:

SELECT * FROM (SELECT * FROM table ORDER BY id FOR UPDATE) ORDER BY another_column;

您可能想在PostgreSQL 邮件列表。

You may want to try your question on the PostgreSQL mailing list.

这篇关于两个“选择更新”是否可以同一张表上的语句会导致死锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！