The view pg_locks
provides access to
information about the locks held by open transactions within the
database server. See Chapter 12, Concurrency Control 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.
There are several distinct types of lockable objects:
whole relations (e.g., tables), individual pages of relations,
individual tuples of relations,
transaction 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.
Table 43.39. pg_locks
Columns
Name | Type | References | Description |
---|---|---|---|
locktype |
text |
type of the lockable object:
relation ,
extend ,
page ,
tuple ,
transactionid ,
object ,
userlock , or
advisory
|
|
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 |
|
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 | |
transactionid |
xid |
ID of a transaction, or NULL if the object is not a transaction ID | |
classid |
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 |
objsubid |
smallint |
For a table column, this is the column number (the
classid and objid refer to the
table itself). For all other object types, this column is
zero. NULL if the object is not a general database object
|
|
transaction |
xid |
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 12.3.1, “Table-Level Locks”) | |
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 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 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.
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 transaction ID of the current holder of that row lock.
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. 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.
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
frequently accessed.
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.
Also, if you are using prepared transactions, the
transaction
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.)