Postgresql 中文操作指南

13.3. Explicit Locking #

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

PostgreSQL provides various lock modes to control concurrent access to data in tables. These modes can be used for application-controlled locking in situations where MVCC does not give the desired behavior. Also, most PostgreSQL commands automatically acquire locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the command executes. (For example, TRUNCATE cannot safely be executed concurrently with other operations on the same table, so it obtains an ACCESS EXCLUSIVE lock on the table to enforce that.)

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

To examine a list of the currently outstanding locks in a database server, use the pg_locks system view. For more information on monitoring the status of the lock manager subsystem, refer to Chapter 28.

13.3.1. Table-Level Locks #

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

The list below shows the available lock modes and the contexts in which they are used automatically by PostgreSQL. You can also acquire any of these locks explicitly with the command LOCK. Remember that all of these lock modes are table-level locks, even if the name contains the word “row”; the names of the lock modes are historical. To some extent the names reflect the typical usage of each lock mode — but the semantics are all the same. The only real difference between one lock mode and another is the set of lock modes with which each conflicts (see Table 13.2). Two transactions cannot hold locks of conflicting modes on the same table at the same time. (However, a transaction never conflicts with itself. For example, it might acquire ACCESS EXCLUSIVE lock and later acquire ACCESS SHARE lock on the same table.) Non-conflicting lock modes can be held concurrently by many transactions. Notice in particular that some lock modes are self-conflicting (for example, an ACCESS EXCLUSIVE lock cannot be held by more than one transaction at a time) while others are not self-conflicting (for example, an ACCESS SHARE lock can be held by multiple transactions).

Table-Level Lock Modes

  • ACCESS SHARE (AccessShareLock)

    • Conflicts with the ACCESS EXCLUSIVE lock mode only.

    • The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode.

  • ROW SHARE (RowShareLock)

    • Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

    • The SELECT command acquires a lock of this mode on all tables on which one of the FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE options is specified (in addition to ACCESS SHARE locks on any other tables that are referenced without any explicit FOR …​ locking option).

  • ROW EXCLUSIVE (RowExclusiveLock)

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

    • The commands UPDATE, DELETE, INSERT, and MERGE 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 data in a table.

  • SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)

    • Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent schema changes and VACUUM runs.

    • Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, and certain ALTER INDEX and ALTER TABLE variants (for full details see the documentation of these commands).

  • SHARE (ShareLock)

    • Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes.

    • Acquired by CREATE INDEX (without CONCURRENTLY).

  • SHARE ROW EXCLUSIVE (ShareRowExclusiveLock)

    • Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.

    • Acquired by CREATE TRIGGER and some forms of ALTER TABLE.

  • EXCLUSIVE (ExclusiveLock)

    • Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.

    • Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.

  • ACCESS EXCLUSIVE (AccessExclusiveLock)

    • Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only transaction accessing the table in any way.

    • Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many forms of ALTER INDEX and ALTER TABLE also acquire a lock at this level. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly.

Tip

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

Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement.

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

Once acquired, a lock is normally held until the end of the transaction. But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it.

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_请求。行级锁定在事务结束时或在保存点回滚期间释放，就像表级锁定一样。

In addition to table-level locks, there are row-level locks, which are listed as below with the contexts in which they are used automatically by PostgreSQL. See Table 13.3 for a complete table of row-level lock conflicts. Note that a transaction can hold conflicting locks on the same row, even in different subtransactions; but other than that, two transactions can never hold conflicting locks on the same row. Row-level locks do not affect data querying; they block only writers and lockers to the same row. Row-level locks are released at transaction end or during savepoint rollback, just like table-level locks.

Row-Level Lock Modes

  • FOR UPDATE

    • FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4.

    • The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values of certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future.

  • FOR NO KEY UPDATE

    • Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

  • FOR SHARE

    • Behaves similarly to FOR NO KEY UPDATE, except that it acquires a shared lock rather than exclusive lock on each retrieved row. A shared lock blocks other transactions from performing UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE on these rows, but it does not prevent them from performing SELECT FOR SHARE or SELECT FOR KEY SHARE.

  • FOR KEY SHARE

    • Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE, and neither does it prevent SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.

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

