PostgreSQL 锁等待与长事务诊断 100 条命令

DBA100

100 条命令系列文章专栏

PostgreSQL 锁等待与长事务诊断 100 条命令

前言

PostgreSQL 业务卡住时，慢 SQL 不一定真的在跑。它可能正在等表锁，也可能在等另一笔事务释放行锁；后者在 pg_locks 里常表现为等待 transactionid，不一定有一条直观的“行锁记录”。

这篇先找正在等待的会话，再追是谁挡住它、对方事务开了多久。定位阻塞源以后还要核对业务影响，不能只因为某个 PID 在链头就直接杀连接。值班遇到锁问题时建议收藏，也可以分享给开发同事一起对照事务边界。

PostgreSQL 锁等待与阻塞链示意 PostgreSQL 锁等待与阻塞链示意

示例使用 PostgreSQL 17。查看其他用户的 SQL 文本需要相应统计权限；终止会话和修改超时参数会影响业务，本篇先从只读诊断写起。

找到正在等待的会话

1. 查看当前活跃会话的等待事件

SQL

1SELECT pid, datname, usename, state,
2       wait_event_type, wait_event, query_start,
3       left(query, 160) AS query
4FROM pg_stat_activity
5WHERE state = 'active'
6ORDER BY query_start NULLS LAST;

state='active' 表示查询正在执行，也可能正在等待。先看 wait_event_type，只有 Lock 才按锁链继续追；IO 和 Client 要走各自的排查路径。

2. 查看目前未拿到的锁

SQL

1SELECT pid, locktype, mode, database, relation,
2       transactionid, virtualxid, waitstart
3FROM pg_locks
4WHERE NOT granted
5ORDER BY waitstart NULLS LAST;

granted=false 是等待锁。waitstart 在刚开始等待时可能还是 NULL；行锁冲突常显示为等待对方的 transactionid，不要以为没看到 tuple 就没有行锁问题。

3. 直接查某个会话被谁阻塞

SQL

1SELECT pg_blocking_pids(12345) AS blocking_pids;

把 12345 换成等待会话的 PID。该函数同时考虑持锁者和队列中排在前面的等待者，比自己对 pg_locks 做简单自连接可靠；返回空数组说明当下没有锁阻塞。

4. 列出所有当前锁等待及直接阻塞者

SQL

1SELECT a.pid AS waiting_pid,
2       pg_blocking_pids(a.pid) AS blocking_pids,
3       a.wait_event, a.query_start,
4       left(a.query, 120) AS waiting_query
5FROM pg_stat_activity AS a
6WHERE a.wait_event_type = 'Lock'
7ORDER BY a.query_start NULLS LAST;

结果是“这一刻”的锁链，下一秒可能已经释放。pg_blocking_pids 也可能返回重复 PID；若由 prepared transaction 持锁，会出现 0，后面要另查两阶段事务。

5. 把等待会话与阻塞会话放在同一行

SQL

1SELECT w.pid AS waiting_pid, b.pid AS blocking_pid,
2       w.wait_event, w.query_start AS wait_query_start,
3       b.state AS blocker_state, b.xact_start AS blocker_xact_start,
4       left(w.query, 100) AS waiting_query,
5       left(b.query, 100) AS blocker_last_query
6FROM pg_stat_activity AS w
7CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS p(pid)
8LEFT JOIN pg_stat_activity AS b ON b.pid = p.pid
9WHERE w.wait_event_type = 'Lock'
10ORDER BY w.query_start NULLS LAST;

阻塞者不一定还在执行 SQL：它可能 idle in transaction，b.query 此时是最后执行过的语句。blocking_pid=0 代表 prepared transaction，不能靠 pg_stat_activity 找到会话。

查阻塞者的事务

6. 按事务开始时间查看当前会话

SQL

1SELECT pid, usename, application_name, client_addr,
2       state, xact_start, now() - xact_start AS xact_age,
3       backend_xid, backend_xmin
4FROM pg_stat_activity
5WHERE xact_start IS NOT NULL
6ORDER BY xact_start
7LIMIT 30;

连接开得久不等于事务开得久。这里用 xact_start 找长事务，再结合第 5 条确认它是否真的在挡当前业务。

7. 专门查 idle in transaction 会话

SQL

1SELECT pid, usename, application_name, client_addr,
2       xact_start, state_change,
3       now() - xact_start AS xact_age,
4       left(query, 160) AS last_query
5FROM pg_stat_activity
6WHERE state LIKE 'idle in transaction%'
7ORDER BY xact_start;

这类会话虽然当前没执行语句，事务仍未结束，锁和旧快照可能继续保留。先找到对应应用和事务调用链，确认为何没有提交或回滚。

8. 查看当前数据库的关系锁

SQL

1SELECT l.pid, l.relation::regclass AS relation_name,
2       l.mode, l.granted, l.waitstart
3FROM pg_locks AS l
4WHERE l.locktype = 'relation'
5  AND l.database = (SELECT oid FROM pg_database
6                    WHERE datname = current_database())
7ORDER BY l.relation, l.granted, l.pid;

pg_locks 覆盖整个集群，但 relation::regclass 应只对当前数据库的关系 OID 解释。若出现等待 AccessExclusiveLock，要查是不是 DDL、VACUUM FULL 或其他需要排他锁的操作。

9. 查看 transactionid 锁等待

SQL

1SELECT pid, transactionid, mode, granted, waitstart
2FROM pg_locks
3WHERE locktype = 'transactionid'
4  AND NOT granted
5ORDER BY waitstart NULLS LAST;

行更新冲突经常落在这里。它显示当前等待哪个事务结束，不直接显示被锁行的表名；要把 PID、SQL 和第 3 条的阻塞者一起对上。

10. 查看锁等待和事务超时参数

SQL

1SELECT name, setting, unit, source
2FROM pg_settings
3WHERE name IN ('lock_timeout', 'statement_timeout',
4               'idle_in_transaction_session_timeout',
5               'deadlock_timeout', 'log_lock_waits')
6ORDER BY name;

超时参数决定等待多久报错，以及是否留下锁等待日志。参数有全局、数据库、角色和会话层级，排查某条连接时还要看那条会话的实际设置。

沿等待链继续追

11. 按实际等锁时间排序

SQL

1SELECT l.pid, l.locktype, l.mode, l.waitstart,
2       now() - l.waitstart AS wait_age,
3       a.usename, a.application_name
4FROM pg_locks AS l
5LEFT JOIN pg_stat_activity AS a ON a.pid = l.pid
6WHERE NOT l.granted
7ORDER BY l.waitstart NULLS LAST;

query_start 是语句开始时间，不一定等于开始等锁的时间。这里看 waitstart；刚开始等待时它可能暂时为空，不能把空值当成无等待。

12. 查看直接阻塞者是否也在等锁

SQL

1SELECT w.pid AS waiting_pid, p.pid AS blocker_pid,
2       b.wait_event_type AS blocker_wait_type,
3       b.wait_event AS blocker_wait_event,
4       pg_blocking_pids(p.pid) AS blocker_blocked_by
5FROM pg_stat_activity AS w
6CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS p(pid)
7LEFT JOIN pg_stat_activity AS b ON b.pid = p.pid
8WHERE w.wait_event_type = 'Lock'
9  AND p.pid <> 0
10ORDER BY w.pid, p.pid;

