在执行批量更新和删除操作时避免 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.
(SELECT does not compete with write-locks.)

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 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    NO KEY UPDATE  -- Postgres 9.3+
-- FOR    UPDATE         -- for older versions or updates on key columns
   ) 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.

虽然不更新键列，但您可以使用较弱的 锁定模式FOR NO KEY UPDATE.需要 Postgres 9.3 或更高版本.

While not updating key columns, you can use the weaker lock mode FOR NO KEY UPDATE. Requires Postgres 9.3 or later.

另一个(慢并且肯定)选项是使用 用于竞争事务的可序列化隔离级别.您必须为序列化失败做好准备，在这种情况下，您必须重试该命令.

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屋！