Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




42.37. pg_locks

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.

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 42-37. pg_locks Columns

Name Type References Description
locktype text   type of the lockable object: relation, extend, page, tuple, transactionid, object, or userlock
database oid pg_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 pg_class .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 pg_class .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)
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.

If user-defined locks are in use, they are displayed using the columns for general database objects. However, the actual meaning of the lock fields in such cases is up to the user.

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

  Published courtesy of The PostgreSQL Global Development Group Design by Interspire