阻塞你的会话也可能排在另一条等待队列中。blocker_blocked_by 非空就继续向上追；这里排除了代表 prepared transaction 的 PID 0，它要另查。

13. 找出当前链条中的候选链头

SQL

1WITH edges AS (
2  SELECT w.pid AS waiting_pid, p.pid AS blocker_pid
3  FROM pg_stat_activity AS w
4  CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS p(pid)
5  WHERE w.wait_event_type = 'Lock'
6)
7SELECT e.blocker_pid, count(*) AS directly_blocked
8FROM edges AS e
9WHERE e.blocker_pid <> 0
10  AND NOT EXISTS (SELECT 1 FROM edges AS x
11                  WHERE x.waiting_pid = e.blocker_pid)
12GROUP BY e.blocker_pid
13ORDER BY directly_blocked DESC, e.blocker_pid;

“候选链头”只说明当前没被另一条锁等待挡住。它可能同时承担关键业务事务，不能据此直接终止；先回查会话、事务开始时间和应用来源。

14. 查看候选链头当前状态和客户端

SQL

1SELECT pid, datname, usename, application_name, client_addr,
2       state, xact_start, state_change,
3       left(query, 200) AS last_query
4FROM pg_stat_activity
5WHERE pid = 12345;

把 12345 换成第 13 条的 PID。state_change 可以帮助判断会话什么时候转入空闲；last_query 是最后一条 SQL，不保证就是最初拿锁的语句。

把锁对象对上业务操作

15. 查某张表正在持有和等待的关系锁

SQL

1SELECT pid, mode, granted, waitstart
2FROM pg_locks
3WHERE locktype = 'relation'
4  AND relation = 'public.orders'::regclass
5  AND database = (SELECT oid FROM pg_database
6                  WHERE datname = current_database())
7ORDER BY granted, waitstart NULLS LAST, pid;

关系锁只解释表级访问。普通 SELECT 会拿 AccessShareLock，它通常只会被 AccessExclusiveLock 阻塞；看到 RowExclusiveLock 不等于它已经把所有读请求挡住。

16. 查等待 ACCESS EXCLUSIVE 的 DDL 会话

SQL

1SELECT l.pid, l.relation::regclass AS relation_name,
2       l.waitstart, a.usename, a.query_start,
3       left(a.query, 200) AS query
4FROM pg_locks AS l
5JOIN pg_stat_activity AS a ON a.pid = l.pid
6WHERE l.locktype = 'relation'
7  AND l.mode = 'AccessExclusiveLock'
8  AND NOT l.granted
9  AND l.database = (SELECT oid FROM pg_database
10                    WHERE datname = current_database())
11ORDER BY l.waitstart NULLS LAST;

TRUNCATE、VACUUM FULL 和不少 DDL 需要这类锁。它排在等待队列里时，后续请求也可能被它软阻塞；要结合第 4 条确认真实队列关系。

17. 查看虚拟事务 ID 的锁等待

SQL

1SELECT pid, virtualxid, mode, granted, waitstart
2FROM pg_locks
3WHERE locktype = 'virtualxid'
4  AND NOT granted
5ORDER BY waitstart NULLS LAST;

有些操作必须等其他事务结束，即便对方还没分配永久 XID。出现 virtualxid 等待时用 pg_blocking_pids 找具体会话，不要把虚拟 ID 当成表 OID。

18. 查看尚未结束的两阶段事务

SQL

1SELECT gid, "transaction" AS xid, prepared, owner, database
2FROM pg_prepared_xacts
3ORDER BY prepared;

prepared transaction 已经离开原会话，但拿到的锁还在。若第 4 条里阻塞 PID 为 0，先检查这张视图；提交还是回滚要由事务协调方决定。

19. 把 prepared transaction 的锁列出来

SQL

1SELECT p.gid, p.prepared, l.locktype, l.mode,
2       l.database, l.relation, l.granted
3FROM pg_locks AS l
4JOIN pg_prepared_xacts AS p
5  ON l.virtualtransaction = '-1/' || p."transaction"
6WHERE l.pid IS NULL
7ORDER BY p.prepared, p.gid, l.locktype;

官方文档给出了 virtualtransaction 的连接方式。这里的 relation 是 OID，跨数据库时不能直接在当前库强制转成 regclass；先核对该两阶段事务属于哪个数据库。

20. 查看当前库的 advisory lock

SQL

1SELECT pid, classid, objid, objsubid, mode, granted, waitstart
2FROM pg_locks
3WHERE locktype = 'advisory'
4  AND database = (SELECT oid FROM pg_database
5                  WHERE datname = current_database())
6ORDER BY granted, waitstart NULLS LAST;

advisory lock 的键由应用定义，不能从 classid/objid 猜出业务对象。先找使用该键的代码和持锁会话；事务级与会话级 advisory lock 的释放时机也不同。

看阻塞范围和留下的证据

21. 统计当前有哪些锁请求还没获准

SQL

1SELECT locktype, mode, count(*) AS waiting_count
2FROM pg_locks
3WHERE NOT granted
4GROUP BY locktype, mode
5ORDER BY waiting_count DESC, locktype, mode;

高峰时先看等待主要落在 relation、transactionid 还是 advisory。这是当前瞬间的请求数，不是过去一小时的等待次数；排查时间点不对，结果可能完全为空。

22. 列出等锁会话和它的直接阻塞者

SQL

1SELECT a.pid, a.datname, a.usename, a.state,
2       a.wait_event, a.query_start,
3       pg_blocking_pids(a.pid) AS blocking_pids,
4       left(a.query, 300) AS query_text
5FROM pg_stat_activity AS a
6WHERE a.wait_event_type = 'Lock'
7ORDER BY a.query_start NULLS LAST;

先确认等待的 SQL 和直接阻塞 PID。pg_blocking_pids 能处理队列中的软阻塞，比简单地用 pg_locks 自连接更可靠；它给的是这一刻的关系，重复采样才能判断阻塞是否持续。

23. 查看指定会话拿着什么锁、等着什么锁

SQL

1SELECT locktype, database, relation, page, tuple,
2       transactionid, virtualxid, mode,
3       granted, waitstart
4FROM pg_locks
5WHERE pid = 12345
6ORDER BY granted, locktype, mode;

把 PID 换成第 22 条看到的会话。一个事务可能持有多种锁；granted=false 才是它当前等待的请求。relation 是 OID，跨数据库不要在当前库直接解释为自己的表名。

24. 查看指定表的等待队列与持锁者

SQL

1SELECT l.pid, l.mode, l.granted, l.waitstart,
2       a.state, a.xact_start,
3       left(a.query, 160) AS query_text
4FROM pg_locks AS l
5LEFT JOIN pg_stat_activity AS a ON a.pid = l.pid
6WHERE l.locktype = 'relation'
7  AND l.relation = 'public.orders'::regclass
8  AND l.database = (SELECT oid FROM pg_database
9                    WHERE datname = current_database())
10ORDER BY l.granted, l.waitstart NULLS LAST;

同一张表上有多个锁不代表它们互相冲突，冲突要按锁模式矩阵判断。持锁者当前显示的 SQL 也可能已经执行完，只是事务尚未结束；优先看 xact_start 和应用事务边界。

25. 查页面或元组锁的显式等待记录

SQL

