Another useful tool for monitoring database activity is the
pg_locks system catalog. This allows the
database administrator to view information about the outstanding
locks in the lock manager. For example, this capability can be used
to:
View all the locks currently outstanding, all the locks on
relations in a particular database, all the locks on a
particular relation, or all the locks held by a particular
PostgreSQL session.
View the relation in the current database with the most
ungranted locks (which might be a source of contention among
database clients).
Determine the effect of lock contention on overall database
performance, as well as the extent to which contention varies
with overall database traffic.
For more information on locking and managing concurrency with
PostgreSQL, refer to the PostgreSQL 7.3 User's Guide.
Note: 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
examined often.
Table 10-3 shows the definition of the
pg_locks columns. The
pg_locks view contains one row per lockable
object and requested lock mode. Thus, the same lockable object may
appear many times, if multiple transactions are holding or waiting
for locks on it. A lockable object is either a relation 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 10-3. Lock Status System View
Column Name
Type
Description
relation
oid
The OID of the locked relation, or null if the lockable object
is a transaction ID. This column can be joined with the
pg_class system catalog to get more
information on the locked relation. Note however that this
will only work for relations in the current database (those for
which the database column is either
the current database's OID or zero).
database
oid
The OID of the database in which the locked relation exists, or
null if the lockable object is a transaction ID. If the lock
is on a globally-shared table, this field will be zero. This
column can be joined with the pg_database
system catalog to get more information on the locked object's
database.
transaction
xid
The ID of a transaction, or null if the lockable object is a
relation. 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.
pid
integer
The process ID of the PostgreSQL
backend belonging to the session that has acquired or is
attempting to acquire the lock. If you have enabled the
statistics collector, this column can be joined with the
pg_stat_activity view to get more
information on the backend holding or waiting to hold the
lock.
mode
text
The mode of the requested or held lock on the lockable
object. For more information on the different lock modes
available in PostgreSQL, refer to
the PostgreSQL 7.3 User's Guide.
isgranted
boolean
True if this lock has been granted (is held by this 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.
This backend will sleep until the other lock is released (or a
deadlock situation is detected). A single backend can be
waiting to acquire at most one lock at a time.
