凌晨两点被告警叫醒通常不是好差事。那次是订单表里一条 UPDATE 卡了十几分钟所有库存操作都在排队业务方连发三条“数据库是不是挂了”。我连上实例第一件事就是看 pg_stat_activity结果锁等待的会话 wait_event_type 是 Lockwait_event 是 transactionid典型的一行数据被另一个事务占着。在 PostgreSQL 9.6 之前这种时候只能去 pg_locks 里手动比对锁的持有者和等待者经常比对到怀疑人生9.6 之后官方直接给了一个现成函数——pg_blocking_pids()一个参数传进去直接返回“谁堵了你”。这篇文章就把这个函数彻底讲清楚它是怎么工作的、在哪些锁等待场景里能用、怎么用它快速定位真凶、生产环境里有哪些坑。1. 认识 pg_blocking_pids一个参数告诉你“谁堵我”1.1 函数签名和返回值pg_blocking_pids 的签名看起来非常简单pg_blocking_pids(integer) RETURNS integer[]传入一个后端进程的 PID也就是 pg_stat_activity 视图里的 pid 字段函数返回一个整型数组里面装的都是“正在阻塞这个 PID 对应会话”的进程 PID。如果当前没有会话阻塞它返回空数组 {}。我第一次看到这个函数名的时候还以为是 PostgreSQL 自己内部的一个辅助函数直到在锁等待现场用了之后才发现这玩意就是专门为 DBA 和运维排查锁等待准备的神器。它不需要你去解析 pg_locks 的复杂结构不需要自己写自连接官方已经帮你把“谁阻塞谁”这个关系直接算好了。需要注意一点这里的“阻塞”主要指重量级锁heavyweight lock等待。如果你查出来的 wait_event_type 是 LWLock、BufferContent 这类内部闩锁pg_blocking_pids 通常帮不上忙后面第 6 节我会详细展开这个边界。1.2 最小可用示例假设你从 pg_stat_activity 里看到一个可疑会话PID 是 12345你想知道谁在阻塞它SELECT pg_blocking_pids(12345) AS blocker;执行结果可能像这样blocker ---------- {6789}这意味着 PID 6789 这个会话正在阻塞 PID 12345。如果返回结果是空数组 {}说明目前没有任何重量级锁阻塞该会话。这里补一句psql 里看到 {6789} 这种格式是 PostgreSQL 的数组字面量如果里面多个值比如 {6789, 9012}就说明这个会话同时被两个会话阻塞了。第一次看到这种输出可能会愣一下习惯了就好。1.3 拿到 PID 之后怎么反查会话信息单知道一个 PID 还远远不够你真正想知道的是这个阻塞者是谁他执行了什么语句已经卡了多久从哪台机器连过来的。所以正确用法是配合 pg_stat_activity 一起查询SELECT pid, usename, application_name, client_addr, state, now() - xact_start AS xact_age, left(query, 80) AS query FROM pg_stat_activity WHERE pid ANY(pg_blocking_pids(12345)); ANY(pg_blocking_pids(12345)) 这句是把函数返回的数组直接当过滤条件用。即使返回多个阻塞者一条查询也能全部列出来。xact_age 是事务已经运行的时间这个字段在判断“谁该背锅”的时候极其重要往往那个 xact_age 最长的 idle in transaction 会话就是元凶。2. 什么时候会想到用它典型锁等待场景全梳理2.1 行锁冲突这是最经典、最高频的场景。两个事务同时修改同一行比如 session A 执行 UPDATE 没提交session B 也去 UPDATE 同一行B 就必须等 A 提交或回滚。此时 B 在 pg_stat_activity 里的 wait_event_type 是 Lockwait_event 是 transactionid调用 pg_blocking_pids(B 的 PID) 返回的就是 A 的 PID。我在生产环境里见到的行锁冲突80% 都来自“应用程序里开了事务第一步更新了某行后面又去做外部接口调用或者批量计算迟迟不提交”。这种事务看起来人畜无害但它咬着的那一行会让所有后续请求全部排大队。2.2 表级锁冲突表级锁冲突往往比行锁冲突更“社会性死亡”。一个会话对表执行 DDL、TRUNCATE、VACUUM FULL、LOCK TABLE这些操作需要 ACCESS EXCLUSIVE 级别的表锁而任何普通的 SELECT/UPDATE/INSERT 需要 ACCESS SHARE 或者 ROW EXCLUSIVE 锁两边直接冲突。此时 wait_event 一般显示 relationpg_blocking_pids 同样能帮你定位到那个持有 ACCESS EXCLUSIVE 锁的会话。我曾经排查过一次凌晨的批量任务事故就是一个维护脚本执行了 ALTER TABLE ADD COLUMN它默认拿 ACCESS EXCLUSIVE 锁然后被一个长事务堵住反过来它又堵住了后面所有的查询。用 pg_blocking_pids 一查凶手立刻现形。2.3 外键、序列锁和串行化快照隔离除了行锁和表锁还有一些容易被忽略的锁等待也能被 pg_blocking_pids 识别。比如外键约束的插入/更新子表插入时会在父表对应行上取 FOR KEY SHARE 锁两边事务如果交叉更新也会互相阻塞再比如 PostgreSQL 的序列虽然走的是缓存机制但某些操作依然要持锁还有 serializable 隔离级别下的 SIRead 锁冲突pg_blocking_pids 也能查出相关会话。所以当你看到某个会话卡在锁等待里但说不清楚它到底等的是什么锁时直接调用 pg_blocking_pids 通常都能给你一个答案。它的底层逻辑不是猜而是读取锁管理器里真实的锁请求和等待队列关系。3. 动手实验用三个会话模拟真实锁等待3.1 准备测试环境纸上谈兵没有意义咱们直接搭一个最小实验环境。任何 PostgreSQL 9.6 及以上版本都行我本机用的是 PostgreSQL 16 的测试实例Windows 上服务起来之后用 psql 连接即可。先建一张简单的表CREATE TABLE t_demo ( id int PRIMARY KEY, status text ); INSERT INTO t_demo VALUES (1, init), (2, init);建表时至少要有主键因为行锁冲突实验需要指定同一行的记录。3.2 实验一构造行锁冲突打开三个 psql 窗口分别称为 S1、S2、S3。S1 窗口执行BEGIN; UPDATE t_demo SET status s1 WHERE id 1;S2 窗口执行同样行的 UPDATEBEGIN; UPDATE t_demo SET status s2 WHERE id 1;此时 S2 会卡住等待 S1 提交或回滚。现在在 S3 窗口里跑查询SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blockers, left(query, 40) AS query FROM pg_stat_activity WHERE pid pg_backend_pid();你会看到 S2 那行数据大概是这个画风pid | wait_event_type | wait_event | blockers | query ----------------------------------------------------------------------------- 3220 | Lock | transactionid | {2189} | UPDATE t_demo SET status...blockers 列里的 {2189} 就是 S1 的 PID和你预想完全一致。这说明 pg_blocking_pids 精准识别出了行锁冲突的阻塞者。之后在 S1 执行 COMMITS2 立刻恢复执行实验结束。3.3 实验二构造表锁冲突仍然用三个窗口。S1 窗口执行BEGIN; LOCK TABLE t_demo IN ACCESS EXCLUSIVE MODE;S2 窗口执行一个普通查询SELECT count(*) FROM t_demo;查询会卡住。再用 S3 查看SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blockers FROM pg_stat_activity WHERE pid pg_backend_pid();S2 的 wait_event 会变成 relationblockers 里又是 S1 的 PID。这次实验说明了一个细节表级锁冲突和行锁冲突在 wait_event 字段上不一样但 pg_blocking_pids 都能一网打尽。3.4 实验三多级阻塞链这一步非常关键因为它能帮你理解 pg_blocking_pids 的一个特性它只返回“直接阻塞者”不会自动向上递归查“阻塞者的阻塞者”。构造一个三级链S1 窗口BEGIN; LOCK TABLE t_demo IN ACCESS EXCLUSIVE MODE;S2 窗口先锁另一张表再尝试锁 t_demo这样它自己也会陷入等待BEGIN; LOCK TABLE t_demo2 IN ACCESS EXCLUSIVE MODE; LOCK TABLE t_demo IN ACCESS EXCLUSIVE MODE;S2 的第二个 LOCK 会被 S1 阻塞。然后 S3 窗口去锁 t_demo2BEGIN; LOCK TABLE t_demo2 IN ACCESS EXCLUSIVE MODE;因为 S2 已经持有 t_demo2 的锁所以 S3 会被 S2 阻塞。此时在 S4 窗口查询SELECT pid, pg_blocking_pids(pid) AS blockers FROM pg_stat_activity WHERE pid IN (S2的PID, S3的PID);结果是S2 的 blockers 是 {S1}S3 的 blockers 是 {S2}注意 S3 的 blockers 里没有 S1尽管 S1 是整条链的根源。这就是“直接阻塞”的含义。如果你只对着 S3 调了一次 pg_blocking_pids很容易以为凶手就是 S2然后误判。要找到 S1 这种源头必须继续递归查 S2 的 blockers。4. 原理拆解这些 PID 到底怎么筛出来的4.1 锁管理器与等待队列PostgreSQL 的锁管理机制可以类比食堂的取餐窗口每个窗口是不同类型的锁资源每个会话需要在对应窗口取号排队。当一个后端进程请求一个锁时如果当前锁已经被别的进程以冲突的模式持有这个进程不会立刻拿到锁而是进入锁管理器维护的“等待队列”。pg_blocking_pids 的实现本质上是去遍历这些等待关系。它会找到目标 PID 正在等待的锁请求找出那些“已经持锁”或者“排在其前面且锁模式冲突”的进程然后把它们的 PID 全部返回。因为锁管理器内部有完整的持有者和等待者对应关系所以这个函数才能做到“直接查询即得答案”不需要你手工拼接。4.2 为什么要返回数组而不是单一 PID很多人第一次见到返回类型是 integer[] 会觉得别扭。实际场景决定了数组是唯一合理的设计。一个会话可能同时等好几个锁资源也可能一个锁资源前面排队了好几个持锁进程。比如在可串行化隔离级别下一个读事务可能同时被多个写事务“干扰”pg_blocking_pids 就会把所有这些干扰者一次性列出来。数组设计还有一个好处可以直接和 pg_stat_activity 做 ANY 匹配一条 SQL 就把所有阻塞者的信息全部带出来这在监控自动化里非常顺手。4.3 为什么只报告直接阻塞者锁管理器里维护的本来就是“等待者——持锁者”的直接关系pg_blocking_pids 不会主动递归。从实现角度看一次函数调用只做一层查询性能好从使用角度看DBA 需要逐步追根因而不是被笼统的“祖先列表”淹没。递归查找根因的方式也很简单先查目标会话的 blockers再对 blockers 里的每个 PID 继续调用 pg_blocking_pids直到某个 PID 的 blockers 为空数组。第 5 节我会给一个现成的递归 SQL。4.4 空数组并不代表没有“问题”pg_blocking_pids 返回空数组时不要急着下结论说“没人阻塞”。它还意味着两个可能一是目标会话确实没在等待重量级锁二是它在等待的东西属于轻量级锁或其他内部资源。比如数据页扩展时的 Extend 锁、缓冲区内容锁 BufferContent、子事务清理锁等等这些虽然本质上是并发竞争但不在 pg_blocking_pids 的射程内。所以看到空数组正确做法是回看 wait_event_type 和 wait_event。如果 wait_event_type 是 Lock但事件是 weird 的比如 relation 在极短时间内就消失可能是锁竞争瞬间结束了如果 wait_event_type 是 LWLock那就要换一套排查思路了。5. 实战升级从单次排查到自动化监控5.1 一条 SQL 列出所有锁等待会话和对应阻塞者靠眼睛一条条记录看太累生产环境里我们通常直接用下面这段 SQL把所有锁等待会话连同它的阻塞者一起拉出来SELECT a.pid, a.usename, a.application_name, a.client_addr, a.state, a.wait_event_type, a.wait_event, pg_blocking_pids(a.pid) AS blockers, array_length(pg_blocking_pids(a.pid), 1) AS blocker_cnt, now() - a.xact_start AS xact_age, left(a.query, 60) AS query FROM pg_stat_activity a WHERE a.wait_event_type Lock ORDER BY blocker_cnt DESC NULLS LAST, xact_age DESC;blocker_cnt 是阻塞者数量按它排序可以快速找到“被最多人堵住”的会话。xact_age 按从大到小排优先看事务年龄最大的会话往往那就是元凶。5.2 递归 CTE 查找阻塞源头3.4 节实验已经证明多层阻塞链里一把 pg_blocking_pids 只能看到直接阻塞者。生产中的长事务阻塞链往往有三层以上A 阻塞 BB 阻塞 CC 阻塞 D。想让人工一层层查太累直接用递归 CTEWITH RECURSIVE chain AS ( SELECT pid, query, pg_blocking_pids(pid) AS blockers, ARRAY[pid] AS path, 1 AS depth FROM pg_stat_activity WHERE pid 12345 -- 替换成你实际要查的 PID UNION ALL SELECT b.pid, b.query, pg_blocking_pids(b.pid), chain.path || b.pid, chain.depth 1 FROM chain JOIN pg_stat_activity b ON b.pid ANY (chain.blockers) WHERE NOT b.pid ANY (chain.path) ) SELECT pid, blockers, path, depth, query FROM chain ORDER BY depth;这段 SQL 的核心是从目标 PID 出发递归地查每个阻塞者的阻塞者直到没有新的 PID 出现。path 字段记录了完整的阻塞链条depth 表示在第几层。实际使用过几次后发现几乎每次都能在三层以内找到真正的根因会话效率远高于人工去 pg_locks 里比对。5.3 做进自动化告警光是人工查还不够出事故时打电话喊人起来看已经晚了半截。我习惯把这个查询直接挂到定时任务里每 30 秒跑一次一旦发现某个会话 wait_event_type 是 Lock、阻塞时间超过 5 分钟就把阻塞者和被阻塞者的完整信息拼成一条告警发到群里。伪代码如下#!/bin/bash # 检查超过 5 分钟的锁等待会话 psql -h 127.0.0.1 -U postgres -d postgres -Atc \ SELECT a.pid FROM pg_stat_activity a WHERE a.wait_event_type Lock AND now() - a.state_change interval 5 minutes \ | while read pid; do echo PID $pid 被以下会话阻塞 psql -h 127.0.0.1 -U postgres -d postgres -c \ SELECT pid, pg_blocking_pids(pid) AS blockers, usename, state, query FROM pg_stat_activity WHERE pid ANY(pg_blocking_pids($pid)) done这个脚本如果直接放到生产环境记得根据你的连接密码配置改成 .pgpass 或 PGPASSWORD 环境变量。脚本的作用不是杀会话而是第一时间通知值班人员把人工排查时间从 10 分钟压缩到 10 秒。5.4 结合锁等待时长做分级处理还有一种更细的玩法把锁等待的时长分级。等待小于 1 秒的也许只是正常并发抖动等待超过 30 秒的基本可以认定是事故等待超过 5 分钟且阻塞者是 idle in transaction 状态的那几乎可以直接 kill 掉。等级划分可以写进监控逻辑里避免一有锁等待就发告警把人吓死。我通常会额外查一下阻塞者本身在干什么SELECT pid, state, CASE WHEN state idle in transaction THEN 事务未关闭疑似元凶 WHEN state active THEN 正在执行查询 END AS blocker_status, query FROM pg_stat_activity WHERE pid ANY(pg_blocking_pids(:target_pid));如果 state 显示 idle in transaction基本可以断定这就是“占着茅坑不拉屎”的源头直接 kill 它往往是正确的第一动作。6. 常见问题与避坑实录6.1 函数返回空数组但会话明明卡住了这是被问得最多的问题。看到 pg_blocking_pids 返回空数组别先怀疑函数坏了先看 wait_event_type。如果 wait_event_type 是 LWLock、BufferContent 这类值说明这个会话等的是内部轻量级锁pg_blocking_pids 确实管不到。比如大量会话争抢同一个数据页的缓冲区时wait_event 会显示 BufferContent这种问题的根因可能是磁盘 IO 太慢、字段宽度过大或者并发热点集中。另一种情况是 wait_event_type 是 ClientRead说明会话只是空闲等客户端发命令它根本没在“等锁”只是刚好卡在了一个长查询的间隙里。很多人误把这种状态当成锁等待白白折腾半天。判断的第一原则永远是看 wait_event_type。6.2 有多个阻塞者时怎么确认谁是主凶返回数组里有多个 PID并不代表每个都是最终责任人。你得逐个看这些阻塞者自身是不是也在等锁。如果阻塞者 A 自己 wait_event_type 也是 Lock说明 A 也被人堵着它只是链条中的一环不是源头如果阻塞者 B 状态是 idle in transaction且事务年龄很大那多半就是最终元凶。我习惯用这样一条展开查询SELECT pid, pg_blocking_pids(pid) AS blockers, wait_event_type, wait_event, state, now() - xact_start AS xact_age FROM pg_stat_activity WHERE pid ANY(pg_blocking_pids(:target_pid));哪一行的 blockers 是空数组、xact_age 又最长基本就是根因。另一种特殊情况多个会话交叉持有不同锁形成死锁。这时 PostgreSQL 会在默认设置下自动检测死锁并回滚其中一个事务pg_stat_activity 里只是短暂出现两方互等pg_blocking_pids 能看到互相返回对方 PID。6.3 PostgreSQL 9.6 之前没有这函数怎么办如果是 9.6 之前的老库没法直接用 pg_blocking_pids。替代方案是从 pg_locks 里手动找出等待者和持有者。核心思路是找一张锁表里 granted 为 true 的会话和 granted 为 false 的会话按 locktype 和其他标识字段做自连接SELECT w.pid AS waiting_pid, b.pid AS blocking_pid FROM pg_locks w JOIN pg_locks b ON b.locktype w.locktype AND b.database IS NOT DISTINCT FROM w.database AND b.relation IS NOT DISTINCT FROM w.relation AND b.page IS NOT DISTINCT FROM w.page AND b.tuple IS NOT DISTINCT FROM w.tuple AND b.virtualxid IS NOT DISTINCT FROM w.virtualxid AND b.transactionid IS NOT DISTINCT FROM w.transactionid WHERE w.granted false AND b.granted true AND w.pid b.pid;这个查询是个简化的自连接能解决大多数场景但行锁和序列锁等特殊情况还是容易被绕晕。我的建议是这种老版本尽量做逻辑迁移升级9.6 之后 pg_blocking_pids 带来的排障效率提升是实打实的不值得为了“稳定”死守老版本。6.4 主从复制场景的“伪锁等待”在备库上有时你会看到查询会话的 wait_event_type 不是 Lock而是和恢复回放相关的冲突事件。比如 hot standby 上的查询和正在应用的 WAL 发生冲突或者回放进程在等待持锁查询结束。遇到这种情况pg_blocking_pids 返回值往往没什么参考价值因为冲突的另一方可能是 walsender 回放进程而不是普通业务会话。排查这类问题要切换到另一套思路检查 pg_stat_replication 里的 walsender 状态看备库当前的 replay_lsn 和应用的 WAL 事件有没有异常再配合 log 里的 recovery conflict 记录确认冲突原因。这不是 pg_blocking_pids 能覆盖的领域别拿着锤子见什么都是钉子。6.5 个人使用心得用了这么多年我很少再直接去看 pg_locks 的原生记录了。不是因为 pg_locks 没用而是 pg_blocking_pids 把最常用的“找阻塞者”这一步封装好了让排查起点降低了非常多。我现在遇到锁等待类告警标准动作就是一条 SQL把 pg_stat_activity 里 wait_event_typeLock 的会话全部列出来顺便带上 pg_blocking_pids 和 xact_age然后按阻塞者数量和事务时长排序直接定位到那个有问题的长事务。最后说个实用的经验很多锁等待事故是从“数据订正脚本忘记提交”开始的。开发团队手动跑了一条 UPDATE 订正数据没 BEGIN 没 COMMIT客户端一关连接迟迟不释放事务一直挂着。用 pg_blocking_pids 查出这类 idle in transaction 会话后大概率直接 kill 掉就能恢复业务。不过 kill 之前还是建议看一眼它的 query 和事务开始时间确认不是正在执行的正常业务毕竟误杀真业务事务的代价有时候比锁等待本身还大。希望这篇实战总结能帮你下次遇到锁等待时少熬一次夜。