1SELECT pid, locktype,
2       relation::regclass AS relation_name,
3       page, tuple, mode, waitstart
4FROM pg_locks
5WHERE locktype IN ('page', 'tuple')
6  AND NOT granted
7  AND database = (SELECT oid FROM pg_database
8                  WHERE datname = current_database())
9ORDER BY waitstart NULLS LAST;

这类记录出现时可以定位对象与页/元组编号，但大多数行级冲突不会直接显示为一条稳定的 tuple 锁等待，常见的是第 9 条 transactionid 等待。查不到这里不能排除行锁问题。

26. 看指定会话的关系锁是否走 fast-path

SQL

1SELECT pid, relation::regclass AS relation_name,
2       mode, fastpath, granted
3FROM pg_locks
4WHERE pid = 12345
5  AND locktype = 'relation'
6  AND database = (SELECT oid FROM pg_database
7                  WHERE datname = current_database())
8ORDER BY relation, mode;

fastpath=true 说明该关系锁经快速路径取得，并不表示“没有锁”或“不会阻塞 DDL”。排查普通读写与排他 DDL 的关系时，把它也算进去；PID 和数据库范围仍要与现场一致。

27. 查当前数据库中积压的两阶段事务

SQL

1SELECT gid, "transaction" AS xid, prepared,
2       now() - prepared AS prepared_age, owner
3FROM pg_prepared_xacts
4WHERE database = current_database()
5ORDER BY prepared;

两阶段事务提交前可能一直保留锁，即使原客户端早已离开。prepared_age 很长是需要联系事务协调方的线索；千万不能只按时间长短决定 COMMIT PREPARED 或 ROLLBACK PREPARED。

28. 只看正在等待 advisory lock 的会话

SQL

1SELECT l.pid, l.classid, l.objid, l.objsubid,
2       l.mode, l.waitstart,
3       pg_blocking_pids(l.pid) AS blocking_pids
4FROM pg_locks AS l
5WHERE l.locktype = 'advisory'
6  AND NOT l.granted
7  AND l.database = (SELECT oid FROM pg_database
8                    WHERE datname = current_database())
9ORDER BY l.waitstart NULLS LAST;

advisory lock 常用于应用自己实现的互斥；键的含义只能回到应用代码确认。先把等待会话与持锁会话的事务/连接生命周期对上，不要把这种等待误归因于 PostgreSQL 的表锁。

29. 看数据库死锁计数及统计起点

SQL

1SELECT datname, deadlocks, stats_reset
2FROM pg_stat_database
3WHERE datname = current_database();

deadlocks 是自 stats_reset 以来的累计次数。要判断今天是否新增，须和之前的取样值相比，再查服务端日志中的具体死锁 SQL 与锁对象；当前没有等待也不代表死锁没有发生。

30. 核对活动 SQL 文本的采集长度

SQL

1SHOW track_activity_query_size;

第 22、24 条的 query 可能因为长度上限而截断，尤其是 ORM 生成的长 SQL。看到不完整文本时，用应用日志、SQL 标识和数据库日志补齐；改大此启动参数也不会复原之前已截断的语句。

看业务影响，再查日志

31. 按应用名统计正在等锁的连接

SQL

1SELECT datname, application_name,
2       count(*) AS waiting_sessions,
3       min(query_start) AS earliest_query_start
4FROM pg_stat_activity
5WHERE wait_event_type = 'Lock'
6  AND backend_type = 'client backend'
7GROUP BY datname, application_name
8ORDER BY waiting_sessions DESC, earliest_query_start;

先看等待是否集中在一个应用，还是多个业务都被拖住。application_name 由客户端上报，空值和随意填写的名字都很常见；它不能代替调用链或连接来源。这里只是取样瞬间的连接数。

32. 按直接阻塞者统计受影响会话数

SQL

1WITH edges AS (
2  SELECT w.pid AS waiting_pid,
3         unnest(pg_blocking_pids(w.pid)) AS blocker_pid
4  FROM pg_stat_activity AS w
5  WHERE w.wait_event_type = 'Lock'
6)
7SELECT blocker_pid,
8       count(DISTINCT waiting_pid) AS affected_sessions
9FROM edges
10GROUP BY blocker_pid
11ORDER BY affected_sessions DESC, blocker_pid;

一个阻塞者后面排了多少会话，比只看最长一条等待更能说明影响范围。pg_blocking_pids 在并行查询时可能返回重复 PID，所以这里按等待 PID 去重；阻塞 PID 为 0 时去查 prepared transaction。

33. 列出已经等锁超过五秒的请求

SQL

1SELECT l.pid, now() - l.waitstart AS wait_age,
2       l.locktype, l.mode,
3       a.datname, a.application_name,
4       pg_blocking_pids(l.pid) AS blocking_pids
5FROM pg_locks AS l
6JOIN pg_stat_activity AS a ON a.pid = l.pid
7WHERE NOT l.granted
8  AND l.waitstart < now() - interval '5 seconds'
9ORDER BY l.waitstart;

这里用真正开始等锁的 waitstart，不用 SQL 的 query_start 冒充等待时间。刚开始等待的记录可能暂时没有 waitstart，因此不会出现在结果中；五秒是示例筛选线，不是 PostgreSQL 的故障阈值。

34. 找正在排队的排他关系锁

SQL

1SELECT l.pid, l.relation::regclass AS relation_name,
2       l.mode, l.waitstart,
3       a.application_name, a.query
4FROM pg_locks AS l
5JOIN pg_stat_activity AS a ON a.pid = l.pid
6WHERE l.locktype = 'relation'
7  AND l.mode = 'AccessExclusiveLock'
8  AND NOT l.granted
9  AND l.database = (SELECT oid FROM pg_database
10                    WHERE datname = current_database())
11ORDER BY l.waitstart NULLS LAST;

排队的 AccessExclusiveLock 常见于 DDL 和某些维护操作，它本身也可能挡住后续请求。先看发起 SQL、目标表和窗口安排，再用第 3 条找直接阻塞者；不要对所有等在队列后的查询逐个处置。

35. 专门找挡住业务的空闲事务

SQL

1SELECT b.pid, b.application_name, b.client_addr,
2       b.xact_start, now() - b.xact_start AS xact_age,
3       b.state_change, left(b.query, 200) AS last_query,
4       count(DISTINCT w.pid) AS blocked_sessions
5FROM pg_stat_activity AS w
6CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS p(pid)
7JOIN pg_stat_activity AS b ON b.pid = p.pid
8WHERE w.wait_event_type = 'Lock'
9  AND b.state LIKE 'idle in transaction%'
10GROUP BY b.pid, b.application_name, b.client_addr,
11         b.xact_start, b.state_change, b.query
12ORDER BY blocked_sessions DESC, b.xact_start;

空闲事务的 query 是最后执行过的 SQL，不是当前正在运行的 SQL。它可能持锁等着应用提交，也可能已经进入 aborted 状态；联系应用查事务边界，确认影响后再讨论取消或断开连接。

36. 查是否有两阶段事务挡住当前等待

SQL

1SELECT w.pid AS waiting_pid,
2       w.application_name, w.wait_event,
3       left(w.query, 200) AS waiting_query
4FROM pg_stat_activity AS w
5WHERE w.wait_event_type = 'Lock'
6  AND 0 = ANY(pg_blocking_pids(w.pid))
7ORDER BY w.query_start NULLS LAST;

