Skip to main content

Useful queries to analyze PostgreSQL lock trees (a.k.a. lock queues)

· 6 min read

Example output of the query for 'lock trees' analysis

For OLTP workloads (such as web and mobile applications), it is important to understand object-level and row-level locks in PostgreSQL. There are several good materials that I can recommend reading:

Database Lab
Database Lab by Postgres.ai

An open-source experimentation platform for PostgreSQL databases. Instantly create full-size clones of your production database and use them to test your database migrations, optimize SQL, or deploy full-size staging apps.

When it comes to the lock monitoring and troubleshooting, you can start with basic queries collected at these PostgreSQL Wiki pages:

Example output of the query for 'lock trees' analysisFor more convenient (hence faster, in many cases) troubleshooting, you might want to use some advanced query, presenting results in a form that allows you to quickly:
  • find the "offending" queries – those that are the "roots" of each blocking tree (a.k.a. "lock queues", "wait queues", or "blocking chains"; in a previous post, we've discussed and demonstrated how queries requiring to acquire locks may organize multiple lock queues, see "Zero-downtime Postgres schema migrations need this: lock_timeout and retries. Problem demonstration"), and
  • decide what to do to fix it – either understand the source of the query (application or human) or just grab the PID and use pg_cancel_backend(..) / pg_terminate_backend(..) to interrupt it and unblock other sessions.

Here are two examples of other people's work that you might find helpful:

  • "Active Session History in PostgreSQL: blocker and wait chain" by Bertrand Drouvot – this post describes the recursive CTE query pg_ash_wait_chain.sql that is useful for those who use the pgsentinel extension. The query is inspired by Tanel Poder's script for Oracle.
  • locktree.sql – query to display a tree of blocking sessions based on the information from pg_locks and pg_stat_activity, by Victor Yegorov.

I've experimented with the latter for some time and eventually started to add some additional bits of information to it – first of all, based on pg_locking_pids(..) introduced in PostgreSQL 9.6. At some point, I rewrote the query from scratch, so consider it as the third option for the "advanced" lock issue troubleshooting.

The function pg_locking_pids(..), per documentation, should not be used often:

Frequent calls to this function could have some impact on database performance, because it needs exclusive access to the lock manager's shared state for a short time.

Still, it is very useful, so I recommend using it as an ad hoc tool, with additional protective measures:

  • do not run it frequently and avoid using it in monitoring,
  • use low values for statement_timeout to minimize the possible impact on other sessions.

It is now time to discuss the query itself. As the first step, let's enabling timing reporting and set a low value for statement_timeout:

\timing onset statement_timeout to '100ms';

And now run the query:

with recursive activity as (  select    pg_blocking_pids(pid) blocked_by,    *,    age(clock_timestamp(), xact_start)::interval(0) as tx_age,    age(clock_timestamp(), state_change)::interval(0) as state_age  from pg_stat_activity  where state is distinct from 'idle'), blockers as (  select    array_agg(distinct c order by c) as pids  from (    select unnest(blocked_by)    from activity  ) as dt(c)), tree as (  select    activity.*,    1 as level,    activity.pid as top_blocker_pid,    array[activity.pid] as path,    array[activity.pid]::int[] as all_blockers_above  from activity, blockers  where    array[pid] <@ blockers.pids    and blocked_by = '{}'::int[]  union all  select    activity.*,    tree.level + 1 as level,    tree.top_blocker_pid,    path || array[activity.pid] as path,    tree.all_blockers_above || array_agg(activity.pid) over () as all_blockers_above  from activity, tree  where    not array[activity.pid] <@ tree.all_blockers_above    and activity.blocked_by <> '{}'::int[]    and activity.blocked_by <@ tree.all_blockers_above)select  pid,  blocked_by,  tx_age,  state_age,  backend_xid as xid,  backend_xmin as xmin,  replace(state, 'idle in transaction', 'idletx') as state,  datname,  usename,  wait_event_type || ':' || wait_event as wait,  (select count(distinct t1.pid) from tree t1 where array[tree.pid] <@ t1.path and t1.pid <> tree.pid) as blkd,  format(    '%s %s%s',    lpad('[' || pid::text || ']', 7, ' '),    repeat('.', level - 1) || case when level > 1 then ' ' end,    left(query, 1000)  ) as queryfrom treeorder by top_blocker_pid, level, pid\watch 10

Note that I've used:

  • a low statement_timeout value, as discussed, and
  • \watch 10 instead of ; – this tells psql to run it in an infinite loop, with 10-second pauses (you can always interrupt it using Ctrl-C).

Here is an example output:

  pid  |  blocked_by   |  tx_age  | state_age | xid | xmin | state  | datname | usename |        wait        | blkd |                        query-------+---------------+----------+-----------+-----+------+--------+---------+---------+--------------------+------+------------------------------------------------------ 46015 | {}            | 00:08:06 | 00:07:58  | 735 |      | idletx | test    | nik     | Client:ClientRead  |    4 | [46015] update table1 set id = id; 46017 | {46015}       | 00:07:55 | 00:07:50  | 736 |  735 | active | test    | nik     | Lock:transactionid |    3 | [46017] . delete from table1 ; 46023 | {46017,46015} | 00:07:47 | 00:07:35  | 737 |  735 | active | test    | nik     | Lock:relation      |    2 | [46023] .. alter table table1 add column data jsonb; 46019 | {46023}       | 00:07:23 | 00:07:23  |     |  735 | active | test    | nik     | Lock:relation      |    0 | [46019] ... select * from table1 where id = 1; 46021 | {46023}       | 00:07:31 | 00:07:31  |     |  735 | active | test    | nik     | Lock:relation      |    0 | [46021] ... select * from table1; 46081 | {}            | 00:06:32 | 00:06:25  | 739 |      | idletx | test    | nik     | Client:ClientRead  |    1 | [46081] drop table table2; 46084 | {46081}       | 00:06:20 | 00:06:20  |     |  735 | active | test    | nik     | Lock:relation      |    0 | [46084] . select * from table2;(7 rows)
Nikolay Samokhvalov
Nikolay Samokhvalov

CEO & Founder of Postgres.ai

Working on tools to balance Dev with Ops in DevOps

Database Lab
Database Lab by Postgres.ai

An open-source experimentation platform for PostgreSQL databases. Instantly create full-size clones of your production database and use them to test your database migrations, optimize SQL, or deploy full-size staging apps.