PostgreSQL 等待事件大典

• 1: 等待事件排查总图
• 2: 版本矩阵
• 3: 值班术语表
• 4: Lock 等待
  • 4.1: Lock: advisory
  • 4.2: Lock: applytransaction
  • 4.3: Lock: extend
  • 4.4: Lock: frozenid
  • 4.5: Lock: object
  • 4.6: Lock: page
  • 4.7: Lock: relation
  • 4.8: Lock: spectoken
  • 4.9: Lock: transactionid
  • 4.10: Lock: tuple
  • 4.11: Lock: userlock
  • 4.12: Lock: virtualxid
• 5: LWLock 等待
  • 5.1: LWLock: AddinShmemInit
  • 5.2: LWLock: AioUringCompletion
  • 5.3: LWLock: AioWorkerSubmissionQueue
  • 5.4: LWLock: AutoFile
  • 5.5: LWLock: Autovacuum
  • 5.6: LWLock: AutovacuumSchedule
  • 5.7: LWLock: BackgroundWorker
  • 5.8: LWLock: BtreeVacuum
  • 5.9: LWLock: BufferContent
  • 5.10: LWLock: BufferMapping
  • 5.11: LWLock: Checkpoint
  • 5.12: LWLock: CheckpointerComm
  • 5.13: LWLock: CommitTs
  • 5.14: LWLock: CommitTsBuffer
  • 5.15: LWLock: CommitTsSLRU
  • 5.16: LWLock: ControlFile
  • 5.17: LWLock: DSMRegistry
  • 5.18: LWLock: DSMRegistryDSA
  • 5.19: LWLock: DSMRegistryHash
  • 5.20: LWLock: DynamicSharedMemoryControl
  • 5.21: LWLock: InjectionPoint
  • 5.22: LWLock: LockFastPath
  • 5.23: LWLock: LockManager
  • 5.24: LWLock: LogicalRepLauncherDSA
  • 5.25: LWLock: LogicalRepLauncherHash
  • 5.26: LWLock: LogicalRepWorker
  • 5.27: LWLock: MultiXactGen
  • 5.28: LWLock: MultiXactMemberBuffer
  • 5.29: LWLock: MultiXactMemberSLRU
  • 5.30: LWLock: MultiXactOffsetBuffer
  • 5.31: LWLock: MultiXactOffsetSLRU
  • 5.32: LWLock: MultiXactTruncation
  • 5.33: LWLock: NotifyBuffer
  • 5.34: LWLock: NotifyQueue
  • 5.35: LWLock: NotifyQueueTail
  • 5.36: LWLock: NotifySLRU
  • 5.37: LWLock: OidGen
  • 5.38: LWLock: OldSnapshotTimeMap
  • 5.39: LWLock: ParallelAppend
  • 5.40: LWLock: ParallelBtreeScan
  • 5.41: LWLock: ParallelHashJoin
  • 5.42: LWLock: ParallelQueryDSA
  • 5.43: LWLock: ParallelVacuumDSA
  • 5.44: LWLock: PerSessionDSA
  • 5.45: LWLock: PerSessionRecordType
  • 5.46: LWLock: PerSessionRecordTypmod
  • 5.47: LWLock: PerXactPredicateList
  • 5.48: LWLock: PgStatsDSA
  • 5.49: LWLock: PgStatsData
  • 5.50: LWLock: PgStatsHash
  • 5.51: LWLock: PredicateLockManager
  • 5.52: LWLock: ProcArray
  • 5.53: LWLock: RelCacheInit
  • 5.54: LWLock: RelationMapping
  • 5.55: LWLock: ReplicationOrigin
  • 5.56: LWLock: ReplicationOriginState
  • 5.57: LWLock: ReplicationSlotAllocation
  • 5.58: LWLock: ReplicationSlotControl
  • 5.59: LWLock: ReplicationSlotIO
  • 5.60: LWLock: SInvalRead
  • 5.61: LWLock: SInvalWrite
  • 5.62: LWLock: SerialBuffer
  • 5.63: LWLock: SerialControl
  • 5.64: LWLock: SerialSLRU
  • 5.65: LWLock: SerializableFinishedList
  • 5.66: LWLock: SerializablePredicateList
  • 5.67: LWLock: SerializableXactHash
  • 5.68: LWLock: SharedTidBitmap
  • 5.69: LWLock: SharedTupleStore
  • 5.70: LWLock: ShmemIndex
  • 5.71: LWLock: SubtransBuffer
  • 5.72: LWLock: SubtransSLRU
  • 5.73: LWLock: SyncRep
  • 5.74: LWLock: SyncScan
  • 5.75: LWLock: TablespaceCreate
  • 5.76: LWLock: TwoPhaseState
  • 5.77: LWLock: WALBufMapping
  • 5.78: LWLock: WALInsert
  • 5.79: LWLock: WALSummarizer
  • 5.80: LWLock: WALWrite
  • 5.81: LWLock: WaitEventCustom
  • 5.82: LWLock: WrapLimitsVacuum
  • 5.83: LWLock: XactBuffer
  • 5.84: LWLock: XactSLRU
  • 5.85: LWLock: XactTruncation
  • 5.86: LWLock: XidGen
