Postgresql 中文操作指南

13.3. Explicit Locking #

PostgreSQL 提供了各种锁定模式，用于控制表中数据的并发访问。这些模式可用于在 MVCC 无法给出所需行为的情况下进行应用程序控制的锁定。此外，大多数 PostgreSQL 命令会自动获取适当模式的锁定，以确保在执行命令时不会删除或以不兼容的方式修改引用的表。（例如， TRUNCATE 无法与同一表上的其他操作同时安全执行，因此它在表上获取 ACCESS EXCLUSIVE 锁定来强制执行此操作。）

若要检查数据库服务器中当前未解决的锁定的列表，请使用 pg_locks 系统视图。有关监视锁定管理器子系统状态的详细信息，请参阅 Chapter 28 。

13.3.1. Table-Level Locks #

以下列表显示了可用的锁定模式以及 PostgreSQL 在其中自动使用它们的上下文。您还可以使用 LOCK 命令显式获取任何这些锁。请记住，即使名称中包含“row”一词，所有这些锁定模式都是表级锁定；锁定模式的名称具有历史意义。在一定程度上，这些名称反映了每个锁定模式的典型用法 - 但语义都是相同的。一种锁定模式与另一种锁定模式之间的唯一实际区别是，它们各自冲突的锁定模式集（请参阅 Table 13.2 ）。两个事务不能同时对同一张表的冲突模式锁定。（但是，事务永远不会与自身冲突。例如，它可能会在同一张表上获取 ACCESS EXCLUSIVE 锁定并随后获取 ACCESS SHARE 锁定。）许多事务可以同时持有不冲突的锁定模式。请特别注意，一些锁定模式自相冲突（例如，一个 ACCESS EXCLUSIVE 锁定不能由多个事务同时持有），而其他锁定模式不冲突（例如，一个 ACCESS SHARE 锁定可以由多个事务持有）。

Table-Level Lock Modes

  • ACCESS SHARE (AccessShareLock)

    • 仅与 ACCESS EXCLUSIVE 锁定模式冲突。

    • SELECT 命令获取对引用表此模式的锁定。通常，仅 reads 表且不会修改表的任何查询都会获取此锁定模式。

  • ROW SHARE (RowShareLock)

    • 与 EXCLUSIVE 和 ACCESS EXCLUSIVE 锁定模式冲突。

    • SELECT 命令在此模式下获取一个对所有表进行锁定的锁，在 FOR UPDATE 、 FOR NO KEY UPDATE 、 FOR SHARE 或 FOR KEY SHARE 选项指定的表上（除了其他表上的 ACCESS SHARE 锁之外）任何未明确指定 FOR …​ 锁定选项的表上。

  • ROW EXCLUSIVE (RowExclusiveLock)

    • 与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。

    • 命令 UPDATE、DELETE、INSERT 和 MERGE 在目标表上获取这种锁模式（除了在任何其他引用表上的 ACCESS SHARE 锁）。通常，这种锁模式将由在表中 modifies data 的任何命令获取。

  • SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)

    • 与 SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。此模式可保护表免遭并发的架构更改和 VACUUM 运行。

    • 由 VACUUM （不带 FULL ）、 ANALYZE 、 CREATE INDEX CONCURRENTLY 、 CREATE STATISTICS 、 COMMENT ON 、 REINDEX CONCURRENTLY 及某些 ALTER INDEX 和 ALTER TABLE 变体获取（有关详情，请参见这些命令的文档）。

  • SHARE (ShareLock)

    • 与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。此模式可保护表免遭并发的 data 更改。

    • 由 CREATE INDEX（不含 CONCURRENTLY）获取。

  • SHARE ROW EXCLUSIVE (ShareRowExclusiveLock)

    • 与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。此模式可保护表免遭并发的 data 更改，并且是自排他的，因此一次只能有一个会话持有它。

    • 由 CREATE TRIGGER 及某些形式的 ALTER TABLE 获取。

  • EXCLUSIVE (ExclusiveLock)

    • 与 ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。此模式仅允许并发的 ACCESS SHARE 锁，即，只有对表的读取才能与持有这种锁模式的事务并行执行。

    • 由 REFRESH MATERIALIZED VIEW CONCURRENTLY 获取。

  • ACCESS EXCLUSIVE (AccessExclusiveLock)

    • 与所有模式的锁产生冲突（ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE）。此模式确保持有者是唯一以任何方式访问该表的的事务。

    • 由 DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL 和 REFRESH MATERIALIZED VIEW 命令（不含 CONCURRENTLY）获取。许多形式的 ALTER INDEX 和 ALTER TABLE 也获取此级别的锁。对于未明确指定模式的 LOCK TABLE 语句，这也是默认锁模式。

Tip

