We have migrated to new Google sites!
Post date: Nov 29, 2012 8:19:12 PM
When a database statement seems to be "hung" or executes very slowly, we often find that SQL statements are holding conflicting locks. It is best practice to run a few basic checks before calling for help. The database catalog tables can tell what each SQL statement is doing, and will reveal if there is a locking problem. The "pg_stat_activity" catalog table contains information about all the connected sessions – the process ID of the server process, the session ID, database user, current query executing, when the current query started, and finally whether the session is waiting for somebody else or working. If a session is waiting, it means another statement is holding a lock that is keeping it from executing. The
statement needs to wait for a while until the other session releases the lock.
The "pg_locks" catalog table contains a list of all locks that database sessions hold and the lock description – the type of the lock, which database the lock is in, which database object is locked, the process ID of the session, the lock mode, the session id, and whether the lock is granted or the session is waiting. If the "hung" session has a non-granted lock entry (granted=false), then it is waiting for another session to release a conflicting lock (granted=true).
1. Queries being executed:
Select * from pg_stat_activity where waiting is false;
2. Queries not being executed and waiting in queue:
Select * from pg_stat_activity where waiting is true;
3. How long since a query has been submitted:
Select sess_id,substr(current_query,1,20),now()-query_start from pg_stat_activity;
4. Table to find more on what is the query waiting on:
Select * from pg_locks where granted is false;
Select * from pg_locks where mppsessionid=<sess_id from pg_Stat_activity>;
It is easy to find out which session has the conflicting lock using the "pg_locks" table. Both tables ("pg_stat_activity" and "pg_locks") share a couple of common columns, which allows for joins and easy correlation between sessions queries and locks.
In addition, the "gp_toolkit" database schema contains two views to help with locks: "gp_toolkit.gp_locks_on_relation" and "gp_toolkit.gp_locks_on_resqueue". The first one shows the locks held and wanted on database objects, and the second shows the locks on resource queues (resource queues in Greenplum database are implemented with the same infrastructure as database locks).