• 6: I/O 等待
  • 6.1: IO: AioIoCompletion
  • 6.2: IO: AioIoUringExecution
  • 6.3: IO: AioIoUringSubmit
  • 6.4: IO: BasebackupRead
  • 6.5: IO: BasebackupSync
  • 6.6: IO: BasebackupWrite
  • 6.7: IO: BuffileRead
  • 6.8: IO: BuffileTruncate
  • 6.9: IO: BuffileWrite
  • 6.10: IO: ControlFileRead
  • 6.11: IO: ControlFileSync
  • 6.12: IO: ControlFileSyncUpdate
  • 6.13: IO: ControlFileWrite
  • 6.14: IO: ControlFileWriteUpdate
  • 6.15: IO: CopyFileCopy
  • 6.16: IO: CopyFileRead
  • 6.17: IO: CopyFileWrite
  • 6.18: IO: DataFileExtend
  • 6.19: IO: DataFileFlush
  • 6.20: IO: DataFileImmediateSync
  • 6.21: IO: DataFilePrefetch
  • 6.22: IO: DataFileRead
  • 6.23: IO: DataFileSync
  • 6.24: IO: DataFileTruncate
  • 6.25: IO: DataFileWrite
  • 6.26: IO: DsmAllocate
  • 6.27: IO: DsmFillZeroWrite
  • 6.28: IO: LockFileAddtodatadirRead
  • 6.29: IO: LockFileAddtodatadirSync
  • 6.30: IO: LockFileAddtodatadirWrite
  • 6.31: IO: LockFileCreateRead
  • 6.32: IO: LockFileCreateSync
  • 6.33: IO: LockFileCreateWrite
  • 6.34: IO: LockFileRecheckdatadirRead
  • 6.35: IO: LogicalChangesRead
  • 6.36: IO: LogicalChangesWrite
  • 6.37: IO: LogicalRewriteCheckpointSync
  • 6.38: IO: LogicalRewriteMappingSync
  • 6.39: IO: LogicalRewriteMappingWrite
  • 6.40: IO: LogicalRewriteSync
  • 6.41: IO: LogicalRewriteTruncate
  • 6.42: IO: LogicalRewriteWrite
  • 6.43: IO: LogicalSubxactRead
  • 6.44: IO: LogicalSubxactWrite
  • 6.45: IO: RelationMapRead
  • 6.46: IO: RelationMapReplace
  • 6.47: IO: RelationMapSync
  • 6.48: IO: RelationMapWrite
  • 6.49: IO: ReorderBufferRead
  • 6.50: IO: ReorderBufferWrite
  • 6.51: IO: ReorderLogicalMappingRead
  • 6.52: IO: ReplicationSlotRead
  • 6.53: IO: ReplicationSlotRestoreSync
  • 6.54: IO: ReplicationSlotSync
  • 6.55: IO: ReplicationSlotWrite
  • 6.56: IO: SlruFlushSync
  • 6.57: IO: SlruRead
  • 6.58: IO: SlruSync
  • 6.59: IO: SlruWrite
  • 6.60: IO: SnapbuildRead
  • 6.61: IO: SnapbuildSync
  • 6.62: IO: SnapbuildWrite
  • 6.63: IO: TimelineHistoryFileSync
  • 6.64: IO: TimelineHistoryFileWrite
  • 6.65: IO: TimelineHistoryRead
  • 6.66: IO: TimelineHistorySync
  • 6.67: IO: TimelineHistoryWrite
  • 6.68: IO: TwophaseFileRead
  • 6.69: IO: TwophaseFileSync
  • 6.70: IO: TwophaseFileWrite
  • 6.71: IO: VersionFileSync
  • 6.72: IO: VersionFileWrite
  • 6.73: IO: WalBootstrapSync
  • 6.74: IO: WalBootstrapWrite
  • 6.75: IO: WalCopyRead
  • 6.76: IO: WalCopySync
  • 6.77: IO: WalCopyWrite
  • 6.78: IO: WalInitSync
  • 6.79: IO: WalInitWrite
  • 6.80: IO: WalRead
  • 6.81: IO: WalSummaryRead
  • 6.82: IO: WalSummaryWrite
  • 6.83: IO: WalSync
  • 6.84: IO: WalSyncMethodAssign
  • 6.85: IO: WalWrite
  • 6.86: IO: WalsenderTimelineHistoryRead
