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 ------------+------------+-------------+----------+----------------+--------+---------+------------+------------ |
|
|