I’d like to keep track of mutual locks in Postgres on a regular basis.
I came across an article on Locks Monitoring and attempted to execute the following query:
SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid WHERE NOT bl.granted;
Unfortunately, it never provides a result set that isn’t empty. If I reduce the following query to its simplest form:
SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, a.query AS blocked_statement FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid WHERE NOT bl.granted;
Then it returns queries that are waiting for a lock to be acquired. However, I can’t seem to get it to return both blocked and blocker queries.
Asked by Roman
This has been a lot easier since 9.6, when the function pg blocking pids() was added to discover the sessions that are blocking another session.
As an example, you could use something like this:
select pid, usename, pg_blocking_pids(pid) as blocked_by, query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;
Answered by a_horse_with_no_name
The following query can be used to extract blocked query and blocker query information from this excellent article on query locks in Postgres.
CREATE VIEW lock_monitor AS( SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item, now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid, blockeda.query as blocked_query, blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid, blockinga.query as blocking_query, blockingl.mode as blocking_mode FROM pg_catalog.pg_locks blockedl JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid JOIN pg_catalog.pg_locks blockingl ON( ( (blockingl.transactionid=blockedl.transactionid) OR (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype) ) AND blockedl.pid != blockingl.pid) JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid WHERE NOT blockedl.granted AND blockinga.datname = current_database() ); SELECT * from lock_monitor;
Because the query is long but useful, the author of the post made a view for it to make it easier to use.
Answered by Devi
In addition to the stopped sessions, this change of a horse with no name’s answer will provide you the blocking session’s last (or current, if it’s still running) query:
SELECT activity.pid, activity.usename, activity.query, blocking.pid AS blocking_id, blocking.query AS blocking_query FROM pg_stat_activity AS activity JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
This allows you to see which activities are conflicting with one another (even if the block is caused by a prior query), as well as determine the impact of ending one session and how to avoid blocking in the future.
Answered by jpmc26
Postgres has a large system catalog that is accessible via SQL tables. The statistics collector in PG is a subsystem that allows you to collect and publish data about server activities.
You may now use pg stat activity to determine the blocking PIDs.
select pg_blocking_pids(pid) as blocked_by from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;
You can self-join or use it as a where clause in a subquery to get the query that corresponds to the blocking PID.
SELECT query FROM pg_stat_activity WHERE pid IN (select unnest(pg_blocking_pids(pid)) as blocked_by from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0);
You must unnest pg blocking pids(pid) before using it in a WHERE pid IN clause because it returns an Integer.
It can be tedious to look for slow requests at times, so be patient. Good luck with your search.
Answered by Lokesh Devnani
If you’re using a version of Postgresql that doesn’t contain the pg blocking pids function, you can use the following query to discover blocked and blocking queries.
SELECT w.query AS waiting_query, w.pid AS waiting_pid, w.usename AS waiting_user, now() - w.query_start AS waiting_duration, l.query AS locking_query, l.pid AS locking_pid, l.usename AS locking_user, t.schemaname || '.' || t.relname AS tablename, now() - l.query_start AS locking_duration FROM pg_stat_activity w JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted JOIN pg_stat_activity l ON l2.pid = l.pid JOIN pg_stat_user_tables t ON l1.relation = t.relid WHERE w.waiting;
Answered by 52Hertz
Post is based on https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres