PostgreSQL / 系统课时
用 pg_stat_activity 定位异常事务
用 pg_stat_activity 定位异常事务
长事务和 idle in transaction 往往表现为锁等待、膨胀或连接占用。pg_stat_activity 能把会话状态、事务起点、最近查询、等待事件和应用来源放到同一张视图中,帮助你先定位异常会话,再决定是否继续追锁或终止连接。
学习目标
- 用
xact_start、query_start与state_change区分事务年龄、查询年龄和空闲时长。 - 结合
state、wait_event_type与wait_event判断会话是在执行、等待锁,还是客户端持有未结束事务。 - 用
application_name、client_addr和pid把数据库会话映射回具体应用实例。
先筛出真正有事务的异常会话
排查时不要只按“查询运行多久”排序。一个会话可能早已执行完 SQL,却停在 idle in transaction,此时 query_start 只是上一条语句的开始时间,真正需要关注的是更早的 xact_start 和进入当前状态的 state_change。
下面的查询筛出事务已持续至少 5 分钟,或处于 idle in transaction / idle in transaction (aborted) 的会话。使用 clock_timestamp() 可以避免诊断语句本身处于长事务时,now() 固定在事务起点造成年龄判断失真。
SELECT
pid,
usename,
application_name,
client_addr,
state,
xact_start,
query_start,
state_change,
clock_timestamp() - xact_start AS xact_age,
clock_timestamp() - state_change AS state_age,
backend_xid,
backend_xmin,
wait_event_type,
wait_event,
left(query, 120) AS query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND xact_start IS NOT NULL
AND (
clock_timestamp() - xact_start >= interval '5 minutes'
OR state IN ('idle in transaction', 'idle in transaction (aborted)')
)
ORDER BY xact_start;预期输出会列出当前实例中符合条件的会话;实际 PID、时间、IP、SQL 和等待事件会不同。
pid | application_name | state | xact_age | state_age | wait_event_type | wait_event | query
--------+------------------+---------------------+----------+-----------+-----------------+---------------+----------------------------
<PID1> | checkout-api | idle in transaction | 00:12:08 | 00:11:55 | Client | ClientRead | UPDATE orders SET ...
<PID2> | billing-worker | active | 00:06:21 | 00:00:09 | Lock | transactionid | UPDATE accounts SET ...跨角色查看完整活动信息通常需要超级用户权限或 pg_read_all_stats;权限不足时,你可能只能看到自己的会话详情,其他会话的部分字段会被隐藏。
正确解释三个时间字段
xact_start 是当前事务开始时间,最适合衡量“这个事务已经活了多久”。长事务即使暂时没有运行 SQL,也可能继续持有锁或旧快照,因此它通常是事务治理的第一排序键。
query_start 表示当前查询开始时间;当 state 不是 active 时,它表示上一条查询的开始时间。对 idle in transaction 来说,不应把它误读成“当前 SQL 仍执行了这么久”。
state_change 表示会话最近一次进入当前状态的时间。对于 idle in transaction,clock_timestamp() - state_change 可以近似理解为“已经空等客户端多久”;但对于正在等待锁的 active 会话,它并不是精确的锁等待时长。
用状态和等待事件判断下一步
当 state = 'active' 且 wait_event_type = 'Lock' 时,会话正在执行 SQL,但被锁阻塞;这时应继续根据 pid 查询阻塞者,而不是把它当成长 SQL 直接终止。
当 state = 'idle in transaction' 且 wait_event_type = 'Client'、wait_event = 'ClientRead' 时,PostgreSQL 通常是在等待客户端发下一条命令,真正的问题是应用打开事务后没有及时提交或回滚。idle in transaction (aborted) 则说明事务已经报错但客户端仍未 ROLLBACK,连接会一直停在失败事务中。
如果 wait_event 为空且状态为 active,说明当前没有被 PostgreSQL 已知等待事件挂起;此时要结合 SQL、执行计划和资源指标判断是否只是计算量大。wait_event 非空也不等于“数据库坏了”,它只是告诉你后端当前在等什么。
从数据库会话追到应用代码
定位异常事务后,先用 application_name、client_addr、usename 和 pid 找到负责它的服务实例,再检查事务边界:是否忘记提交、异常分支漏回滚、连接池把关闭连接误当成结束事务,或业务代码在事务内等待远程 HTTP。
修复应用后,再运行同一筛选 SQL:异常 PID 应消失,新事务的 xact_age 应保持在业务可接受范围内。生产环境还可以配置 idle_in_transaction_session_timeout 作为兜底,但它只能限制后果,不能替代对事务边界和慢调用的修复。