We have migrated to new Google sites!
Checking for Locks (Contention)
If a transaction is holding a lock on an object, there may be other queries that are waiting for that lock to be released before they can continue. This may appear to the user as if their query is hanging. The pg_locks system catalog view allows you to view information about outstanding locks. Examining pg_locks for ungranted locks can help identify contention between database client sessions. pg_locks provides a global view of all locks in the database system, not only those relevant to the current database. Although its relation column can be joined against pg_class.oid to identify locked relations (such as tables), this will only work correctly for relations in the current database. The pid column can be joined to the pg_stat_activity.procpid to get more information on the session holding or waiting to hold a lock.
For example:
SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query
FROM pg_locks l, pg_class c, pg_stat_activity a WHERE l.relation=c.oid AND l.pid=a.procpid
ORDER BY c.relname;
If you are using resource queues for workload management, queries that are waiting in a queue will also show in pg_locks. To see how many queries are waiting to run from a particular resource queue, use the gp_resqueue_status system catalog view.
For example:
SELECT * FROM gp_toolkit.gp_resqueue_status;