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等提示重点关注stateidle 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_scan0 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 THRESHOLD10 # 分钟 EMAILdbaexample.com BLOCKED$(psql -U postgres -c SELECT count(*) FROM pg_stat_activity WHERE wait_event_typeLock 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_typeLock AND now()-query_start interval ${THRESHOLD} min; | mail -s PostgreSQL Blocked Queries Alert $EMAIL fi5. 典型场景案例分析5.1 案例一未提交事务症状多个查询等待ShareLock阻塞会话状态为idle in transaction解决方案-- 查找未提交事务 SELECT pid, now()-xact_start AS duration, query FROM pg_stat_activity WHERE stateidle 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_typeLock 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_typeLock;在实际运维中我发现大多数阻塞问题都源于应用层的事务管理不当。建议开发团队遵循短事务原则任何事务都不应超过业务必需的最短时间。对于报表类查询考虑使用REPEATABLE READ隔离级别或建立专用副本。