更新WHERE子句扫描表时，Postgres锁定行为是什么？

Question

假设您有一个有数千万行的大表。

你想要UPDATE large_table SET col=value WHERE col=other_value..。但是col没有索引，EXPLAIN显示这个查询将对整个表执行seq扫描。

这里的锁行为是什么？根据大多数帐户，Postgres只锁定更新查询的受影响行，并且没有锁升级。那么，它是否首先搜索要更新的行，然后只锁定已找到的行？不过，在这种情况下，其他查询同时更新行似乎可能会出现问题。它是否在“找到它们时”锁定每一行，即在通过seq扫描时逐步锁定行？

因此，我认为最好的情况是，它在找到行时锁定行，并且受影响的行(仅)将被锁定，直到更新查询完成为止。

但是，我担心这个查询最终可能会阻塞所有对表的写入，直到它完成为止。

我读过这样的文章：https://habr.com/en/company/postgrespro/blog/503008/和我认为最坏的情况不会发生，但是在这里，https://blog.heroku.com/curious-case-table-locking-update-query可能是类似信息的一个不准确的表示，这让我有些怀疑。

应用程序只使用SELECT、SELECT FOR UPDATE和UPDATE查询(也就是说，没有其他显式锁与这些锁分开)。该表具有其他表的外键，其他表具有此表的外键。

我们在波斯特格雷斯11号。

Answer 1

对于讨论，让我们假设您的执行计划如下

        QUERY PLAN        
--------------------------
 Update on mytab
   ->  Seq Scan on mytab
         Filter: (id = 1)

我还假设您使用的是默认的READ COMMITTED隔离级别。

然后PostgreSQL将按顺序读取到表中。

每当它找到与筛选器匹配的行时，该行将被锁定和更新。

如果并发查询阻止锁定行，则PostgreSQL将等待直到锁消失。然后重新评估筛选条件，然后继续(如果条件由于并发修改而不再适用)，或者锁定和更新修改过的行。

请参见文献资料：

UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令在搜索目标行方面的行为与SELECT相同:它们只会找到在命令开始时已提交的目标行。但是，这样的目标行在找到时可能已经被另一个并发事务更新(或删除或锁定)。在这种情况下，可能更新的更新程序将等待第一个更新事务提交或回滚(如果它仍在进行中)。如果第一个更新程序回滚，那么它的效果将被否定，第二个更新程序可以继续更新原来找到的行。如果第一个更新程序提交，则如果第一个更新程序删除了该行，第二个更新程序将忽略该行，否则它将尝试将其操作应用于该行的更新版本。重新计算命令的搜索条件( WHERE子句)，以查看更新后的行版本是否仍然与搜索条件匹配。如果是的话，第二个更新程序将继续使用行的更新版本进行操作。

特别是，两个UPDATE语句可能会彼此修改多个行死锁，因为它们在进行时获得锁，锁始终保持到事务结束。