运行UPDATE时PostgreSQL中的死锁 [英] Deadlocks in PostgreSQL when running UPDATE

问题描述

关于PostgreSQL死锁，我有点困惑。

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

一个典型的死锁示例是：

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?

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

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

预先感谢！

推荐答案

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

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 xmin to indicate which transaction made that tuple current (by insert or update) and a system field called xmax to 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.

如果您正在执行UPDATE和一个与您的搜索条件匹配的元组具有一个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.

很明显，死锁是由于不同行中发生这种情况而导致的订购。 RAM中无法同时获得所有行的行级锁定，但是如果以相同的顺序更新行，则无法进行循环锁定。不幸的是，建议的 IN（1，2）语法不能保证这一点。不同的会话可能具有不同的活动成本因素，后台的分析任务可能会在一个计划与另一个计划的生成之间更改表的统计信息，或者可能正在使用seqscan并受到PostgreSQL优化的影响，这会导致新的seqscan

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.

如果您以相同的顺序一次执行一次更新，则可以在应用程序代码中加入已进行的更新并循环以减少磁盘I / O。或使用游标，那么您将只有简单的阻塞，而没有死锁。通常，关系数据库很容易发生序列化失败，最好通过一个框架访问它们，该框架将基于SQLSTATE识别它们并从头开始自动重试整个事务。在PostgreSQL中，序列化失败将始终具有40001或40P01的SQLSTATE。

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.

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

这篇关于运行UPDATE时PostgreSQL中的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！