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
|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.
The following query lists the contents on the pg_locks view but also joins on the pg_class and pg_database views so you can see the names of the tables and databases involved. I don't think there is any particular problem with the query below but feel free to point out if I've missed something obvious.
SELECT pg_class.relname AS table, pg_database.datname AS database, transaction, pid, mode, granted
FROM pg_locks, pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid
AND pg_locks.database = pg_database.oid
I use the following query when I run into locking troubles. This query gives you lock information for both the relations and the transactions.
select pg_stat_activity.datname,pg_class.relname,pg_locks.transaction, pg_locks.mode, pg_locks.granted,pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start, age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid order by query_start;