两个并发但相同的DELETE语句会导致死锁吗？ [英] Can two concurrent but identical DELETE statements cause a deadlock?

问题描述

假设 some_table 有两行，主键为 1 和 2 。以下语句序列可能会导致死锁：

Assume some_table has two rows, with primary key 1 and 2. The following sequence of statements can cause a deadlock:

session 1: begin;
session 2: begin;
session 1: DELETE FROM my_table WHERE my_key = 1;
session 2: DELETE FROM my_table WHERE my_key = 2;
session 1: DELETE FROM my_table WHERE my_key = 2;
session 2: DELETE FROM my_table WHERE my_key = 1;

如果两个会话以相同顺序删除，则不会发生死锁。

The deadlock would not have occurred if both sessions deleted in the same order.

现在，我的问题是，如果DELETE语句触及多行会怎样？例如：

Now, coming to my question, what happens if the DELETE statement touches multiple rows? For example:

session 1: begin;
session 2: begin;
session 1: DELETE FROM my_table;
session 2: DELETE FROM my_table;

两个并发但相同的DELETE语句是否有可能以不同顺序删除行？

Is it possible that two concurrent but identical DELETE statements will delete rows in a different order? Is it possible to enforce the deletion order to avoid a deadlock?

我在文档中找不到此信息，所以我不能保证删除顺序（尽管它可能间接地作为实现细节）。我想在这里再次检查。

I could not find this information in the documentation, so I would say that deletion order is not guaranteed (although it might be indirectly as an implementation detail). I wanted to double check here.

推荐答案

是的，这可能导致死锁，因为表中行的顺序是

Yes, this could lead to a deadlock, because the order of rows in a table is not fixed.

任何 UPDATE 可能会更改顺序表扫描返回的行顺序，如果 synchronize_seqscans 的默认值为 ，在顺序执行的情况下，即使表未同时执行，顺序也可能会改变（例如

Any UPDATE may change the order of rows returned by a sequential table scan, and if synchronize_seqscans is at its default value on, the order may change even if the table doesn't if several sequential scans are executed concurrently (like in your case).

您应该先运行 SELECT ... FOR UPDATE 和 ORDER BY 子句可以减少出现死锁的风险，但是即使如此，您也不能绝对确定，除非您对不会同时更新的列进行排序（例如主键）。

You should first run a SELECT ... FOR UPDATE with an ORDER BY clause to reduce the risk of a deadlock, but even then you cannot be absolutely certain, unless you sort by a column that will not get updated concurrently (like the primary key).

这篇关于两个并发但相同的DELETE语句会导致死锁吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！