Skip to content

Instantly share code, notes, and snippets.

@zr40
Last active September 29, 2017 17:25
Show Gist options
  • Save zr40/5ab168f4d6bfa0df9d0b to your computer and use it in GitHub Desktop.
Save zr40/5ab168f4d6bfa0df9d0b to your computer and use it in GitHub Desktop.
Show blocking locks
select
object,
case "lock requested"
when 'AccessExclusiveLock' then 'access exclusive'
when 'ExclusiveLock' then 'exclusive'
when 'ShareRowExclusiveLock' then 'share row exclusive'
when 'ShareLock' then 'share'
when 'ShareUpdateExclusiveLock' then 'share update exclusive'
when 'RowExclusiveLock' then 'row exclusive'
when 'RowShareLock' then 'row share'
when 'AccessShareLock' then 'access share'
else "lock requested"
end "lock requested",
-- only show the most heavy lock type that's been granted
case
when array_agg("lock granted") @> array['AccessExclusiveLock'] then 'access exclusive'
when array_agg("lock granted") @> array['ExclusiveLock'] then 'exclusive'
when array_agg("lock granted") @> array['ShareRowExclusiveLock'] then 'share row exclusive'
when array_agg("lock granted") @> array['ShareLock'] then 'share'
when array_agg("lock granted") @> array['ShareUpdateExclusiveLock'] then 'share update exclusive'
when array_agg("lock granted") @> array['RowExclusiveLock'] then 'row exclusive'
when array_agg("lock granted") @> array['RowShareLock'] then 'row share'
when array_agg("lock granted") @> array['AccessShareLock'] then 'access share'
else array_agg("lock granted")::text
end "lock granted",
"blocked pid",
"blocked query",
"running pid",
"running query",
"xact age",
"query age"
from (
select
case blocked.locktype
when 'relation' then 'relation ' || blocked.relation::regclass
when 'extend' then 'extend ' || blocked.relation::regclass
when 'transactionid' then 'transaction ' || blocked.transactionid
when 'advisory' then 'advisory ' || ((blocked.classid::bigint << 32) | blocked.objid::bigint)
when 'tuple' then 'tuple ' || blocked.relation::regclass || '/' || blocked.page || '/' || blocked.tuple
else blocked.locktype || ' (todo)'
end "object",
blocked.mode "lock requested",
running.mode "lock granted",
blocked.pid "blocked pid",
coalesce(
blocked_query.query,
case when pg_is_in_recovery() then '-- replication'
else '-- SERIALIZABLE transaction'
end
) "blocked query",
running.pid "running pid",
coalesce(
running_query.query,
case when running.pid is not null then
case when pg_is_in_recovery() then '-- replication'
else '-- SERIALIZABLE transaction'
end
end
) "running query",
date_trunc('second', now() - running_query.xact_start) "xact age",
date_trunc('second', now() - running_query.query_start) "query age"
from pg_locks blocked
left join pg_locks running
on running.granted
and blocked.locktype = running.locktype
and running.pid != blocked.pid
and (
-- match locks depending on type
(
blocked.locktype in ('extend', 'relation') and
(blocked.locktype, blocked.database, blocked.relation) =
(running.locktype, running.database, running.relation)
) or (
blocked.locktype = 'advisory' and
(blocked.database, blocked.classid, blocked.objid) =
(running.database, running.classid, running.objid)
) or (
blocked.locktype = 'transactionid' and blocked.transactionid = running.transactionid
) or (
blocked.locktype = 'tuple' and
(blocked.database, blocked.relation, blocked.page, blocked.tuple) =
(running.database, running.relation, running.page, running.tuple)
) or (
-- TODO: unforeseen lock type
blocked.locktype not in ('extend', 'relation', 'advisory', 'transactionid', 'tuple')
)
)
left join pg_stat_activity blocked_query on blocked_query.pid = blocked.pid
left join pg_stat_activity running_query on running_query.pid = running.pid
where not blocked.granted
-- ignore compatible lock combinations
and (blocked.mode, running.mode) not in (
('AccessShareLock', 'AccessShareLock'),
('AccessShareLock', 'RowShareLock'),
('AccessShareLock', 'RowExclusiveLock'),
('AccessShareLock', 'ShareUpdateExclusiveLock'),
('AccessShareLock', 'ShareLock'),
('AccessShareLock', 'ShareRowExclusiveLock'),
('AccessShareLock', 'ExclusiveLock'),
('RowShareLock', 'AccessShareLock'),
('RowShareLock', 'RowShareLock'),
('RowShareLock', 'RowExclusiveLock'),
('RowShareLock', 'ShareUpdateExclusiveLock'),
('RowShareLock', 'ShareLock'),
('RowShareLock', 'ShareRowExclusiveLock'),
('RowExclusiveLock', 'AccessShareLock'),
('RowExclusiveLock', 'RowShareLock'),
('RowExclusiveLock', 'RowExclusiveLock'),
('RowExclusiveLock', 'ShareUpdateExclusiveLock'),
('ShareUpdateExclusiveLock', 'AccessShareLock'),
('ShareUpdateExclusiveLock', 'RowShareLock'),
('ShareUpdateExclusiveLock', 'RowExclusiveLock'),
('ShareLock', 'AccessShareLock'),
('ShareLock', 'RowShareLock'),
('ShareLock', 'ShareLock'),
('ShareRowExclusiveLock', 'AccessShareLock'),
('ShareRowExclusiveLock', 'RowShareLock'),
('ExclusiveLock', 'AccessShareLock')
)
) x
group by "blocked pid", "running pid", object, "lock requested", "blocked query", "running query", "xact age", "query age"
order by "blocked pid", "running pid";
@jdreaver
Copy link

This snippet is great, thanks a lot! We made a slack bot that reports really slow queries on our prod databases using pg_stat_activity, but we couldn't see what was causing some locks. We just threw this snippet in there now and it helps a ton with that bot's usefulness.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment