在PostgreSQL中，对同一个表中不同行的多次更新会有冲突锁吗？

Question

我想知道我正在对一个大表进行的更新，以及我是否需要担心锁。

我有一张像这样的桌子：

CREATE TABLE "ItemsToProcess"( 
"id" text, 
"WorkerInstanceId" text, 
"ProcessingStartTime" timestamp with time zone, 
"UpdatedTime" timestamp with time zone, 
CONSTRAINT "ITP_PK" PRIMARY KEY ("id")
)WITH (
  OIDS=FALSE
);

最初，此表有~200万行，并且只有id列填充-WorkerInstanceId，默认情况下和运行开始时两个时间戳为NULL。

发生的情况是，一些辅助角色应用（至少两个，但在生产环境中大约为 10-13 个）将从此表中标记一批 ID（我计划将 batchSize 设置为 200），以便它们进行处理。处理过程中发生的事情现在并不重要。

批次的标记如下所示:

UPDATE "ItemsToProcess" 
   SET "WorkerInstanceId" = ?, "ProcessingStartTime" = current_timestamp()
 WHERE "WorkerInstanceId" is NULL
 LIMIT 200;

我的问题是，在进行更新之前，是否需要担心锁定要更新的行？

Postgres文档 说：

排他

与“共享”、“共享”行“独占”、“独占”和“访问独占”锁定模式冲突。

命令UPDATE、DELETE和INSERT在目标表上获取此锁定模式（除了访问任何其他引用表上的共享锁之外）。通常，此锁定模式将由修改表中数据的任何命令获取。

因此，我认为，每当一个工人进行此更新时，整个表都会被锁定，200行被更新，最后锁被释放。在锁到位之前，其他工人正在等待锁释放。这是对的还是我遗漏了什么？

Answer 1

你错过了一些东西。

首先，PostgreSQL不提供用于更新的LIMIT选项。请参阅UPDATE文档。

其次，注意< code>ROW EXCLUSIVE本身并不冲突，它与不同的< code>SHARE ROW EXCLUSIVE冲突。因此，您的< code>UPDATE语句可以安全地从多个工作线程并发运行。你仍然希望你的更新次数少。但是，如果遇到问题，您已经有了一个内置的方法来调整它，即降低< code>batchSize。

Answer 2

UPDATE锁定行，因此不需要先锁定它。如果尝试同时＜code＞更新

您的方法的一个大问题是，除了＜code＞UPDATE

• worker1：筛选表以查找200行并锁定它们。
• worker1:开始更新行
• worker2：筛选表以查找200行
• worker2:尝试开始更新行，但已选择与worker1相同的行，因此会阻止worker1的锁。
• worker1:完成更新行
• worker2：在锁释放后，重新检查WHERE条件，发现所有行都不再匹配，因为worker1已经更新了它们。更新零行

...重复！

您需要：

• 有一个中央队列，以适当的并发安全方式分发行；或
• 为工作人员分配不重叠的ID范围

至于 LIMIT - 您可以使用 WHERE id in （SELECT t.id FROM thetable t LIMIT 200 ORDER BY ID） - 但是两个 worker 选择同一组行进行更新时，您会遇到同样的问题。