pg_blocking_pids 返回 0 表示 prepared transaction 持有冲突锁。这类事务没有原会话 PID；下一步读第 18、19 条的 GID 和锁信息，找事务协调方确认，不能把 0 当成进程号去终止。

37. 查数据库或角色是否单独设置了锁超时

SQL

1SELECT COALESCE(d.datname, '*') AS database_name,
2       COALESCE(r.rolname, '*') AS role_name,
3       s.setconfig
4FROM pg_db_role_setting AS s
5LEFT JOIN pg_database AS d ON d.oid = s.setdatabase
6LEFT JOIN pg_roles AS r ON r.oid = s.setrole
7WHERE EXISTS (
8  SELECT 1 FROM unnest(s.setconfig) AS c(setting)
9  WHERE c.setting LIKE 'lock_timeout=%'
10     OR c.setting LIKE 'statement_timeout=%'
11     OR c.setting LIKE 'idle_in_transaction_session_timeout=%'
12)
13ORDER BY database_name, role_name;

同一实例的连接可能因为角色/数据库默认值不同，等锁多久后报错也不同。* 表示该默认值不限定数据库或角色；查询这里只读出目录里的默认设置，某个已连接会话后来执行的 SET 还需从它自己的环境或应用配置确认。

38. 确认当前锁等待日志是否会记录

SQL

1SELECT name, setting, unit, source
2FROM pg_settings
3WHERE name IN ('log_lock_waits', 'deadlock_timeout',
4               'log_min_error_statement')
5ORDER BY name;

打开 log_lock_waits 后，等待超过 deadlock_timeout 才会记锁等待日志；它不是每次取得锁都记录。log_min_error_statement 决定超时报错的 SQL 是否随错误一起留在日志。先读实际值，再判断日志里“没看到”等待是否有意义。

39. 查看当前日志文件路径

SQL

1SELECT pg_current_logfile() AS current_log_path;

只在启用 logging collector 且日志格式符合要求时能给出路径；结果为空并不能证明没有数据库日志，可能是日志由服务管理器或外部平台接收。函数默认需要超级用户或 pg_monitor 权限；查到路径后还要按故障时间和 PID 到对应日志找锁等待、死锁记录。

40. 确认日志采集方式与输出格式

SQL

1SELECT name, setting, source
2FROM pg_settings
3WHERE name IN ('logging_collector', 'log_destination',
4               'log_directory', 'log_filename')
5ORDER BY name;

第 39 条返回空值时先看这些设置。log_destination 可以有多个目标，日志也可能被容器或主机服务收集；要能按 PID 追锁链，还得确认日志前缀或结构化日志中有没有进程号。

advisory lock 要看清键值和持有者

41. 还原单个 bigint 的 advisory lock 键值

SQL

1SELECT d.datname, l.pid, l.mode, l.granted,
2       (l.classid::bigint << 32) | l.objid::bigint
3         AS advisory_key
4FROM pg_locks AS l
5JOIN pg_database AS d ON d.oid = l.database
6WHERE l.locktype = 'advisory'
7  AND l.objsubid = 1
8ORDER BY d.datname, advisory_key, l.pid;

objsubid = 1 表示应用用了单个 bigint 键；高、低 32 位分别在 classid 和 objid。锁键的业务含义要找应用约定，数据库不会替你翻译成“订单号”或“任务名”。

42. 查看两个整数构成的 advisory lock 键

SQL

1SELECT d.datname, l.pid, l.mode, l.granted,
2       l.classid AS key1, l.objid AS key2
3FROM pg_locks AS l
4JOIN pg_database AS d ON d.oid = l.database
5WHERE l.locktype = 'advisory'
6  AND l.objsubid = 2
7ORDER BY d.datname, key1, key2, l.pid;

双整数键和单个 bigint 键是两个互不重叠的键空间。分析阻塞时必须把 database、objsubid、classid、objid 一起对照，不能只拿一个整数去猜持锁者。

43. 按 advisory lock 等待时间排序

SQL

1SELECT l.pid, a.application_name, l.mode,
2       l.classid, l.objid, l.objsubid,
3       l.waitstart,
4       clock_timestamp() - l.waitstart AS waited,
5       pg_blocking_pids(l.pid) AS blocking_pids
6FROM pg_locks AS l
7LEFT JOIN pg_stat_activity AS a ON a.pid = l.pid
8WHERE l.locktype = 'advisory'
9  AND NOT l.granted
10ORDER BY l.waitstart NULLS LAST;

waitstart 可能在刚开始等待的一瞬间还是空值。阻塞者以 pg_blocking_pids() 为准；不要把同键上的所有已授予锁行都当作直接阻塞者，共享锁与排队顺序都可能改变链条。

44. 查持有 advisory lock 的空闲会话

SQL

1SELECT a.pid, a.datname, a.application_name,
2       a.state, a.xact_start, a.state_change,
3       COUNT(*) AS held_lock_rows
4FROM pg_locks AS l
5JOIN pg_stat_activity AS a ON a.pid = l.pid
6WHERE l.locktype = 'advisory'
7  AND l.granted
8  AND a.state IN ('idle', 'idle in transaction')
9GROUP BY a.pid, a.datname, a.application_name,
10         a.state, a.xact_start, a.state_change
11ORDER BY a.state_change;

idle 会话仍持有 advisory lock 时，常要确认应用是否用了会话级锁且漏了释放。pg_locks 不直接标明会话级还是事务级，也不会显示同一进程重复获取了几次；先联系应用核对锁的生命周期。

45. 找哪些 advisory 键已经有人排队

SQL

1SELECT d.datname, l.objsubid, l.classid, l.objid,
2       COUNT(*) FILTER (WHERE l.granted) AS holder_rows,
3       COUNT(*) FILTER (WHERE NOT l.granted) AS waiter_rows
4FROM pg_locks AS l
5JOIN pg_database AS d ON d.oid = l.database
6WHERE l.locktype = 'advisory'
7GROUP BY d.datname, l.objsubid, l.classid, l.objid
8HAVING COUNT(*) FILTER (WHERE NOT l.granted) > 0
9ORDER BY waiter_rows DESC, d.datname;

这条找热点键，holder_rows 是当前锁行数，不是锁被同一进程获取的次数。它也不判断锁模式是否冲突；具体等待者和直接阻塞者再看第 43 条。

46. 按数据库区分 advisory lock 的分布

SQL

1SELECT d.datname,
2       COUNT(*) FILTER (WHERE l.granted) AS granted_rows,
3       COUNT(*) FILTER (WHERE NOT l.granted) AS waiting_rows
4FROM pg_locks AS l
5JOIN pg_database AS d ON d.oid = l.database
6WHERE l.locktype = 'advisory'
7GROUP BY d.datname
8ORDER BY waiting_rows DESC, granted_rows DESC;

pg_locks 是集群视图，advisory lock 却按数据库区分。同一数字键在两个数据库中不是同一把锁；分析应用跨库任务时，先确认连接落在哪个库。

47. 核对锁共享内存的容量参数

SQL

1SELECT name, setting, unit, source
2FROM pg_settings
3WHERE name IN ('max_locks_per_transaction',
4               'max_connections',
5               'max_prepared_transactions')
6ORDER BY name;

advisory lock 也占锁管理器共享内存。持有键值过多时要和连接数、预备事务一起评估容量；max_locks_per_transaction 不是“每个事务最多只能拿这么多锁”的硬上限，不要按字面解释。

