运行 UPDATE 时 PostgreSQL 中的死锁

Deadlocks in PostgreSQL when running UPDATE

提问by vyakhir

I'm a little bit confused reading about PostgreSQL deadlocks.

我在阅读 PostgreSQL 死锁时有点困惑。

A typical deadlock example is:

一个典型的死锁例子是：

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

But what if I change the code as follows:

但是如果我将代码更改如下：

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

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

Will be a possibility of deadlock here?

这里会不会有死锁的可能？

Essentially my question is: in the 2nd case does PostgreSQL lock rows one-by-one, or lock the entire scope covered by the WHEREcondition?

基本上我的问题是：在第二种情况下，PostgreSQL 是逐行锁定行，还是锁定WHERE条件涵盖的整个范围？

Thanks in advance!

提前致谢！

回答by kgrittn

In PostgreSQL the rows will be locked as they are updated -- in fact, the way this actually works is that each tuple (version of a row) has a system field called xminto indicate which transaction made that tuple current (by insert or update) and a system field called xmaxto indicate which transaction expired that tuple (by update or delete). When you access data, it checks each tuple to determine whether it is visible to your transaction, by checking your active "snapshot" against these values.

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

If you are executing an UPDATE and a tuple which matches your search conditions has an xmin which would make it visible to your snapshot and an xmax of an active transaction, it blocks, waiting for that transaction to complete. If the transaction which first updated the tuple rolls back, your transaction wakes up and processes the row; if the first transaction commits, your transaction wakes up and takes action depending on the current transaction isolation level.

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

Obviously, a deadlock is the result of this happening to rows in different order. There is no row-level lock in RAM which can be obtained for all rows at the same time, but if rows are updated in the same order you can't have the circular locking. Unfortunately, the suggested IN(1, 2)syntax doesn't guarantee that. Different sessions may have different costing factors active, a background "analyze" task may change statistics for the table between the generation of one plan and the other, or it may be using a seqscan and be affected by the PostgreSQL optimization which causes a new seqscan to join one already in progress and "loop around" to reduce disk I/O.

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

If you do the updates one at a time in the same order, in application code or using a cursor, then you will have only simple blocking, not deadlocks. In general, though, relational databases are prone to serialization failures, and it is best to access them through a framework which will recognize them based on SQLSTATE and automatically retry the entire transaction from the start. In PostgreSQL a serialization failure will always have a SQLSTATE of 40001 or 40P01.

如果您以相同的顺序在应用程序代码中或使用游标一次更新一个，那么您只会遇到简单的阻塞，而不是死锁。但是，一般而言，关系数据库容易出现序列化失败，最好通过框架访问它们，该框架将基于 SQLSTATE 识别它们并从一开始就自动重试整个事务。在 PostgreSQL 中，序列化失败的 SQLSTATE 总是 40001 或 40P01。

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

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