只有 ACCESS EXCLUSIVE 锁才能阻止 SELECT（不含 FOR UPDATE/SHARE）语句。

一旦获取了锁，通常会持有它直到事务结束。但是，如果在建立保存点之后获取了锁，那么如果向后回滚到保存点，则该锁将立即释放。这与 ROLLBACK 自保存点之后取消所有命令效果的原则是一致的。在 PL/pgSQL 异常块内获取的锁也是如此：从块中跳出错误将释放块内获取的锁。

Table 13.2. Conflicting Lock Modes

Requested Lock Mode

Existing Lock Mode

ACCESS SHARE

ROW SHARE

ROW EXCL.

SHARE UPDATE EXCL.

SHARE

SHARE ROW EXCL.

EXCL.

ACCESS EXCL.

ACCESS SHARE

X

ROW SHARE

X

X

ROW EXCL.

X

X

X

X

SHARE UPDATE EXCL.

X

X

X

X

X

SHARE

X

X

X

X

X

SHARE ROW EXCL.

X

X

X

X

X

X

EXCL.

X

X

X

X

X

X

X

ACCESS EXCL.

X

X

X

X

X

X

X

X

13.3.2. Row-Level Locks #

除了表级锁定之外，还有行级锁定，这些锁定会在 PostgreSQL 自动使用的上下文中列在下面。有关行级锁定冲突的完整表，请参见 Table 13.3。请注意，一个事务可以对同一行持有冲突的锁定，即使在不同的子事务中也是如此；但除此之外，两个事务绝不会对同一行持有冲突的锁定。行级锁定不会影响数据查询；它们只阻止对同一行发出_writers and lockers_请求。行级锁定在事务结束时或在保存点回滚期间释放，就像表级锁定一样。

Row-Level Lock Modes

  • FOR UPDATE

    • FOR UPDATE_会使 _SELECT 语句检索的行被锁定，就像要更新一样。这会防止其他事务在当前事务结束之前锁定、修改或删除这些行。也就是说，尝试对这些行发出 UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或 SELECT FOR KEY SHARE 的其他事务将被阻止，直到当前事务结束为止；反之，SELECT FOR UPDATE 会等待同时发生的并在同一行上运行过任何这些命令的事务，然后锁定并返回更新后的行（如果行被删除了，则不返回任何行）。然而，如果要锁定的行在事务开始之后发生了更改，REPEATABLE READ 或 SERIALIZABLE 事务中会出现错误。有关详细信息，请参见 Section 13.4。

    • FOR UPDATE 锁模式也会被行上的任何 DELETE 以及修改某些列的值的 UPDATE 获取。目前，UPDATE cases 考虑的列集是对它们具有唯一索引的那些列，该唯一索引可用于外键（因此不考虑部分索引和表达式索引），但这可能在将来发生变化。

  • FOR NO KEY UPDATE

    • 与 FOR UPDATE 类似，但获得的锁更弱：此锁不会阻止尝试在同一行上获取锁的 SELECT FOR KEY SHARE 命令。不获取 FOR UPDATE 锁的任何 UPDATE 也会获取此锁模式。

  • FOR SHARE

    • 与 FOR NO KEY UPDATE 类似，但它获取共享锁而不是在每行检索上获取独占锁。共享锁阻止其他事务对这些行执行 UPDATE、DELETE、SELECT FOR UPDATE 或 SELECT FOR NO KEY UPDATE，但不会阻止它们执行 SELECT FOR SHARE 或 SELECT FOR KEY SHARE。

  • FOR KEY SHARE

    • 与 FOR SHARE 类似，但锁较弱：阻塞 SELECT FOR UPDATE，但不阻塞 SELECT FOR NO KEY UPDATE。密钥共享锁阻止其他事务执行 DELETE 或更改键值的任何 UPDATE，但不会阻止其他 UPDATE，也不会阻止 SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或 SELECT FOR KEY SHARE。

PostgreSQL 不记住内存中已修改行的任何信息，因此一次锁定的行数没有限制。但是，对行进行锁定可能会导致磁盘写入，例如 SELECT FOR UPDATE 修改已选择的行以将其标记为已锁定，并且会产生磁盘写入。

Table 13.3. Conflicting Row-Level Locks

Requested Lock Mode

Current Lock Mode

FOR KEY SHARE

FOR SHARE

FOR NO KEY UPDATE

FOR UPDATE

FOR KEY SHARE

X

FOR SHARE

X

X

FOR NO KEY UPDATE

X

X

X

FOR UPDATE

X

X

X

X

13.3.3. Page-Level Locks #

除了表级和行级锁之外，页级共享/独占锁用于控制共享缓冲池中表页的读/写访问。这些锁在行获取或更新后立即释放。应用程序开发人员通常不必关注页级锁，但此处提到它们是为了完整性。

