Checking for Locks

Checking for Locks

When a transaction accesses a relation (such as a table), it acquires a lock. Depending on the type of lock acquired, subsequent transactions may have to wait before they can access the same relation. For more information on the types of locks, see "Managing Data" in the Greenplum Database Administrator Guide. Greenplum Database resource queues (used for workload management) also use locks to control the admission of queries into the system.

The gp_locks_* family of views can help diagnose queries and sessions that are waiting to access an object due to a lock.

gp_locks_on_relation

This view shows any locks currently being held on a relation, and the associated session information about the query associated with the lock. For more information on the types of locks, see "Managing Data" in the Greenplum Database Administrator Guide. This view is accessible to all users, however non-superusers will only be able to see the locks for relations that they have permission to access.

Table 1. gp_locks_on_relation view
Column Description
lorlocktype Type of the lockable object: relation, extend, page, tuple, transactionid, object, userlock, resource queue, or advisory
lordatabase Object ID of the database in which the object exists, zero if the object is a shared object.
lorrelname The name of the relation.
lorrelation The object ID of the relation.
lortransaction The transaction ID that is affected by the lock.
lorpid Process ID of the server process holding or awaiting this lock. NULL if the lock is held by a prepared transaction.
lormode Name of the lock mode held or desired by this process.
lorgranted Displays whether the lock is granted (true) or not granted (false).
lorcurrentquery The current query in the session.

gp_locks_on_resqueue

This view shows any locks currently being held on a resource queue, and the associated session information about the query associated with the lock. This view is accessible to all users, however non-superusers will only be able to see the locks associated with their own sessions.

Table 2. gp_locks_on_resqueue view
Column Description
lorusename Name of the user executing the session.
lorrsqname The resource queue name.
lorlocktype Type of the lockable object: resource queue
lorobjid The ID of the locked transaction.
lortransaction The ID of the transaction that is affected by the lock.
lorpid The process ID of the transaction that is affected by the lock.
lormode The name of the lock mode held or desired by this process.
lorgranted Displays whether the lock is granted (true) or not granted (false).
lorwaiting Displays whether or not the session is waiting.