PostgreSQL doesn’t remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes.

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 #

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

In addition to table and row locks, page-level share/exclusive locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a row is fetched or updated. Application developers normally need not be concerned with page-level locks, but they are mentioned here for completeness.

13.3.4. Deadlocks #

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

The use of explicit locking can increase the likelihood of deadlocks, wherein two (or more) transactions each hold locks that the other wants. For example, if transaction 1 acquires an exclusive lock on table A and then tries to acquire an exclusive lock on table B, while transaction 2 has already exclusive-locked table B and now wants an exclusive lock on table A, then neither one can proceed. PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to predict and should not be relied upon.)

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

Note that deadlocks can also occur as the result of row-level locks (and thus, they can occur even if explicit locking is not used). Consider the case in which two concurrent transactions modify a table. The first transaction executes:

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

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

This acquires a row-level lock on the row with the specified account number. Then, the second transaction executes:

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 执行：

The first UPDATE statement successfully acquires a row-level lock on the specified row, so it succeeds in updating that row. However, the second UPDATE statement finds that the row it is attempting to update has already been locked, so it waits for the transaction that acquired the lock to complete. Transaction two is now waiting on transaction one to complete before it continues execution. Now, transaction one executes:

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

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

Transaction one attempts to acquire a row-level lock on the specified row, but it cannot: transaction two already holds such a lock. So it waits for transaction two to complete. Thus, transaction one is blocked on transaction two, and transaction two is blocked on transaction one: a deadlock condition. PostgreSQL will detect this situation and abort one of the transactions.

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

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order. In the example above, if both transactions had updated the rows in the same order, no deadlock would have occurred. One should also ensure that the first lock acquired on an object in a transaction is the most restrictive mode that will be needed for that object. If it is not feasible to verify this in advance, then deadlocks can be handled on-the-fly by retrying transactions that abort due to deadlocks.

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

So long as no deadlock situation is detected, a transaction seeking either a table-level or row-level lock will wait indefinitely for conflicting locks to be released. This means it is a bad idea for applications to hold transactions open for long periods of time (e.g., while waiting for user input).

13.3.5. Advisory Locks #

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

PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly. Advisory locks can be useful for locking strategies that are an awkward fit for the MVCC model. For example, a common use of advisory locks is to emulate pessimistic locking strategies typical of so-called “flat file” data management systems. While a flag stored in a table could be used for the same purpose, advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of the session.

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

There are two ways to acquire an advisory lock in PostgreSQL: at session level or at transaction level. Once acquired at session level, an advisory lock is held until explicitly released or the session ends. Unlike standard lock requests, session-level advisory lock requests do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback, and likewise an unlock is effective even if the calling transaction fails later. A lock can be acquired multiple times by its owning process; for each completed lock request there must be a corresponding unlock request before the lock is actually released. Transaction-level lock requests, on the other hand, behave more like regular lock requests: they are automatically released at the end of the transaction, and there is no explicit unlock operation. This behavior is often more convenient than the session-level behavior for short-term usage of an advisory lock. Session-level and transaction-level lock requests for the same advisory lock identifier will block each other in the expected way. If a session already holds a given advisory lock, additional requests by it will always succeed, even if other sessions are awaiting the lock; this statement is true regardless of whether the existing lock hold and new request are at session level or transaction level.

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

Like all locks in PostgreSQL, a complete list of advisory locks currently held by any session can be found in the pg_locks system view.

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

Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all. This imposes an upper limit on the number of advisory locks grantable by the server, typically in the tens to hundreds of thousands depending on how the server is configured.

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

In certain cases using advisory locking methods, especially in queries involving explicit ordering and LIMIT clauses, care must be taken to control the locks acquired because of the order in which SQL expressions are evaluated. For example:

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 中查看。

In the above queries, the second form is dangerous because the LIMIT is not guaranteed to be applied before the locking function is executed. This might cause some locks to be acquired that the application was not expecting, and hence would fail to release (until it ends the session). From the point of view of the application, such locks would be dangling, although still viewable in pg_locks.

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

The functions provided to manipulate advisory locks are described in Section 9.27.10.