PostgreSQL 锁等待排查实战：从 pg_locks 到 Spring Boot 连接池的连锁阻塞

PostgreSQL 锁等待排查实战从 pg_locks 到 Spring Boot 连接池的连锁阻塞1. 一次“CPU 不高但接口全挂”的故障假设这样一个场景某天下午订单服务的接口大面积超时监控上看数据库 CPU 只有 20%慢查询日志里最新的记录还是半小时前的。运维重启应用后短暂恢复几分钟后又全员卡住。很多人第一反应是“数据库是不是挂了”或者“网络抖动”。但真正的原因往往更隐蔽某个事务在等锁它拿不到锁就不提交于是它占着数据库连接不放连接池里的连接被一个个借走后没有归还后面的请求拿不到连接只能排队到超时。表现出来是“接口全挂”本质却是一条锁等待链。这篇文章要解决的就是这类问题当接口集体超时、数据库却不忙时如何用 PostgreSQL 自带的视图把锁等待链还原出来并顺着链条找到那个“源头事务”以及它上面的应用代码。先给出一句话模型方便后面复述锁等待不只是一个数据库现象它是一条从行到事务、从事务到连接、从连接到线程的占用链链条任意一环被堵死上游的请求都会排队。2. 先把整体框架串起来在进入具体视图之前先建立一个全局图。一次典型的“更新订单”请求会经过下面这些角色应用线程Spring Boot 的 Tomcat 工作线程负责接收 HTTP 请求。连接池HikariCP负责把数据库连接借给线程用完归还。数据库会话PostgreSQL 为每个连接维护一个后端进程和一个会话状态。事务会话中开启的事务是持锁和等锁的主体。行锁事务对具体数据行的加锁记录。锁视图pg_locks记录锁与等待关系pg_stat_activity记录会话在干什么。把它们串起来看HTTP 请求 | v Tomcat 工作线程 ---- 借用 ---- HikariCP 连接池 | | | 等待超过 connectionTimeout | 借出一个物理连接 v v 请求超时返回 PostgreSQL 会话 (backend) | v 事务 BEGIN | v 对数据行加行级锁 | v pg_locks / pg_stat_activity 可见这里有一个关键点数据库里的锁等待和连接池的耗尽不是两个问题而是同一个问题的两端。数据库侧看到的是“事务在等锁”应用侧看到的是“线程在等连接”。排查时必须把两边对齐。还要区分两个容易混淆的东西概念谁在等等待对象典型现象锁等待数据库事务另一事务持有的锁pg_locks.granted false连接等待应用线程连接池里的空闲连接接口超时数据库空闲这两者会互相放大锁等待时间越长连接被占用的时间越长连接池越容易耗尽连接池耗尽后应用侧看起来像雪崩但数据库侧可能只有一条链条。3. 先看三个视图谁在等、等什么、等了多久PostgreSQL 提供了几个用于观测并发状态的系统视图它们的分工可以这样理解pg_stat_activity每个会话一行回答“这个会话在干什么、执行了多久、状态是什么”。pg_locks每把锁一行回答“谁锁了什么、有没有拿到、在等哪种锁”。pg_blocking_pids()给定一个被阻塞的进程号返回阻塞它的进程号列表是还原等待链的快捷函数。先看一个最小查询列出当前所有非空闲会话SELECTpid,usename,datname,state,wait_event_type,wait_event,now()-xact_startASxact_age,left(query,60)ASquery_headFROMpg_stat_activityWHEREstateidleORDERBYxact_start;预期输出类似pid | usename | state | wait_event_type | wait_event | xact_age | query_head ---------------------------------------------------------------------------------------- 8123 | app | active | Lock | transactionid | 00:04:12 | UPDATE orders SET status... 8150 | app | active | Lock | tuple | 00:04:10 | UPDATE orders SET status... 8177 | app | active | Client | ClientRead | 00:00:01 | COMMIT解读要点wait_event_type Lock说明它在等锁wait_event进一步说明等的是事务号还是行上的元组锁。xact_age是事务已经开启多久不是语句执行多久。事务开得久往往意味着它一直没提交。如果看到大量ClientRead那是会话在等应用发下一条命令通常是应用侧事务没关脚本里叫“idle in transaction”。这里最容易误解的是state active不等于“正在消耗 CPU”。一个会话可以在active状态下等锁CPU 完全不忙。判断是否真的在干活要看wait_event_type是不是Lock或IO。4. pg_locks把持锁和等锁画成一张表pg_locks每一行代表一把锁或一次加锁请求。核心字段不多但含义要弄清楚字段含义排查时怎么用locktype锁的对象类型常见有 relation、tuple、transactionid判断是表锁、行锁还是事务锁relation表或索引的对象号关联pg_class得到表名transactionid事务号事务锁等待时看这里mode锁模式如 RowExclusiveLock判断锁的强度granted是否已获得false表示正在等待pid持有或等待锁的进程号关联pg_stat_activity先看一个能直接回答“谁在等谁”的查询SELECTblocked.pidASblocked_pid,blocked.queryASblocked_query,blocker.pidASblocker_pid,blocker.queryASblocker_query,blocked.wait_eventFROMpg_stat_activity blockedJOINpg_locks blONbl.pidblocked.pidANDbl.grantedfalseJOINpg_locks klONkl.locktypebl.locktypeANDkl.databaseISNOTDISTINCTFROMbl.databaseANDkl.relationISNOTDISTINCTFROMbl.relationANDkl.transactionidISNOTDISTINCTFROMbl.transactionidANDkl.grantedtrueANDkl.pidbl.pidJOINpg_stat_activity blockerONblocker.pidkl.pid;这段 SQL 的思路是先在pg_locks里找到没拿到的锁请求再找到同一把锁上已经拿到的持有者然后把两个 pid 关联回会话。输出会显示“谁被谁挡住了”。不过手工连接条件多、容易写错。PostgreSQL 内置了更方便的函数SELECTpid,pg_blocking_pids(pid)ASblocking_pids,wait_event_type,wait_event,left(query,80)ASquery_headFROMpg_stat_activityWHEREcardinality(pg_blocking_pids(pid))0;pg_blocking_pids()直接返回阻塞当前进程的进程号数组。如果一行显示blocking_pids {8123}说明当前这个 pid 正在被 8123 挡住。它的边界也很明确它只回答“谁挡了谁”不告诉你锁是行锁还是事务锁也不告诉你这条链条再往上是哪个事务。真正要复原完整链条还是要结合pg_locks一起看。5. 锁等待链一条链而不是一个点很多文章讲到锁只讲“A 等 B”。但从排障角度看重要的是整条链。假设有三个事务 T1、T2、T3它们按下面的顺序执行T1: BEGIN; UPDATE orders SET statusPAID WHERE id100; -- 持有 id100 的行锁 T2: BEGIN; UPDATE orders SET statusSHIPPED WHERE id100; -- 等待 T1 T3: BEGIN; UPDATE orders SET statusCANCELLED WHERE id100; -- 等待 T1排在 T2 之后形成的关系是T1 (持有行锁未提交) ^ | T2 (等待行锁) ^ | T3 (等待行锁)此时如果应用只给 T2 设置了超时并回滚T3 依然在等但如果应用停止重试T2 和 T3 的等待会一直挂到超时。如果 T1 永远不提交整条链都不会动。可以先用pg_blocking_pids找到直接阻塞者再用递归查询展开整条链。下面这段 SQL 是一个简化版递归查询用来展示链条WITHRECURSIVE blockersAS(SELECTpid,pg_blocking_pids(pid)ASbps,0ASdepthFROMpg_stat_activityWHEREcardinality(pg_blocking_pids(pid))0UNIONALLSELECTb.pid,pg_blocking_pids(b.pid),d.depth1FROMblockers dJOINpg_stat_activity bONb.pidANY(d.bps)WHEREd.depth5)SELECTDISTINCTdepth,pid,bpsFROMblockersORDERBYdepth;注意这只是一个排查用的小工具不是生产定时任务因为递归在极端情况下可能重复展开。真正定位时拿到最顶端depth 最小且 bps 为空的前一层的 pid再用pg_stat_activity看它的事务开始时间和最近一次查询。这条最顶端的会话就是我们常说的“源头事务”。它往往不是报错最多的那个而是最沉默的那个它没有超时、没有报错只是迟迟不提交。6. 行级锁为什么更新同一行会互相排队PostgreSQL 的行级锁不是锁整行数据的“物理锁”而是在元组tuple上标记xmax表示这一行被哪个事务更新。两个事务更新同一行第二个事务会发现这行的最新版本还没提交于是等待。这里要区分几种锁模式它们在 READ COMMITTED 隔离级别下的行为不同操作锁模式行为UPDATE、DELETE、SELECT ... FOR UPDATERowExclusiveLock 行锁行被占用其他事务更新同一行要等普通SELECT无行锁走 MVCC 快照不阻塞也不被阻塞SELECT ... FOR UPDATE行级排他锁和 UPDATE 一样会排队CREATE INDEXShareLock阻塞写入可能长时间无法完成最容易误解的一点普通 SELECT 永远不参与行锁等待。如果你看到一个 SELECT 卡住多半不是行锁而是表锁、DDL 冲突或者连接池层面拿不到连接。区分方法是看pg_locks里它等的是relation还是tuple。下面用一个可以复现的最小实验证明行锁等待。准备一张表CREATETABLEorders(idbigintPRIMARYKEY,statustextNOTNULL,updated_at timestamptzDEFAULTnow());INSERTINTOorders(id,status)VALUES(100,CREATED);打开两个终端对应两个会话-- 会话 ABEGIN;UPDATEordersSETstatusPAIDWHEREid100;-- 不提交保持事务开启-- 会话 BBEGIN;UPDATEordersSETstatusSHIPPEDWHEREid100;-- 这里会一直等待直到会话 A 提交或回滚此时在第三个会话里执行第 3 节的查询就能看到会话 B 的wait_event_type Lock。如果会话 A 执行COMMIT会话 B 会立刻拿到锁并继续执行。这个实验说明行锁等待的持续时间等于“持锁事务从加锁到提交之间的时间”而不是 UPDATE 语句本身的时间。所以真正要治理的往往不是 SQL 快慢而是事务太长。7. 从数据库到连接池连锁阻塞是怎样发生的现在把视角切到应用侧。Spring Boot 默认使用 HikariCP 作为连接池。相关参数里下面几个和锁等待直接相关参数默认值作用maximumPoolSize10池中最多多少个物理连接connectionTimeout30000 ms线程等待可用连接的最长时间idleTimeout600000 ms连接空闲多久被回收maxLifetime1800000 ms连接最长存活时间当所有连接都被借出时后面的线程会在getConnection()上排队等到connectionTimeout后抛出SQLTransientConnectionException。这就形成了本文开头描述的场景数据库只有一个源头事务在等锁但应用侧几十个线程都在等连接。用一个时序图把连锁关系画清楚时间轴 ------------------------------------------------------------ T1 事务: BEGIN -- UPDATE id100 -- (不提交) ---------------- COMMIT 锁状态: 持锁 ................................. 释放 T2 事务: BEGIN -- UPDATE id100 [等待锁 20s] -------------- 执行 连接池: 借出 conn#1 给 T1一直不归还 借出 conn#2 给 T2阻塞在锁上也不归还 借出 ... 直到 maximumPoolSize 用尽 应用线程: thread-3 getConnection() [等待连接 30s] - 抛异常 thread-4 getConnection() [等待连接 30s] - 抛异常这张图想说明的是连接池耗尽不是根因而是锁等待的放大结果。如果只调大maximumPoolSize短期能缓解但长期只会把更多连接堆在同一个锁上让数据库侧的等待链更长。判断方向的方法很简单如果应用在报“获取连接超时”同时数据库pg_stat_activity里有多个会话等待Lock那就先排查锁等待而不是扩池。8. 一个可复现的 Spring Boot 示例下面给一个完整的、可以用本地 PostgreSQL 复现的 Spring Boot 示例。目标是模拟两个并发线程更新同一订单行观察连接池持有情况和锁等待。前置环境JDK 17 或以上。PostgreSQL 13 或以上已建库lockdemo。Maven 或 Gradle 可拉取 Spring Boot 依赖。先建表CREATETABLEorders(idbigintPRIMARYKEY,statustextNOTNULL,updated_at timestamptzDEFAULTnow());INSERTINTOorders(id,status)VALUES(100,CREATED);application.ymlspring:datasource:url:jdbc:postgresql://localhost:5432/lockdemousername:apppassword:apphikari:maximum-pool-size:3connection-timeout:5000核心代码importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.CommandLineRunner;importorg.springframework.boot.SpringApplication;importorg.springframework.boot.autoconfigure.SpringBootApplication;importorg.springframework.jdbc.core.JdbcTemplate;importorg.springframework.transaction.support.TransactionTemplate;importjava.util.concurrent.CountDownLatch;SpringBootApplicationpublicclassLockDemoApplicationimplementsCommandLineRunner{AutowiredprivateJdbcTemplatejdbcTemplate;AutowiredprivateTransactionTemplatetransactionTemplate;publicstaticvoidmain(String[]args){SpringApplication.run(LockDemoApplication.class,args);}Overridepublicvoidrun(String...args)throwsException{CountDownLatchstartednewCountDownLatch(1);CountDownLatchupdatednewCountDownLatch(1);Threadt1newThread(()-transactionTemplate.execute(status-{jdbcTemplate.update(UPDATE orders SET status ? WHERE id ?,PAID,100L);started.countDown();try{updated.await();}catch(InterruptedExceptione){Thread.currentThread().interrupt();}returnnull;}),t1-holder);Threadt2newThread(()-{try{started.await();transactionTemplate.execute(status-{jdbcTemplate.update(UPDATE orders SET status ? WHERE id ?,SHIPPED,100L);returnnull;});}catch(Exceptione){System.out.println(t2 失败: e.getClass().getSimpleName() - e.getMessage());}},t2-waiter);t1.start();t2.start();Thread.sleep(2000);updated.countDown();t1.join();t2.join();System.out.println(结束);}}运行步骤与预期结果启动应用后t1 先更新id100并持有行锁然后等待updated信号。t2 随后尝试更新同一行进入锁等待。2 秒后 t1 收到信号并提交t2 立刻拿到锁并完成更新。控制台不会出现 t2 失败因为等待时间远小于connection-timeout。如果把updated.countDown()改成 8 秒后再执行而connection-timeout保持 5 秒会发生什么t2 在锁上等不到提交连接也没归还如果池里连接数不足其他线程会先在连接上超时。你可以把池大小改成 1然后开三个线程验证这个连锁现象。容易改错的地方忘记让 t1 持有锁一段时间会看不到等待。把Transactional加在run方法上会导致整个启动流程在一个事务里现象和预期不符。使用自动提交模式时每条语句各自提交不会形成长事务。9. 定位源头事务之后怎么止血和修复定位到源头事务后处理分两步先止血再修复。止血手段-- 查看源头事务的详细信息SELECTpid,state,xact_start,now()-xact_startASage,queryFROMpg_stat_activityWHEREpid8123;-- 需要时终止该会话谨慎使用SELECTpg_terminate_backend(8123);pg_terminate_backend会强制结束会话并回滚其事务属于紧急手段。执行前必须确认该事务是否正在做重要写入否则可能造成数据不完整。修复方向通常有四个缩短事务把非数据库操作远程调用、文件读取移出事务范围。减小锁粒度避免全表更新尽量按主键或唯一索引更新少量行。统一加锁顺序多个事务更新多行时按相同顺序加锁避免交叉等待形成环。设置锁等待超时用lock_timeout让等待的事务主动失败避免无限期占用连接。其中lock_timeout是最容易被忽略但最有效的一道保险SETlock_timeout3s;UPDATEordersSETstatusPAIDWHEREid100;当等待超过 3 秒语句会以错误55P03 lock_not_available结束。应用层捕获后可以决定重试还是直接返回失败至少不会把连接永久占死。这里要注意lock_timeout是语句级或事务级设置需要在连接上执行如果使用连接池连接是复用的建议通过 HikariCP 的connection-init-sql或在事务开始时显式设置。10. 常见误区排查锁等待时下面这些判断很容易走偏误区实际情况数据库 CPU 不高就没有问题等锁的会话不消耗 CPU但会占用连接接口超时一定是数据库慢也可能是连接池拿不到连接调大连接池就能解决可能让更多事务堆在同一把锁上看到active就是在执行等锁时状态也是active普通 SELECT 不会有锁等待它可能等表锁或 DDL 锁锁等待会自动超时默认不超时除非设置了lock_timeout或statement_timeout这些误区的共同点是把数据库现象和应用现象割裂开看。只看一边很容易得出错误结论。11. 生产实践建议把上面的机制落到工程规范上可以形成几条可执行的约定事务边界尽量短不要在事务中做 HTTP 调用或大文件读写。所有涉及多行更新的批量操作按主键排序后执行降低死锁概率。关键业务语句设置合理的lock_timeout和statement_timeout避免无限期等待。连接池maximumPoolSize要和数据库max_connections匹配避免应用侧把库压垮。对idle in transaction状态设置告警这类会话是锁等待的常见来源。定期用第 4 节的查询巡检锁等待而不是等到故障才查。这些建议的共同逻辑是在应用层控制事务时长在数据库层设置等待上限把“无限等待”变成“有限失败”。12. 排障清单当出现“接口超时但数据库不忙”时可以按这个清单依次确认查pg_stat_activity找wait_event_type Lock或Client的会话。用pg_blocking_pids()找直接阻塞者。用递归查询展开完整等待链找到源头事务。看源头事务的xact_start和query定位对应的应用代码。看应用侧连接池指标确认是否已经耗尽。决定是终止源头会话还是等待其自然提交。修复代码加超时与事务边界约束防止复发。把这份清单和前面的框架图一起放在手边遇到类似问题时可以直接按顺序执行。13. 面试与复盘问题下面这些问题可以用来检验是否真正理解了整条链路pg_locks里granted false代表什么它和pg_stat_activity.wait_event_type Lock是什么关系为什么数据库 CPU 不高接口却会大面积超时行级锁等待的持续时间由什么决定一个事务在等锁它占用的连接会被连接池回收吗lock_timeout和statement_timeout分别作用于什么范围如果只调大maximumPoolSize可能带来什么后果这些问题的答案都可以从本文的框架图中推导出来锁等待链的上游是事务下游是连接连接的上游是应用线程。14. 总结回到开头的问题为什么数据库不忙接口却全部超时因为真正被阻塞的不是 CPU而是占用链。一个长时间不提交的事务持有行锁导致其他事务在锁上排队排队的事务持有数据库连接导致连接池被逐步耗尽连接池耗尽后应用线程拿不到连接只能超时返回。整条链路可以压缩成一句话行锁等待 → 事务积压 → 连接占用 → 连接池耗尽 → 应用雪崩。排查时反过来走这条链从应用的连接池告警出发去数据库看pg_stat_activity和pg_locks找到锁等待链最顶端的源头事务再回到代码里缩短它的事务边界。最后可以用一张决策表收尾现象优先排查常用手段接口超时 数据库锁等待锁等待链pg_blocking_pids、pg_locks接口超时 数据库空闲连接池HikariCP 指标、maximumPoolSize事务一直不提交长事务idle_in_transaction_session_timeout进程互相等待死锁统一加锁顺序、缩短事务参考资料PostgreSQL 官方文档Monitoring Database Activitypg_stat_activityPostgreSQL 官方文档Monitoring Lockspg_locks、pg_blocking_pidsPostgreSQL 官方文档Explicit LockingPostgreSQL 官方文档Transaction IsolationHikariCP 官方文档Configuration KnobsSpring Framework 官方文档Transaction Management