Postgresql 中文操作指南
54.12. pg_locks #
视图 _pg_locks_提供了访问数据库服务器中活动进程持有的锁的信息。参见 Chapter 13以了解关于锁定的更多讨论。
The view pg_locks provides access to information about the locks held by active processes within the database server. See Chapter 13 for more discussion of locking.
pg_locks 对每个活动可锁定对象、请求的锁定模式和相关的进程包含一行。因此，如果多个进程正在持有或等待对同一对象的锁定，则同一可锁定对象可能会出现多次。但是，如果某个对象当前没有任何锁定，则它将完全不会出现。
pg_locks contains one row per active lockable object, requested lock mode, and relevant process. Thus, the same lockable object might appear many times, if multiple processes are holding or waiting for locks on it. However, an object that currently has no locks on it will not appear at all.
有几种不同类型的可锁对象：整个关系（例如，表）、关系的各个页面、关系的各个元组、事务 ID（虚拟 ID 和永久 ID）和通用数据库对象（通过类 OID 和对象 OID 标识，类似于 pg_description 或 pg_depend 中的标识方式）。此外，对关系进行扩展的权利表示为一个单独的可锁对象，就像更新 pg_database datfrozenxid 的权利一样。此外，还可以在具有用户定义意义的数字上获取“建议”锁。
There are several distinct types of lockable objects: whole relations (e.g., tables), individual pages of relations, individual tuples of relations, transaction IDs (both virtual and permanent IDs), and general database objects (identified by class OID and object OID, in the same way as in pg_description or pg_depend). Also, the right to extend a relation is represented as a separate lockable object, as is the right to update pg_database.datfrozenxid. Also, “advisory” locks can be taken on numbers that have user-defined meanings.
Table 54.12. pg_locks Columns
Table 54.12. pg_locks Columns
Column Type Description |
locktype text Type of the lockable object: relation, extend, frozenid, page, tuple, transactionid, virtualxid, spectoken, object, userlock, advisory, or applytransaction. (See also Table 28.11.) |
database oid (references pg_database.oid) OID of the database in which the lock target exists, or zero if the target is a shared object, or null if the target is a transaction ID |
relation oid (references pg_class.oid) OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation |
page int4 Page number targeted by the lock within the relation, or null if the target is not a relation page or tuple |
tuple int2 Tuple number targeted by the lock within the page, or null if the target is not a tuple |
virtualxid text Virtual ID of the transaction targeted by the lock, or null if the target is not a virtual transaction ID; see Chapter 74 |
transactionid xid ID of the transaction targeted by the lock, or null if the target is not a transaction ID; Chapter 74 |
classid oid (references pg_class.oid) OID of the system catalog containing the lock target, or null if the target is not a general database object |
objid oid (references any OID column) OID of the lock target within its system catalog, or null if the target is not a general database object |
objsubid int2 Column number targeted by the lock (the classid and objid refer to the table itself), or zero if the target is some other general database object, or null if the target is not a general database object |
virtualtransaction text Virtual ID of the transaction that is holding or awaiting this lock |
pid int4 Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction |
mode text Name of the lock mode held or desired by this process (see Section 13.3.1 and Section 13.2.3) |
granted bool True if lock is held, false if lock is awaited |
fastpath bool True if lock was taken via fast path, false if taken via main lock table |
waitstart timestamptz Time when the server process started waiting for this lock, or null if the lock is held. Note that this can be null for a very short period of time after the wait started even though granted is false. |
granted 在表示由指定进程持有的锁定的行中为 true。False 表示此进程当前正等待获取此锁定，这意味着至少还有另一个进程在持有或等待对相同可锁定对象的冲突锁定模式。等待进程将休眠，直到其他锁定释放（或检测到死锁情况）。单个进程最多可以一次等待获取一个锁定。
granted is true in a row representing a lock held by the indicated process. False indicates that this process is currently waiting to acquire this lock, which implies that at least one other process is holding or waiting for a conflicting lock mode on the same lockable object. The waiting process will sleep until the other lock is released (or a deadlock situation is detected). A single process can be waiting to acquire at most one lock at a time.
在运行事务期间，服务器进程对事务的虚拟事务 ID 持有独占锁定。如果为事务分配永久 ID（通常只有在事务更改数据库状态时才会发生），它还将对事务的永久事务 ID 持有独占锁定，直至结束。当进程发现需要专门等待另一个事务结束时，它将尝试获取对另一个事务的 ID（虚拟 ID 或永久 ID，具体取决于情况）的共享锁定。只有在另一个事务终止并释放其锁定时，此操作才会成功。
Throughout running a transaction, a server process holds an exclusive lock on the transaction’s virtual transaction ID. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on the transaction’s permanent transaction ID until it ends. When a process finds it necessary to wait specifically for another transaction to end, it does so by attempting to acquire share lock on the other transaction’s ID (either virtual or permanent ID depending on the situation). That will succeed only when the other transaction terminates and releases its locks.
虽然元组是可锁定类型的对象，但有关行级锁定的信息存储在磁盘上，而不是存储在内存中，因此通常不显示行级锁定此视图中。如果某个进程正在等待行级锁定，它通常会显示在此视图中，等待持有此行锁定的当前持有者的永久事务 ID。
Although tuples are a lockable type of object, information about row-level locks is stored on disk, not in memory, and therefore row-level locks normally do not appear in this view. If a process is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock.
推测插入锁定由事务 ID 和推测插入令牌组成。推测插入令牌显示在 objid 列中。
A speculative insertion lock consists of a transaction ID and a speculative insertion token. The speculative insertion token is displayed in the objid column.
对键获取咨询锁定，其中键要么是单个 bigint 值，要么是两个整型值。bigint 键在 classid 列中用它的高阶一半显示，在 objid 列中用它的低阶一半显示，且 objsubid 等于 1。使用表达式 (classid::bigint << 32) | objid::bigint 可以重新组装原始 bigint 值。整型键在 classid 列中用第一个键显示，在 objid 列中用第二个键显示，且 objsubid 等于 2。键的实际含义由用户决定。咨询锁定在每个数据库中都是本地的，因此 database 列对于咨询锁定是有意义的。
Advisory locks can be acquired on keys consisting of either a single bigint value or two integer values. A bigint key is displayed with its high-order half in the classid column, its low-order half in the objid column, and objsubid equal to 1. The original bigint value can be reassembled with the expression (classid::bigint << 32) | objid::bigint. Integer keys are displayed with the first key in the classid column, the second key in the objid column, and objsubid equal to 2. The actual meaning of the keys is up to the user. Advisory locks are local to each database, so the database column is meaningful for an advisory lock.
应用事务锁定在并行模式中使用，以在逻辑复制中应用事务。远程事务 ID 显示在 transactionid 列中。objsubid 显示锁定子类型，其中用于同步变更集的锁定为 0，用于等待事务结束以确保提交顺序的锁定为 1。
Apply transaction locks are used in parallel mode to apply the transaction in logical replication. The remote transaction ID is displayed in the transactionid column. The objsubid displays the lock subtype which is 0 for the lock used to synchronize the set of changes, and 1 for the lock used to wait for the transaction to finish to ensure commit order.
pg_locks 提供数据库集群中所有锁定的全局视图，不仅包括与当前数据库相关的锁。虽然其 relation 列可以链接到 pg_class . oid 以识别锁定关系，但此方法仅适用于当前数据库中的关系（即 database 列为当前数据库的 OID 或 0 的关系）。
pg_locks provides a global view of all locks in the database cluster, not only those relevant to the current database. Although its relation column can be joined against pg_class.oid to identify locked relations, this will only work correctly for relations in the current database (those for which the database column is either the current database’s OID or zero).
pid 列可以链接到 pg_stat_activity 视图的 pid 列，以获取有关持有或等待每个锁定的会话的更多信息，例如
The pid column can be joined to the pid column of the pg_stat_activity view to get more information on the session holding or awaiting each lock, for example
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
ON pl.pid = psa.pid;此外，如果您使用已准备的事务，那么 virtualtransaction 列可以链接到 pg_prepared_xacts 视图的 transaction 列，以获取有关持有锁定的已准备事务的更多信息。（已准备的事务绝不会等待锁定，但会继续持有其在运行过程中获取的锁定。）例如：
Also, if you are using prepared transactions, the virtualtransaction column can be joined to the transaction column of the pg_prepared_xacts view to get more information on prepared transactions that hold locks. (A prepared transaction can never be waiting for a lock, but it continues to hold the locks it acquired while running.) For example:
SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
ON pl.virtualtransaction = '-1/' || ppx.transaction;虽然可以通过将_pg_locks_与它自身联接以获取有关哪个进程阻塞哪个其他进程的信息，但要详细正确地获取信息非常困难。这样的查询必须编码有关哪些锁定模式与哪些其他锁定模式冲突的知识。更糟的是，pg_locks_视图没有公开有关哪个进程在锁定等待队列中领先于哪个其他进程的信息，也没有公开有关哪个进程是代表哪个其他客户端会话运行的并行工作进程的信息。最好使用 _pg_blocking_pids() 函数（参见 Table 9.67）来识别等待进程被哪个进程阻塞。
While it is possible to obtain information about which processes block which other processes by joining pg_locks against itself, this is very difficult to get right in detail. Such a query would have to encode knowledge about which lock modes conflict with which others. Worse, the pg_locks view does not expose information about which processes are ahead of which others in lock wait queues, nor information about which processes are parallel workers running on behalf of which other client sessions. It is better to use the pg_blocking_pids() function (see Table 9.67) to identify which process(es) a waiting process is blocked behind.
pg_locks 视图显示来自常规锁定管理器和谓词锁定管理器的数据，它们是分离的系统；此外，常规锁定管理器将其锁定细分为常规锁定和 fast-path 锁定。不能保证此数据完全一致。在查询视图时，从每个后端一次收集快速路径锁定（其中 fastpath = true）上的数据，而不冻结整个锁定管理器的状态，因此有可能会在收集信息时获取或释放锁定。但是，请注意，这些锁定已知不会与当前存在的任何其他锁定冲突。在查询所有后端以查找快速路径锁定后，常规锁定管理器剩余部分将作为一个单元进行锁定，然后以原子操作的方式收集所有剩余锁定的快照。在解锁常规锁定管理器后，将以类似方式锁定谓词锁定管理器，并以原子操作的方式收集所有谓词锁定。因此，除了快速路径锁定之外，每个锁定管理器都会提供一组一致的结果，但是由于我们没有同时锁定两个锁定管理器，所以有可能在我们询问常规锁定管理器并在我们询问谓词锁定管理器之前，锁定已被获取或释放。
The pg_locks view displays data from both the regular lock manager and the predicate lock manager, which are separate systems; in addition, the regular lock manager subdivides its locks into regular and fast-path locks. This data is not guaranteed to be entirely consistent. When the view is queried, data on fast-path locks (with fastpath = true) is gathered from each backend one at a time, without freezing the state of the entire lock manager, so it is possible for locks to be taken or released while information is gathered. Note, however, that these locks are known not to conflict with any other lock currently in place. After all backends have been queried for fast-path locks, the remainder of the regular lock manager is locked as a unit, and a consistent snapshot of all remaining locks is collected as an atomic action. After unlocking the regular lock manager, the predicate lock manager is similarly locked and all predicate locks are collected as an atomic action. Thus, with the exception of fast-path locks, each lock manager will deliver a consistent set of results, but as we do not lock both lock managers simultaneously, it is possible for locks to be taken or released after we interrogate the regular lock manager and before we interrogate the predicate lock manager.
如果此视图非常频繁地访问，则锁定常规锁定管理器和/或谓词锁定管理器可能会对数据库性能产生一些影响。这些锁定只保持获取锁定管理器数据所需的最小时间，但这并不能完全消除性能影响的可能性。
Locking the regular and/or predicate lock manager could have some impact on database performance if this view is very frequently accessed. The locks are held only for the minimum amount of time necessary to obtain data from the lock managers, but this does not completely eliminate the possibility of a performance impact.