1. 问题现象与核心场景剖析遇到PostgreSQL里一条SQL语句比如一个看似简单的TRUNCATE或者一个复杂的UPDATE在客户端执行后光标就一直转啊转界面卡在那里一动不动。你等了几分钟甚至十几分钟它既不报错给你一个痛快也不执行完成给你一个结果。这种“薛定谔的SQL”——既不死也不活的状态最是让人抓狂。这通常意味着你的语句陷入了某种等待最常见的就是锁等待。数据库内部有一套复杂的锁机制来保证数据的一致性当一个会话Session持有了另一个会话想要的锁时后者就只能乖乖排队在pg_stat_activity视图里它的wait_event_type和wait_event字段就会告诉你它在等什么。这种现象绝非偶然它背后是数据库并发控制的核心体现。不同于应用层的“卡死”数据库语句卡住往往是因为它在等待一个它认为很快会释放的资源只是这个“很快”可能因为前一个事务设计不当而被无限拉长。理解并解决这类问题是每个PostgreSQL DBA和开发者的必修课。无论是新手在测试环境误操作还是老手在生产环境面对突发流量都可能撞上这堵“无形的墙”。接下来我们就从根上拆解看看怎么把这堵墙给拆了。2. 诊断工具箱定位卡住元凶的实战步骤当语句卡住时盲目重启服务或杀死连接是下策。正确的做法是像侦探一样利用PostgreSQL提供的强大工具箱层层深入定位问题根源。整个诊断流程的核心是pg_stat_activity系统视图它是我们观察数据库当前所有会话状态的“监控大屏”。2.1 第一步锁定问题会话与等待事件首先我们需要找到那个“卡住”的会话以及它到底在等什么。连接到数据库最好使用具有超级用户权限的账户如postgres执行以下查询SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event, query, query_start, now() - query_start AS duration FROM pg_stat_activity WHERE state ! idle AND query NOT LIKE %pg_stat_activity% -- 排除掉这个查询自身 ORDER BY duration DESC;这个查询结果会告诉我们pid: 会话的进程ID这是后续操作如终止会话的关键标识。state: 会话状态。active表示正在执行查询idle in transaction表示事务已开启但当前未执行语句这常常是锁问题的源头idle是空闲状态通常无害。wait_event_type和wait_event: 这是诊断的黄金指标。如果会话在等待这里会显示等待的类型和具体事件。例如Lock类型下的relation表示在等待表锁transactionid表示在等待事务结束。query: 正在执行或最后执行的SQL语句。duration: 该查询已经执行了多久。卡住的语句通常duration会异常地长。通过这个列表你能快速找到那个duration最长、状态为active或idle in transaction且wait_event不为空的会话。记下它的pid和query。2.2 第二步深挖锁依赖关系找到了等待的会话我们称之为“受害者”下一步是找出“加害者”——谁持有了它需要的锁PostgreSQL提供了pg_locks和pg_blocking_pids函数来理清锁的依赖链。方法A使用内置函数快速定位SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) 0;这个查询会直接列出所有正在被阻塞的会话pid以及阻塞它的会话pid数组。结果一目了然是最高效的初步定位方法。方法B手动关联查询获取详细信息如果你想获得更详细的锁信息比如锁的类型、关联的关系表、事务ID等可以执行更复杂的关联查询SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocked_activity.query AS blocked_query, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocking_activity.query AS blocking_query, blocked_locks.locktype, blocked_locks.relation::regclass AS locked_relation, blocked_locks.mode AS blocked_mode, blocking_locks.mode AS blocking_mode 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;这个查询逻辑是找出所有未授予的锁NOT granted然后通过锁的各个维度关系、事务ID、虚拟事务ID等去匹配已授予的锁从而找到阻塞者。它信息全面但稍微复杂。实操心得在紧急故障处理时优先使用pg_blocking_pids()函数它能秒级定位阻塞链的源头。详细查询更适合在问题复盘或复杂死锁分析时使用。务必注意查询pg_locks视图本身也可能需要短暂的AccessShareLock在极端锁竞争下可能稍有延迟但这通常是可接受的。2.3 第三步检查长事务与预备事务除了显式的行锁、表锁一个长时间未提交或回滚的事务长事务是锁问题的常见温床。它会持有其修改过程中产生的所有锁直到事务结束。同时失败的两阶段提交2PC留下的“预备事务”也会持锁不释放。检查长事务SELECT pid, usename, application_name, client_addr, state, backend_xmin, backend_xid, query, now() - xact_start AS xact_duration FROM pg_stat_activity WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL ORDER BY GREATEST(backend_xmin, backend_xid);关注xact_duration很长的事务。backend_xmin和backend_xid显示了该会话涉及的最老事务ID对于判断哪些事务阻碍了VACUUM清理也很有用。检查预备事务SELECT gid, prepared, owner, database, transaction AS xid FROM pg_prepared_xacts;如果有记录返回说明存在预备事务。它们会一直持有锁直到被COMMIT PREPARED或ROLLBACK PREPARED。3. 根因解析为什么锁会发生定位到阻塞链后我们需要理解其背后的原因才能从根本上预防。PostgreSQL的锁机制非常精细从表级锁到行级锁从事务锁到咨询锁。3.1 锁的冲突矩阵与常见场景PostgreSQL锁有多个层级和模式。表级锁的冲突是许多问题的根源。例如ACCESS EXCLUSIVE锁这是最强的锁与所有其他锁模式冲突。TRUNCATE、DROP TABLE、ALTER TABLE的大部分操作以及显式锁表LOCK TABLE ... IN ACCESS EXCLUSIVE MODE会获取此锁。一个正在进行的TRUNCATE会阻塞其他所有试图访问该表的操作包括简单的SELECT。SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE这些锁的冲突关系各不相同。比如CREATE INDEX CONCURRENTLY会获取SHARE UPDATE EXCLUSIVE锁它只与SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE冲突而不会阻塞SELECT。典型场景一DDL操作阻塞DML这是最经典的场景。开发人员在业务高峰期或者在一个未结束的事务中执行了ALTER TABLE ADD COLUMN。这条DDL需要ACCESS EXCLUSIVE锁它会等待该表上所有现有的锁释放并且一旦获得就会阻塞后续所有对该表的操作。如果此时有一个慢查询或一个未提交的事务正持有该表的ACCESS SHARE锁例如一个长时间的SELECTDDL就会卡住进而导致后续所有访问此表的请求排队。典型场景二未提交的长事务应用程序中如果开启了事务BEGIN执行了一些修改操作UPDATE、DELETE但既没有COMMIT也没有ROLLBACK这个事务就会一直处于idle in transaction状态。它持有的所有行锁甚至表锁都不会释放。任何尝试修改相同数据行的事务都会被阻塞。在ORM框架如Hibernate、MyBatis中如果连接池配置不当或业务代码异常导致事务未正确关闭就极易产生此问题。典型场景三锁升级与死锁两个事务以不同的顺序请求资源如表、行的锁可能导致循环等待即死锁。PostgreSQL的死锁检测机制会定期deadlock_timeout默认1秒扫描并回滚其中一个事务来打破僵局。但在此之前相关会话会表现为卡住。更隐蔽的是“锁饥饿”一个需要弱锁的频繁短事务可能因为一直排队等待一个长事务持有的强锁而无法执行。3.2 系统级资源瓶颈的伪装有时语句卡住不完全是逻辑锁的问题。系统资源耗尽也会导致类似现象但通常在wait_event上有所区别。IO等待如果磁盘性能达到瓶颈例如正在做大量写入或备份wait_event可能显示为IO相关的等待如DataFileRead、DataFileWrite。此时语句是在等待物理IO完成。CPU或内存竞争极端情况下系统负载极高进程调度缓慢也可能让语句执行看起来“卡住”。此时在操作系统层面如用top、htop命令会看到CPU或内存使用率异常。复制延迟在流复制环境下如果配置了同步复制synchronous_commit on或remote_apply主库上的提交必须等待至少一个备库的确认。如果备库延迟高或宕机主库的写事务也会被卡住等待事件通常是SyncRep。注意事项区分“锁等待”和“资源等待”至关重要。pg_stat_activity中的wait_event_type是首要判断依据。Lock类型指向锁竞争而IO、Activity、Timeout等类型则指向系统资源或内部操作。解决资源问题需要从硬件、系统配置或数据库参数如shared_buffers、work_mem、检查点配置入手。4. 应急处理与根治方案诊断完成后就要采取行动。行动分为两步应急处理快速恢复业务和根治方案避免再次发生。4.1 应急处理安全终止会话找到阻塞源头后如果确认该会话可以中断例如它是一个被遗忘的查询或一个可以重试的操作最直接的方法是使用pg_terminate_backend()函数终止它。操作步骤再次确认阻塞链。假设通过pg_blocking_pids()找到源头会话的pid是12345。尝试向该会话发送终止信号SELECT pg_terminate_backend(12345);如果返回t表示信号已发送。该会话会被强制终止它正在执行的事务会回滚它持有的所有锁将被释放。观察被阻塞的会话是否恢复正常执行。可以再次查询pg_stat_activity确认。重要警告与技巧pg_terminate_backend()是“强制杀死”类似于kill -9。如果被杀会话正在进行一个大的写事务回滚可能需要时间在此期间锁可能不会立即释放。更温和的方式是先使用pg_cancel_backend()它类似于发送CtrlC尝试取消当前查询而非整个会话。对于只是查询卡住的情况可能更合适SELECT pg_cancel_backend(12345);绝对不要在生产环境盲目批量终止会话尤其是那些持有重要未提交事务的会话。务必先确认会话的性质是什么应用、谁发起的、执行什么操作。终止pid为12345的会话后如果发现另一个之前被它阻塞的会话pid为67890变成了新的阻塞源头这是正常的。因为67890可能立刻获得了锁并开始执行一个本身也很慢的查询或者它本身也是一个长事务。你需要继续分析新的阻塞链。4.2 根治方案从开发到运维的防御体系杀掉会话只是治标优化设计和流程才能治本。1. 事务设计最小化原则事务应尽可能短小。尽快提交或回滚释放锁。实操在业务代码中避免在事务内执行不必要的网络调用、文件IO或长时间的计算。将事务范围严格限定在必须原子执行的数据库操作内。对于ORM要清晰理解其事务边界管理。2. 谨慎使用DDL特别是强锁操作变更窗口像TRUNCATE、ALTER TABLE这类需要ACCESS EXCLUSIVE锁的操作必须在业务低峰期或维护窗口进行。使用并发索引创建索引尽量使用CREATE INDEX CONCURRENTLY它避免了写锁虽然耗时更长且不能在一个事务内完成但对业务影响最小。设置锁超时在会话或事务级别设置lock_timeout参数例如SET lock_timeout 5s;。这样当一个语句获取锁超过指定时间后它会自动失败并报错而不是无限期等待便于问题快速暴露和重试机制介入。3. 监控与告警监控长事务定期扫描pg_stat_activity对state idle in transaction且持续时间超过阈值如1分钟的会话发出告警。监控锁等待监控pg_stat_activity中wait_event_type Lock且等待时间过长的会话。监控预备事务定期检查pg_prepared_xacts如有残留记录立即告警。可以使用Prometheus Grafana配合pg_stat_statements、pg_stat_activity等扩展搭建完善的监控面板。4. 连接池与语句超时配置连接池使用PgBouncer或Pgpool-II等连接池并正确配置其事务模式。避免应用层连接泄漏导致的事务未结束。语句超时设置statement_timeout参数例如SET statement_timeout 30s;防止单个慢查询耗尽资源、长期持锁。5. 应用层重试与降级逻辑对于因锁超时lock_timeout或语句超时statement_timeout失败的操作在应用层设计合理的重试机制最好是指数退避重试。在架构上考虑降级方案例如当核心表更新被锁时能否先操作缓存或写入队列异步处理。5. 高级疑难杂症与深度排查有些锁问题隐藏得更深需要更专业的工具和知识。5.1 排查死锁与锁升级当pg_blocking_pids()显示出一个循环依赖或者你怀疑是死锁时可以查看PostgreSQL的日志log_line_prefix中需包含%p等。死锁发生时数据库会在日志中记录类似以下信息ERROR: deadlock detected DETAIL: Process 12345 waits for ShareLock on transaction 987654; blocked by process 67890. Process 67890 waits for ShareLock on transaction 123456; blocked by process 12345. HINT: See server log for query details.数据库会自动选择一个“代价最小”的事务进行回滚。排查时需要分析日志中记录的等待关系梳理出事务1和事务2的完整执行语句序列找出锁请求顺序不一致的根源。锁升级在PostgreSQL中并不像某些数据库那样是自动行为但不当的SELECT ... FOR UPDATE或LOCK TABLE语句可能无意中获取了过强的锁。使用EXPLAIN (ANALYZE, BUFFERS)分析查询计划看是否因全表扫描导致FOR UPDATE锁住了大量不必要的行。5.2 扩展Extension与后台进程持锁某些PostgreSQL扩展或者后台维护进程也可能持有锁。例如逻辑复制逻辑复制槽可能会阻止VACUUM清理旧的WAL日志和表元组间接导致表膨胀和性能下降在极端情况下可能影响并发。自动清理AutoVacuum虽然AutoVacuum通常使用较弱的锁但在处理大量死元组时其最终的VACUUM或ANALYZE阶段也可能与某些DDL或强锁操作冲突。监控pg_stat_progress_vacuum视图可以了解其进度。第三方扩展一些管理类或工具类扩展可能会在后台执行需要锁的操作。排查时除了pg_stat_activity也要关注pg_locks中那些不属于你应用会话的pid可能是后台进程PID。结合pg_stat_activity和pg_locks可以查询所有持锁的会话详情SELECT l.pid, a.usename, a.application_name, a.state, a.query, l.locktype, l.relation::regclass, l.mode, l.granted FROM pg_locks l LEFT JOIN pg_stat_activity a ON l.pid a.pid WHERE l.pid ! pg_backend_pid() ORDER BY l.pid;5.3 性能视图与历史分析对于间歇性、难以复现的锁问题需要依赖历史数据。启用pg_stat_statements这个扩展记录了所有SQL语句的执行统计信息需在postgresql.conf中设置shared_preload_libraries pg_stat_statements。通过分析其视图可以找出哪些高频率或高耗时的语句最可能引发锁竞争。配置详细日志调整log_line_prefix包含时间戳、会话ID、事务ID等。设置log_lock_waits on这样当任何会话等待锁的时间超过deadlock_timeout时就会在日志中生成一条记录这对于追踪锁等待非常有用。使用性能监控工具如pgBadger日志分析器、PoWA性能仪表盘等它们能聚合历史性能数据帮助你发现锁等待的趋势和模式。6. 预防性架构与设计思考从根本上减少锁问题需要在架构和设计之初就进行考虑。1. 合理的物理设计表分区对大表进行分区可以将锁的粒度从整表缩小到单个分区。对某个分区的TRUNCATE或大量更新不会阻塞其他分区的访问。选择合适的主键使用高并发度的主键如UUID、雪花算法ID避免序列生成的整型主键在插入时的尾部热点争用。2. 乐观锁与悲观锁的取舍悲观锁即SELECT ... FOR UPDATE。适用于冲突频率高的场景但会主动加锁增加死锁风险。使用时务必确保事务短小且锁顺序一致。乐观锁不在数据库层面加锁而是在应用层检查数据版本如通过UPDATE ... SET ... WHERE version x。适用于冲突频率低的读多写少场景。失败后由应用层重试。这能极大减少数据库锁竞争。3. 读写分离与队列削峰对于读多写少的业务使用读写分离将读流量导向只读副本减轻主库压力间接减少写锁的竞争窗口。对于高并发写入场景可以考虑引入消息队列如RabbitMQ、Kafka。将直接的写请求先放入队列后端服务异步消费队列并写入数据库。这样可以将突发的、并发的写请求转为串行或可控并发的处理彻底避免数据库层面的锁冲突高峰。4. 定期健康检查与压力测试建立定期的数据库健康检查脚本涵盖长事务、锁等待、索引使用、表膨胀等。在上线前对核心业务场景进行压力测试模拟真实并发观察数据库的锁等待和死锁情况提前发现潜在的设计缺陷。处理PostgreSQL语句卡住的问题是一个从应急到根治、从现象到本质的系统性工程。它考验的不仅是故障排查的技巧更是对数据库并发原理、应用设计模式和系统架构的深刻理解。每一次成功的排查和解决都是对系统稳定性和团队技术能力的一次加固。记住最快的解决方式不一定是pg_terminate_backend()而是在设计和开发阶段就考虑到并发与锁防患于未然。