13.3.4. Deadlocks #

使用显式锁可以增加发生 deadlocks 的可能性，其中两个（或更多）事务各自持有彼此想要的锁。例如，如果事务 1 在表 A 上获取一个独占锁，然后尝试在表 B 上获取一个独占锁，而事务 2 已经独占锁定了表 B，现在想要独占锁定表 A，那么两者都无法继续进行。PostgreSQL 会自动检测到死锁情况，并通过中止所涉及的事务之一来解决死锁情况，从而允许其他事务完成。（究竟中止哪个事务很难预测，不应依赖它。）

请注意，死锁还可以是行级锁的结果（因此，即使不使用显式锁，也可能发生死锁）。考虑两个并发事务修改一个表的情况。第一个事务执行：

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;

这在具有指定帐号的行上获取行级锁。然后，执行第二个事务：

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;

第一个 UPDATE 语句成功在指定行上获取行级锁，因此成功更新该行。但是，第二个 UPDATE 语句发现它尝试更新的行已被锁定，因此它等待获取该锁的事务完成。事务 2 现在正在等待事务 1 完成，然后继续执行。现在，事务 1 执行：

UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

事务 1 尝试在指定行的基础上获取行级锁，但它无法：事务 2 已经持有这样的锁。因此，它等待事务 2 完成。因此，事务 1 被事务 2 阻止，事务 2 被事务 1 阻止：死锁条件。PostgreSQL 将检测到这种情况并中止一个事务。

避免死锁的最佳方法通常是通过确保使用数据库的所有应用程序按一致顺序获取多个对象上的锁。在上面的示例中，如果两个事务都以相同的顺序更新行，则不会发生死锁。还应确保在事务中的对象上获取的第一个锁是对该对象需要的最严格模式。如果无法预先验证这一点，则可以通过重试因死锁而中止的事务来动态处理死锁。

只要未检测到死锁情况，一个试图获取表级或行级锁的事务将无限期地等待释放冲突锁。这意味着应用程序长时间保留事务是不明智的（例如，在等待用户输入时）。

13.3.5. Advisory Locks #

PostgreSQL 提供了一种创建具有应用程序定义的含义的锁的方法。这些称为 advisory locks，因为系统不会强制使用它们——应用程序有责任正确使用它们。建议锁对于难以适应 MVCC 模型的锁定策略很有用。例如，建议锁的常见用法是模拟典型的所谓“平面文件”数据管理系统中的悲观锁定策略。虽然存储在表中的标志可用于相同目的，但建议锁更快，可以避免表膨胀，并且会在会话结束时由服务器自动清除。

有两种方法可以在 PostgreSQL 中获取建议锁：在会话级别或在事务级别。一旦在会话级别获取，建议锁将一直保留，直至明确释放或会话结束。与标准锁请求不同，会话级建议锁请求不遵循事务语义：在稍后回滚的事务中获取的锁在回滚后仍将保留，同样，即使调用事务稍后失败，解锁仍然有效。一个锁可以被其所有进程多次获取；对于每个已完成的锁请求，都必须有相应的解锁请求，然后才能实际释放该锁。另一方面，事务级锁请求的行为更像是常规锁请求：它们会在事务结束时自动释放，并且没有明确的解锁操作。对于建议锁的短期使用，此行为通常比会话级行为更方便。针对相同建议锁标识符的会话级和事务级锁请求将按预期方式相互阻止。如果一个会话已经持有给定的建议锁，那么它会一直成功发出更多请求，即使其他会话正在等待锁也是如此；无论现有的锁持有和新请求是在会话级还是事务级，此语句都是正确的。

与 PostgreSQL 中的所有锁定一样，当前由任何会话持有的建议锁定的完整列表可以在 pg_locks 系统视图中找到。

通告锁定和常规锁定都存储在共享内存池中，共享内存池的大小由配置文件变量 max_locks_per_transaction和 max_connections定义。必须小心，不要耗尽此内存，否则服务器根本无法授予任何锁。这会对服务器能授予的通告锁定的数量施加上限，通常为几十万到几十万，具体取决于服务器配置。

在某些情况下使用建议锁定时，尤其是在涉及明确排序和 LIMIT 子句的查询中，必须小心控制获得的锁，因为这取决于 SQL 表达式的评估顺序。例如：

SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
SELECT pg_advisory_lock(q.id) FROM
(
  SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok

在以上查询中，第二种形式很危险，因为无法保证在执行锁定功能之前应用 LIMIT。这可能会导致获取应用程序意外的某些锁，因此无法释放（直到它结束会话）。从应用程序的角度来看，此类锁将处于未决状态，尽管仍可以在 pg_locks 中查看。

用于操作建议性锁的函数在 Section 9.27.10 中进行说明。