Lock/advisory

各版本描述

相邻版本里类型、名称与英文描述都相同的合并成一段，旧的在前。中文取自图谱或本站手册译文，没有译文的只给英文原文。

1. 9.6–20

   Lock/advisory

   等待获得一个建议用户锁。

   Waiting to acquire an advisory user lock.

触发机制

PG_WAIT_LOCK 位于 src/backend/storage/lmgr/proc.c:1487，是经 grep 核验的报告路径。公共身份/资源映射位于 src/backend/utils/activity/wait_event_names.txt:428。探针覆盖的操作是：等待获取用户 advisory lock。锁管理器无法立即授予 advisory 重量级锁；ProcSleep 报告等待，并把后端挂到该锁的等待队列，直到持有者释放或请求被取消。

PG_WAIT_LOCK at src/backend/storage/lmgr/proc.c:1487 is the grep-verified reporting path. The public identity/resource is mapped at src/backend/utils/activity/wait_event_names.txt:428. The instrumented operation is: Waiting to acquire an advisory user lock. The lock manager could not grant the advisory heavyweight lock immediately. ProcSleep reports the lock wait and parks the backend on the lock's wait queue until owners release or the request is cancelled.

诊断 SQL

直接在 psql 里跑；标注的是语句里用到的视图与列所要求的最低版本。

SELECT pid, backend_type, usename, datname, application_name,
       state, now() - query_start AS query_age,
       now() - xact_start AS xact_age,
       wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocking_pids,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND wait_event = 'advisory'
ORDER BY query_age DESC NULLS LAST;

SELECT wait_event, count(*) AS waiting_sessions,
       count(*) FILTER (WHERE state = 'active') AS active_waiters,
       max(now() - query_start) AS oldest_query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
GROUP BY wait_event
ORDER BY waiting_sessions DESC, wait_event;

SELECT a.pid, l.locktype, l.mode, l.granted, l.fastpath,
       d.datname, n.nspname, c.relname,
       l.page, l.tuple, l.virtualxid, l.transactionid,
       l.classid, l.objid, l.objsubid
FROM pg_stat_activity AS a
LEFT JOIN pg_locks AS l ON l.pid = a.pid
LEFT JOIN pg_database AS d ON d.oid = l.database
LEFT JOIN pg_class AS c
  ON c.oid = l.relation
 AND l.database = (
       SELECT oid FROM pg_database WHERE datname = current_database()
     )
LEFT JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE a.wait_event_type = 'Lock'
  AND a.wait_event = 'advisory'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

处置步骤

1. 沿 pg_blocking_pids 构建到根节点的阻塞图。
2. 检查根持有者状态、事务年龄与业务目的。
3. 确认最安全的根动作后，再选择取消、超时或调整负载顺序。

相关参数与指标

lock_timeoutdeadlock_timeout waiting_sessionswait_event_shareblocked_sessionslocks_per_backend

源码证据

图谱在各版本源码树里核对到的位置，新的在前。「触发点」是真正上报等待事件的调用处，「目录定义」只说明这个名字在枚举里存在。

演化历史

相邻两个大版本之间的差异，新的在前。版本号链到该版的变动清单。

1. PostgreSQL 9.6 新增此事件

   9.6 引入等待事件机制，此为本事件的首个版本

   等待获得一个建议用户锁。

同类事件