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.5 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.5 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. |