gp toolkit views to diagnose queries and sessions that are waiting to access an object due to a lock

Post date: Jan 20, 2014 8:46:14 PM

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 the Greenplum Database Database Administrator Guide. Greenplum Database resource queues (used for workload management) also use locks to control the admission of queries into the system.

Following 2 gp_toolkit schema views can help diagnose queries and sessions that are waiting to access an object due to a lock.

1. gp_locks_on_relation

2. gp_locks_on_resqueue

1. gp_locks_on_relation

gpadmin=# \d gp_toolkit.gp_locks_on_relation

View "gp_toolkit.gp_locks_on_relation"

     Column      |  Type   | Modifiers 

-----------------+---------+-----------

 lorlocktype     | text    | 

 lordatabase     | oid     | 

 lorrelname      | name    | 

 lorrelation     | oid     | 

 lortransaction  | xid     | 

 lorpid          | integer | 

 lormode         | text    | 

 lorgranted      | boolean | 

 lorcurrentquery | text    | 

View definition:

 SELECT pgl.locktype AS lorlocktype, pgl.database AS lordatabase, pgc.relname AS lorrelname, pgl.relation AS lorrelation, pgl.transaction AS lortransaction, pgl.pid AS lorpid, pgl.mode AS lormode, pgl.granted AS lorgranted, pgsa.current_query AS lorcurrentquery

   FROM pg_locks pgl

   JOIN pg_class pgc ON pgl.relation = pgc.oid

   JOIN pg_stat_activity pgsa ON pgl.pid = pgsa.procpid

  ORDER BY pgc.relname;

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.

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 the Greenplum Database 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.

gpadmin=# select * from gp_toolkit.gp_locks_on_relation;

lorlocktype | lordatabase | lorrelname | lorrelation | lortransaction | lorpid | lormode | lorgranted | lorcurrentq

uery

-------------+-------------+-----------------------------+-------------+----------------+--------+-----------------+------------+----------------------------

---------------------

relation | 16992 | gp_locks_on_relation | 16649 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 0 | pg_authid | 1260 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 0 | pg_authid_oid_index | 2677 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 0 | pg_authid_rolname_index | 2676 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 0 | pg_authid_rolresqueue_index | 6029 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 16992 | pg_class | 1259 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 16992 | pg_class_oid_index | 2662 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 16992 | pg_class_relname_nsp_index | 2663 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 0 | pg_database | 1262 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 0 | pg_database_datname_index | 2671 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 0 | pg_database_oid_index | 2672 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 16992 | pg_locks | 10337 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

relation | 16992 | pg_stat_activity | 10405 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g

p_locks_on_relation;

(13 rows)

gpadmin=# 

2. gp_locks_on_resqueue

gpadmin=# \d gp_toolkit.gp_locks_on_resqueue

View "gp_toolkit.gp_locks_on_resqueue"

     Column     |  Type   | Modifiers 

----------------+---------+-----------

 lorusename     | name    | 

 lorrsqname     | name    | 

 lorlocktype    | text    | 

 lorobjid       | oid     | 

 lortransaction | xid     | 

 lorpid         | integer | 

 lormode        | text    | 

 lorgranted     | boolean | 

 lorwaiting     | boolean | 

View definition:

 SELECT pgsa.usename AS lorusename, pgrq.rsqname AS lorrsqname, pgl.locktype AS lorlocktype, pgl.objid AS lorobjid, pgl.transaction AS lortransaction, pgl.pid AS lorpid, pgl.mode AS lormode, pgl.granted AS lorgranted, pgsa.waiting AS lorwaiting

   FROM pg_stat_activity pgsa

   JOIN pg_locks pgl ON pgsa.procpid = pgl.pid

   JOIN pg_resqueue pgrq ON pgl.objid = pgrq.oid;

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.

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.

gpadmin=# select * from  gp_toolkit.gp_locks_on_resqueue;

 lorusename | lorrsqname | lorlocktype | lorobjid | lortransaction | lorpid | lormode | lorgranted | lorwaiting 

------------+------------+-------------+----------+----------------+--------+---------+------------+------------

(0 rows)