How to detect query which holds the lock in Postgres

Question

I want to track mutual locks in postgres constantly   I came across Locks Monitoring article and tried to run 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 returns non-empty result set  If I simplify given query to the following 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 which are waiting to acquire a lock  But I cannot manage to change it so that it can return both blocked and blocker queries   Any ideas

User · Answer

From this excellent article on query locks in Postgres, one can get blocked query and blocker query and their information from the following query.

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;

As the query is long but useful, the article author has created a view for it to simplify it's usage.

User · Answer

Since 9 6 this is a lot easier as it introduced the function pg blocking pids   to find the sessions that are blocking another session   So you can 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    gt  0

User · Answer

One thing I find that is often missing from these is an ability to look up row locks   At least on the larger databases I have worked on  row locks are not shown in pg locks  if they were  pg locks would be much  much larger and there isn t a real data type to show the locked row in that view properly    I don t know that there is a simple solution to this but usually what I do is look at the table where the lock is waiting and search for rows where the xmax is less than the transaction id present there   That usually gives me a place to start  but it is a bit hands-on and not automation friendly   Note that shows you uncommitted writes on rows on those tables   Once committed  the rows are not visible in the current snapshot   But for large tables  that is a pain

User · Answer

This modification of a horse with no name s answer will give you the blocking queries in addition to just the blocked sessions   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

User · Answer

Postgres has a very rich system catalog exposed via SQL tables  PG s statistics collector is a subsystem that supports collection and reporting of information about server activity   Now to figure out the blocking PIDs you can simply query pg stat activity   select pg blocking pids pid  as blocked by from pg stat activity where cardinality pg blocking pids pid    gt  0    To  get the query corresponding to the blocking PID  you can self-join or use it as a where clause in a subquery   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    gt  0     Note  Since pg blocking pids pid  returns an Integer    so you need to unnest it before you use it in a WHERE pid IN clause   Hunting for slow queries can be tedious sometimes  so have patience  Happy hunting

[sql] How to detect query which holds the lock in Postgres?

Examples related to sql

Examples related to postgresql

Examples related to locking