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.

关键的 tables and functions:

• pg_stat_activity: A table with one entry per server process, showing details of the running query for each.
• pg_locks: Information on current locks held within the database by open transactions, with one row per lockable object.
• pg_blocking_pids(): A function that can find the process IDs (PIDs) of sessions that are blocking the PostgreSQL server process of a supplied PID.
• pg_cancel_backend(): Function that cancels the currently running query by sending a SIGINT to a process ID.
• pg_terminate_backend(): Terminate a backend process completely (the query and usually the connection) on the database (uses SIGTERM instead of SIGINT).

当涉及到锁监控和故障排除时，首先您可以从以下 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)

Finding blocked processes and blocking queries

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
   pid   | usename |        query         | blocking_id |   blocking_query
---------+---------+----------------------+-------------+---------------------
 2381560 | system  | truncate table test; |     2380294 | select * from test;

Find queries running longer than 5 minutes

SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
   pid   |  user  |          query_start          |          query_time           |        query         |        state        | wait_event_type | wait_event
---------+--------+-------------------------------+-------------------------------+----------------------+---------------------+-----------------+------------
 2381560 | system | 2025-04-27 16:00:44.745345+08 | +000000000 00:31:23.826376000 | truncate table test; | idle in transaction | Client          | ClientRead
(1 row)

Сombination of blocked and blocking activity

  SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted;
 blocked_pid | blocked_user | blocking_pid | blocking_user |  blocked_statement  | current_statement_in_blocking_process
-------------+--------------+--------------+---------------+---------------------+---------------------------------------
     2380294 | system       |      2381560 | system        | select * from test; | truncate table test;
     2380294 | system       |      2381560 | system        | select * from test; | truncate table test;
(2 rows)

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process,
         blocked_activity.application_name AS blocked_application,
         blocking_activity.application_name AS blocking_application
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;
 blocked_pid | blocked_user | blocking_pid | blocking_user |  blocked_statement  | current_statement_in_blocking_process | blocked_application | blocking_application
-------------+--------------+--------------+---------------+---------------------+---------------------------------------+---------------------+----------------------
     2380294 | system       |      2381560 | system        | select * from test; | truncate table test;                  | ksql                | ksql
     2380294 | system       |      2381560 | system        | select * from test; | truncate table test;                  | ksql                | ksql
(2 rows)

— enjoy —