1. 问题现象当你的SQL语句在PostgreSQL中“假死”如果你正在管理或开发基于PostgreSQL的应用迟早会遇到一个令人抓狂的场景你在客户端比如psql、pgAdmin或应用代码里执行了一条SQL语句然后光标就卡在那里一直转圈。它不报错也不返回任何结果就像掉进了一个时间静止的黑洞。你等了一分钟、五分钟甚至更久它依然“活着”连接没断但就是没有任何进展。这种情况我们通常称之为语句“卡住”或“挂起”。它比直接报错更棘手因为错误信息至少给了你一个排查方向而“假死”则让你无从下手只能干瞪眼。从网络热词来看truncate、锁、pg_stat_activity是高频关联词这已经为我们指明了最可能的罪魁祸首锁竞争和长事务。简单来说你的语句很可能在等待某个它需要的资源最常见的是某张表或某行数据的锁而这个资源正被另一个会话Session长时间占用着。你的会话进入了等待队列如果那个占用的会话不释放资源你的等待就可能无限期持续下去。在深入排查之前我们先明确一个核心认知PostgreSQL是一个多版本并发控制MVCC的数据库。它的“锁”机制是为了在并发环境下保证数据的一致性。大多数时候它工作得很好但不当的SQL设计、缺失的索引、未提交的事务都可能导致锁的持有时间远超预期从而引发我们遇到的“卡住”问题。2. 第一响应使用系统视图快速诊断当语句卡住时盲目重启服务或杀死连接是下策。正确的第一步是“望闻问切”利用PostgreSQL内置的“仪表盘”——系统视图来诊断。这里最核心的工具就是pg_stat_activity。2.1 探查所有活动会话打开另一个数据库连接非常重要不要用被卡住的同一个会话执行以下SQLSELECT pid, -- 进程ID也是杀进程的关键标识 usename, -- 用户名 application_name, -- 应用名称如psql, JDBC, 等 client_addr, -- 客户端IP地址 state, -- 状态active, idle, idle in transaction, 等 wait_event_type, -- 等待事件类型Lock, IO, 等 wait_event, -- 具体的等待事件 query, -- 正在执行或最后执行的SQL语句 query_start, -- 查询开始时间 xact_start, -- 事务开始时间 backend_start -- 后端进程启动时间 FROM pg_stat_activity WHERE state ! idle -- 过滤掉完全空闲的连接 ORDER BY xact_start; -- 按事务开始时间排序老的在前关键字段解读state ‘active’: 表示该后端正在执行查询。state ‘idle in transaction’:这是一个危险信号表示会话在一个打开的事务中但当前没有执行查询。这个事务可能持有着锁。wait_event_type和wait_event: 如果这里显示Lock相关的值如relation,tuple,transactionid说明该会话正在等待一个锁。这是语句卡住的直接证据。query: 查看正在运行的SQL。如果看到UPDATE,DELETE,TRUNCATE, 或加了FOR UPDATE的SELECT这些是常见的锁来源。xact_start: 事务开始时间。如果一个事务开启了很久比如几小时但state是idle in transaction它极有可能是锁的源头。2.2 定位锁的持有者与等待者仅仅知道谁在等待还不够我们需要找到“罪魁祸首”——谁持有了被等待的锁。PostgreSQL提供了pg_locks系统视图但直接查询比较晦涩。更有效的方法是关联pg_stat_activity和pg_locks。以下是一个经典的锁等待链查询语句它能清晰地展示“谁阻塞了谁”SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocked_activity.query AS blocked_statement, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, 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; -- 关键只查未授予的锁即正在等待的锁执行这个查询后你会得到类似这样的结果blocked_pidblocked_userblocked_statementblocking_pidblocking_userblocking_statement12345app_userUPDATE orders SET status‘shipped’ WHERE id100;67890batch_userSELECT * FROM orders WHERE customer_id1 FOR UPDATE;这个结果一目了然进程67890执行的SELECT … FOR UPDATE语句在orders表的相关行上持有了一个行级排他锁导致进程12345的UPDATE语句无法获得锁而等待。注意pg_stat_activity.query字段可能只显示当前正在执行的语句。如果阻塞者处于idle in transaction状态query字段可能显示为空或是之前已完成的语句。此时blocking_statement列可能为空但blocking_pid和blocking_user仍然能告诉你谁是阻塞源。这时就需要结合事务开始时间(xact_start)和应用程序逻辑来推断它可能做了什么。3. 根因剖析为什么锁会被长时间持有找到阻塞者之后我们要问它为什么长时间不释放锁根据我的经验主要有以下几类原因。3.1 未提交或挂起的长事务这是最常见的原因没有之一。应用程序中的代码缺陷或异常处理不当可能导致事务开始后既没有提交(COMMIT)也没有回滚(ROLLBACK)。典型场景编程框架的默认行为某些ORM框架在非查询操作后可能默认开启事务如果开发者没有显式控制事务可能随着HTTP请求结束而悬空取决于连接池配置。异常处理缺失在BEGIN … EXCEPTION … END的PL/pgSQL块中如果异常被捕获但后续没有COMMIT或ROLLBACK事务会继续保持。交互式操作遗忘在psql中手动执行了BEGIN或启动了一个事务块然后去做其他事情忘记了提交。如何识别在pg_stat_activity中寻找state ‘idle in transaction’且xact_start时间非常早的会话。3.2 低效或缺失索引的查询一条执行缓慢的UPDATE或DELETE语句本身就会长时间持有锁。如果WHERE条件中的列没有索引PostgreSQL可能被迫进行全表扫描。在全表扫描过程中它会对扫描过的行依次加锁具体锁类型和范围取决于事务隔离级别这会极大地增加锁冲突的概率和范围。例如UPDATE large_table SET flag true WHERE category ‘old’;如果category列没有索引这个更新会锁住大量甚至全部行阻塞其他任何需要修改此表的操作。3.3 不恰当的锁模式与操作某些SQL语句会申请强锁如果使用不当极易引发问题。LOCK TABLE语句显式锁表尤其是ACCESS EXCLUSIVE模式会阻塞所有其他操作。TRUNCATE TABLE热词中提到了它。TRUNCATE在PostgreSQL中是一个DDL操作它会申请ACCESS EXCLUSIVE锁这是最强的锁。如果执行TRUNCATE时有其它任何活动事务哪怕是只读查询正在访问该表TRUNCATE就必须等待。反过来一个正在执行的、缓慢的TRUNCATE也会阻塞所有后续访问。SELECT … FOR UPDATE/SHARE这些语句显式地申请行级锁。如果在一个大结果集上使用FOR UPDATE或者在应用程序逻辑中持有这些锁的时间过长例如在应用层进行复杂的业务计算就会成为阻塞点。长运行的DDL操作如给大表添加字段尤其是带有默认值的、创建索引CONCURRENTLY方式除外等都会长时间持有排他锁。3.4 连接池与会话管理问题连接池如PgBouncer, HikariCP配置不当可能导致“僵尸事务”。例如连接池在将连接归还给池之前没有确保连接处于干净状态即没有未提交的事务。当下一个应用线程拿到这个“脏连接”时它可能无意中继续在一个旧事务中操作或者被之前的事务持有的锁所影响。4. 应急处理安全地终止阻塞进程诊断出阻塞源后如果它确实是一个异常进程最直接的解决方法是终止它。但务必谨慎操作4.1 使用pg_terminate_backend()函数这是终止后端进程的标准函数。-- 终止进程ID为 67890 的会话 SELECT pg_terminate_backend(67890);重要注意事项权限你需要是超级用户superuser或者是该阻塞进程的所有者。影响这相当于“拔电源”。正在该会话中执行的事务会立即回滚。如果那是一个正在修改大量数据的事务回滚可能会花费一些时间并产生WAL日志。客户端反应客户端会收到类似FATAL: terminating connection due to administrator command的错误。应用程序必须有良好的连接异常处理机制。确认再操作务必通过pg_stat_activity再次确认你要终止的PID避免误杀重要业务进程。4.2 更温和的取消pg_cancel_backend()如果阻塞进程正在执行一个查询而你只是想停止这个查询而不是整个会话可以使用pg_cancel_backend()。这类似于在psql中按CtrlC。SELECT pg_cancel_backend(67890);这会导致当前查询被取消事务本身不会回滚除非这个查询是事务中的唯一操作且被取消。如果该会话处于idle in transaction状态这个函数可能无效因为已经没有正在运行的查询可以取消了此时仍需使用pg_terminate_backend。警告无论是取消还是终止都只是“治标”。如果不找到根本原因并修复问题很可能再次出现。5. 根治与预防从架构和开发习惯入手应急处理救火之后必须建立防火机制。以下是我在实践中总结的有效预防措施。5.1 实施事务超时控制这是防止长事务最有效的防线。可以在PostgreSQL服务器层面或会话层面设置。会话级设置在应用连接初始化后执行。SET statement_timeout ‘30s’; -- 单条语句超时 SET idle_in_transaction_session_timeout ‘5min’; -- 空闲事务超时强烈推荐idle_in_transaction_session_timeout这个参数是PostgreSQL 9.6引入的“神器”。它自动清理那些被遗忘的、处于idle in transaction状态的会话从根本上杜绝一类锁问题。数据库/用户级设置修改postgresql.conf或使用ALTER DATABASE/ALTER ROLE。ALTER DATABASE mydb SET idle_in_transaction_session_timeout ‘10min’; ALTER ROLE app_user SET statement_timeout ‘1min’;5.2 优化查询与索引策略为高频条件字段添加索引分析慢查询日志 (log_min_duration_statement)确保UPDATE/DELETE/SELECT … FOR UPDATE的WHERE条件列有合适的索引。避免全表锁操作尽量不用LOCK TABLE。对于TRUNCATE确保没有其他活动事务在使用该表或在维护窗口期进行。缩小锁范围使用SELECT … FOR UPDATE SKIP LOCKED来跳过已被锁定的行避免等待。这在实现任务队列等场景时非常有用。加快事务提交在事务内部尽早执行会持有强锁的操作如修改核心表然后尽快提交减少锁的持有窗口。5.3 规范应用开发与框架使用事务边界最小化遵循“短事务”原则。只在必要时开启事务操作完成后立即提交或回滚。显式控制事务避免依赖框架的自动事务管理。在代码中清晰定义try { commit; } catch { rollback; } finally { close connection; }的逻辑。异常处理中务必回滚在任何捕获异常的代码块中如果无法继续必须执行事务回滚。连接池配置配置连接池的testOnBorrow或类似的健康检查选项确保归还到池里的连接是“干净”的没有未提交的事务。例如HikariCP可以配置connectionTestQuery“SELECT 1”但这不检查事务状态。更可靠的是在归还连接前由应用代码或连接池拦截器执行一次ROLLBACK。5.4 建立监控与告警体系不能等到业务被卡死才手动登录服务器查看。监控关键指标idle in transaction会话的数量和最长持续时间。锁等待的数量。长事务根据业务定义如超过1分钟的数量。配置告警当上述指标超过阈值时通过邮件、钉钉、企业微信等渠道告警。定期巡检定期运行诊断查询如第2部分的锁等待链查询生成报告。一个简单的监控查询示例用于查找长时间的空闲事务SELECT pid, usename, application_name, client_addr, now() - xact_start AS transaction_duration, query FROM pg_stat_activity WHERE state ‘idle in transaction’ AND now() - xact_start interval ‘5 minutes’ ORDER BY transaction_duration DESC;6. 进阶排查当常规手段失效时有时候问题可能更加隐蔽。例如等待事件显示的不是Lock而是IO或Activity相关。或者你遇到了“分布式锁”场景下的协调问题虽然热词中提到了Redis分布式锁但那属于应用层PostgreSQL内部锁是另一回事。6.1 检查系统资源与IO磁盘IO瓶颈如果wait_event_type是IO可能是磁盘速度跟不上。检查磁盘使用率、IOPS和延迟。使用iostat,iotop等工具。内存不足如果工作内存 (work_mem) 不足复杂的排序、哈希操作会使用磁盘临时文件导致性能骤降。监控交换分区swap使用情况。CPU或IO资源竞争服务器上其他进程包括其他数据库实例可能正在消耗大量资源。6.2 分析查询执行计划对于执行缓慢的阻塞语句获取其执行计划至关重要。EXPLAIN (ANALYZE, BUFFERS) 你的慢查询SQL;查看计划中是否有全表扫描Seq Scan、巨大的嵌套循环、不准确的行数估计等。BUFFERS选项可以帮助你了解查询产生了多少IO。6.3 考虑扩展与并发控制机制对于高并发更新同一行的极端场景“热点行”问题PostgreSQL的常规行锁可能成为瓶颈。此时可以考虑乐观锁在应用层使用版本号或时间戳检查减少数据库锁持有时间。排队将请求放入消息队列如RabbitMQ, Kafka由消费者串行处理从根本上避免锁竞争。应用层分片如果业务允许将数据逻辑拆分让更新分散到不同的行上。数据库的锁问题如同交通堵塞排查时需要像交警一样先找到事故点锁等待链再查明事故原因长事务、慢查询最后疏通道路终止进程并制定交规设置超时、优化查询以防再次发生。掌握pg_stat_activity和pg_locks这两个核心工具理解MVCC和锁的基本原理你就能从面对“假死”时的手足无措成长为从容应对的数据库“外科医生”。记住预防永远比治疗更重要将idle_in_transaction_session_timeout这类参数配好是从源头减少问题的最佳实践。