Lock dependency information

At times it is very useful to see which locks depend upon each other.
== Flat View of Blocking ==
All columns prefixed with waiting_ hold information about the not granted lock, while the colums prefixed with other_ hold information about other locks on the same relation respectively transactionid 
-- For PostgreSQL Version < 9.2
SELECT 
 waiting.locktype AS waiting_locktype,
 waiting.relation::regclass AS waiting_table,
 waiting_stm.current_query AS waiting_query,
 waiting.mode AS waiting_mode,
 waiting.pid AS waiting_pid,
 other.locktype AS other_locktype,
 other.relation::regclass AS other_table,
 other_stm.current_query AS other_query,
 other.mode AS other_mode,
 other.pid AS other_pid,
 other.granted AS other_granted
FROM
 pg_catalog.pg_locks AS waiting
JOIN
 pg_catalog.pg_stat_activity AS waiting_stm
 ON (
 waiting_stm.procpid = waiting.pid
 )
JOIN
 pg_catalog.pg_locks AS other
 ON (
 (
 waiting."database" = other."database"
 AND waiting.relation = other.relation
 )
 OR waiting.transactionid = other.transactionid
 )
JOIN
 pg_catalog.pg_stat_activity AS other_stm
 ON (
 other_stm.procpid = other.pid
 )
WHERE
 NOT waiting.granted
AND
 waiting.pid <> other.pid
-- For PostgreSQL Version >= 9.2
SELECT 
 waiting.locktype AS waiting_locktype,
 waiting.relation::regclass AS waiting_table,
 waiting_stm.query AS waiting_query,
 waiting.mode AS waiting_mode,
 waiting.pid AS waiting_pid,
 other.locktype AS other_locktype,
 other.relation::regclass AS other_table,
 other_stm.query AS other_query,
 other.mode AS other_mode,
 other.pid AS other_pid,
 other.granted AS other_granted
FROM
 pg_catalog.pg_locks AS waiting
JOIN
 pg_catalog.pg_stat_activity AS waiting_stm
 ON (
 waiting_stm.pid = waiting.pid
 )
JOIN
 pg_catalog.pg_locks AS other
 ON (
 (
 waiting."database" = other."database"
 AND waiting.relation = other.relation
 )
 OR waiting.transactionid = other.transactionid
 )
JOIN
 pg_catalog.pg_stat_activity AS other_stm
 ON (
 other_stm.pid = other.pid
 )
WHERE
 NOT waiting.granted
AND
 waiting.pid <> other.pid