48. 查看预备事务是否仍持有 advisory lock

SQL

1SELECT p.gid, p.prepared, p.owner, p.database,
2       l.mode, l.classid, l.objid, l.objsubid
3FROM pg_locks AS l
4JOIN pg_prepared_xacts AS p
5  ON l.virtualtransaction = '-1/' || p.transaction
6WHERE l.locktype = 'advisory'
7  AND l.granted
8ORDER BY p.prepared, p.gid;

预备事务没有普通会话 PID，却仍可能持有事务级 advisory lock。出现结果时先找两阶段事务协调方和对应 gid；直接提交或回滚预备事务会改变业务事务结果，不能为了清锁擅自操作。

几种容易混淆的等待

49. 给当前等待事件查官方说明

SQL

1SELECT a.pid, a.datname, a.state,
2       a.wait_event_type, a.wait_event,
3       w.description
4FROM pg_stat_activity AS a
5LEFT JOIN pg_wait_events AS w
6  ON w.type = a.wait_event_type
7 AND w.name = a.wait_event
8WHERE a.state = 'active'
9  AND a.wait_event IS NOT NULL
10ORDER BY a.pid;

Lock、BufferPin、IO 和 LWLock 不是同一类问题。先读说明，再决定是否沿第 3—5 条找阻塞链；state = active 且有等待事件，只说明 SQL 正在执行但卡在某个等待点。

50. 找正在等 speculative insertion token 的会话

SQL

1SELECT a.pid, a.datname, a.application_name,
2       a.wait_event, a.query,
3       pg_blocking_pids(a.pid) AS blocking_pids
4FROM pg_stat_activity AS a
5WHERE a.wait_event_type = 'Lock'
6  AND a.wait_event = 'spectoken';

spectoken 常要结合并发插入、唯一索引和 INSERT ... ON CONFLICT 的业务 SQL 看。它与普通关系锁等待不同；仍应以直接阻塞 PID、事务状态和日志为证据，不能看到事件名就宣布唯一键冲突。

51. 查关系扩展锁等待

SQL

1SELECT l.pid, l.database, l.relation,
2       l.mode, l.waitstart,
3       pg_blocking_pids(l.pid) AS blocking_pids
4FROM pg_locks AS l
5WHERE l.locktype = 'extend'
6  AND NOT l.granted
7ORDER BY l.waitstart NULLS LAST;

这类锁保护关系扩展，不等同于业务 DDL 的 AccessExclusiveLock。热点写入表持续扩展时，可对照该表的写入、文件增长和等待分布；关系 OID 只有在当前数据库中才能可靠地关联 pg_class。

52. 分出 BufferPin 等待，别当普通锁链处理

SQL

1SELECT pid, datname, application_name,
2       state, wait_event, query_start, query
3FROM pg_stat_activity
4WHERE wait_event_type = 'BufferPin'
5ORDER BY query_start NULLS LAST;

Buffer pin 等待不一定在 pg_locks 留下一条待授予关系锁。官方文档提到开放游标可能延长 buffer pin；先看会话、游标和执行路径，不能靠杀一个“关系锁持有者”解决。

53. 单独清点 SERIALIZABLE 的 SIReadLock

SQL

1SELECT locktype, COUNT(*) AS predicate_lock_rows,
2       COUNT(DISTINCT pid) AS backend_count
3FROM pg_locks
4WHERE mode = 'SIReadLock'
5GROUP BY locktype
6ORDER BY predicate_lock_rows DESC;

SIReadLock 是 SERIALIZABLE 用来跟踪读写依赖的谓词锁，本身不会阻塞其他事务，也不会造成死锁。行数多时看访问计划和事务范围；不能把它们算进第 21 条的“未获锁请求”。

54. 找落到整张关系上的 SIReadLock

SQL

1-- 只在当前数据库解释 relation OID
2SELECT l.pid, l.relation::regclass AS relation_name,
3       a.application_name, a.state, a.query_start
4FROM pg_locks AS l
5LEFT JOIN pg_stat_activity AS a ON a.pid = l.pid
6WHERE l.mode = 'SIReadLock'
7  AND l.locktype = 'relation'
8  AND l.database = (SELECT oid FROM pg_database
9                    WHERE datname = current_database())
10ORDER BY l.relation, l.pid;

顺序扫描或谓词锁升级可能让锁落到关系级，带来更多 SERIALIZABLE 冲突概率；这里只展示当前锁，不直接证明某次 40001 错误就是这张表造成的。还要核对 SQL 和计划。

55. 核对谓词锁的内存与升级参数

SQL

1SELECT name, setting, unit, source
2FROM pg_settings
3WHERE name IN ('max_pred_locks_per_transaction',
4               'max_pred_locks_per_relation',
5               'max_pred_locks_per_page')
6ORDER BY name;

谓词锁过于粗粒度时，先查是否因内存容量或查询计划而升级。参数调整需要按并发事务和共享内存预算评估，不能把“看见 SIReadLock”直接转成加大参数的建议。

56. 区分逻辑复制 applytransaction 等待

SQL

1SELECT a.pid, a.backend_type, a.datname,
2       a.wait_event, a.query,
3       pg_blocking_pids(a.pid) AS blocking_pids
4FROM pg_stat_activity AS a
5WHERE a.wait_event_type = 'Lock'
6  AND a.wait_event = 'applytransaction';

applytransaction 是并行逻辑复制应用事务的同步等待。若出现该项，先结合订阅端应用 worker、事务顺序和复制延迟查；它不是普通业务 UPDATE 行锁。空结果也不能证明订阅应用没有其他瓶颈。

索引维护与备库冲突

57. 查看并发建索引卡在哪个阶段

SQL

1SELECT pid, datname, command, phase,
2       lockers_total, lockers_done, current_locker_pid,
3       blocks_total, blocks_done
4FROM pg_stat_progress_create_index
5WHERE command IN ('CREATE INDEX CONCURRENTLY',
6                  'REINDEX CONCURRENTLY')
7ORDER BY pid;

并发建索引要等旧事务退出几个关键阶段。phase 显示 waiting for writers 或 waiting for old snapshots 时，先查 current_locker_pid；blocks_done 只适合有扫描进度的阶段，不能拿它估算等锁还要多久。

58. 找出并发建索引当前等的会话

SQL

1SELECT p.pid AS index_pid, p.command, p.phase,
2       p.current_locker_pid,
3       a.state AS locker_state, a.xact_start,
4       a.application_name, a.query
5FROM pg_stat_progress_create_index AS p
6LEFT JOIN pg_stat_activity AS a
7  ON a.pid = p.current_locker_pid
8WHERE p.current_locker_pid IS NOT NULL
9ORDER BY p.pid;

current_locker_pid 是当前阶段正在等待的进程，不一定是普通关系锁意义上的直接阻塞者。若是 idle in transaction，找应用确认事务为什么未提交；取消建索引会留下待检查的无效索引，不能只看命令取消成功。

59. 查看索引维护时表和索引的名称

SQL

1SELECT p.pid, p.command, p.phase,
2       t.oid::regclass AS table_name,
3       CASE WHEN p.index_relid = 0 THEN NULL
4            ELSE p.index_relid::regclass END AS index_name
5FROM pg_stat_progress_create_index AS p
6JOIN pg_class AS t ON t.oid = p.relid
7WHERE p.datname = current_database()
8ORDER BY p.pid;

