| PostgreSQL 7.4.3 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 43. System Catalogs | Fast Forward | Next |
The view pg_locks provides access to information about the locks held by open transactions within the database server. See Chapter 12 for more discussion of locking.
pg_locks contains one row per active lockable object, requested lock mode, and relevant transaction. Thus, the same lockable object may 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. A lockable object is either a relation (e.g., a table) or a transaction ID.
Note that this view includes only table-level locks, not row-level ones. If a transaction is waiting for a row-level lock, it will appear in the view as waiting for the transaction ID of the current holder of that row lock.
Table 43-32. pg_locks Columns
| Name | Type | References | Description |
|---|---|---|---|
| relation | oid | pg_class.oid | OID of the locked relation, or NULL if the lockable object is a transaction ID |
| database | oid | pg_database.oid | OID of the database in which the locked relation exists, or zero if the locked relation is a globally-shared table, or NULL if the lockable object is a transaction ID |
| transaction | xid | ID of a transaction, or NULL if the lockable object is a relation | |
| pid | integer | process ID of a server process holding or awaiting this lock | |
| mode | text | name of the lock mode held or desired by this process (see Section 12.3.1) | |
| granted | boolean | true if lock is held, false if lock is awaited |
granted is true in a row representing a lock held by the indicated session. False indicates that this session is currently waiting to acquire this lock, which implies that some other session is holding a conflicting lock mode on the same lockable object. The waiting session will sleep until the other lock is released (or a deadlock situation is detected). A single session can be waiting to acquire at most one lock at a time.
Every transaction holds an exclusive lock on its transaction ID for its entire duration. If 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. That will succeed only when the other transaction terminates and releases its locks.
When the pg_locks view 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 read often.
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).
If you have enabled the statistics collector, the pid column can be joined to the procpid column of the pg_stat_activity view to get more information on the session holding or waiting to hold the lock.