postgresql - 在 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 文档说:

ROW EXCLUSIVE

Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies the data in a table.

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

Answer 1

UPDATE 锁定该行，因此您无需先锁定它。如果您尝试同时 UPDATE 重叠行集，第二个 UPDATE 将等待第一个事务提交或回滚。

除了 UPDATE 没有 LIMIT 子句之外，您的方法的最大问题是多个工作人员都会尝试获取相同的行.这是发生了什么:

• worker1:过滤表找到200行并锁定
• worker1:开始更新行
• worker2:筛选表以找到 200 行
• worker2:尝试开始更新行，但选择了与 worker1 相同的行，因此它阻塞在 worker1 的锁上
• worker1:完成更新行
• worker2:释放锁后，重新检查WHERE条件，发现没有一行匹配了，因为worker1已经更新了。更新零行。

... 重复!

您需要:

• 有一个中心queue以适当的并发安全方式分发行；或
• 分配 ID 范围不重叠的工作人员进行工作

至于 LIMIT - 你可以使用 WHERE id IN (SELECT t.id FROM thetable t LIMIT 200 ORDER BY id) - 但你会遇到同样的问题两个工作人员都选择同一组行进行更新。