每次线上数据库卡死我脑子里第一个动作永远是同一件事——查锁。Postgres本身对锁的处理已经相当成熟行锁、表锁、咨询锁分得清清楚楚可一旦某个query把持着锁不释放后面几乎所有的写操作都会堵成一串。这个场景虽然不常发生但每次出现都是线上事故级别的压力DBA和研发都必须第一时间定位出“持锁的query是谁”。这篇文章想把排查Postgres锁问题的完整打法拆一遍从原理到SQL从定位到处理给和我一样被线上锁问题折磨过的人一份能直接上手用的排查手册。1. 什么在挡路先搞懂Postgres里的锁机制很多人一听到锁就皱眉觉得锁是性能杀手但其实锁是Postgres保证数据一致性的根基。真正让系统卡死的是“锁持有时间过长”或“持锁事务不结束”而不是锁本身。如果你不理解锁的基本分类后面看pg_locks视图大概率是懵的所以我先把锁的底细梳理清楚。1.1 锁不是敌人但乱持锁真的要命Postgres里的锁主要分几类对应的应用场景完全不同。表级锁比如ACCESS EXCLUSIVE多出现在ALTER TABLE、TRUNCATE、VACUUM FULL这类DDL操作里。它一旦生效整张表基本等于被单独占用连SELECT都要排队。行级锁是最常见的业务锁来源。一条UPDATE或者SELECT ... FOR UPDATE会对目标行加锁保证同一时刻只有一个人能改这行数据。行级锁粒度小并发性好但如果一个事务更新完不提交持有行锁的时间会无限拉长其他要更新同一行的事务全部被卡住。咨询锁则是应用层自己申请的锁比如用pg_advisory_lock(key)锁一个业务ID。这类锁不挂在任何表的行或列上排查起来最隐蔽因为你在业务表里看不到任何异常但整个应用可能都被它拖住。三种锁的持锁方式不同但检测思路一致先找到锁对象再找到持有这条锁的事务最后看这个事务在等什么、在跑什么query。锁对象、持锁进程、执行的SQL这三者必须串在一起看才能把问题钉死。1.2 定位的核心锁请求的两个状态在所有锁排查工具里最重要的概念是granted这个字段。pg_locks里的每一条记录都代表一个锁请求granted true表示这个锁已经被该进程拿到手了granted false表示这个进程正在排队等待别人释放锁。你可以把锁队列想象成银行柜台。granted true的人已经坐进柜台里办事占着资源不撒手granted false的人在大厅排队叫号。我们要抓的“持锁query”就是那些granted true、并且导致其他人granted false的进程。只看pid往往不够同一个进程可能持有多个锁也可能同时等待多个锁。比如一个事务先拿到了A表的行锁接着去更新B表的某一行而B表那行被别的事务锁住于是这个进程既持有锁又在等锁。这种情况在pg_locks里会同时出现granted true和granted false的记录判断时必须看锁对象是否相同。1.3 用pg_blocking_pids建立阻塞链Postgres从9.6版本开始提供了一个非常方便的函数pg_blocking_pids(pid)。你传入一个疑似被阻塞的进程号它会直接返回阻塞这个进程的pid数组而且数组中至少有一个pid返回结果才说明该进程处于等待状态。这个函数本质上就是对pg_locks的封装但省掉了复杂的自关联查询推荐优先使用。它返回的只是“直接阻塞者”不是整条阻塞链。如果A被B阻塞B又被C阻塞pg_blocking_pids(A的pid)只会返回B你要再对B调用一次才能继续摸到C。理解这一点排查多级锁才不会被表面现象带偏。2. 核心细节与实操要点让锁持有者当场现形定位锁问题的核心查询集中在pg_stat_activity和pg_locks两个系统视图上。前者告诉你进程在干什么后者告诉你进程握着什么锁、在等什么锁。两边的信息缺一不可只查其一很容易判断失误。2.1 先看pg_stat_activity每个进程到底在干嘛pg_stat_activity是排查数据库进程状态的第一站。我最常用的字段就这几个。字段含义排查价值pid后端进程号后续cancel或terminate的输入参数state当前状态重点看active和idle in transactionwait_event_type等待类型Lock代表锁等待Client常见于空闲事务wait_event等待的具体事件等待锁时为relation、tuple等query正在执行的SQL锁持有者和等待者的真实现场query_startquery开始时间判断卡了多久时间越久越紧急xact_start当前事务开始时间排查长事务的关键字段state字段最容易迷惑人。active表示进程正在执行SQLidle表示连接空闲还有一种最坑的状态是idle in transaction意思是事务内已经执行过SQL但一直没提交。这种进程当前没在跑任何query但手里可能牢牢握着一堆行锁把别人堵死。看到这种状态基本可以高度怀疑它就是锁持有者。wait_event_type Lock时wait_event会告诉你等在什么对象上。如果等的是表锁通常是relation等的是行锁可能是tuple等等。结合query字段里正在等待的SQL就能判断它想干什么事。2.2 再看pg_locks锁的全景图pg_stat_activity只能看到进程本身看不到锁对象。锁是谁持有、谁在等待必须去pg_locks里看。这个视图核心字段有这么几个。locktype锁类型。常见有relation表锁、tuple行锁、transactionid事务锁、virtualxid虚拟事务锁、advisory咨询锁等。database锁所在数据库的OIDNULL可能是因为锁不依赖数据库。relation锁的目标表OID可以用::regclass转成表名。page和tuple行锁的目标位置两者组合能定位到具体数据行。virtualxid和transactionid事务相关锁标识每个事务都会持有自己的事务ID锁。mode锁强度比如AccessShareLock、RowExclusiveLock、AccessExclusiveLock。granted上面讲过的关键状态true为持有false为等待。只看单条记录永远不够要把pg_locks和pg_stat_activity关联起来或者直接依赖pg_blocking_pids函数判断阻塞关系效率会高很多。2.3 几条现成的SQL直接复制先跑一个全景快照把当前不是idle的进程全部列出来。这个查询能让你快速看清数据库里到底有多少活跃进程、谁卡了多久。SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event, now() - query_start AS query_age, query FROM pg_stat_activity WHERE state idle ORDER BY query_age DESC;注意now() - query_start算出的时间间隔直接按倒序看卡得最久的排在最上面。如果某个query_age已经好几分钟甚至更久而它的wait_event_type又是Lock那基本可以确认这是一个锁等待现场。再跑一个专门找“谁在阻塞谁”的查询把阻塞者和被阻塞者一次拉出来。SELECT blocked.pid AS blocked_pid, blocker.pid AS blocking_pid, now() - blocked.query_start AS blocked_age, blocked.query AS blocked_query, blocker.query AS blocking_query FROM pg_stat_activity AS blocked JOIN pg_stat_activity AS blocker ON blocker.pid ANY (pg_blocking_pids(blocked.pid)) WHERE blocked.state idle ORDER BY blocked_age DESC;这个查询用ANY (pg_blocking_pids(...))把阻塞关系展开输出结果里blocking_query就是你要找的“持锁query”。注意如果blocker.query显示为NULL通常意味着track_activities参数没开启或该进程处于空闲事务状态。3. 实操过程与核心环节实现从卡死到解除的全流程工具原理讲完接下来用一个我在生产环境真实处理过的场景把整个排查过程走一遍。你不需要完全照抄我的命令照着这个思路去套自己的业务基本能解决绝大多数锁问题。3.1 一个让我印象深刻的生产案例有一次业务方反馈某个订单系统的列表接口大面积超时数据库CPU却很低业务上看起来又不像慢查询。一查活动进程才发现所有写订单的操作全部堆积在active状态而它们的query_age都在一两分钟以上正常的接口早该执行完了。我立刻跑了上文的第一个全景观测SQL瞬间定位到一批等待锁的UPDATE语句它们的wait_event_type是Lock而且都在等待同一个relation。在Postgres里出现大范围集中等待同一个表往往是表级锁出了问题基本可以判断有一个大事务或者DDL卡住了整张表。继续看那些等待锁的进程它们的blocking_pids都指向同一个进程。顺着那个pid查pg_stat_activity发现该进程的state是active正在跑的query是一条没走索引的大范围UPDATE。这条UPDATE更新了十几万行事务一直没提交表级锁和行级锁混合在一起把后面所有写操作全堵死了。3.2 现场排查三步走这类问题虽然看着吓人但排查就三步。第一步拍快照。用psql连接数据库打开扩展显示模式手动执行全景观测SQL把当前进程状态和锁等待信息抓下来。psql -h 你的主机 -U 你的用户 -d 你的数据库 \x onSELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS query_age, query FROM pg_stat_activity WHERE state idle ORDER BY query_age DESC;第二步找阻塞关系。刚刚那条SQL只能看到单个进程的等待事件未必能看出谁挡了谁。紧接着跑两个关键查询。SELECT blocked.pid AS blocked_pid, blocker.pid AS blocking_pid, blocked.query AS blocked_query, blocker.query AS blocking_query FROM pg_stat_activity AS blocked JOIN pg_stat_activity AS blocker ON blocker.pid ANY (pg_blocking_pids(blocked.pid)) WHERE blocked.state idle;如果输出中有清晰的blocking_query直接锁定那条SQL。如果blocking_pids字段结果是空数组说明这个进程没有被人阻塞需要去别的方向排查慢查询或IO问题。第三步确认源头后结合业务线判断处理方式。是前台业务还是后台任务还是已经跑了几分钟的大清理不同场景对“这个锁能不能杀”的判断完全不一样。3.3 解除阻塞的两种武器怎么选确定了持锁query之后接下来才是核心难题这个锁到底怎么解。Postgres给DBA提供了两个内置函数。pg_cancel_backend(pid)相当于给那个进程发一个取消当前query的信号。如果被取消的进程正处于active状态它的SQL会被中断连接本身还保留着。适合处理那些还在执行的SQL比如上文的大范围UPDATE。pg_terminate_backend(pid)一步到位断开整个后端进程释放它持有的所有锁和事务。适合处理idle in transaction状态的进程因为这种进程根本没有在跑的querycancel对它是无效的只能断开连接。我当时的处理顺序是先给持锁事务的pid调用pg_cancel_backend观察了几秒钟发现锁没释放。原因是那条UPDATE虽然被取消了但事务本身还没有结束行锁依然由事务继续持有。这时候只能升级处理调用pg_terminate_backend直接把持锁事务端掉。需要提醒的是pg_terminate_backend不是银弹。如果数据库本身配置了连接池断开一个后端连接之后连接池很可能会立即创建一个新连接来补位如果补位的连接又默认开启了事务去处理同样的SQL很可能锁问题再次出现。所以每次处理后都要观察一段时间确认没有“杀完又堵”的现象。4. 常见问题与排查要点我的实战经验总结锁排查处理过太多次之后我发现有几个坑反复出现。这里集中写下来既是给你的提醒也是我自己的备忘。4.1 idle in transaction最冤枉的锁源头线上最容易制造“无头悬案”的就是idle in transaction。业务代码里开启事务后执行了一条SELECT或UPDATE后续逻辑在应用层卡住了或者等待外部接口返回事务一直不提交。从数据库进程看它不在跑任何SQLquery字段停在最后执行的那句语句上但事务id和锁全都在。这种状态特别容易被新手指错过。大家总是盯着active的query看却忘了xact_start字段。我在排查时只要看到某个进程的xact_start比query_start早了很多就会多留一个心眼。最好的办法是设置idle_in_transaction_session_timeout比如设定为5分钟让这种长事务自动被清理别让它拖到线上事故爆发。4.2 多层阻塞查锁要顺着链条往下摸前面说过pg_blocking_pids返回的是直接阻塞者。真正的生产环境中锁排队往往是多米诺式的。A进程在等BB又在等CC才是那个持有最底层锁的进程。如果你只看A的blocking_pids是B就把B杀掉结果B被杀了C还在那里A可能继续等待问题一点没解决。我第一次遇到这种嵌套阻塞时也是一层层往上追。先查A发现阻塞pid是B再单独查B发现B的blocking_pids里有C查C之后才看到C是一个忘了提交的事务。如果提前不看链条上来就杀B不仅白忙活还可能误伤正常业务。处理这种多级情况我的习惯是写一个循环查询脚本或者用递归CTE把整个阻塞树拉出来。但在紧急时刻手工顺着blocking_pids逐层往上翻也很快。关键是记住一个原则目标永远是找到阻塞链条最深处的那个源头而不是被阻塞进程的直接前驱。4.3 锁类型复杂时别只看表面很多新手排查锁只盯着业务表看到relation类型的锁就以为完事了结果怎么查都查不出来。比如autovacuum进程有时候会持有短时间的锁它对应的application_name是autovacuum worker如果你不小心把它杀掉可能影响回收空间和统计信息甚至触发更糟的IO问题。还有一个容易忽略的是咨询锁。应用里如果用了pg_advisory_lock或者pg_try_advisory_lock这张锁不会体现在任何业务表的行上pg_locks里显示的locktype是advisory。排查的时候如果业务进程之间互相调用、彼此等待但表锁和行锁都找不到嫌疑可以考虑往advisory锁方向查。query字段为空的进程也要格外注意。如果track_activities被关闭了或者进程处于idle状态query就是空的。这个时候别慌通过xact_start和client_addr去判断这个连接从哪来、开了多久仍然可以拼凑出线索。4.4 提前设好参数别等事故教你做人锁问题排查得再多也不如提前预防。我建议在Postgres配置里提前把几个关键参数调好。lock_timeout可以设置一个合理的等待上限比如5秒或10秒。一条SQL如果在锁队列里排队超过这个时间就直接放弃执行并报错避免请求无限堆积。statement_timeout也可以给单条SQL设置总执行时长上限防止SQL跑飞不返回。idle_in_transaction_session_timeout前面提过一定要设。连接池和管理工具导致的空闲事务是线上锁事故第一元凶有了这个参数等于多了个自动巡警。业务层面也要尽量缩短事务。不要在事务里做远程调用、消息发送、文件读写这类不确定耗时的操作该提交就提交。如果需要批量更新大量数据建议拆成小批次循环执行每个批次独立事务不要用一个巨大事务扛到底。批量和事务拆分这招我实测下来对锁冲突的缓解效果最明显。最后再分享一个小技巧锁问题排查不要等到卡死了才做。平时完全可以每小时跑一次锁监控脚本把会话数、锁等待数、idle in transaction数记录下来画成趋势图。数据库卡死前通常不是毫无征兆的锁等待数量会突然暴涨有监控和没监控完全是两种体验。真到报警那一刻你已经提前知道该盯谁了。