在执行批量更新和删除操作时避免PostgreSQL死锁 [英] Avoiding PostgreSQL deadlocks when performing bulk update and delete operations

问题描述

我们有一个表没有引用任何其他表。

We have a single table which does not have references to any other tables.

┬────────────┬─────────────┬───────────────┬───────────────╮
│id_A(bigint)│id_B(bigint) │val_1(varchar) │val_2(varchar) │
╪════════════╪═════════════╪═══════════════╪═══════════════╡

表的主键是id_A和id_B的组合。

The primary key of the table is a composite of id_A and id_B.

此表的读取和写入是高度并发的，表有数百万行。
我们有几个存储过程进行大量更新和删除。主要通过触发器和应用程序代码来同时调用这些存储过程。

Reads and writes of this table are highly concurrent and the table has millions of rows. We have several stored procedures which do mass updates and deletes. Those stored procedures are being called concurrently mainly by triggers and application code.

操作通常如下所示，它可以匹配成千上万条记录进行更新/删除：

The operations usually look like the following where it could match thousands of records to update/delete:

DELETE FROM table_name 
WHERE id_A = ANY(array_of_id_A)
AND id_B = ANY(array_of_id_B)

UPDATE table_name
SET val_1 = 'some value', val_2 = 'some value'
WHERE id_A = ANY(array_of_id_A)
AND id_B = ANY(array_of_id_B)

我们遇到死锁和我们尝试使用锁执行操作（使用 SELECT FOR UPDATE 和表级锁）似乎没有解决这些死锁问题。 （请注意，由于性能的影响，我们不能以任何方式使用此表上的访问排他锁）

We are experiencing deadlocks and all our attempts to perform operations with locks (row level using SELECT FOR UPDATE and table level locks) do not seem to solve these deadlock issues. (Note that we cannot in any way use access exclusive locking on this table because of the performance impact)

有没有另一种方法，我们可以尝试解决这些死锁情况？ 参考手册说明：

Is there another way that we could try to solve these deadlock situations? The reference manual says:

针对死锁的最佳防御措施通常是通过
避免使用数据库的所有应用程序在
上获取锁定多个对象

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

但是我们如何在上述情况下实现这一点。是否有一种以特定顺序批量更新插入操作的有保证的方法？

But how could we achieve this in the above scenario. Is there a guaranteed way to do bulk update inset operations in a particular order?

推荐答案

在所有竞争查询强>。 （简单 SELECT 不会竞争。）

Use explicit row-level locking in ordered subqueries in all competing queries. (Simple SELECT does not compete.)

DELETE FROM table_name t
USING (
   SELECT id_A, id_B
   FROM   table_name 
   WHERE  id_A = ANY(array_of_id_A)
   AND    id_B = ANY(array_of_id_B)
   ORDER  BY id_A, id_B
   FOR    UPDATE
   ) del
WHERE  t.id_A = del.id_A
AND    t.id_B = del.id_B;

UPDATE

UPDATE

UPDATE table_name t
SET    val_1 = 'some value'
     , val_2 = 'some value'
FROM (
   SELECT id_A, id_B
   FROM   table_name 
   WHERE  id_A = ANY(array_of_id_A)
   AND    id_B = ANY(array_of_id_B)
   ORDER  BY id_A, id_B
   FOR    UPDATE
   ) upd
WHERE  t.id_A = upd.id_A
AND    t.id_B = upd.id_B;

这样，行按照手册中的建议以一致的顺序锁定。

This way, rows are locked in consistent order as advised in the manual.

假设 id_A ， id_B 永远不会更新， 详细信息请参阅手动是不可能的。

Assuming that id_A, id_B are never updated, even rare corner case complications like detailed in the "Caution" box in the manual are not possible.

另一个（慢速和确定）选项是使用可串行化隔离级别。您必须准备序列化失败，在这种情况下您必须重试该命令。

The other (slow and sure) option is to use the Serializable Isolation Level for competing transactions. You would have to prepare for serialization failures, in which case you have to retry the command.

这篇关于在执行批量更新和删除操作时避免PostgreSQL死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！