Coder Perfect

How can I tell which query in Postgres is holding the lock?


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     AS blocked_pid,
     a.usename  AS blocked_user,     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 =
 JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND !=
 JOIN pg_catalog.pg_stat_activity ka ON =
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     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 =
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, 
       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(
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode, 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 =
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND !=
JOIN pg_stat_activity blockinga ON =
  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:

    activity.query, AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON = ANY(pg_blocking_pids(;

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,                            AS waiting_pid,
       w.usename                        AS waiting_user,
       now() - w.query_start            AS waiting_duration,
       l.query                          AS locking_query,                            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 = AND NOT l1.granted
         JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted
         JOIN pg_stat_activity l ON =
         JOIN pg_stat_user_tables t ON l1.relation = t.relid
WHERE w.waiting;

Answered by 52Hertz

Post is based on