• 7: IPC 等待
  • 7.1: IPC: AppendReady
  • 7.2: IPC: ArchiveCleanupCommand
  • 7.3: IPC: ArchiveCommand
  • 7.4: IPC: BackendTermination
  • 7.5: IPC: BackupWaitWalArchive
  • 7.6: IPC: BgworkerShutdown
  • 7.7: IPC: BgworkerStartup
  • 7.8: IPC: BtreePage
  • 7.9: IPC: BufferIo
  • 7.10: IPC: CheckpointDelayComplete
  • 7.11: IPC: CheckpointDelayStart
  • 7.12: IPC: CheckpointDone
  • 7.13: IPC: CheckpointStart
  • 7.14: IPC: ExecuteGather
  • 7.15: IPC: HashBatchAllocate
  • 7.16: IPC: HashBatchElect
  • 7.17: IPC: HashBatchLoad
  • 7.18: IPC: HashBuildAllocate
  • 7.19: IPC: HashBuildElect
  • 7.20: IPC: HashBuildHashInner
  • 7.21: IPC: HashBuildHashOuter
  • 7.22: IPC: HashGrowBatchesDecide
  • 7.23: IPC: HashGrowBatchesElect
  • 7.24: IPC: HashGrowBatchesFinish
  • 7.25: IPC: HashGrowBatchesReallocate
  • 7.26: IPC: HashGrowBatchesRepartition
  • 7.27: IPC: HashGrowBucketsElect
  • 7.28: IPC: HashGrowBucketsReallocate
  • 7.29: IPC: HashGrowBucketsReinsert
  • 7.30: IPC: LogicalApplySendData
  • 7.31: IPC: LogicalParallelApplyStateChange
  • 7.32: IPC: LogicalSyncData
  • 7.33: IPC: LogicalSyncStateChange
  • 7.34: IPC: MessageQueueInternal
  • 7.35: IPC: MessageQueuePutMessage
  • 7.36: IPC: MessageQueueReceive
  • 7.37: IPC: MessageQueueSend
  • 7.38: IPC: MultixactCreation
  • 7.39: IPC: ParallelBitmapScan
  • 7.40: IPC: ParallelCreateIndexScan
  • 7.41: IPC: ParallelFinish
  • 7.42: IPC: ProcSignalBarrier
  • 7.43: IPC: ProcarrayGroupUpdate
  • 7.44: IPC: Promote
  • 7.45: IPC: RecoveryConflictSnapshot
  • 7.46: IPC: RecoveryConflictTablespace
  • 7.47: IPC: RecoveryEndCommand
  • 7.48: IPC: RecoveryPause
  • 7.49: IPC: ReplicationOriginDrop
  • 7.50: IPC: ReplicationSlotDrop
  • 7.51: IPC: RestoreCommand
  • 7.52: IPC: SafeSnapshot
  • 7.53: IPC: SyncRep
  • 7.54: IPC: WalReceiverExit
  • 7.55: IPC: WalReceiverUpstreamCatchup
  • 7.56: IPC: WalReceiverWaitStart
  • 7.57: IPC: WalSummaryReady
  • 7.58: IPC: XactGroupUpdate
• 8: Client 等待
  • 8.1: Client: ClientRead
  • 8.2: Client: ClientWrite
  • 8.3: Client: GssOpenServer
  • 8.4: Client: LibpqwalreceiverConnect
  • 8.5: Client: LibpqwalreceiverReceive
  • 8.6: Client: SslOpenServer
  • 8.7: Client: WaitForStandbyConfirmation
  • 8.8: Client: WalSenderWaitForWal
  • 8.9: Client: WalSenderWriteData
