遇到 Postgres 卡住、应用超时、或者某个查询一直堵着不动第一反应肯定是谁握着锁不放这个标题的问题几乎每个 DBA 和用 Postgres 的后端都遇到过。你自己可能也经历过一个UPDATE或者ALTER TABLE跑到底然后一头扎进lock_timeout的报错里抬眼一看pg_stat_activity全是Waiting。这篇文章不扯概念直接从实操角度讲清楚怎么通过系统视图把那个罪魁祸首揪出来以及揪出来之后怎么处理比较稳妥。1. 先弄清楚 Postgres 的锁到底在锁什么Postgres 的锁机制和 MySQL 的 InnoDB 在细节上有不少差异。MySQL 里你可能习惯看SHOW PROCESSLIST和information_schema.innodb_trx但 Postgres 这边主力是pg_locks、pg_stat_activity、pg_blocking_pids()这三个东西。很多人一上来就查pg_locks结果发现输出一大片锁记录反而更懵了。pg_locks表里每一行代表一个锁对象。里面有locktype字段可能是relation、tuple、transactionid、virtualxid等等。这些名字各有含义relation表级的锁比如你ALTER TABLE的时候会拿一个AccessExclusiveLock这就是典型的表级锁。tuple行级锁对应某一行数据的锁。一般是被UPDATE、DELETE操作的元组。transactionid事务锁。每一个事务都有一个 transaction id当一个事务要修改某行时它会持有这个锁另一个事务想要修改同一行时就得等前一个事务释放。这个是行级锁冲突的大头。virtualxid每个事务都有的虚拟事务 ID。这个锁其实不太会产生明显的冲突但你会经常在pg_locks里看到一堆virtualxid记录别被它们迷惑了。它们通常是自己等自己不影响别人。锁的模式也分几个主要级别从弱到强包括RowShareLock、RowExclusiveLock、ShareLock、ShareRowExclusiveLock、AccessExclusiveLock等。SELECT平时不加锁但UPDATE/DELETE/INSERT会拿RowExclusiveLock多个RowExclusiveLock之间不互斥。所以不要因为看到一堆RowExclusiveLock就以为出问题锁多不代表有阻塞。真正恶心人的通常是AccessExclusiveLockALTER TABLE、TRUNCATE、DROP之类 DDL 会拿这个和所有其他锁互斥。ShareLock外键约束插入、VACUUM等情况会拿到冲突范围也不少。tuple级锁和transactionid级锁两个事务写同一行时行锁冲突就会卡住。第二个要理解的点是Postgres 的锁是排队机制但不是按照全局顺序来的。一个查询可能被另一个持锁的查询阻塞也可能被阻塞得久之后直接卡住后面所有要申请同一资源的查询。所以有时候你看到的第一个Waiting并不一定是罪魁祸首要顺着阻塞链一直往上找。我试过一种情况一个报表查询把某张表一直ShareLock持着不放后面TRUNCATE就一直等然后几个INSERT也跟着等。表面上看INSERT是被TRUNCATE挡住了实际根因却是那个报表查询。所以找根因必须看阻塞链路不能只盯着单条查询。2. 定位元凶前先把这些视图用熟练2.1 pg_stat_activity所有会话的实时状态pg_stat_activity是排查一切数据库问题的第一站。它记录了当前连接上的数据库、用户、客户端地址、查询内容、查询状态、等待事件等信息。核心字段有pid后端进程 ID后面pg_cancel_backend()和pg_terminate_backend()要用。state常见的有active、idle、idle in transaction、fastpath function call等。其中idle in transaction是非常容易被人忽略的状态意思是事务开着但没提交还攥着锁。query当前正在执行的 SQL。要注意如果 state 是idle in transaction批量的 query 字段可能显示的是最后一条 SQL不是真正造成问题的根源你有可能会被误导。wait_event_type和wait_event反映当前会话在等什么资源。如果wait_event_type是Lock说明它在等锁这个信号就很直接。query_start、xact_start查询和事务的开始时间判断谁持锁时间最长很好用。光看pg_stat_activity也能找出部分问题但它的致命弱点是你不知道它到底是阻塞者还是被阻塞者。此时需要结合下一节的函数。2.2 pg_locks锁对象的底层快照pg_locks里记录的是锁的持有与等待关系。每个锁记录有granted字段值为true表示这个锁已经被会话拿到了false表示这个会话还在等锁。对于排查锁冲突通常需要把pg_locks关联到pg_stat_activity去查看每个锁是谁持有的谁在等待。一个常用的查询模板SELECT pid, locktype, relation::regclass AS relname, mode, granted FROM pg_locks ORDER BY pid;这条 SQL 能帮你把当前数据库里所有锁的分配情况拉出来。通过granted false的记录你能快速定位哪些会话在等待然后顺藤摸瓜找到等的是哪一把锁。锁的pid指的是锁所属会话的 pid而不是数据库里某个 pid。你可以拿它过来 joinpg_stat_activity。2.3 pg_blocking_pids()一张图看懂阻塞链这是最关键的函数。它接收一个pid作为参数返回一个整数数组里面是阻塞这个 pid 的所有 Postgres 后端进程的 id。比如SELECT pg_blocking_pids(12345);执行后如果是空数组说明12345没有被任何会话阻塞。如果返回{9876}说明 9876 这个会话正在阻塞 12345。然后你再对 9876 调用一次pg_blocking_pids()如果又返回了别的 pid说明阻塞链还有上游一路递归追下去总能找到一个被阻塞查询返回空数组的 pid那就是链条最顶端的真凶。其实 Postgres 官方也有一个内置函数叫pg_stat_get_blockers不过大多数人日常还是习惯用pg_blocking_pids因为更直观。在写文章、做自动化告警脚本时我经常用这条 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;这条能直接生成阻塞对包括被阻塞的 SQL 和阻塞源头的 SQL排查起来效率直接翻倍。如果整张结果就是空表那你当前环境根本没有锁阻塞问题安心。3. 核心定位手法三种经典组合拳3.1 组合拳一最简单粗暴的全量锁视图不管三七二十一先执行一段万能查询把当前所有会话的锁关系、等待状态都拉出来SELECT a.pid, a.usename, a.application_name, a.client_addr, a.state, a.wait_event_type, a.wait_event, a.query, l.locktype, l.mode, l.granted, l.relation::regclass AS locked_relation FROM pg_stat_activity a LEFT JOIN pg_locks l ON l.pid a.pid WHERE a.pid pg_backend_pid() ORDER BY a.pid;执行后注意看每一行的wait_event_type和wait_event。如果state是active或idle in transaction而且wait_event_type Lock那么这个会话正在等锁。granted false的锁记录对应的就是等待的锁。我看这种查询时习惯先按wait_event_type分组点出所有Lock等待的记录再去对锁的记录反查持有锁的会话。如果你的库规模不大这种全量查询完全没有问题几毫秒就能跑完。3.2 组合拳二用 pg_blocking_pids 递归揪源头这一招用得最多尤其是生产环境出了问题我要快速判断谁是根因而不是被阻塞者。先写一个查询拿到所有正在等待锁的 pidSELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type Lock;把每一个 pid 逐个丢进pg_blocking_pids()一般马上就能找到阻塞链。稍微写个递归 CTE 也能自动化平时我更喜欢直接手动一层一层点因为生产环境一次出现太多条递归 CTE 可能反而看得不够清楚。简单说下递归的思路先找所有 wait_event_type Lock 或者说 wait_event 以 transactionid 开头的会话 pid然后对他们调用 pg_blocking_pids()对返回的 blocker pid 再执行查询pg_stat_activity看看它的状态和 SQL。一路递归到某个 pid 的pg_blocking_pids()返回空数组那就是真正握着锁的源头。3.3 组合拳三辅助日志开锁等待记录如果你手头没有实时看视图的窗口或者问题发生在凌晨、回溯困难那么打开log_lock_waits参数是个好习惯。log_lock_waits参数需要搭配deadlock_timeout一起看。Postgres 默认的deadlock_timeout是 1 秒。只要某个锁等待超过了这个时间日志就会记录一条 LOG。它虽然不等同于死锁但是用来事后定位锁问题非常有效。设置方法是在postgresql.conf里改log_lock_waits on deadlock_timeout 1s改完需要SELECT pg_reload_conf();重新加载配置即可不用重启数据库。生产环境我强烈建议打开这个日志里会出现类似这样的一行LOG: process 12345 still waiting for AccessExclusiveLock on relation 16789 of database 16384 after 1000.123 ms DETAIL: Process holding the lock: 9876. Wait queue: 9876, 12345DETAIL 里的 Process holding the lock 直接告诉我们是谁拿着锁这个和实时视图双管齐下基本不会漏掉问题。4. 实操排查场景从卡住到定位的完整过程假设你在生产环境收到告警某个重要报表表查询时间过长应用日志显示lock_timeout报错。下面是我常用的排查流程按顺序来4.1 第一步整体看一眼谁在等锁先跑一条快速查询把所有wait_event_type Lock的会话拉出来SELECT pid, usename, state, wait_event_type, wait_event, left(query, 80) AS query_preview, query_start FROM pg_stat_activity WHERE wait_event_type Lock;正常情况下这个列表应该很短甚至为空。一旦看到好几条就说明有锁等待发生。顺手也看一下有没有长时间idle in transaction的会话SELECT pid, usename, state, xact_start, age(now(), xact_start) AS xact_age, left(query, 80) AS query_preview FROM pg_stat_activity WHERE state idle in transaction ORDER BY xact_start LIMIT 10;idle in transaction是隐患好友。因为如果在事务里执行过写操作但一直不提交即使客户端没发新 SQL数据库事务仍然持有着之前拿的锁。这个状态很容易在应用层的连接池里埋雷。我遇到过一个业务一个事务里先UPDATE了一行然后去调用一个外部接口外部接口超时了事务一直没提交。后面的应用都卡在这行上排查起来很费劲。所以先排查idle in transaction是很必要的。4.2 第二步生成阻塞关系对把上一步里等锁的 pid 拿出来逐个调用pg_blocking_pids()。更快的办法是执行前面写过的 join 查询SELECT blocked.pid AS blocked_pid, blocker.pid AS blocking_pid, blocked.query AS blocked_query, blocker.query AS blocking_query, blocker.state AS blocker_state FROM pg_stat_activity AS blocked JOIN pg_stat_activity AS blocker ON blocker.pid ANY (pg_blocking_pids(blocked.pid)) WHERE blocked.wait_event_type Lock;这个结果列出来你会很直观地看到谁阻塞谁。比如blocked_pidblocking_pidblocked_queryblocking_query10039876UPDATE ...ALTER TABLE ...10049876UPDATE ...ALTER TABLE ...这里所有 UPDATE 都被一个 ALTER TABLE 阻塞说明真凶大概率是 ALTER TABLE 持有表级锁没放。如果blocking_query显示的是一个SELECT这时候多半是长事务里握着行锁或表锁没提交。4.3 第三步查看持锁时间与锁内容确认了旁氏链顶端的会话之后需要判断它为什么不释放锁。查看它的事务开始时间和持续时间SELECT pid, xact_start, query_start, now() - xact_start AS xact_duration, now() - query_start AS query_duration, state, query FROM pg_stat_activity WHERE pid blocking_pid;如果state是active并且query_duration特别长那大概率是一张不合理的 SQL 或者低效查询导致事务很长时间不结束。如果state是idle in transaction那问题就不是查询本身了而是应用层的事务管理有问题SQL 已经执行完但事务一直开着没提交。这种情况其实更常见也更危险因为 SQL 本身不见得慢单纯是事务管理没写好。同时可以看你锁的具体内容方便确认锁的对象SELECT l.locktype, l.relation::regclass AS relation_name, l.mode, l.granted, a.pid, a.state FROM pg_locks l JOIN pg_stat_activity a ON a.pid l.pid WHERE a.pid blocking_pid ORDER BY l.granted DESC, l.locktype;如果看到一条AccessExclusiveLock的granted true那基本坐实了 DDL 操作导致的阻塞。如果看到很多transactionid锁说明有事务修改某行后一直没提交后面来的事务都在排队。4.4 第四步选择处理方式确认了根因后有几个处理动作按强度从轻到重依次是pg_cancel_backend(pid)发送取消信号取消这个会话当前正在执行的查询。如果会话处于active状态且正在执行一个不合适的查询这一步足够了。但对于idle in transaction一般不一定管用因为它现在没有在执行任务Cancel 无从下手。pg_terminate_backend(pid)断开会话连接。这是狠手段会把这个后端进程直接终止并且回滚它没有提交的事务。对付idle in transaction时这个最有效。systemctl restart postgresql重来不要走到这一步。除非数据库完全无可挽回一般不应该用重启库来释放锁那是最后的手段。执行SELECT pg_terminate_backend(9876);后被它阻塞的会话基本会立刻恢复。这时候盯着pg_stat_activity看那些wait_event_type Lock的会话正常情况下它们马上就能继续执行下去了。我常用watch -n 1 SELECT ...在终端里定时刷新确认状态恢复正常。注意一点pg_cancel_backend和pg_terminate_backend都需要相应权限普通用户一般只能操作自己的会话要操作别人的会话需要超级用户权限或者相应授权。所以生产环境的读账号往往不适合做这个操作这类动作尽量用专门的运维账号完成。5. 常见问题与排查技巧实录5.1 “明明没有写操作为什么还是有行锁冲突”有不一定是写操作。Postgres 的SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE以及外键约束在插入时会自动检查父表是否存在对应行都有可能加行级锁或 ShareLock。如果你的业务里有一张父表子表频繁插入/更新外键校验会短暂地持锁。如果父表上同时有人改数据或者做 DDL就容易卡住。另外VACUUM也可能和你抢锁。虽然它通常不阻塞正常的SELECT/UPDATE但和ALTER TABLE、TRUNCATE、DROP这类操作存在冲突。每次长事务结束后VACUUM迟迟跑不完也可能造成可见性问题。5.2 锁等待的会话在不同的数据库里查不到pg_stat_activity是全实例共享的但pg_locks里relation字段通过regclass类型转换会按当前连接的 database 解析。如果锁对象是在另一个数据库里直接转换可能显示成奇怪的数字甚至报错。排查时我一般加上datname字段确认每个锁在哪个数据库。跨库锁更多时候是连接池的问题一个连接池里的所有连接共享同一个数据库实例的不同数据库尤其要注意database之间互相阻塞的场景。5.3 查了很久没看到任何等待但应用还是报锁超时有一种情况锁等待非常短暂你晚了几秒去查已经释放了。应用却因为lock_timeout设置得非常小比如 50ms、100ms只要有一次短暂的锁冲突就直接放弃了。这种就不该用pg_stat_activity查了直接打开log_lock_waits和log_min_duration_statement通过日志抓取“锁等待超过阈值”的记录能更可靠地捕捉这些瞬时锁冲突。另一种情况是idle in transaction不占当前wait_event_type Lock但它持有锁。如果你只在看当前等锁的会话很容易漏掉它。我把idle in transaction的排查放在第一步就是为了避免踩这个坑。5.4 锁等待和死锁要怎么分死锁会在日志里出现deadlock detected并且 Postgres 会主动检测回滚其中一个事务来解除死锁。锁等待则纯粹是等待不会自动结束。实际运维时经常遇到的问题是锁等待超时而不是真正的死锁。因为 Postgres 自带死锁检测大多数死锁会在几秒内被自动处理反而是长时间的锁等待更让人头疼。在排查时你要分清前面查的方法看的是锁等待。如果日志里全是 deadlock 相关那就得往应用层的事务顺序、事务隔离级别方向去查了。5.5 如何快速判断锁持有者是不是“僵尸”会话有些会话可能已经和客户端断开了但服务端进程还没回收或者连接池里保持着已断开的连接进程显示idle但其实无人问津。这种会话如果持有锁也会一直卡住别人。判断方法可以看它的client_port、application_name以及pg_stat_activity里的backend_start、state_change。如果是连接池如 pgbouncer 或应用自建连接池里的会话通常application_name能看出点名堂。对于长期处于idle且xact_start为空的连接数据库本身干预不了要由连接池或应用去主动释放。真有夸张的时候得直接把连接池里的连接清掉。6. 预防与治理怎么避免下次再踩坑锁问题只有拿到两次“现场”才真正长记性。我给自己的运维习惯里加了几个硬规定写在这里也算给大家一个参考模板。6.1 设置合理的 lock_timeout、statement_timeout 和 idle_in_transaction_session_timeout这是最廉价、回报最高的防护。ALTER DATABASE mydb SET lock_timeout 5s; ALTER DATABASE mydb SET statement_timeout 30s; ALTER DATABASE mydb SET idle_in_transaction_session_timeout 1min;lock_timeout控制在等待锁多久后放弃statement_timeout控制单条 SQL 的运行上限idle_in_transaction_session_timeout控制事务开启后多少秒内没有任何下一条命令就自动断开。尤其是idle_in_transaction_session_timeout它的存在能在根源上杜绝大量因应用逻辑漏掉 commit/rollback 导致的隐性锁持有问题。这三个参数按业务实际微调但千万别全设为 0不限制否则下次还得靠人肉人工杀会话。6.2 在 DDL 操作时使用锁超时或者低峰期ALTER TABLE是生产环境锁问题的平民级噩梦。对于一些重要的表格ALTER TABLE动辄拿AccessExclusiveLock一旦有长事务就不会释放所有写操作全被堵死。建议如非必要DDL 尽量放在业务低谷并且可以在客户端先跑一个带lock_timeout的会话例如SET lock_timeout 10s; ALTER TABLE orders ADD COLUMN remark text;如果 10 秒内没拿到锁就主动放弃起码不会无限等下去把业务全部拖垮。ALTER TABLE之前也可以先用pg_blocking_pids探测一下有没有其他进程持有锁。6.3 设计业务时尽量缩短事务时间这一个听起来是废话但是很多人就是做不到。我见过一个业务一个事务里做了几十次查询最后才做一次更新全程开着事务。你说它有错吗功能上没问题但对数据库的锁管理非常不友好。这种事务里如果夹着任何写操作其他对这个数据行的更新就只能干等着。建议把长查询尽量放到事务外或者拆开事务让写操作尽量一步到位少占用事务。6.4 定期巡检锁状态不用写太复杂的系统每天一条 SQL 定时检查一下锁的等待情况就够。上面那版 join 查询配合 cron 或者外部监控系统每天跑一次把结果存档以后出了问题能直接对照历史快照判断问题趋势。尤其是那些开始是granted true却长期不释放的锁巡检脚本里应该直接告警。6.5 视图和索引不要乱加大量索引能加快读取但也意味着写操作时维护索引的锁更加复杂。这个账要算清楚。如果一个表上有多个大索引UPDATE要同时更新索引插入时也要把索引项放到对应位置锁范围相对更大。虽然 Postgres 用的是行级锁事务提交后才可见索引的锁冲突更多体现在 DDL 和 vacuum 场景下。基本的思路是不要无脑建索引索引不是越多越好锁冲突的风险随着索引数量增加而上升。说实话锁排查这件事难度不大但极其考验耐心和对系统视图的熟悉程度。你把pg_stat_activity、pg_locks、pg_blocking_pids()这三个工具用熟了加上日志开关开好任何锁问题基本都能快速定位。再配上合理的超时参数和事务设计很多锁问题压根就不会发生。最后再多说一句遇到一个锁住好几小时的会话别急着 kill。先花一分钟看看是不是有业务正在跑特殊操作比如大表VACUUM FULL、CLUSTER、或者pg_dump在做备份。这种情况如果你直接终止可能会导致备份失败或者数据文件问题。操作前确认一下这个会话的来源和用途实在拿不准就贴到群里一起看比你一个人闷头猜靠谱得多。