PostgreSQL运行UPDATE时出现死锁

Question

PostgreSQL运行UPDATE时出现死锁

postgresqltransactionsdatabase-deadlocks

39

我有些困惑于阅读关于PostgreSQL死锁的内容。

一个典型的死锁例子是：

-- Transaction 1
UPDATE customer SET ... WHERE id = 1
UPDATE customer SET ... WHERE id = 2

-- Transaction 2
UPDATE customer SET ... WHERE id = 2
UPDATE customer SET ... WHERE id = 1

但是，如果我将代码更改如下会怎样：

-- Transaction 1
UPDATE customer SET ... WHERE id IN (1, 2)

-- Transaction 2
UPDATE customer SET ... WHERE id IN (1, 2)

这里会存在死锁的可能吗？

我的问题本质上是：在第二种情况下，PostgreSQL是逐个锁定行，还是锁定由WHERE条件涵盖的整个作用域？

提前感谢！

- vyakhir

3个回答

6

PostgreSQL是逐行锁定，还是锁定整个范围？

PostgreSQL是逐行锁定。

令人沮丧的是，更新（或删除）没有像查询和插入那样的顺序。

解决方法是使用SELECT FOR UPDATE提前锁定记录，并进行自连接。

UPDATE customer AS c SET ...
FROM (
  SELECT ctid
  FROM customer
  WHERE id IN (1, 2)
  ORDER BY id -- the optimal ordering varies, but it must be strict and consistent
  FOR UPDATE
) AS c2
WHERE c.ctid = c2.ctid

（这里我使用行的物理ID ctid 来进行连接，这样可以稍微提高速度。）

PostgreSQL会找到记录，按顺序锁定记录，然后更新记录。

您可以查看查询计划来自己验证这一点。

虽然有一些额外开销，但是它很小，特别是考虑到UPDATE通常不是轻量级操作。

- Paul Draper

0

根据Paul Draper的解决方案改进SQL语句。

UPDATE customer SET ...
WHERE id IN (
  SELECT id
  FROM customer
  WHERE id IN (1, 2)
  ORDER BY id
  FOR UPDATE
)

我在PostgreSQL v10.1上进行了一个测试，使用一个shell脚本循环执行SQL语句1000次作为后台任务。原始的UPDATE命令总是陷入死锁的情况，而Paul Draper的命令和我的命令都成功完成了。当我在一个只有两行的小表上测试它们时，并没有明显的性能差异。

我不了解内部的PostgreSQL实现知识，不同版本对SQL命令的优化方法可能会影响结果。

根据Erwin Brandstetter在类似问题上的回答，这个方法也应该有效。

BEGIN;
SELECT id FROM customer WHERE id IN (1, 2)
  ORDER BY id
  FOR UPDATE;
UPDATE customer SET ... WHERE id IN (1, 2);
END;

- D.Liu

看起来并没有改善。据我所知，它又回到了kgrittn回答中描述的问题中。 - undefined

@RabbanKeyak 謝謝您的評論。我已成功測試過了。 - undefined

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

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

- kgrittn · Accepted Answer

在PostgreSQL中，当更新行时它们将被锁定——实际上，这实际上的工作方式是每个元组（一行的版本）都有一个称为的系统字段，以指示哪个事务使该元组变为当前状态（通过插入或更新），并且有一个称为的系统字段，以指示哪个事务过期了该元组（通过更新或删除）。在访问数据时，它将检查每个元组，以确定您的事务是否可见，方法是根据这些值检查您的活动“快照”。

如果您正在执行UPDATE，并且与您的搜索条件匹配的元组具有可以使其在快照中可见的xmin和一个处于活动状态的事务的xmax，则会阻塞并等待该事务完成。如果首先更新元组的事务回滚，则您的事务将唤醒并处理该行；如果第一个事务提交，则您的事务将唤醒并根据当前的事务隔离级别采取行动。

显然，死锁是在以不同的顺序发生在不同行时的结果。在RAM中没有可以同时获得所有行的行级锁，但如果以相同的顺序更新行，则不能出现循环锁定。不幸的是，建议使用IN（1,2）语法并不能保证这一点。不同会话可能具有不同的成本因子活动，后台“分析”任务可能会在生成一个计划和另一个计划之间更改表的统计信息，或者它可能正在使用seqscan并受到PostgreSQL优化的影响，该优化导致新的seqscan加入一个已经在进展中的seqscan，并“环绕”以减少磁盘I/O。

如果您按相同顺序逐个更新，可以使用应用程序代码或游标，则只会出现简单的阻塞而不是死锁。然而，关系数据库通常容易发生串行化失败，最好通过能够根据SQLSTATE自动识别并从头开始重试整个事务的框架来访问它们。在PostgreSQL中，串行化故障将始终具有40001或40P01的SQLSTATE。

http://www.postgresql.org/docs/current/interactive/mvcc-intro.html