在当前数据库里把 relid 对上业务表，避免只拿 PID 给应用沟通。普通 CREATE INDEX 建立过程中 index_relid 可能为 0；这里保留 NULL，不伪造索引名。

60. 查是否留下无效索引

SQL

1SELECT i.indrelid::regclass AS table_name,
2       i.indexrelid::regclass AS index_name,
3       i.indisvalid, i.indisready, i.indislive
4FROM pg_index AS i
5WHERE NOT i.indisvalid
6ORDER BY i.indrelid, i.indexrelid;

并发建索引失败或被取消后可能留下无效索引。indisvalid = false 说明规划器不能把它当作有效索引；是否仍参与更新还要看 indisready。先查失败原因、唯一索引冲突及业务写入，再安排重建或清理。

61. 分开统计备库的回放冲突

SQL

1SELECT datname, confl_lock, confl_snapshot,
2       confl_bufferpin, confl_deadlock, confl_tablespace
3FROM pg_stat_database_conflicts
4ORDER BY datname;

这是备库上因 WAL 回放冲突而取消查询的累计计数，confl_lock 不能当成主库普通锁等待次数。它也不显示是哪条 SQL 被取消；结合备库日志、查询时长和复制延迟，看计数是否持续增加。

62. 确认备库允许查询等待回放多久

SQL

1SELECT name, setting, unit, source
2FROM pg_settings
3WHERE name IN ('max_standby_streaming_delay',
4               'max_standby_archive_delay',
5               'hot_standby_feedback')
6ORDER BY name;

前两个值控制备库回放等待与取消查询的边界，不是单条 SQL 的执行超时。hot_standby_feedback 能减轻部分快照冲突，但可能让主库垃圾元组积累；调参前要同时看备库查询和主库清理压力。

63. 查看当前备库是否在回放 WAL

SQL

1SELECT pg_is_in_recovery() AS is_standby,
2       pg_last_wal_receive_lsn() AS received_lsn,
3       pg_last_wal_replay_lsn() AS replayed_lsn,
4       pg_last_xact_replay_timestamp() AS last_replay_time;

先确认查询跑在备库上，再比较接收和回放位置。last_replay_time 是最近已回放事务的提交时间；主库若暂时没有新事务，它会变旧，不能单凭时间差判定复制故障。

64. 对照备库正在等待的回放锁

SQL

1SELECT pid, backend_type, state,
2       wait_event_type, wait_event,
3       pg_blocking_pids(pid) AS blocking_pids,
4       query
5FROM pg_stat_activity
6WHERE pg_is_in_recovery()
7  AND wait_event_type = 'Lock'
8ORDER BY pid;

只在备库看当前锁等待，结合第 61 条的回放冲突计数和日志判断影响。pg_stat_activity 是即时快照，取消过的查询不会一直留在这里；空结果不代表备库没有发生过冲突。

取消、断开与超时

65. 处置前重新确认目标 PID

SQL

1-- 把 12345 换成待处置 PID
2SELECT pid, usename, datname, application_name,
3       client_addr, state, xact_start, query_start,
4       wait_event_type, wait_event, query
5FROM pg_stat_activity
6WHERE pid = 12345
7  AND pid <> pg_backend_pid();

先核对用户、应用、客户端、事务开始时间和 SQL；不要凭几分钟前的阻塞截图操作 PID。目标已退出时 PID 可能被新连接复用。特别是 idle in transaction，query 是上次执行的语句，不代表它还在跑。

66. 取消阻塞会话正在执行的 SQL

SQL

1-- 确认 PID 后执行；只取消当前语句
2SELECT pg_cancel_backend(12345);

取消的是当前查询，不是整个连接。目标若已空闲且仍处在事务中，发出取消信号不等于释放已持有的锁；操作后必须再查事务状态和阻塞链。普通角色只能取消有权限处理的 backend，超级用户会话需超级用户权限。

67. 取消 SQL 后确认锁是否释放

SQL

1SELECT a.pid, a.state, a.xact_start,
2       COUNT(l.pid) FILTER (WHERE l.granted) AS granted_locks,
3       COUNT(l.pid) FILTER (WHERE NOT l.granted) AS waiting_locks
4FROM pg_stat_activity AS a
5LEFT JOIN pg_locks AS l ON l.pid = a.pid
6WHERE a.pid = 12345
7GROUP BY a.pid, a.state, a.xact_start;

如果连接还在、xact_start 未清且仍持锁，要找应用结束事务。取消语句后事务可能处于失败状态，业务需要 ROLLBACK；只看 pg_cancel_backend 返回 true 不足以证明锁已解除。

68. 结束确认过的阻塞连接

SQL

1-- 10000 是等待退出的毫秒数；操作前确认业务事务可回滚
2SELECT pg_terminate_backend(12345, 10000);

这会断开连接，未提交事务随之回滚，应用也会收到连接错误。PostgreSQL 17 提供第二个等待参数：返回 true 表示超时内进程已退出；不带超时参数的 true 只表示信号已发送，不能当成连接已断开。

69. 结束连接后再看阻塞链

SQL

1SELECT w.pid AS waiting_pid,
2       w.wait_event, w.query,
3       pg_blocking_pids(w.pid) AS blocking_pids
4FROM pg_stat_activity AS w
5WHERE w.wait_event_type = 'Lock'
6ORDER BY w.pid;

回读所有当前等待者，而不只看原来的一个 PID。若仍有 blocking_pids，可能还有其他持锁事务或队列前面的请求；核对业务恢复情况，再决定是否继续处置。

70. 给本次维护事务设置锁等待上限

SQL

1BEGIN;
2SET LOCAL lock_timeout = '5s';
3-- 在同一事务内执行本次维护 SQL
4COMMIT;

SET LOCAL 只对当前事务有效。维护语句若超过五秒还没获得锁，会报错；错误后事务须 ROLLBACK，上面的 COMMIT 是成功路径示意。它只限制等锁时间，不限制已经取得锁后的执行时间。

71. 核对本连接实际生效的超时

SQL

1SELECT current_setting('lock_timeout') AS lock_timeout,
2       current_setting('statement_timeout') AS statement_timeout,
3       current_setting('idle_in_transaction_session_timeout')
4         AS idle_transaction_timeout;

第 10、37 条查的是参数来源或角色默认值，这里看当前连接的生效值。statement_timeout 若比 lock_timeout 更短，语句超时可能先触发；应用连接参数也可能覆盖角色默认值。

72. 检查备库回放冲突日志开关

SQL

1SELECT name, setting, unit, source
2FROM pg_settings
3WHERE name IN ('log_recovery_conflict_waits',
4               'deadlock_timeout')
5ORDER BY name;

备库回放等待超过 deadlock_timeout 是否写日志，取决于 log_recovery_conflict_waits。先确认开关，再谈“日志里没有冲突”；该设置针对 WAL 回放冲突，不等于第 38 条的普通锁等待日志。

业务 SQL 怎么避免排队

73. 更新指定记录前用 NOWAIT 抢行锁

SQL

1-- job_queue(id, status) 为示例业务表
2SELECT id, status
3FROM job_queue
4WHERE id = 1001
5FOR UPDATE NOWAIT;

别人正锁着这行时会立即报错，适合调用方能明确重试或返回“处理中”的操作。它仍需取得表级 ROW SHARE 锁；遇到强 DDL 表锁仍可能等待，不能把 NOWAIT 当作整个语句的零等待保证。

