PostgreSQL查询一直等待:先找阻塞它的事务

适用范围:PostgreSQL;跨会话可见信息取决于监控权限,避免向普通应用账号授予过大权限。

一个很简单的UPDATE一直不结束,CPU却不高,可能是在等锁,而不是计算太慢。先确认等待事件,再找出真正阻塞它的事务。

先看等待者,不先结束进程

由有监控权限的账号查询:

SELECT pid, usename, datname, state,
       wait_event_type, wait_event,
       xact_start, query_start,
       pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

先看wait_event_type是否为Lock,以及blockers数组。没有阻塞PID并不等于查询健康,它也可能在等待IO、客户端或其他事件。[1][2]

这里故意不输出完整SQL,避免把查询参数里的个人信息带进截图。必须查看SQL时只在受控终端进行,提交前脱敏。

沿阻塞链找到源头

用数组中的PID回到活动视图核对,不把等待最久的连接误当成根因。阻塞者也可能继续被第三个连接阻塞,应沿链查到底。

如果blockers里出现0,表示持锁者可能是已经进入预备状态的两阶段事务,不是普通后台进程。交由数据库维护者核对预备事务及其业务协调方,不能把0当成可终止的会话PID。

重点检查长时间 idle in transaction 的会话:它当前没有执行查询,但未结束的事务仍可能持锁。还要核对应用是否把外部HTTP请求、人工等待或大量业务处理放在事务内部。

联系持有事务的业务方结束事务

优先让所属程序按业务判断提交或回滚,不直接重启整库。取消某条运行中的SQL与断开整个会话,影响不同;持锁事务处于空闲时,仅取消查询未必能释放锁。

确需终止连接时,先确认PID、事务范围、未完成业务、回滚成本和重试行为,并由授权维护者执行。不要把活动视图结果整批拼成kill列表。

将等待限制放到正确范围

应用可以评估事务级lock_timeout或statement_timeout,让请求在可接受时间内失败,而不是无限堆积。但超时是故障边界,不是修复锁持有者;失败后仍应按驱动要求回滚。

长期处理要缩短事务、保持一致的访问顺序、检查索引与批量任务。不能把所有延迟都归因于服务器配置小。[2]

验收

同一查询恢复后,确认阻塞链消失、事务已结束、连接池没有持续增长;再复测并发场景。只看某次查询终于返回,可能是阻塞者偶然退出,仍需对应代码或任务层面的原因。

继续排查

PostgreSQL提示连接数已满怎么办;PostgreSQL磁盘占用上涨:WAL与日志排查。

技术依据:[1] PostgreSQL活动统计视图;[2] PostgreSQL锁机制;[3] PostgreSQL pg_blocking_pids。适用版本见本文说明。