• 9: Activity 等待
  • 9.1: Activity: ArchiverMain
  • 9.2: Activity: AutovacuumMain
  • 9.3: Activity: BgwriterHibernate
  • 9.4: Activity: BgwriterMain
  • 9.5: Activity: CheckpointerMain
  • 9.6: Activity: CheckpointerShutdown
  • 9.7: Activity: IoWorkerMain
  • 9.8: Activity: LogicalApplyMain
  • 9.9: Activity: LogicalLauncherMain
  • 9.10: Activity: LogicalParallelApplyMain
  • 9.11: Activity: PgStatMain
  • 9.12: Activity: RecoveryWalStream
  • 9.13: Activity: ReplicationSlotsyncMain
  • 9.14: Activity: ReplicationSlotsyncShutdown
  • 9.15: Activity: SysloggerMain
  • 9.16: Activity: WalReceiverMain
  • 9.17: Activity: WalSenderMain
  • 9.18: Activity: WalSummarizerWal
  • 9.19: Activity: WalWriterMain
• 10: Timeout 等待
  • 10.1: Timeout: BaseBackupThrottle
  • 10.2: Timeout: CheckpointWriteDelay
  • 10.3: Timeout: PgSleep
  • 10.4: Timeout: RecoveryApplyDelay
  • 10.5: Timeout: RecoveryRetrieveRetryInterval
  • 10.6: Timeout: RegisterSyncRequest
  • 10.7: Timeout: SpinDelay
  • 10.8: Timeout: VacuumDelay
  • 10.9: Timeout: VacuumTruncate
  • 10.10: Timeout: WalSummarizerError
• 11: BufferPin 等待
  • 11.1: BufferPin: BufferPin
• 12: 扩展等待事件
• 13: 关联 GUC
  • 13.1: [[guc:autovacuum_freeze_max_age]]
  • 13.2: [[guc:autovacuum_multixact_freeze_max_age]]
  • 13.3: [[guc:autovacuum_naptime]]
  • 13.4: [[guc:checkpoint_timeout]]
  • 13.5: [[guc:deadlock_timeout]]
  • 13.6: [[guc:full_page_writes]]
  • 13.7: [[guc:idle_in_transaction_session_timeout]]
  • 13.8: [[guc:lock_timeout]]
  • 13.9: [[guc:max_connections]]
  • 13.10: [[guc:max_locks_per_transaction]]
  • 13.11: [[guc:max_parallel_workers_per_gather]]
  • 13.12: [[guc:max_wal_size]]
  • 13.13: [[guc:shared_buffers]]
  • 13.14: [[guc:synchronous_commit]]
  • 13.15: [[guc:synchronous_standby_names]]
  • 13.16: [[guc:vacuum_freeze_table_age]]
  • 13.17: [[guc:vacuum_multixact_freeze_table_age]]
  • 13.18: [[guc:wal_buffers]]
  • 13.19: [[guc:wal_sender_timeout]]
  • 13.20: [[guc:wal_writer_delay]]
  • 13.21: [[guc:wal_writer_flush_after]]
• 14: 关联指标
  • 14.1: [[metric:active_backends]]
  • 14.2: [[metric:blocked_sessions]]
  • 14.3: [[metric:blocks_read]]
  • 14.4: [[metric:buffer_hit_ratio]]
  • 14.5: [[metric:checkpoint_time]]
  • 14.6: [[metric:commit_latency]]
  • 14.7: [[metric:io_read_time]]
  • 14.8: [[metric:io_write_time]]
  • 14.9: [[metric:locks_per_backend]]
  • 14.10: [[metric:multixact_member_io]]
  • 14.11: [[metric:oldest_multixact_age]]
  • 14.12: [[metric:oldest_transaction_age]]
  • 14.13: [[metric:replication_flush_lag]]
  • 14.14: [[metric:statement_latency]]
  • 14.15: [[metric:statement_wal_bytes]]
  • 14.16: [[metric:wait_event_share]]
  • 14.17: [[metric:waiting_sessions]]
  • 14.18: [[metric:wal_bytes]]
  • 14.19: [[metric:wal_fpi]]
  • 14.20: [[metric:wal_sync_time]]
  • 14.21: [[metric:wal_write_time]]
  • 14.22: [[metric:xact_slru_io]]

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 IS NOT NULL
ORDER BY query_age DESC NULLS LAST;