74. 多个 worker 从队列表跳过被占的任务

SQL

1SELECT id, status
2FROM job_queue
3WHERE status = 'pending'
4ORDER BY id
5LIMIT 10
6FOR UPDATE SKIP LOCKED;

查询与领取任务必须在同一个事务内完成，提交前要更新状态。SKIP LOCKED 会略过其他 worker 正锁着的行，返回的是不一致视图，不适合财务对账或精确清单；长期被锁的任务还需有单独的超时和补偿机制。

75. 维护前试探表锁能否立即取得

SQL

1BEGIN;
2LOCK TABLE job_queue IN SHARE ROW EXCLUSIVE MODE NOWAIT;
3-- 成功后在同一事务中执行维护语句；失败则 ROLLBACK
4ROLLBACK;

示例只做锁试探，所以最后回滚。NOWAIT 失败说明此刻有冲突，但试探成功后若释放锁，再执行维护仍可能遇到新事务；正式维护须在持锁的同一事务内完成，并评估该锁模式对业务写入的影响。

76. 分批更新时跳过别的 worker 正在处理的行

SQL

1-- job_queue(id, status, created_at) 为示例业务表
2WITH picked AS (
3  SELECT id
4  FROM job_queue
5  WHERE status = 'pending'
6  ORDER BY created_at, id
7  LIMIT 100
8  FOR UPDATE SKIP LOCKED
9)
10UPDATE job_queue AS j
11SET status = 'processing'
12FROM picked AS p
13WHERE j.id = p.id
14RETURNING j.id;

这一条把领取和改状态放在同一语句，减少先查后改的窗口。RETURNING 是本次实际领取的任务；空结果不代表全部处理完，可能只是候选行都被锁着。若用于有限批量的数据修复，最后还要不带 SKIP LOCKED 核对漏项。

77. 尝试拿事务级 advisory lock

SQL

1-- 业务预先约定资源键 1001 的含义
2SELECT pg_try_advisory_xact_lock(1001::bigint) AS acquired;

拿不到就返回 false，不会排队。事务级锁在提交或回滚后自动释放，适合一个事务内完成的互斥业务；若在自动提交连接里单独运行此 SELECT，语句结束锁就释放，不能据此继续执行受保护的工作。

78. 尝试拿会话级 advisory lock

SQL

1SELECT pg_try_advisory_lock(1001::bigint) AS acquired;

返回 true 后由同一连接完成业务并调用 pg_advisory_unlock(1001::bigint)。会话级锁不会因事务回滚自动释放；连接池用它时要保证归还连接前解锁，且同一连接重复取得的锁要对应重复解锁。

79. 释放当前连接持有的 advisory lock

SQL

1-- 只在第 78 条 acquired = true 且当前连接确实持锁时执行
2SELECT pg_advisory_unlock(1001::bigint) AS released;

true 才表示这次解锁成功。不能在另一条连接上替原连接解锁；如果同一键被本连接多次取得，一次解锁后仍可能持有。排查连接池遗留锁时结合第 41—48 条看持有 PID 和键值。

80. 用共享事务级 advisory lock 保留并发读取

SQL

1SELECT pg_try_advisory_xact_lock_shared(1001::bigint)
2         AS acquired_shared;

多个共享持有者之间不冲突，但独占申请会与它们冲突。只适合业务已经约定同一资源键、读写双方都遵守 advisory lock 协议的场景；数据库不会替业务自动给表行加锁。

行锁强度与维护任务

81. 不改主键时使用较弱的行锁

SQL

1SELECT id, status
2FROM job_queue
3WHERE id = 1001
4FOR NO KEY UPDATE NOWAIT;

不准备改唯一键、主键等键值时，可以考虑 FOR NO KEY UPDATE；它仍会阻挡同一行的普通更新和删除，但不会挡 FOR KEY SHARE。先核对业务后续 SQL，别为了减少等待而把需要保护键值的操作降级。

SQL

1SELECT id
2FROM job_queue
3WHERE id = 1001
4FOR KEY SHARE NOWAIT;

FOR KEY SHARE 会阻止删除该行以及修改相关键值，但允许不改键的更新。它适合检查引用对象的存在性；不适合用来保证 status 等非键字段在后续事务里完全不变。NOWAIT 冲突仍需由应用处理重试。

83. 查看普通 VACUUM 的阶段和扫描进度

SQL

1SELECT pid, datname, relid, phase,
2       heap_blks_total, heap_blks_scanned,
3       index_vacuum_count, indexes_total, indexes_processed
4FROM pg_stat_progress_vacuum
5ORDER BY pid;

普通 VACUUM 和 autovacuum 都在这里，VACUUM FULL 不在此视图。扫描页数只在 scanning heap 阶段前进；卡在 vacuuming indexes 时不能用 heap_blks_scanned 不变直接判断死锁。

84. 对上 VACUUM 的目标表与当前等待

SQL

1SELECT v.pid, v.phase, v.relid::regclass AS table_name,
2       a.backend_type, a.wait_event_type, a.wait_event,
3       pg_blocking_pids(v.pid) AS blocking_pids
4FROM pg_stat_progress_vacuum AS v
5JOIN pg_stat_activity AS a ON a.pid = v.pid
6WHERE v.datname = current_database()
7ORDER BY v.pid;

仅在当前数据库转换 relid。若 blocking_pids 有值，再看阻塞者的事务和锁对象；若没有而 wait_event_type 为 IO 或 LWLock，别把进度停滞都解释成业务表锁。

85. 单独查看 VACUUM FULL 与 CLUSTER

SQL

1SELECT pid, datname, relid, command, phase,
2       heap_blks_total, heap_blks_scanned,
3       index_rebuild_count
4FROM pg_stat_progress_cluster
5WHERE command IN ('VACUUM FULL', 'CLUSTER')
6ORDER BY pid;

这两种操作会重写表，锁影响与普通 VACUUM 不同。先看阶段，再查目标表的关系锁和业务等待；不要因为第 83 条无结果，就认为现场没有 VACUUM FULL。

86. 查看维护进程正在等哪把锁

SQL

1SELECT l.pid, a.backend_type, a.query,
2       l.locktype, l.mode, l.relation,
3       l.waitstart, pg_blocking_pids(l.pid) AS blocking_pids
4FROM pg_locks AS l
5JOIN pg_stat_activity AS a ON a.pid = l.pid
6WHERE NOT l.granted
7  AND (a.backend_type = 'autovacuum worker'
8       OR a.query ~* '^(vacuum|cluster|create index|reindex)')
9ORDER BY l.waitstart NULLS LAST;

这是正在排队的维护进程快照，不是所有维护进程清单。SQL 文本可能被截断、脱敏或包含注释，因此正则可能漏项；autovacuum worker 由 backend_type 识别，阻塞关系仍以 pg_blocking_pids 为准。

87. 查指定表的维护锁与业务写锁

SQL

1-- 把 public.job_queue 换成目标表
2SELECT l.pid, a.backend_type, a.state,
3       l.mode, l.granted, l.waitstart
4FROM pg_locks AS l
5LEFT JOIN pg_stat_activity AS a ON a.pid = l.pid
6WHERE l.locktype = 'relation'
7  AND l.database = (SELECT oid FROM pg_database
8                    WHERE datname = current_database())
9  AND l.relation = 'public.job_queue'::regclass
10ORDER BY l.granted, l.waitstart NULLS LAST, l.pid;