It would be useful to add extra columns indicating how long the waiting statement has been blocked.
== Recursive View of Blocking ==
WITH RECURSIVE
 c(requested, current) AS
 ( VALUES
 ('AccessShareLock'::text, 'AccessExclusiveLock'::text),
 ('RowShareLock'::text, 'ExclusiveLock'::text),
 ('RowShareLock'::text, 'AccessExclusiveLock'::text),
 ('RowExclusiveLock'::text, 'ShareLock'::text),
 ('RowExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('RowExclusiveLock'::text, 'ExclusiveLock'::text),
 ('RowExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ShareLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('ShareLock'::text, 'RowExclusiveLock'::text),
 ('ShareLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ShareLock'::text, 'ShareRowExclusiveLock'::text),
 ('ShareLock'::text, 'ExclusiveLock'::text),
 ('ShareLock'::text, 'AccessExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'RowExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'ShareLock'::text),
 ('ShareRowExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'ExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('ExclusiveLock'::text, 'RowShareLock'::text),
 ('ExclusiveLock'::text, 'RowExclusiveLock'::text),
 ('ExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ExclusiveLock'::text, 'ShareLock'::text),
 ('ExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('ExclusiveLock'::text, 'ExclusiveLock'::text),
 ('ExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'AccessShareLock'::text),
 ('AccessExclusiveLock'::text, 'RowShareLock'::text),
 ('AccessExclusiveLock'::text, 'RowExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'ShareLock'::text),
 ('AccessExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'ExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'AccessExclusiveLock'::text)
 ),
 l AS
 (
 SELECT
 (locktype,DATABASE,relation::regclass::text,page,tuple,virtualxid,transactionid,classid,objid,objsubid) AS target,
 virtualtransaction,
 pid,
 mode,
 granted
 FROM pg_catalog.pg_locks
 ),
 t AS
 (
 SELECT
 blocker.target AS blocker_target,
 blocker.pid AS blocker_pid,
 blocker.mode AS blocker_mode,
 blocked.target AS target,
 blocked.pid AS pid,
 blocked.mode AS mode
 FROM l blocker
 JOIN l blocked
 ON ( NOT blocked.granted
 AND blocker.granted
 AND blocked.pid != blocker.pid
 AND blocked.target IS NOT DISTINCT FROM blocker.target)
 JOIN c ON (c.requested = blocked.mode AND c.current = blocker.mode)
 ),
 r AS
 (
 SELECT
 blocker_target,
 blocker_pid,
 blocker_mode,
 '1'::int AS depth,
 target,
 pid,
 mode,
 blocker_pid::text || ',' || pid::text AS seq
 FROM t
 UNION ALL
 SELECT
 blocker.blocker_target,
 blocker.blocker_pid,
 blocker.blocker_mode,
 blocker.depth + 1,
 blocked.target,
 blocked.pid,
 blocked.mode,
 blocker.seq || ',' || blocked.pid::text
 FROM r blocker
 JOIN t blocked
 ON (blocked.blocker_pid = blocker.pid)
 WHERE blocker.depth < 1000
 )
SELECT * FROM r
 ORDER BY seq;
====== TIP ======
* To make it more helpful for real-time monitoring, you could create a view generating a hierarchical report and use the watch command to refresh the report as follows.
* The following view also adds useful information about the sessions in the blocking tree.
CREATE OR REPLACE VIEW blocking_tree AS
WITH RECURSIVE
 lock_composite(requested, current) AS (VALUES
 ('AccessShareLock'::text, 'AccessExclusiveLock'::text),
 ('RowShareLock'::text, 'ExclusiveLock'::text),
 ('RowShareLock'::text, 'AccessExclusiveLock'::text),
 ('RowExclusiveLock'::text, 'ShareLock'::text),
 ('RowExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('RowExclusiveLock'::text, 'ExclusiveLock'::text),
 ('RowExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ShareLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'ExclusiveLock'::text),
 ('ShareUpdateExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('ShareLock'::text, 'RowExclusiveLock'::text),
 ('ShareLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ShareLock'::text, 'ShareRowExclusiveLock'::text),
 ('ShareLock'::text, 'ExclusiveLock'::text),
 ('ShareLock'::text, 'AccessExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'RowExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'ShareLock'::text),
 ('ShareRowExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'ExclusiveLock'::text),
 ('ShareRowExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('ExclusiveLock'::text, 'RowShareLock'::text),
 ('ExclusiveLock'::text, 'RowExclusiveLock'::text),
 ('ExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('ExclusiveLock'::text, 'ShareLock'::text),
 ('ExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('ExclusiveLock'::text, 'ExclusiveLock'::text),
 ('ExclusiveLock'::text, 'AccessExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'AccessShareLock'::text),
 ('AccessExclusiveLock'::text, 'RowShareLock'::text),
 ('AccessExclusiveLock'::text, 'RowExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'ShareLock'::text),
 ('AccessExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'ExclusiveLock'::text),
 ('AccessExclusiveLock'::text, 'AccessExclusiveLock'::text)
 )
, lock AS (
 SELECT pid,
 virtualtransaction,
 granted,
 mode,
 (locktype,
 CASE locktype
 WHEN 'relation' THEN concat_ws(';', 'db:'||datname, 'rel:'||relation::regclass::text)
 WHEN 'extend' THEN concat_ws(';', 'db:'||datname, 'rel:'||relation::regclass::text)
 WHEN 'page' THEN concat_ws(';', 'db:'||datname, 'rel:'||relation::regclass::text, 'page#'||page::text)
 WHEN 'tuple' THEN concat_ws(';', 'db:'||datname, 'rel:'||relation::regclass::text, 'page#'||page::text, 'tuple#'||tuple::text)
 WHEN 'transactionid' THEN transactionid::text
 WHEN 'virtualxid' THEN virtualxid::text
 WHEN 'object' THEN concat_ws(';', 'class:'||classid::regclass::text, 'objid:'||objid, 'col#'||objsubid)
 ELSE concat('db:'||datname)
 END::text) AS target
 FROM pg_catalog.pg_locks
 LEFT JOIN pg_catalog.pg_database ON (pg_database.oid = pg_locks.database)
 )
, waiting_lock AS (
 SELECT
 blocker.pid AS blocker_pid,
 blocked.pid AS pid,
 concat(blocked.mode,blocked.target) AS lock_target
 FROM lock blocker
 JOIN lock blocked
 ON ( NOT blocked.granted
 AND blocker.granted
 AND blocked.pid != blocker.pid
 AND blocked.target IS NOT DISTINCT FROM blocker.target)
 JOIN lock_composite c ON (c.requested = blocked.mode AND c.current = blocker.mode)
 )
, acquired_lock AS (
 WITH waiting AS (
 SELECT lock_target, count(lock_target) AS wait_count FROM waiting_lock GROUP BY lock_target
 )
 SELECT
 pid,
 array_agg(concat(mode,target,' + '||wait_count) ORDER BY wait_count DESC NULLS LAST) AS locks_acquired
 FROM lock
 LEFT JOIN waiting ON waiting.lock_target = concat(mode,target)
 WHERE granted
 GROUP BY pid
 )
, blocking_lock AS (
 SELECT
 ARRAY[date_part('epoch', query_start)::int, pid] AS seq,
 0::int AS depth,
 -1::int AS blocker_pid,
 pid,
 concat('Connect: ',usename,' ',datname,' ',coalesce(host(client_addr)||':'||client_port, 'local')
 , E'\nSQL: ',replace(substr(coalesce(query,'N/A'), 1, 60), E'\n', ' ')
 , E'\nAcquired:\n '
 , array_to_string(locks_acquired[1:5] ||
 CASE WHEN array_upper(locks_acquired,1) > 5
 THEN '... '||(array_upper(locks_acquired,1) - 5)::text||' more ...'
 END,
 E'\n ')
 ) AS lock_info,
 concat(to_char(query_start, CASE WHEN age(query_start) > '24h' THEN 'Day DD Mon' ELSE 'HH24:MI:SS' END),E' started\n'
 ,CASE WHEN wait_event IS NOT NULL THEN 'waiting' ELSE state END,E'\n'
 ,date_trunc('second',age(now(),query_start)),' ago'
 ) AS lock_state
 FROM acquired_lock blocker
 LEFT JOIN pg_stat_activity act USING (pid)
 WHERE EXISTS
 (SELECT 'x' FROM waiting_lock blocked WHERE blocked.blocker_pid = blocker.pid)
 AND NOT EXISTS
 (SELECT 'x' FROM waiting_lock blocked WHERE blocked.pid = blocker.pid)
UNION ALL
 SELECT
 blocker.seq || blocked.pid,
 blocker.depth + 1,
 blocker.pid,
 blocked.pid,
 concat('Connect: ',usename,' ',datname,' ',coalesce(host(client_addr)||':'||client_port, 'local')
 , E'\nSQL: ',replace(substr(coalesce(query,'N/A'), 1, 60), E'\n', ' ')
 , E'\nWaiting: ',blocked.lock_target
 , CASE WHEN locks_acquired IS NOT NULL
 THEN E'\nAcquired:\n ' ||
 array_to_string(locks_acquired[1:5] ||
 CASE WHEN array_upper(locks_acquired,1) > 5
 THEN '... '||(array_upper(locks_acquired,1) - 5)::text||' more ...'
 END,
 E'\n ')
 END
 ) AS lock_info,
 concat(to_char(query_start, CASE WHEN age(query_start) > '24h' THEN 'Day DD Mon' ELSE 'HH24:MI:SS' END),E' started\n'
 ,CASE WHEN wait_event IS NOT NULL THEN 'waiting' ELSE state END,E'\n'
 ,date_trunc('second',age(now(),query_start)),' ago'
 ) AS lock_state
 FROM blocking_lock blocker
 JOIN waiting_lock blocked
 ON (blocked.blocker_pid = blocker.pid)
 LEFT JOIN pg_stat_activity act ON (act.pid = blocked.pid)
 LEFT JOIN acquired_lock acq ON (acq.pid = blocked.pid)
 WHERE blocker.depth < 5
 )
SELECT concat(lpad('=> ', 4*depth, ' '),pid::text) AS "PID"
, lock_info AS "Lock Info"
, lock_state AS "State"
FROM blocking_lock
ORDER BY seq;
Category:Administration
Category:Performance
Category:Monitoring