1 - 等待事件排查总图

这张图刻意保守：先保全证据，再区分预期空闲与真正停滞，最后进入各类别的专项排查。

flowchart TD
  A[采集 pg_stat_activity] --> B{wait_event 为空？}
  B -- 是 --> C[后端可能在用 CPU 或处于探针之间]
  B -- 否 --> D{Activity 或 Client？}
  D -- 是 --> E{空闲状态或预期的后台主循环？}
  E -- 是 --> F[通常正常；检查未结束事务与连接规模]
  E -- 否 --> G[检查客户端背压、网络与应用责任方]
  D -- 否 --> H{Lock 或 BufferPin？}
  H -- 是 --> I[构建阻塞链，定位根阻塞者]
  H -- 否 --> J{IO？}
  J -- 是 --> K[关联 pg_stat_io、文件系统延迟与负载阶段]
  J -- 否 --> L{LWLock？}
  L -- 是 --> M[重复采样，确认单个发热的内部资源]
  L -- 否 --> N[检查 IPC 对端或 Timeout 策略]
  I --> O[选择影响最小且有边界的动作]
  K --> O
  M --> O
  N --> O

最小证据包

• 两张或更多带时间戳的快照。
• pid、backend_type、state、查询时长、事务时长、等待类型和名称。
• 每个等待后端的 pg_blocking_pids(pid)。
• 精确的 PostgreSQL 大版本与小版本。
• 负载标记：发布、批处理、检查点、清理、备份、DDL 或故障切换。

2 - 版本矩阵

版本矩阵对账两条事实路径：PostgreSQL 13–16 官方监控章节表格，以及在 PostgreSQL 17–18 Docker 实例上实查得到的 pg_wait_events 行。

并集包含 327 个精确的 (type, name) 身份。其中 45 个历史身份归并为 282 个语义档案：281 个核心事件条目，加 1 个 Extension 机制占位记录。映射包括 PG14 两次类型迁移、PG16 两个经核验的 Parallel Hash 改名，以及 PostgreSQL 在 PG17 进行的等待名称拼写规范化。

下载

• 完整 CSV 矩阵
• 结构化 JSON 矩阵
• 含分类计数与来源哈希的清单 manifest
• 富化后的大典 JSONL

CSV 会把新旧名称保留为独立行，并提供 canonical 列。事件页面通过显式 alias 合并阅读，但不会抹掉它们真实的版本范围。

改名判定策略

只有证据确定时才建立映射：相邻版本替代项在忽略大小写并移除非字母数字后完全相同，或源码/文档能够建立经核验的改名关系。描述相似本身不足以判定；PgStatMain 等移除项会保留为历史事件，而不是猜测别名。

完整身份矩阵

3 - 值班术语表

本站阈值是运维默认值。PostgreSQL 并不保证“10%”或“五秒”在所有场景都坏；必须结合本集群基线与服务目标。

4 - Lock 等待

Lock 是最容易直接处置的一类：后端向锁管理器申请重量级锁，但不兼容的持有者尚未释放。与 LWLock 不同，这些等待通常在 pg_locks 中有对应行，pg_blocking_pids() 也能给出有效答案。

看阻塞图，不要只数受害者

二十个等待者可能都只是一个空闲事务的症状。先走到阻塞图最前端，再判断持有者是在做有效工作、已被遗弃，还是处于计划内 DDL/发布窗口。

• 正常： 普通写入或计划内 DDL 期间，亚秒级的锁交接。
• 关注： 任一面向用户的等待超过其延迟目标，或至少 10% 的活动会话等待同一个根阻塞者。
• 紧急： 根持有者是 idle in transaction、阻塞链持续增长、关键 DDL 阻塞流量，或死锁开始增加。

值得先认出的事件

阻塞者查询

SELECT w.pid AS waiting_pid, b.pid AS blocking_pid,
       w.wait_event, now() - w.query_start AS waiting_for,
       now() - b.xact_start AS blocker_xact_age,
       b.state AS blocker_state,
       left(w.query, 100) AS waiting_query,
       left(b.query, 100) AS blocking_query
