在执行批量更新和删除操作时避免PostgreSQL死锁

Question

在执行批量更新和删除操作时避免PostgreSQL死锁

databasepostgresqlconcurrencydeadlockdatabase-deadlocks

21

我们有一张单独的表格，没有引用任何其他表格。

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

id_A	id_B	value
1	a	old_value_1a
2	b	old_value_2b
3	c	old_value_3c
...	...	...

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和表级锁定进行行级锁定)，但这些都似乎无法解决死锁问题。(请注意，由于性能影响，我们无法以任何方式在此表上使用访问排他锁定)



除了上述方法，是否有其他方法可以尝试解决这些死锁情况？参考手册中提到：


 通常避免死锁的最佳方法是确保所有使用数据库的应用程序都以一致的顺序获取多个对象的锁定。


但在上述情况下，我们如何实现这一点呢？是否有一种可靠的方法来按特定顺序执行批量更新插入操作？

- sanjayav

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

使用显式的行级锁定在所有竞争查询中的有序子查询中。
(SELECT 不会与写入锁竞争。)

`DELETE`

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;

这样一来，行就会按照手册中建议的一致顺序被锁定。

假设id_A和id_B从不被更新，即使像手册中“注意”框中详细说明的那样的罕见边角情况也是不可能出现的。

在不更新关键列的情况下，您可以使用较弱的锁定模式 FOR NO KEY UPDATE。需要Postgres 9.3或更高版本。

另一个（缓慢而稳定的）选项是使用可序列化隔离级别来处理竞争事务。您需要准备好处理序列化失败的情况，此时您需要重试命令。