PostgreSQL阻塞查询检测与优化实战-平芜编程栈

1. 为什么需要关注PostgreSQL阻塞查询

在数据库运维过程中，阻塞查询就像交通堵塞中的头车——它不仅自己无法前进，还会导致后方所有依赖它的查询陷入等待状态。我曾在生产环境遇到过一起典型的阻塞案例：一个简单的报表查询阻塞了整个业务系统的更新操作长达2小时，直接导致业务中断。

PostgreSQL采用多版本并发控制(MVCC)机制，通常情况下读写操作不会相互阻塞。但当出现以下情况时，阻塞就会发生：

长时间运行的事务持有锁未释放
查询未正确使用索引导致全表扫描加锁
应用程序未正确处理事务生命周期
死锁检测未及时触发

2. 阻塞查询检测方法论

2.1 系统视图三剑客

PostgreSQL提供了三个关键系统视图来检测阻塞：

SELECT * FROM pg_locks; -- 当前锁状态 SELECT * FROM pg_stat_activity; -- 活动会话 SELECT * FROM pg_stat_all_tables; -- 表级统计

我通常使用这个组合查询来快速定位问题：

SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_query, blocking_activity.query AS blocking_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED;

2.2 关键字段解读

pid: 进程ID，终止会话时会用到
locktype: 锁类型（relation, tuple等）
mode: 锁模式（AccessShareLock, RowExclusiveLock等）
query_start: 查询开始时间，判断长事务
state: 会话状态（active, idle in transaction等）

提示：重点关注state='idle in transaction'的会话，这些通常是忘记提交的事务

3. 实战处理流程

3.1 紧急处理方案

当发现阻塞链时，我通常按照这个流程操作：

记录阻塞详情（包括完整的查询语句）
尝试联系阻塞会话的负责人
评估是否可以终止阻塞会话：
```
SELECT pg_terminate_backend(pid);
```
如果阻塞会话是关键业务，考虑终止被阻塞会话

3.2 根治措施

根据多年经验，90%的阻塞问题可以通过以下方式预防：

事务优化：
- 避免业务逻辑中使用长时间事务
- 设置语句超时：SET statement_timeout = '30s'
- 使用idle_in_transaction_session_timeout参数

索引策略：

-- 查找缺失索引 SELECT relname, seq_scan-idx_scan AS too_much_seq, CASE WHEN seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END FROM pg_stat_user_tables ORDER BY too_much_seq DESC;

锁监控：

-- 创建扩展 CREATE EXTENSION pg_stat_statements; -- 查询锁等待统计 SELECT wait_event_type, wait_event, COUNT(*) FROM pg_stat_activity WHERE wait_event IS NOT NULL GROUP BY 1,2 ORDER BY 3 DESC;

4. 高级监控方案

4.1 使用pg_stat_activity增强视图

我通常会创建这个视图方便日常监控：

CREATE VIEW blocked_queries_monitor AS SELECT now() - a.query_start AS duration, a.pid, a.usename, a.datname, a.state, a.wait_event_type, a.wait_event, a.query, l.mode, l.locktype, l.relation::regclass FROM pg_stat_activity a LEFT JOIN pg_locks l ON l.pid = a.pid WHERE a.state != 'idle' ORDER BY duration DESC;

4.2 自动化监控脚本

这个shell脚本可以定期检查并发送告警：

#!/bin/bash THRESHOLD=10 # 分钟 EMAIL="dba@example.com" BLOCKED=$(psql -U postgres -c "SELECT count(*) FROM pg_stat_activity WHERE wait_event_type='Lock' AND now()-query_start > interval '${THRESHOLD} min';" -t) if [ $BLOCKED -gt 0 ]; then psql -U postgres -c "SELECT now() as time, pid, usename, datname, query, wait_event_type, wait_event, now()-query_start as duration FROM pg_stat_activity WHERE wait_event_type='Lock' AND now()-query_start > interval '${THRESHOLD} min';" | mail -s "PostgreSQL Blocked Queries Alert" $EMAIL fi

5. 典型场景案例分析

5.1 案例一：未提交事务

症状：多个查询等待ShareLock，阻塞会话状态为idle in transaction

解决方案：

-- 查找未提交事务 SELECT pid, now()-xact_start AS duration, query FROM pg_stat_activity WHERE state='idle in transaction' ORDER BY duration DESC; -- 终止会话 SELECT pg_terminate_backend(pid);

5.2 案例二：缺少索引

症状：大量Seq Scan，等待RowShareLock

解决方案：

-- 识别热表 SELECT relname, seq_scan, seq_tup_read, seq_tup_read/seq_scan AS avg_tuples_per_scan FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 10; -- 添加适当索引 CREATE INDEX CONCURRENTLY idx_table_column ON table(column);

5.3 案例三：死锁循环

症状：多个会话相互等待形成环路

解决方案：

-- 死锁检测日志 ALTER SYSTEM SET deadlock_timeout = '1s'; SELECT pg_reload_conf(); -- 查看日志中的deadlock记录 SELECT pg_read_file(log_filename) FROM pg_ls_logdir() WHERE log_filename LIKE '%postgresql-%' ORDER BY log_filename DESC LIMIT 1;

6. 性能优化建议

锁参数调优：

-- 减少锁冲突 ALTER SYSTEM SET max_locks_per_transaction = 128; -- 加快死锁检测 ALTER SYSTEM SET deadlock_timeout = '1s';

连接池配置：
- 使用PgBouncer设置事务池模式
- 限制每个用户的最大连接数

监控指标：

-- 锁等待率 SELECT 100.0*sum(CASE WHEN wait_event_type='Lock' THEN 1 ELSE 0 END)/count(*) FROM pg_stat_activity; -- 平均锁等待时间 SELECT extract(epoch FROM avg(now()-query_start)) FROM pg_stat_activity WHERE wait_event_type='Lock';

在实际运维中，我发现大多数阻塞问题都源于应用层的事务管理不当。建议开发团队遵循"短事务"原则，任何事务都不应超过业务必需的最短时间。对于报表类查询，考虑使用REPEATABLE READ隔离级别或建立专用副本。