FROM pg_stat_activity AS w
CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS x(pid)
JOIN pg_stat_activity AS b ON b.pid = x.pid
ORDER BY waiting_for DESC;

常见误读

• 被列为阻塞者的会话并不一定可以安全终止；它可能是维护业务不变量的唯一写入者。
• transactionid 不代表事务 ID 即将耗尽。
• relation 本身不说明具体关系；需要把 pg_locks.relation 关联到 pg_class。
• 增大 lock_timeout 只改变受害者等多久，并不会移除阻塞者。

4.1 - Lock: advisory

官方描述译文

等待获取用户 advisory 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 报告等待，并把后端挂到该锁的等待队列，直到持有者释放或请求被取消。

正常还是麻烦？

• 正常: 普通写入或计划内 DDL 的亚秒级锁交接可能正常。
• 需要调查: 面向用户的等待超过延迟目标、阻塞链持续增长，或根持有者处于 idle in transaction 时，应当调查。

诊断 SQL

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. 确认最安全的根动作后，再选择取消、超时或调整负载顺序。

源码证据

• PostgreSQL 18.6 · src/backend/storage/lmgr/proc.c:1487 — PG_WAIT_LOCK
• PostgreSQL 18.6 · src/backend/utils/activity/wait_event_names.txt:428 — advisory
• PostgreSQL 18.6 · src/backend/utils/adt/lockfuncs.c:39 — advisory

关联控制项与信号

• GUC: [guc:lock_timeout] · [guc:deadlock_timeout]
• 指标: [metric:waiting_sessions] · [metric:wait_event_share] · [metric:blocked_sessions] · [metric:locks_per_backend]

4.2 - Lock: applytransaction

官方描述译文

等待获取逻辑复制订阅端正在应用的远程事务上的锁。

触发机制

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

正常还是麻烦？

• 正常: 普通写入或计划内 DDL 的亚秒级锁交接可能正常。
• 需要调查: 面向用户的等待超过延迟目标、阻塞链持续增长，或根持有者处于 idle in transaction 时，应当调查。

诊断 SQL

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 = 'applytransaction'
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 = 'applytransaction'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

处置建议

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

源码证据

• PostgreSQL 18.6 · src/backend/storage/lmgr/proc.c:1487 — PG_WAIT_LOCK
• PostgreSQL 18.6 · src/backend/utils/activity/wait_event_names.txt:429 — applytransaction
• PostgreSQL 18.6 · src/backend/utils/adt/lockfuncs.c:40 — applytransaction

关联控制项与信号

• GUC: [guc:lock_timeout] · [guc:deadlock_timeout]
• 指标: [metric:waiting_sessions] · [metric:wait_event_share] · [metric:blocked_sessions] · [metric:locks_per_backend]

4.3 - Lock: extend

官方描述译文

等待扩展 relation。

触发机制

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

正常还是麻烦？

• 正常: 普通写入或计划内 DDL 的亚秒级锁交接可能正常。
• 需要调查: 面向用户的等待超过延迟目标、阻塞链持续增长，或根持有者处于 idle in transaction 时，应当调查。

诊断 SQL

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 = 'extend'
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 = 'extend'
ORDER BY a.pid, l.granted, l.locktype, l.mode;

处置建议

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

源码证据

• PostgreSQL 18.6 · src/backend/storage/lmgr/proc.c:1487 — PG_WAIT_LOCK
• PostgreSQL 18.6 · src/backend/utils/activity/wait_event_names.txt:419 — extend
• PostgreSQL 18.6 · src/backend/utils/adt/lockfuncs.c:30 — extend

关联控制项与信号

• GUC: [guc:lock_timeout] · [guc:deadlock_timeout]
• 指标: [metric:waiting_sessions] · [metric:wait_event_share] · [metric:blocked_sessions] · [metric:locks_per_backend]

4.4 - Lock: frozenid

官方描述译文

等待更新 pg_database.datfrozenxid 和 pg_database.datminmxid。

触发机制

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

正常还是麻烦？

• 正常: 普通写入或计划内 DDL 的亚秒级锁交接可能正常。
• 需要调查: 面向用户的等待超过延迟目标、阻塞链持续增长，或根持有者处于 idle in transaction 时，应当调查。

诊断 SQL

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 = 'frozenid'
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 = 'frozenid'
ORDER BY a.pid, l.granted, l.locktype, l.mode;