【PostgreSQL】使用查询语句分析锁队列

/timing on
set statement_timeout to '100ms';

with recursive activity as (
  select
    pg_blocking_pids(pid) blocked_by,
    *,
    age(clock_timestamp(), xact_start)::interval(0) as tx_age,
    -- "pg_locks.waitstart" – PG14+ only; for older versions:  age(clock_timestamp(), state_change) as wait_age
    age(clock_timestamp(), (select max(l.waitstart) from pg_locks l where a.pid = l.pid))::interval(0) as wait_age
  from pg_stat_activity a
  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,
  case when wait_event_type <> 'Lock' then replace(state, 'idle in transaction', 'idletx') else 'waiting' end as state,
  wait_event_type || ':' || wait_event as wait,
  wait_age,
  tx_age,
  to_char(age(backend_xid), 'FM999,999,999,990') as xid_age,
  to_char(2147483647 - age(backend_xmin), 'FM999,999,999,990') as xmin_ttf,
  datname,
  usename,
  (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 || ']', 9, ' '),
    repeat('.', level - 1) || case when level > 1 then ' ' end,
    left(query, 1000)
  ) as query
from tree
order by top_blocker_pid, level, pid

/watch 10

j结果示例：

   pid   |   blocked_by    |  state  |        wait        | wait_age |  tx_age  | xid_age |   xmin_ttf    | datname  | usename  | blkd |                          query
---------+-----------------+---------+--------------------+----------+----------+---------+---------------+----------+----------+------+---------------------------------------------------------
  641449 | {}              | idletx  | Client:ClientRead  |          | 00:01:23 | 4       |               |     test |      nik |    4 |   [641449] update table1 set id = id;
  641586 | {641449}        | waiting | Lock:transactionid | 00:01:12 | 00:01:12 | 3       | 2,147,483,637 |     test |      nik |    3 |   [641586] . delete from table1 ;
  641594 | {641586,641449} | waiting | Lock:relation      | 00:00:53 | 00:00:53 | 2       | 2,147,483,637 |     test |      nik |    2 |   [641594] .. alter table table1 add column data jsonb;
  641588 | {641594}        | waiting | Lock:relation      | 00:00:49 | 00:00:49 |         | 2,147,483,637 |     test |      nik |    0 |   [641588] ... select * from table1 where id = 1;
  641590 | {641594}        | waiting | Lock:relation      | 00:00:45 | 00:00:45 |         | 2,147,483,637 |     test |      nik |    0 |   [641590] ... select * from table1;
  641667 | {}              | idletx  | Client:ClientRead  |          | 00:00:39 | 1       |               |     test |      nik |    1 |   [641667] drop table table2;
  641669 | {641667}        | waiting | Lock:relation      | 00:00:23 | 00:00:23 |         | 2,147,483,637 |     test |      nik |    0 |   [641669] . select * from table2;
(7 rows)

https://postgres.ai/blog/20211018-postgresql-lock-trees