postgresql - 多个线程会导致约束集上的重复更新吗？

Question

在postgres中，如果我运行以下语句

update table set col = 1 where col = 2

在默认的 READ COMMITTED isolation level中，我保证来自多个并发 session :

在单个匹配的情况下，只有1个线程的行数为1(表示仅一个线程写入)

在多重匹配的情况下，只有1个线程将获得ROWCOUNT> 0(这意味着只有一个线程将写入批处理)

Answer 1

您声明的保证适用于这种简单情况，但不一定适用于稍微复杂一些的查询。有关示例，请参见答案末尾。

简单的情况

假设col1是唯一的，具有一个正确的值“2”或具有稳定的顺序，那么每个UPDATE都以相同的顺序匹配相同的行:

该查询将发生的事情是，线程将找到col = 2的行，并且所有线程都试图在该元组上获取写锁。恰好其中之一会成功。其他将阻止等待第一个线程的事务提交。

第一个TX将写入，提交并返回行计数1。提交将释放锁。

其他发送方将再次尝试捕获该锁。他们会一一成功。每个交易将依次执行以下过程:

获取有争议的元组上的写锁。

获取锁定后，重新检查WHERE col=2条件。

重新检查将显示条件不再匹配，因此UPDATE将跳过该行。

UPDATE没有其他行，因此它将报告零行更新。

提交，为下一个试图释放它的TX释放锁。

在这种简单情况下，行级锁定和条件重新检查可以有效地序列化更新。在更复杂的情况下，不需要那么多。

您可以轻松地演示这一点。打开说四个psql session 。首先，使用 BEGIN; LOCK TABLE test; *锁定表。在其余的 session 中，运行相同的 UPDATE －它们将在表级锁上阻塞。现在，通过 COMMIT设置您的第一个 session 来释放锁。观看他们的比赛。只有一个报告行计数为1，其他报告行计数为0。这很容易实现自动化，并编写了脚本，可重复执行并扩展到更多连接/线程。

要了解更多信息，请阅读 rules for concurrent writing的第11页 PostgreSQL concurrency issues，然后阅读该演示文稿的其余部分。

如果col1不唯一？

正如Kevin在评论中指出的那样，如果 col不是唯一的，那么您可能会匹配多行，那么 UPDATE的不同执行可能会获得不同的顺序。如果他们选择了不同的计划(例如，一个是通过 PREPARE和 EXECUTE的计划，另一个是直接的，或者您搞砸了 enable_ GUC)，或者如果他们都使用的计划使用了一种不稳定的相等值，则可能会发生这种情况。如果它们以不同的顺序获取行，则tx1将锁定一个元组，tx2将锁定另一个元组，然后它们将各自尝试对彼此已经锁定的元组进行锁定。 PostgreSQL将使用死锁异常中止其中之一。这是为什么所有数据库代码都应始终准备重试事务的又一个很好的理由。

如果您要确保并发 UPDATE始终以相同的顺序获取相同的行，则仍然可以依靠答案第一部分中描述的行为。

令人沮丧的是，PostgreSQL没有提供 UPDATE ... ORDER BY，因此确保您的更新始终以相同的顺序选择相同的行并不是您所希望的那样简单。通常，最安全的是 SELECT ... FOR UPDATE ... ORDER BY和其后的单独 UPDATE。

更复杂的查询，排队系统

如果您要执行的查询具有多个阶段，涉及多个元组或除相等条件以外的其他条件，则可能会得到与串行执行结果不同的令人惊讶的结果。特别是，并发运行任何类似的内容:

UPDATE test SET col = 1 WHERE col = (SELECT t.col FROM test t ORDER BY t.col LIMIT 1);

或构建简单的“队列”系统的其他工作将*无法*按预期工作。有关更多信息，请参见 PostgreSQL docs on concurrency和 this presentation。

如果您希望工作队列由数据库支持，则可以使用经过良好测试的解决方案来处理所有令人惊讶的复杂情况。最受欢迎的之一是 PgQ。该主题上有一个有用的 PgCon paper，而 a Google search for 'postgresql queue'充满了有用的结果。

*顺便说一句，您可以使用 LOCK TABLE代替 SELECT 1 FROM test WHERE col = 2 FOR UPDATE;来获得对元组的写锁定。这将阻止针对它的更新，但不会阻止对其他元组的写入或任何读取。这使您可以模拟不同种类的并发问题。