把 ShareUpdateExclusiveLock、ShareLock、RowExclusiveLock 等模式放在同一个对象上比较。模式名带 Row 也仍是表级锁；是否冲突要按官方兼容矩阵判断，不能只凭名字推断。

88. 找仍在等锁的 autovacuum worker

SQL

1SELECT a.pid, a.datname, a.query_start,
2       a.wait_event, a.query,
3       pg_blocking_pids(a.pid) AS blocking_pids
4FROM pg_stat_activity AS a
5WHERE a.backend_type = 'autovacuum worker'
6  AND a.wait_event_type = 'Lock'
7ORDER BY a.query_start;

自动清理被其他操作挡住时，先找锁对象和阻塞事务，评估表膨胀或 XID 冻结风险。空结果只表示此刻没有等锁的 worker；autovacuum 也可能因 I/O、限速或其他阶段进度慢。

留下可复盘的证据

89. 记录等锁 SQL 和阻塞 SQL 的 query_id

SQL

1SELECT w.pid AS waiting_pid, w.query_id AS waiting_query_id,
2       b.pid AS blocking_pid, b.query_id AS blocking_query_id,
3       w.query AS waiting_query, b.query AS blocking_last_query
4FROM pg_stat_activity AS w
5CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS p(pid)
6LEFT JOIN pg_stat_activity AS b ON b.pid = p.pid
7WHERE w.wait_event_type = 'Lock'
8ORDER BY w.pid, b.pid;

留存 PID、query_id 和 SQL，便于和日志及统计视图对照。阻塞者若已空闲，blocking_last_query 是最后一条 SQL，不一定是拿锁那条；query_id 也可能为 NULL。blocking_pid = 0 表示预备事务，左连接不到普通会话。

90. 确认 query_id 是否启用

SQL

1SELECT name, setting, source
2FROM pg_settings
3WHERE name IN ('compute_query_id',
4               'shared_preload_libraries');

compute_query_id = auto 并不保证每条会话都有 query_id，还要看是否有模块启用计算。pg_stat_statements 需预加载并在目标数据库安装扩展；只看 shared_preload_libraries 不能证明视图已经可查询。

91. 检查当前库是否安装 pg_stat_statements

SQL

1SELECT extname, extversion, extnamespace::regnamespace AS schema_name
2FROM pg_extension
3WHERE extname = 'pg_stat_statements';

没安装就别直接运行下一条。安装扩展只创建当前数据库可用的对象，模块还须在实例启动时预加载；补装和重启是变更操作，先评估窗口与权限。

92. 用 query_id 查看历史 SQL 的累计耗时

SQL

1-- 仅在当前库已安装并启用 pg_stat_statements 时执行
2SELECT dbid, userid, queryid, calls,
3       total_exec_time, mean_exec_time, max_exec_time,
4       query
5FROM pg_stat_statements
6WHERE queryid = 123456789::bigint
7  AND dbid = (SELECT oid FROM pg_database
8              WHERE datname = current_database())
9ORDER BY total_exec_time DESC;

把第 89 条的 ID 换进来，结合数据库和执行角色辨认。这里是 SQL 总执行时间，不是累计等锁时间；一条长期未结束的 SQL 也未必马上反映在累计统计中。不同大版本或对象重建后不应把 queryid 当永久稳定键。

93. 查看日志前缀能否关联到会话

SQL

1SELECT name, setting, source
2FROM pg_settings
3WHERE name = 'log_line_prefix';

文本日志里有 %p（PID）或 %c（会话标识）更方便串起等待与报错；%Q 需能计算 query ID。若用 CSV/JSON 日志，按对应结构化字段检索，别套用文本前缀的规则。

94. 算出当前会话在日志里的会话标识

SQL

1SELECT pid, backend_start,
2       to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer)
3         || '.' || to_hex(pid) AS log_session_id
4FROM pg_stat_activity
5WHERE pid = 12345;

这是官方文档给 %c 的对应算法。PID 可能被复用，带上 backend_start 或 %c 才更容易区分前后两个连接；事后会话已退出时，要从日志自身找这个标识。

95. 确认报错日志会不会带 SQL

SQL

1SELECT name, setting, source
2FROM pg_settings
3WHERE name IN ('log_min_error_statement',
4               'log_min_messages');

发生 lock_timeout、死锁或 NOWAIT 错误时，是否能从服务端日志看到语句，取决于这些阈值与日志出口。应用参数可能含敏感数据，调高日志量前先核对脱敏和存储策略；没日志不等于没报错。

给业务连接设边界

96. 只给指定库的业务角色设置 lock_timeout

SQL

1-- app_user、appdb 替换为现场角色和数据库
2ALTER ROLE app_user IN DATABASE appdb
3  SET lock_timeout = '5s';

这是新连接的默认值，已有连接不自动更新；应用显式 SET 或连接参数还能覆盖它。先和业务约定超时报错、重试次数及事务回滚方式，别把五秒当作所有 SQL 的总耗时上限。

97. 限制业务连接的空闲事务时间

SQL

1ALTER ROLE app_user IN DATABASE appdb
2  SET idle_in_transaction_session_timeout = '2min';

连接在事务内空闲超过该时间会被断开，未提交工作回滚。它能防止长时间占锁的空闲事务，但也可能误伤需要人为交互的后台作业；上线前先查第 7、35 条中的应用名和事务时长。

98. 检查角色与数据库绑定的两项默认值

SQL

1SELECT r.rolname, d.datname, s.setconfig
2FROM pg_db_role_setting AS s
3JOIN pg_roles AS r ON r.oid = s.setrole
4JOIN pg_database AS d ON d.oid = s.setdatabase
5WHERE r.rolname = 'app_user'
6  AND d.datname = 'appdb';

核对第 96、97 条是否写到了指定角色与指定数据库，而不是全库或全角色。这里只证明默认值已存到目录；要确认生效，还须用该角色新建连接并查第 71 条的当前值。

99. 撤销指定角色的锁等待默认值

SQL

1ALTER ROLE app_user IN DATABASE appdb
2  RESET lock_timeout;

恢复时只撤掉这一条角色与数据库绑定设置，保留其他参数。新连接随后继承更低优先级的角色、数据库或实例默认值；不要用 RESET ALL 把现场其他配置一起清掉。

100. 撤销指定角色的空闲事务默认值

SQL

1ALTER ROLE app_user IN DATABASE appdb
2  RESET idle_in_transaction_session_timeout;

业务已修好事务收尾逻辑或阈值需要重新评估时，按原作用域撤销。已有连接的生效值不会因为角色默认值被重置而立即改变；回读目录设置，并用新连接验证。

总结

现场查锁，先保存等待者、阻塞者和事务开始时间，再对上锁对象与业务连接。行锁、表锁、备库回放冲突和 BufferPin 不是一回事；动连接前先确认 PID 还属于原会话。处理完回头查阻塞链，下一次再从应用事务收尾和超时设置上减少积压。

ORA100 DBA100 命令系列海报 ORA100 DBA100 命令系列海报

其他数据库运维专题收在 ORA100 · DBA100：https://ora100.com/dba100

微信小程序搜索 「三笠的百令册」，也能查这套命令。

PostgreSQL 锁等待与长事务诊断 100 条命令 - DBA 学习之路