LWLock/ProcArray

各版本描述

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

1. 9.6

   LWLockNamed/ProcArrayLock曾用名

   等待获取快照，或在事务结束时清除事务 ID。

   Waiting to get a snapshot or clearing a transaction id at transaction end.

2. 10–12

   LWLock/ProcArrayLock曾用名

   等待获取快照，或在事务结束时清除事务 ID。

   Waiting to get a snapshot or clearing a transaction id at transaction end.

3. 13–20

   LWLock/ProcArray

   等待访问各进程的共享数据结构（通常是为了获取快照或报告会话的事务 ID）。

   Waiting to access the shared per-process data structures (typically, to get a snapshot or report a session's transaction ID).

触发机制

ProcArrayLock 保护用于快照、事务可见性与事务结束处理的共享 PGPROC/PGXACT 数组。获取快照以及更新进程事务状态都必须短暂经过它协调。

ProcArrayLock protects the shared PGPROC/PGXACT arrays used for snapshots, transaction visibility, and transaction end. Snapshot acquisition and updates to process transaction state must briefly coordinate through it.

诊断 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 = 'LWLock'
  AND wait_event = 'ProcArray'
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 = 'LWLock'
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 = 'LWLock'
  AND a.wait_event = 'ProcArray'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

处置步骤

1. 测量活动后端与事务年龄，不要只看总连接数。
2. 找出让可见性边界停滞的长事务与 idle in transaction 会话。
3. 使用连接池、缩短事务，并避免大量微事务同步突发。

相关参数与指标

max_connectionsidle_in_transaction_session_timeout waiting_sessionswait_event_shareactive_backendsoldest_transaction_age

源码证据

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

演化历史

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

1. PostgreSQL 13 ← 12 更名

   更名 ProcArrayLock→ProcArray 描述措辞更新

   等待访问各进程的共享数据结构（通常是为了获取快照或报告会话的事务 ID）。

   描述前后对照

   Waiting to get a snapshot or clearing a transaction id at transaction end. Waiting to access the shared per-process data structures (typically, to get a snapshot or report a session's transaction ID).

2. PostgreSQL 10 ← 9.6 类型变动

   类型 LWLockNamed→LWLock

   等待获取快照，或在事务结束时清除事务 ID。

3. PostgreSQL 9.6 新增此事件

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

   等待获取快照，或在事务结束时清除事务 ID。

同类事件