在PostgreSQL中，对同一表中不同行的多个UPDATE是否具有冲突的锁? [英] In PostgreSQL, do multiple UPDATEs to different rows in the same table have a conflicting locks?

问题描述

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

I'm wondering about an update I am making to a large table, and whether I need to worry about locks.

我有一张桌子，像这样:

I have a table looking like this:

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.

Initially, this table has ~2.0 million rows, and only the id column filled in - WorkerInstanceId and the two timestamps are NULL by default and on the start of the run.

发生的事情是某些工作人员应用程序(至少两个，但在生产中大约为10-13个)将从该表中标记一批ID(我计划将batchSize设置为200)供它们处理.处理过程中发生的事情现在并不重要.

What happens is that some worker apps (at least two, but will be around 10-13 in production) will mark a batch of IDs (I plan to set batchSize to 200) from this table for them to process. What happens during processing doesn't really matter now.

批处理的标记如下:

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

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

My question is, do I need to worry about locking the rows I'm going to update before making the update?

Postgres文档说:

Postgres documentation says:

排他性

与SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE锁定模式冲突.

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

命令UPDATE，DELETE和INSERT在目标表上获得此锁定方式(除了对任何其他引用表的ACCESS SHARE锁定外).通常，任何修改表中数据的命令都将获取此锁定模式.

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行，最后释放该锁.在锁到位之前，其他工人正在等待锁释放.这是对的还是我错过了什么?

So I think that whenever one of the workers makes this update, the whole table is locked, 200 rows are updated and the lock is freed up in the end. Until the lock is in place, the other workers are waiting for the lock to free up. Is this right or am I missing something?

推荐答案

您缺少几件事.

首先，PostgreSQL不提供用于更新的LIMIT选项.请参见有关UPDATE的文档.

First, PostgreSQL does not offer a LIMIT option for update. See the docs for UPDATE.

第二，请注意，ROW EXCLUSIVE与其自身不冲突，它与SHARE ROW EXCLUSIVE冲突.因此，您的UPDATE语句可以安全地从多个工作线程并行运行.您仍然希望您的更新时间短.但是，如果遇到问题，您已经有一种内置的方法可以通过降低batchSize来进行调整.

Second, note that ROW EXCLUSIVE does not conflict with itself, it conflicts with SHARE ROW EXCLUSIVE which is different. So, your UPDATE statements can safely run concurrently from multiple workers. You still will want your update times to be low. However, you already have a built-in way to tune that by lowering your batchSize if you run into problems.

这篇关于在PostgreSQL中，对同一表中不同行的多个UPDATE是否具有冲突的锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！