We have migrated to new Google sites!
Post date: Jan 20, 2014 8:47:44 PM
gp_toolkit.gp_locks_on_relation helps diagnose queries and sessions that are waiting to access an object due to a lock.
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=#