©
This document usesPHP Chinese website manualRelease
The viewpg_locksprovides access to information about the locks held by open transactions within the database server. See Chapter 13 for more discussion of locking.
pg_lockscontains one row per active lockable object, requested lock mode, and relevant transaction. Thus, the same lockable object might appear many times, if multiple transactions are holding or waiting for locks on it. However, an object that currently has no locks on it will not appear at all.
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 inpg_descriptionorpg_depend). Also, the right to extend a relation is represented as a separate lockable object.
Table 45-51.pg_locksColumns
| Name | Type | References | Description |
|---|---|---|---|
| locktype | text | Type of the lockable object:relation,extend,page,tuple,transactionid,virtualxid,object,userlock, oradvisory | |
| database | oid | pg_database.oid | OID of the database in which the object exists, or zero if the object is a shared object, or null if the object is a transaction ID |
| relation | oid | pg_class.oid | OID of the relation, or null if the object is not a relation or part of a relation |
| page | integer | Page number within the relation, or null if the object is not a tuple or relation page | |
| tuple | smallint | Tuple number within the page, or null if the object is not a tuple | |
| virtualxid | text | Virtual ID of a transaction, or null if the object is not a virtual transaction ID | |
| transactionid | xid | ID of a transaction, or null if the object is not a transaction ID | |
| classid | oid | pg_class.oid | OID of the system catalog containing the object, or null if the object is not a general database object |
| objid | oid | any OID column | OID of the object within its system catalog, or null if the object is not a general database object. For advisory locks it is used to distinguish the two key spaces (1for an int8 key,2for two int4 keys). |
| objsubid | smallint | For a table column, this is the column number (theclassidandobjidrefer to the table itself). For all other object types, this column is zero. Null if the object is not a general database object | |
| virtualtransaction | text | Virtual ID of the transaction that is holding or awaiting this lock | |
| pid | integer | Process ID of the server process holding or awaiting this lock. 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) | |
| granted | boolean | True if lock is held, false if lock is awaited |
grantedis true in a row representing a lock held by the indicated transaction. False indicates that this transaction is currently waiting to acquire this lock, which implies that some other transaction is holding a conflicting lock mode on the same lockable object. The waiting transaction will sleep until the other lock is released (or a deadlock situation is detected). A single transaction can be waiting to acquire at most one lock at a time.
Every transaction holds an exclusive lock on its virtual transaction ID for its entire duration. 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 its permanent transaction ID until it ends. When one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID (either virtual or permanent ID depending on the situation). That will succeed only when the other transaction terminates and releases its locks.
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 transaction 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.
Advisory locks can be acquired on keys consisting of either a singlebigintvalue or two integer values. Abigintkey is displayed with its high-order half in theclassidcolumn, its low-order half in theobjidcolumn, andobjsubidequal to 1. Integer keys are displayed with the first key in theclassidcolumn, the second key in theobjidcolumn, andobjsubidequal to 2. The actual meaning of the keys is up to the user. Advisory locks are local to each database, so thedatabasecolumn is meaningful for an advisory lock.
When thepg_locksview is accessed, the internal lock manager data structures are momentarily locked, and a copy is made for the view to display. This ensures that the view produces a consistent set of results, while not blocking normal lock manager operations longer than necessary. Nonetheless there could be some impact on database performance if this view is frequently accessed.
pg_locksprovides a global view of all locks in the database cluster, not only those relevant to the current database. Although itsrelationcolumn can be joined againstpg_class.oidto identify locked relations, this will only work correctly for relations in the current database (those for which thedatabasecolumn is either the current database's OID or zero).
Thepidcolumn can be joined to theprocpidcolumn of thepg_stat_activityview to get more information on the session holding or waiting to hold each lock. Also, if you are using prepared transactions, thetransactioncolumn can be joined to thetransactioncolumn of thepg_prepared_xactsview 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.)
