PostgreSQL阻塞查询检测与解决方案

1. PostgreSQL阻塞查询问题概述

在PostgreSQL数据库运维过程中，阻塞查询(Blocked Queries)是最常见的性能瓶颈之一。当某个会话持有锁资源而长时间不释放时，其他需要相同锁资源的会话就会被阻塞，导致系统响应变慢甚至完全卡死。这种情况在OLTP系统中尤为常见，特别是在高并发写入场景下。

重要提示：阻塞不同于死锁(Deadlock)。死锁是PostgreSQL能够自动检测并解决的，而阻塞需要DBA人工干预才能解除。

我最近处理过一个典型的生产案例：某电商平台的订单提交接口在促销期间频繁超时，经排查发现是因为库存扣减操作被长时间运行的报表查询阻塞。通过本文介绍的方法，我们快速定位并解决了问题。

2. 阻塞检测的核心技术

2.1 PostgreSQL锁机制基础

PostgreSQL采用多版本并发控制(MVCC)机制，配合多种锁类型来管理并发访问：

• 表级锁：ACCESS SHARE（最弱）、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE（最强）
• 行级锁：FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE、FOR KEY SHARE
• 咨询锁：应用层控制的特殊锁

锁冲突矩阵决定了哪些锁可以共存。例如，当一个会话持有ROW EXCLUSIVE锁（通常由UPDATE语句获取）时，其他会话尝试获取相同行的FOR UPDATE锁就会被阻塞。

2.2 阻塞查询检测方法

2.2.1 使用pg_stat_activity视图

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement 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;

这个查询会返回所有被阻塞的会话及其阻塞源的关键信息，包括：

• 被阻塞会话的PID和用户名
• 阻塞会话的PID和用户名
• 被阻塞的SQL语句
• 导致阻塞的SQL语句

2.2.2 使用pg_blocking_pids函数（PostgreSQL 9.6+）

SELECT pid, usename, query, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

这个更简洁的查询会列出所有被阻塞的会话及其阻塞源的PID。

2.3 高级阻塞分析技巧

2.3.1 锁等待超时设置

-- 设置锁等待超时（单位毫秒） SET lock_timeout = 5000; -- 5秒后自动取消被阻塞的查询

这个参数可以在会话级别设置，避免查询无限期等待。

2.3.2 查看锁详情

SELECT lock.locktype, lock.relation::regclass, lock.mode, lock.virtualtransaction, lock.pid, stat.usename, stat.query, stat.query_start, age(now(), stat.query_start) AS query_age FROM pg_catalog.pg_locks lock JOIN pg_catalog.pg_stat_activity stat ON lock.pid = stat.pid WHERE NOT lock.granted;

这个查询提供了更详细的锁信息，包括锁类型、锁模式、持有时间等。

3. 阻塞问题解决方案

3.1 即时解决方案

3.1.1 终止阻塞会话

-- 谨慎操作！先确认会话是否可以安全终止 SELECT pg_terminate_backend(blocking_pid) FROM ( SELECT blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks 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 WHERE NOT blocked_locks.GRANTED LIMIT 1 ) blockers;

警告：直接终止生产会话可能导致数据不一致，应作为最后手段使用。

3.1.2 优化阻塞查询

有时可以通过优化查询来减少锁持有时间：

• 添加适当的索引加速查询
• 减少事务范围（避免在事务中执行耗时操作）
• 使用更弱的锁模式（如FOR KEY SHARE替代FOR UPDATE）

3.2 长期预防措施

3.2.1 应用设计优化

• 短事务原则：保持事务尽可能短小
• 锁顺序：确保所有事务以相同顺序获取锁
• 重试机制：对可能被阻塞的操作实现自动重试

3.2.2 监控告警设置

-- 创建阻塞监控视图 CREATE VIEW blocking_queries AS SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement, now() - blocked_activity.query_start AS blocked_duration, now() - blocking_activity.query_start AS blocking_duration 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;

可以定期查询此视图或设置监控系统在阻塞超过阈值时告警。

4. 实战案例解析

4.1 案例1：长事务阻塞DDL操作

现象：ALTER TABLE操作被阻塞，无法完成。

分析：

SELECT pid, usename, query, query_start, state FROM pg_stat_activity WHERE state = 'idle in transaction';

发现有一个已空闲但未提交的事务持有锁。

解决方案：

1. 联系应用团队确认是否可以提交/回滚该事务
2. 如无法联系，评估风险后终止该会话

4.2 案例2：批量更新阻塞关键业务查询

现象：订单支付接口响应变慢，发现被后台批量作业阻塞。

解决方案：

1. 将批量作业拆分为小批次处理
2. 在业务低峰期执行批量作业
3. 为批量作业设置较低的lock_timeout

4.3 案例3：应用连接池配置不当

现象：连接池中的连接长时间保持事务打开状态。

解决方案：

1. 配置连接池的max_idle_time和idle_in_transaction_session_timeout
2. 确保应用正确关闭事务

5. 高级技巧与工具

5.1 使用pg_stat_statements分析问题查询

-- 先启用扩展 CREATE EXTENSION pg_stat_statements; -- 查看消耗资源最多的查询 SELECT query, calls, total_time, rows, 100.0 * total_time / NULLIF(sum(total_time) OVER(), 0) AS percentage FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

5.2 使用auto_explain记录慢查询执行计划

-- 在postgresql.conf中设置 shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '1s' auto_explain.log_analyze = on

5.3 使用pgBadger分析日志

pgBadger可以分析PostgreSQL日志，生成包含阻塞查询统计的HTML报告。

6. 性能优化建议

1. 索引优化：确保频繁查询的字段有适当索引
2. 查询重构：避免在事务中执行不必要的操作
3. 连接管理：使用连接池并正确配置
4. 超时设置：合理配置statement_timeout和lock_timeout
5. 监控告警：建立完善的监控体系

在实际生产环境中，我通常会设置一个定期任务，每小时检查一次阻塞情况并记录到日志表中，这样可以帮助识别长期存在的阻塞模式。同时，对于关键业务系统，建议实现实时监控，当阻塞超过一定阈值时立即告警。