主页 / dba / 问题 / 278736
Accepted
Anentropic
Asked: 2020-10-28 04:15:58 +0800 CST

当 UPDATE WHERE 子句进行表扫描时，Postgres 锁定行为是什么？

  • 772

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

您想要UPDATE large_table SET col=value WHERE col=other_value...但未col编入索引，并且EXPLAIN显示此查询将对整个表执行 seq 扫描。

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

所以我认为这里最好的情况是它在找到行时锁定行，并且只要 UPDATE 查询需要完成，受影响的行（仅）将被锁定。

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

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

该应用程序仅使用SELECT,SELECT FOR UPDATE和UPDATE查询（即除这些之外没有其他显式锁）。该表具有其他表的外键，其他表具有该表的外键。

我们在 Postgres 11 上。

postgresql lock-escalation

1 个回答

  1. Best Answer

    对于讨论，让我们假设您的执行计划看起来像

            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语句各自修改多行相互死锁，因为它们在执行过程中获取锁，并且锁始终保持到事务结束。

    • 5

相关问题