Problem
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.
Any ideas?
Asked by Roman
Solution #1
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
Solution #2
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
Solution #3
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
Solution #4
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
Solution #5
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