ANBOB™

Egor Rogov 对 在postgrespro分享PostgreSQL 的工作原理进行了全面而深入的解释，文章非常的优秀，推荐拜读。

1. Relation-level locks .
2. Row-level locks.
3. Locks on other objects and predicate locks.
4. Locks in RAM.

当涉及到锁监控和故障排除时，首先您可以从以下 PostgreSQL Wiki 页面收集的基本查询开始：

• 锁监控
• 锁定依赖项信息

为了更方便进行故障排除，您可能需要使用一些高级查询，以允许您快速执行以下操作的表单显示结果：

• Bertrand Drouvot 的“PostgreSQL 中的活动会话历史,分享了递归 CTE 查询pg_ash_wait_chain.sql，这对使用 pgsentinel 扩展的人很有用，该查询的灵感来自同样是我敬仰的大神Tanel Poder 的 Oracle 脚本。
• Victor Yegorov基于pg_locks 和pg_stat_activity,编写的查询堵塞树脚本locktree.sql

如locktree.sql在kingbase也可以查询
kingbase-#  
     ts_age      |   change_age    | datname | usename | client_addr |  pid  | state  | lvl | blocked |              query
-----------------+-----------------+---------+---------+-------------+-------+--------+-----+---------+---------------------------------
 00:01:22.000000 | 00:01:19.000000 | test    | system  | 127.0.0.1   | 19784 | idletx |   0 |       1 |  select * from t1 for update;
 00:00:21.000000 | 00:00:21.000000 | test    | system  | 127.0.0.1   | 27559 | active |   1 |       0 |  . select * from t1 for update;
(2 rows)

从 PostgreSQL 9.6 开始引入pg_blocking_pids 可以更方便查询，但是不建议频繁查询，和oracle的v$lock一样有额外的锁消耗。Postgresql文档记录:

频繁调用此函数可能会对数据库性能产生一些影响，因为它需要在短时间内对锁管理器的共享状态进行独占访问。

PostgreSQL 14 中添加了另一项改进，在pg_locks中增加了waitstart它显示了进程等待锁定的时间。

Nikolay Samokhvalov增强查询如下

\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


   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)