1. 项目背景与核心问题为什么单独把PostgreSQL事务并发控制拎出来讲做数据库管理这些年处理过MySQL、Oracle、SQL Server也搞过一些国产数据库但真正让我觉得需要单独花时间去啃底层的还是PostgreSQL的事务并发控制。大概在三月份我接到一个项目要求用PostgreSQL做核心业务库的支撑线上环境一跑起来先是出现不明所以的锁等待然后偶发死锁排查起来还挺费劲。翻了半天文档查了pg_stat_activity发现很多session卡在等待锁业务直接报超时。那段时间我几乎天天蹲在服务器前面盯日志最后总结出来PostgreSQL的事务并发控制如果你只停留在“知道有事务、有隔离级别”这个层面那线上肯定踩坑。这个项目标题是Postgresql数据库管理-事务并发控制0313正好对应我那次实战复盘。0313大概是某个版本或日期编号这不重要重要的是PostgreSQL的事务管理和并发控制机制比很多人想象的复杂得多。很多同行问我PostgreSQL和MySQL到底怎么选事务这块差别在哪。其实PostgreSQL默认的并发控制方案是MVCC多版本并发控制这条路线和MySQL的InnoDB有点像但细节差异极大。如果你习惯了MySQL的undo log那一套转到PostgreSQL很容易在快照行为、可见性判断、垃圾回收这些环节上犯迷糊。这篇文章不是教科书式的API文档是我从一次真实项目里抠出来的经验。我会把我踩过的坑、写过的排查SQL、调整过的参数全部摊开讲适合三类人看一是刚接手PostgreSQL的DBA二是写业务代码但对数据库隔离级别理解不深的开发三是准备从MySQL迁移到PostgreSQL的团队核心成员。看完之后你应该能回答三个问题PostgreSQL的多版本到底怎么工作为什么锁冲突突然飙升以及遇到类似线上事故第一步该看什么。这个项目里我实际做的几件事其实每一步背后都有坑。先梳理项目需要解决的问题再看机制原理最后落到排查和调优这个顺序也是我希望读者走通的路。2. 事务管理的基石ACID与四档隔离级别的真实边界2.1 ACID在PostgreSQL里是怎么落地的事务是数据库最核心的抽象。你用BEGIN开启一个事务要么全部提交要么全部回滚这就是原子性Atomicity。PostgreSQL通过事务日志WAL保证这一点数据变更先写WAL再写数据页崩溃后靠WAL重放恢复未提交的部分直接丢弃。这个顺序是硬性设计我来项目里第一件事就是查wal_level和fsync配置因为如果这里配置不合理整个数据的可靠性就是空谈。一致性Consistency靠约束、触发器和规则来维护这部分业务负责居多。隔离性Isolation就是本文的重点PostgreSQL通过MVCC加锁的组合实现。持久性Durability在PostgreSQL里主要由fsyncon和合适的commit_delay、commit_siblings来控制。你如果图快手动把fsync关掉一个断电就可能整库损坏这是DBA绝对不能碰的底线。我在项目中注意到一个现象很多开发只把事务理解成“要么全成功要么全失败”完全忽略隔离级别对并发的影响。实际上隔离级别决定了你的事务在看到别人未提交/已提交数据时的行为这直接影响业务正确性。2.2 四种隔离级别默认不是最高的但合理PostgreSQL对标SQL标准提供了四个隔离级别隔离级别脏读不可重复读幻读说明Read Uncommitted可能实际PG不会发生可能可能行为等同于Read Committed但很少用Read Committed默认避免可能可能PostgreSQL默认级别每个语句取新快照Repeatable Read避免避免可能PG中不会出现幻读事务开始取快照直到事务结束Serializable避免避免避免通过SSI可串行化监控代价高有个容易被误解的点PostgreSQL的Read Uncommitted并不真正读取未提交数据它在内部升级为Read Committed的行为所以脏读不会发生。这其实是PostgreSQL故意设计的因为MVCC架构下未提交的版本对别人的快照不可见有必要单独为“读未提交”做一套特殊实现得不偿失。Repeatable Read在SQL标准里允许幻读但PostgreSQL用快照实现让事务的查询看到的都是事务启动那一刻的数据库状态所以幻读也不会出现。这里有一个我项目里真实踩过的问题在Repeatable Read下如果两个事务都更新同一行先提交的成功后提交的会收到串行化失败错误could not serialize access due to concurrent update很多业务代码没有处理这个错误直接导致接口报错。你得明确告诉开发PostgreSQL的并发冲突可能直接抛错这个错不是bug是隔离机制在起作用。2.3 隔离级别的选择不是越高越好我给业务建议很简单默认用Read Committed除非你有明确理由才升级。为什么因为我见过太多团队盲目把隔离级别调到Serializable然后发现系统并发能力暴跌延迟从几十毫秒涨到几百毫秒。PostgreSQL的Serializable是通过SSI可串行化快照隔离机制实现的它会跟踪读写集合发现潜在冲突就中止事务这个开销极大。反过来如果你做账务类系统要求在转账过程中不允许余额被并发修改覆盖那Repeatable Read是合适的配合SELECT ... FOR UPDATE对关键行加锁就能保证读改写序列正确。我做过账务服务行锁配合Repeatable Read是黄金组合。而普通电商库存查询读多写少Read Committed完全够用。不是所有业务都需要最高的隔离级别我们要追求的是“恰好满足需求的隔离边界”而不是“最安全的配置”。这句话写在SQL标准里但真正体会它需要经历一次并发事故。3. 深入PostgreSQL的MVCC快照、可见性与垃圾回收3.1 行版本与xmin/xmax每条数据都有自己的时间戳PostgreSQL的MVCC实现方式行业里叫“基于堆表的行版本链”。表里的每一行都可能存在多个版本每个版本带有两个隐藏的系统列xmin表示创建这个版本的事务IDxmax表示删除/锁定这个版本的事务ID。事务在读取数据时通过比较自身的事务快照和行的xmin/xmax判断哪个版本对它是可见的。我还记得刚入门时把xmin/xmax当成普通字段查出来看突然就理解了所有并发问题。比如执行SELECT xmin, xmax, * FROM t;你会看到每一行是被哪个事务插入的被哪个事务删除的。大量业务表里的旧版本没有被及时回收时你就能从xmin分布里看到极其古老的事务ID。这里有一个不得不说的老话题事务ID回卷。事务ID是32位整数理论上会循环。PostgreSQL设计了一个“冻结”freeze机制把足够老的行事务ID标记为 FrozenXID让它们对所有事务可见从而避免回卷导致数据不可见。但如果你关闭了autovacuum或者VACUUM长期不跑事务ID回卷一旦发生数据库会强制关闭保护这是生产事故级别的问题。我以前处理过一台老库datfrozenxid已经逼近临界值紧急手动VACUUM才救回来这个检查项一定要列入日常巡检。3.2 事务快照给并发事务“拍一张合影”快照Snapshot是MVCC判断可见性的核心数据结构。简单说一个事务开始或语句开始时PostgreSQL记录当时的活动事务列表生成一个快照。快照里包含所有活跃事务的ID列表、最小活跃事务ID、下一个事务ID等。数据行的事务ID如果落在快照范围之外并且已经提交那就可见如果落在活跃事务列表里说明还在运行中不可见。拿生活场景类比快照就像一群人合影你按下快门时记录下在场的所有人活跃事务。之后照片里有人走了提交有人来了新事务都不影响照片里的内容。这就是为什么Repeatable Read下两次查询结果一致因为用的是同一个快照。Read Committed默认每个语句重新拍快照所以两次SELECT之间如果有其他事务提交结果可能不同。这不是“幽灵数据”是PostgreSQL隔离级别的设计选择。项目里有一个报表查询在Read Committed下反复出现数据抖动排查到最后发现是某个高频事务在不停提交订单状态改动方式就是给报表事务升到Repeatable Read数据一下子稳定了。3.3 可见性规则老版本为什么会被看到新手最容易误解的地方是老版本数据为什么还在是不是有脏数据其实MVCC的妙处就在这——旧版本不会立刻消失而是保留在堆表里供持锁老事务或长事务读取。一个Update操作会产生新版本同时保留旧版本让正在执行的其他事务仍然可以读到旧值这样读写互不阻塞。但代价也随之而来数据的多版本堆积会让表膨胀。膨胀到什么程度呢我做过一个测试表频繁更新某一行两个小时不到表的大小扩大了一倍。如果你不跑VACUUM那些死行版本就会一直占用磁盘并拖慢索引扫描。这里我给出一个可见性的朴素判断方法执行一个查询前找到该事务的快照然后逐行判断——如果行的xmin早于快照中最小活跃事务ID且已提交则可见如果xmax是某活跃事务ID说明该行正被删除/更新通常不可见。具体判断细节当然更复杂有专门的函数HeapTupleSatisfiesMVCC但理解到这个程度你对线上SQL的行为就已经能做出八九不离十的判断了。3.4 VACUUM与autovacuum多版本下的清道夫VACUUM是PostgreSQL特有的垃圾回收机制。它负责清理死行版本、更新可见性映射、回收空间并维护统计信息。PostgreSQL默认开启autovacuum但默认参数并不一定能适配所有业务。我在项目里设置的几个重要参数是autovacuum_vacuum_scale_factor、autovacuum_vacuum_threshold、autovacuum_vacuum_cost_delay。默认scale_factor0.2的意思是表中20%的行变为死行时才触发autovacuum。对于大表来说20%是一个很大的量级十万行的小表没问题但一张两亿行的订单表要堆积四千万死行才触发表早就膨胀得不成样子了。我的建议是对超大表调低scale_factor比如0.05或0.01同时提高autovacuum_vacuum_cost_limit让回收更积极。另外高频更新的表可以单独设置autovacuum_vacuum_scale_factor 0.01和autovacuum_vacuum_threshold 1000这样可以显著控制膨胀率。不过别走向另一个极端——把autovacuum调得过于激进导致它反复扫描表大量消耗CPU和IO。我见过有的同学把autovacuum_vacuum_cost_delay0直接让VACUUM以全速运行结果业务时段CPU长时间100%核心查询全部变慢。VACUUM要的是长期稳定不是一次清完。我们项目的策略是业务低峰期调高强度高峰期恢复默认让系统自动平衡。还有一个很实用的检查SQL查表膨胀率。用pgstattuple扩展或者简单用pg_relation_size对比实际行数估算。我每天巡检时会看最大的十几张表的死行比例超过30%就考虑手动执行VACUUM (VERBOSE, ANALYZE)。4. 锁机制MVCC之外的另一根支柱4.1 表级锁与行级锁的层级关系MVCC解决读写的冲突但写与写之间的冲突还是要靠锁。PostgreSQL有一整套锁体系表级锁、行级锁、页级锁、咨询锁等其中我们日常打交道最多的是表级锁和行级锁。表级锁有8种ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE。命名看着挺绕但本质是锁粒度从低到高互相之间有冲突矩阵。例如普通的SELECT拿ACCESS SHARE锁UPDATE/DELETE/INSERT拿ROW EXCLUSIVE锁。最为暴力的ALTER TABLE拿ACCESS EXCLUSIVE锁它和所有其他锁都冲突会阻塞几乎所有操作。这解释了一个常见现象为什么你在业务低峰期跑一个ALTER TABLE整个表的读都卡住了。因为ACCESS EXCLUSIVE锁会把普通的SELECT也挡住即使PostgreSQL已经支持了ALTER TABLE ... ADD COLUMN的快速默认值填充但整个表的重写操作仍然是致命的锁源。行级锁主要是FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE、FOR KEY SHARE。这几个加锁强度不同FOR UPDATE是最重的不允许其他事务修改或删除该行FOR SHARE则允许别人读取不允许修改。在并发更新同一行时行锁是事务提交才释放的这就是很多锁等待的根源。4.2 锁等待与死锁的处理逻辑两个事务更新同一行后发起的那个会进入锁等待。锁等待本身不是错误但等太久就是问题。PostgreSQL中lock_timeout参数可以控制等待时长默认是0也就是无限等。我在项目里把关键业务连接的lock_timeout设为5秒配合应用层重试机制防止后台任务把系统拖死。死锁的处理就更有意思了。PostgreSQL的后台进程deadlock_timeout默认1秒会周期性检查等待图如果发现有环路会主动中止其中一个事务抛出类似deadlock detected的错误。受害者事务会被回滚其余事务得以继续。我之前处理过一个死锁案例是两张业务表顺序更新不一致导致的事务A先更新甲表再更新乙表事务B先更新乙表再更新甲表两者交叉等待就形成了环路。解决办法很土但有效——在应用层约定所有事务必须按照字典序依次获取表锁彻底消除环路。这类问题在代码评审阶段就该发现等线上报错已经晚了。4.3 锁监控用一条SQL看清锁全貌遇到锁等待第一件事就是查表。下面这条SQL是我项目里用了无数次的可以一眼看出当前阻塞和被阻塞的会话关系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, blocking_activity.state AS blocking_state, blocking_activity.xact_start AS blocking_xact_start FROM pg_catalog.pg_locks AS blocked_locks JOIN pg_catalog.pg_stat_activity AS blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks AS 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.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity AS blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.granted;这条SQL的思维是找到所有没获得锁的会话grantedfalse再去找持有同一对象锁且已获得锁的会话由后到前反推出阻塞源。一旦定位到阻塞源头判断该会话是活跃还是空闲如果是一个长时间未提交的空闲事务基本可以判定是业务代码漏了commit直接pg_terminate_backend(阻塞pid)就能解锁。线上第一次执行这个SQL时我同时开了好几遍、反复对比从直观可见性的角度你还需要配合pg_blocking_pids(pid)函数。例如想查某会话被谁阻塞直接SELECT pg_blocking_pids(1234);输出就是阻塞它的PID数组非常直观。4.4 咨询锁跨进程协作的利器PostgreSQL提供了咨询锁Advisory Lock它不是绑定在具体数据库行上而是由应用定义的锁。对DBA和开发来说这是实现分布式互斥、控制并发任务执行的宝贝。我项目中有一个场景多台应用服务器同时跑定时任务必须保证同一个时刻只有一台在执行。用咨询锁最优雅——在事务里执行SELECT pg_try_advisory_lock(任务编号)返回true就继续执行返回false就说明别的节点正在跑自动跳过。这个方案比数据库行锁、Redis分布式锁更简单可靠而且不依赖额外组件。不过要注意咨询锁分事务级和会话级pg_advisory_lock是会话级需要手动解锁pg_advisory_xact_lock是事务级事务结束自动释放。我一开始用错了函数导致锁一直挂在会话上差点造成线上任务全部堵死。后来统一改为事务级在事务内完成互斥逻辑干净利落。5. 线上实战一次事务并发事故的完整排查与解决5.1 事故现场业务报错开始到数据库卡死这个项目当时遇到的情况是促销活动一开始订单服务频繁报“Sorry, too many clients already”紧接着数据库CPU飙升大量查询堆积。我登录数据库以后发现pg_stat_activity里state是active的查询并不多大量会话处于idle in transaction状态这就是典型的“事务没结束还把持着锁”。深入查下去发现是项目里有个接口调用了外部支付渠道支付回调迟迟没返回而代码在回调返回之前不提交事务。结果就是每个支付请求都开一个事务占着一行订单的FOR UPDATE锁其他请求更新同一行时全部等待越积越多最终连接池被打满。这暴露了两个问题一是开发错误地将外部IO放在数据库事务内这是非常常见但很要命的模式二是缺少锁等待监控等到爆发才发现。索引监控等都对但没有对事务时长做告警等于裸奔。5.2 处理手段快速止血与治本止血操作很简单找出所有idle in transaction超过阈值比如5分钟的会话直接终止。SELECT pid, now() - xact_start AS duration, usename, state, query FROM pg_stat_activity WHERE state idle in transaction AND xact_start now() - interval 5 minutes ORDER BY duration DESC;确认无误后执行pg_terminate_backend(pid)批量清理。注意这一步操作要谨慎先看PID对应的业务确认是可以牺牲的不然应用端收到异常也麻烦。但在这个场景里外部回调早就超时了留着事务只会让系统更糟。治本措施有三条第一把外部调用移到事务之外先查库、调支付、拿到结果后再开启事务改状态第二给应用层加上事务超时设置比如JDBC层的socketTimeout和transactionTimeout防止捞不到回调时无限等待第三部署锁等待监控脚本每30秒扫描一次锁等待链超过阈值自动告警。5.3 用隔离级别和锁的组合优化业务SQL处理完事故我们又顺手优化了一批业务SQL。一个典型的更新操作比如商品库存扣减UPDATE inventory SET stock stock - 1 WHERE product_id 123 AND stock 0 RETURNING id;这句SQL的正确性依赖两个点stock 0条件防止超卖UPDATE自带行锁防止并发扣减覆盖。只要事务隔离级别是Read Committed以上这个写法就没问题。但项目中有人偷懒用:SELECT stock FROM inventory WHERE product_id 123; -- 应用层判断stock 0 UPDATE inventory SET stock stock - 1 WHERE product_id 123;这是经典的先查后改竞态条件。并发情况下多个请求都读到stock1然后同时通过判断最终库存变成负数。修复方式就是改成原子UPDATE或者加上SELECT ... FOR UPDATE把行先锁住。DB的并发控制是最后一道防线应用层的争抢逻辑才是一切的起点两边都得管。另外大批量更新时不要一个事务里Update几百万行。我见过有人用循环一条一条更新结果事务时间超长VACUUM完全来不及回收。更合理的做法是分批提交每批几百行既保证事务短小又能尽量少地持有锁。这个经验在数据订正、历史数据迁移时特别有用。5.4 参数调优事务并发的物理层面当并发量真的很大时光靠SQL写法已经不够需要从参数层面找空间。我在项目中调优的几个关键参数max_connections默认100对高并发应用太低了。但也不要无脑调到1000因为每个连接都会消耗内存PostgreSQL是进程模型连接越多内存开销越大。更好的方案是用连接池如PgBouncer把前端连接收敛到一两百个后端实际连接保持在几十个。max_prepared_transactions如果不用两阶段提交保持0和低值即可调大只会增加共享内存压力。shared_buffers默认128MB太小一般设置为物理内存的25%左右。但这个参数改了要重启我一般先在测试环境验证线上修改前一定要停业务窗口。deadlock_timeout默认1秒可以略微调大到2秒防止过于频繁的死锁检测给系统添负担但也不要太大否则死锁后响应太慢。lock_timeout建议全局设一个5~10秒的兜底防止某个会话无限等锁。一批参数改完一定要做压力测试看效果别直接上生产。我用pgbench模拟过100并发的场景对比调优前后的TPS和延迟效果最直观的就是shared_buffers和work_mem调整后查询耗时下降明显。但work_mem设太大内存也可能被大量排序操作占满要根据实际情况设一个平衡值比如排序比较重的库可以开到64MB~128MB普通OLTP设32MB就够。6. 事务持久化与高可用联动WAL、复制与并发控制的关系6.1 WAL在事务提交后的角色事务提交时PostgreSQL会把日志先刷到WAL再由后台进程异步将数据页刷到磁盘。这两个刷盘动作是有先后顺序的先WAL后数据崩溃恢复时才能保证一致性。在项目就是靠WAL实现物理复制和PITR时间点恢复的这是PostgreSQL和高可用架构能够平稳配合的根基。我在方案设计时常被问到既然MVCC在数据库内部能保证并发正确性为什么还需要主从复制答案是MVCC解决的是“单节点并发读写的正确性”但如果整个单节点宕机你连数据都没有了还谈什么一致性所以至少一主一从主库负责读写从库只读并提供故障转移能力。事务提交后WAL被传送到备机备机重放WAL最终达到事务级同步。事务的持久性在单机层面靠WAL在集群层面靠同步复制。PostgreSQL的synchronous_commit参数有多个级别remote_apply意味着备机已经应用了这个事务的WAL才返回客户端成功remote_write意味着备机只把WAL写到操作系统缓冲。如果业务能容忍极小概率的数据丢失用remote_write可以提高吞吐核心交易强烈建议remote_apply或至少on。我在一个偏金融类项目里用的是remote_apply事务提交延迟会显著增加但换来的是主库故障切换后备机没有丢任何已提交事务。这就是“事务并发控制”之外的更高维度——集群级的一致性。6.2 从库查询的隔离性问题一个容易忽视的点是从库上查询的快照基于它与主库同步的WAL位置。读到的事务状态有可能比主库稍微滞后但不会出现“部分事务可见”这种撕裂状态。因为PostgreSQL在从库上重放WAL时会保证事务提交记录的原子性——要么整个事务的变更全部可见要么全部不可见。项目中有个数据分析团队直接从备库做报表有一次跑出来的数据跟主库实时值差了几分钟他们以为是bug。其实是同步延迟导致的不是并发控制出错。我给他们重新解释了备库的隔离性边界备库数据是“某个时间点的一致性快照”适合报表和只读分析不适合需要强一致的余额查询。这类查询必须走主库。了解主从复制与MVCC的配合逻辑你才能在设计架构时不犯方向性错误。并发控制不是数据库单机的事而是整条数据链路上每个环节都要考虑的事。6.3 热点行更新序列化与控制在 Rocket 场景的妥协回到前面说的库存扣减如果是秒杀场景同一行被成千上万的请求同时更新即使有MVCC和行锁热点行依然是性能瓶颈。行锁本质是串行化的你不可能让两个事务同时修改同一行。常见优化手段是“分桶”把库存拆成多个子记录比如100件库存拆成10个桶每个桶10件更新时随机选一个桶扣减。这样热点从一行分散成十行冲突大幅下降。代价是查询总库存时要聚合逻辑变得复杂。我用这个方案帮一个电商朋友解决了大促时的锁等待效果立竿见影。另一个手段是用SKIP LOCKED跳过多余的锁冲突让多个并发任务各自取不同的行处理。比如批量任务从一张待处理表里取100行多个worker同时跑每个worker只想拿到未被别人处理的行。这时候用SELECT ... FOR UPDATE SKIP LOCKED LIMIT 100拿不到的行就跳过而不是阻塞等待。这个特性极大提升了并行批处理的吞吐值得每个人掌握。7. 项目中遇到的高频问题与排查脚本速查7.1 高频问题汇总我整理了项目中反复出现的几个问题每个都够写一篇单独的文章但这里用表格给你速查现象最可能原因快速定位方法解决方向连接数打满长事务不提交 / 连接泄漏pg_stat_activity查idle in transaction终止空闲事务、加连接池告警大量锁等待事务中执行外部IO / 长事务持锁pg_locks关联pg_stat_activity移出外部调用、设置lock_timeout死锁报错多表更新顺序不一致查看死锁日志细节统一加锁顺序、重试机制表膨胀严重VACUUM不勤 / 大量更新删除查pg_class.relpages对比行数调autovacuum参数、手动VACUUM查询结果漂移Read Committed下多次快照不同查询隔离级别与事务范围升级隔离级别或保持事务短小复制延迟大备库大量查询/主库WAL量大查pg_stat_replication的replay lag优化备库查询、增加资源事务ID接近回卷autovacuum停摆太久查pg_database.datfrozenxid立即VACUUM冻结这些坑我全部踩过写出来主要是让你少跳几次。别嫌麻烦数据库这东西平时的巡检和自查习惯比任何高深技巧都重要。7.2 我常年维护的巡检SQL清单除了上面锁等待SQL我还会每天跑一个综合巡检。这里把最常用的几条列出来你可以做成脚本定时执行检查活跃查询和长事务SELECT pid, usename, application_name, client_addr, now() - xact_start AS transaction_age, now() - query_start AS query_age, state, wait_event_type, wait_event, left(query, 100) AS query_preview FROM pg_stat_activity WHERE state idle ORDER BY query_age DESC;检查表膨胀程度需要pgstattuple扩展或估算SELECT relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup n_dead_tup 0 THEN round(100.0 * n_dead_tup / (n_live_tup n_dead_tup), 2) ELSE 0 END AS dead_pct, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;检查复制延迟SELECT client_addr, state, sent_lsn, replay_lsn, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag FROM pg_stat_replication;这些SQL放在脚本里每天固定时间跑一遍输出异常即触发告警。三个月跑下来你会对系统行为产生非常强的直觉知道什么数值波动是正常的什么信号是要出大事的前奏。7.3 一次典型的锁等待抢救流程假设警报说业务超时了你登录上去看到大量“waiting for lock”。这个时候别慌按下面的流程走一般五分钟内可以恢复服务SELECT * FROM pg_stat_activity WHERE wait_event_type Lock AND state active找出所有等锁的会话。用上面那条pg_locks关联SQL找出每个等锁会话对应的阻塞源。判断阻塞源是什么类型的会话。如果是idle in transaction基本可以确定是事务没提交直接终止。如果阻塞源是正在跑一个慢查询比如全表扫描那就分析它的query_start看跑了多久。如果是报表任务占道了业务可以终止或者等它跑完。终止阻塞源后观察锁等待数量是否回落通常几秒内业务就会恢复。这一步最关键的是判断“能不能杀”我见过把别人正当业务会话杀掉导致后续错误的情况。所以我一般在终止前先把要杀的PID对应的application_name和客户端IP确认一遍确认不属于核心交易链路再动手。如果你拿不准宁可多等一会儿也别乱杀数据库抢救讲究的是稳妥而不是手快。8. 从单机到复杂链路事务并发不只是数据库的事8.1 数据库事务与分布式事务的边界当一个系统拆分成多个微服务每个服务用自己的数据库时单库事务已经覆盖不了全局。PostgreSQL虽然支持两阶段提交但分布式事务的协调成本非常高很多团队最终选择BASE模型用最终一致性弥补强一致性的不足。我记得有一次设计订单系统一开始想用一个事务同时更新订单库和库存库后来发现根本走不通——跨库事务在PG里可以通过dblink或外部数据包装器实现但性能很差脆弱且难以排障。最后我们改为本库事务保证自身数据一致跨库的状态流转用本地消息表和异步补偿完成。这个思路比硬憋全局事务稳妥得多也符合实际业务弹性要求。这个项目让我明白PostgreSQL的事务并发控制再强大也只是单库层面的一致性工具。设计系统时要清晰划分哪些状态可以用数据库事务保证哪些必须靠分布式协调。这个边界画得越清楚后面的故障就越少。8.2 性能与事务并发控制的平衡艺术调优到最后你会发现并发控制其实是在性能与正确性之间找平衡。MVCC降低了读写互斥但带来了膨胀和回收代价隔离级别提高了正确性但增加了冲突回滚概率行锁保证了写写安全但带来的等待个阻隔需要用算法优化来弥补。没有一种配置是万能的每个系统都要根据读写比、热点分布、业务容忍度来定。我自己的经验是先保证正确性再追求性能。如果业务逻辑有并发盲区哪怕TPS再高上线后也会被数据错误浇灭。反过来说如果你仔细梳理了业务发现很多地方其实不需要严格隔离那就可以用Read Committed加短事务去换吞吐。这个权衡没有标准答案只有case by case的判断。8.3 长事务与自动清理的长期运营长期运营中最隐蔽的敌人是长事务。一个开着事务不结束的会话不仅持有锁还会让VACUUM无法清理它启动之前产生的旧版本导致表膨胀和事务ID老化同时加剧。我在项目里设置了一个硬性规则应用层强制所有事务必须在5秒内完成超过就告警。这里不是限制业务逻辑而是倒逼程序设计把外部调用移出事务让每个事务真正做到“短小精悍”。从根本上看很多并发问题都不是数据库配置导致的而是由事务设计太粗放引起的。如果你也遇到类似问题建议先统计一下线上事务的平均时长和最大时长分布。如果最大时长超过10秒要仔细分析它做了什么是不是把外部IO、网络等待放进来了。清理这些坏事务比调任何参数都有效。9. 最后的一些实操心得这个项目做到尾声回头看整个排查和优化过程最大的体会是PostgreSQL的事务并发控制不是看几篇文档就能掌握的东西必须亲手去查pg_stat_activity去观察xmin分布去复现锁等待才能形成直觉。我强烈建议你在自己的测试环境里也跑一遍上面提到的这些SQL故意开两个事务去模拟更新冲突看看锁是怎么等待、怎么释放的。这种“手搓实验”比看一百遍概念都有用。另外要提的一个小技巧是PostgreSQL的日志配置值得认真对待。打开log_min_duration_statement把超过500毫秒的SQL记录下来打开log_lock_waits on和deadlock_timeout 2s锁等待超过阈值就写到日志。这样即使你不在现场事后翻日志也能复盘事故全过程。这个习惯救了我好几次强烈推荐。如果有条件建议给关键业务集群配置Prometheus监控把pg_locks的数量、pg_stat_activity里各种状态的数量、复制延迟、事务年龄都做成指标配好告警。否则等业务方来喊救命数据库通常已经拖了很久了。最后再分享一个判断标准如果你部署PostgreSQL半年一次死锁报错都没见过未必是系统很健康也可能是你的并发度太低死锁根本没机会触发。这背后的意思很简单——事务并发控制是一个在压力下才会显现真功夫的领域别等线上出事故才去重视平时就应当把它的原理和排查工具吃透。希望这篇复盘能给你省下一些我当初绕过的弯路。
PostgreSQL事务并发控制实战：MVCC、锁与调优全解析
1. 项目背景与核心问题为什么单独把PostgreSQL事务并发控制拎出来讲做数据库管理这些年处理过MySQL、Oracle、SQL Server也搞过一些国产数据库但真正让我觉得需要单独花时间去啃底层的还是PostgreSQL的事务并发控制。大概在三月份我接到一个项目要求用PostgreSQL做核心业务库的支撑线上环境一跑起来先是出现不明所以的锁等待然后偶发死锁排查起来还挺费劲。翻了半天文档查了pg_stat_activity发现很多session卡在等待锁业务直接报超时。那段时间我几乎天天蹲在服务器前面盯日志最后总结出来PostgreSQL的事务并发控制如果你只停留在“知道有事务、有隔离级别”这个层面那线上肯定踩坑。这个项目标题是Postgresql数据库管理-事务并发控制0313正好对应我那次实战复盘。0313大概是某个版本或日期编号这不重要重要的是PostgreSQL的事务管理和并发控制机制比很多人想象的复杂得多。很多同行问我PostgreSQL和MySQL到底怎么选事务这块差别在哪。其实PostgreSQL默认的并发控制方案是MVCC多版本并发控制这条路线和MySQL的InnoDB有点像但细节差异极大。如果你习惯了MySQL的undo log那一套转到PostgreSQL很容易在快照行为、可见性判断、垃圾回收这些环节上犯迷糊。这篇文章不是教科书式的API文档是我从一次真实项目里抠出来的经验。我会把我踩过的坑、写过的排查SQL、调整过的参数全部摊开讲适合三类人看一是刚接手PostgreSQL的DBA二是写业务代码但对数据库隔离级别理解不深的开发三是准备从MySQL迁移到PostgreSQL的团队核心成员。看完之后你应该能回答三个问题PostgreSQL的多版本到底怎么工作为什么锁冲突突然飙升以及遇到类似线上事故第一步该看什么。这个项目里我实际做的几件事其实每一步背后都有坑。先梳理项目需要解决的问题再看机制原理最后落到排查和调优这个顺序也是我希望读者走通的路。2. 事务管理的基石ACID与四档隔离级别的真实边界2.1 ACID在PostgreSQL里是怎么落地的事务是数据库最核心的抽象。你用BEGIN开启一个事务要么全部提交要么全部回滚这就是原子性Atomicity。PostgreSQL通过事务日志WAL保证这一点数据变更先写WAL再写数据页崩溃后靠WAL重放恢复未提交的部分直接丢弃。这个顺序是硬性设计我来项目里第一件事就是查wal_level和fsync配置因为如果这里配置不合理整个数据的可靠性就是空谈。一致性Consistency靠约束、触发器和规则来维护这部分业务负责居多。隔离性Isolation就是本文的重点PostgreSQL通过MVCC加锁的组合实现。持久性Durability在PostgreSQL里主要由fsyncon和合适的commit_delay、commit_siblings来控制。你如果图快手动把fsync关掉一个断电就可能整库损坏这是DBA绝对不能碰的底线。我在项目中注意到一个现象很多开发只把事务理解成“要么全成功要么全失败”完全忽略隔离级别对并发的影响。实际上隔离级别决定了你的事务在看到别人未提交/已提交数据时的行为这直接影响业务正确性。2.2 四种隔离级别默认不是最高的但合理PostgreSQL对标SQL标准提供了四个隔离级别隔离级别脏读不可重复读幻读说明Read Uncommitted可能实际PG不会发生可能可能行为等同于Read Committed但很少用Read Committed默认避免可能可能PostgreSQL默认级别每个语句取新快照Repeatable Read避免避免可能PG中不会出现幻读事务开始取快照直到事务结束Serializable避免避免避免通过SSI可串行化监控代价高有个容易被误解的点PostgreSQL的Read Uncommitted并不真正读取未提交数据它在内部升级为Read Committed的行为所以脏读不会发生。这其实是PostgreSQL故意设计的因为MVCC架构下未提交的版本对别人的快照不可见有必要单独为“读未提交”做一套特殊实现得不偿失。Repeatable Read在SQL标准里允许幻读但PostgreSQL用快照实现让事务的查询看到的都是事务启动那一刻的数据库状态所以幻读也不会出现。这里有一个我项目里真实踩过的问题在Repeatable Read下如果两个事务都更新同一行先提交的成功后提交的会收到串行化失败错误could not serialize access due to concurrent update很多业务代码没有处理这个错误直接导致接口报错。你得明确告诉开发PostgreSQL的并发冲突可能直接抛错这个错不是bug是隔离机制在起作用。2.3 隔离级别的选择不是越高越好我给业务建议很简单默认用Read Committed除非你有明确理由才升级。为什么因为我见过太多团队盲目把隔离级别调到Serializable然后发现系统并发能力暴跌延迟从几十毫秒涨到几百毫秒。PostgreSQL的Serializable是通过SSI可串行化快照隔离机制实现的它会跟踪读写集合发现潜在冲突就中止事务这个开销极大。反过来如果你做账务类系统要求在转账过程中不允许余额被并发修改覆盖那Repeatable Read是合适的配合SELECT ... FOR UPDATE对关键行加锁就能保证读改写序列正确。我做过账务服务行锁配合Repeatable Read是黄金组合。而普通电商库存查询读多写少Read Committed完全够用。不是所有业务都需要最高的隔离级别我们要追求的是“恰好满足需求的隔离边界”而不是“最安全的配置”。这句话写在SQL标准里但真正体会它需要经历一次并发事故。3. 深入PostgreSQL的MVCC快照、可见性与垃圾回收3.1 行版本与xmin/xmax每条数据都有自己的时间戳PostgreSQL的MVCC实现方式行业里叫“基于堆表的行版本链”。表里的每一行都可能存在多个版本每个版本带有两个隐藏的系统列xmin表示创建这个版本的事务IDxmax表示删除/锁定这个版本的事务ID。事务在读取数据时通过比较自身的事务快照和行的xmin/xmax判断哪个版本对它是可见的。我还记得刚入门时把xmin/xmax当成普通字段查出来看突然就理解了所有并发问题。比如执行SELECT xmin, xmax, * FROM t;你会看到每一行是被哪个事务插入的被哪个事务删除的。大量业务表里的旧版本没有被及时回收时你就能从xmin分布里看到极其古老的事务ID。这里有一个不得不说的老话题事务ID回卷。事务ID是32位整数理论上会循环。PostgreSQL设计了一个“冻结”freeze机制把足够老的行事务ID标记为 FrozenXID让它们对所有事务可见从而避免回卷导致数据不可见。但如果你关闭了autovacuum或者VACUUM长期不跑事务ID回卷一旦发生数据库会强制关闭保护这是生产事故级别的问题。我以前处理过一台老库datfrozenxid已经逼近临界值紧急手动VACUUM才救回来这个检查项一定要列入日常巡检。3.2 事务快照给并发事务“拍一张合影”快照Snapshot是MVCC判断可见性的核心数据结构。简单说一个事务开始或语句开始时PostgreSQL记录当时的活动事务列表生成一个快照。快照里包含所有活跃事务的ID列表、最小活跃事务ID、下一个事务ID等。数据行的事务ID如果落在快照范围之外并且已经提交那就可见如果落在活跃事务列表里说明还在运行中不可见。拿生活场景类比快照就像一群人合影你按下快门时记录下在场的所有人活跃事务。之后照片里有人走了提交有人来了新事务都不影响照片里的内容。这就是为什么Repeatable Read下两次查询结果一致因为用的是同一个快照。Read Committed默认每个语句重新拍快照所以两次SELECT之间如果有其他事务提交结果可能不同。这不是“幽灵数据”是PostgreSQL隔离级别的设计选择。项目里有一个报表查询在Read Committed下反复出现数据抖动排查到最后发现是某个高频事务在不停提交订单状态改动方式就是给报表事务升到Repeatable Read数据一下子稳定了。3.3 可见性规则老版本为什么会被看到新手最容易误解的地方是老版本数据为什么还在是不是有脏数据其实MVCC的妙处就在这——旧版本不会立刻消失而是保留在堆表里供持锁老事务或长事务读取。一个Update操作会产生新版本同时保留旧版本让正在执行的其他事务仍然可以读到旧值这样读写互不阻塞。但代价也随之而来数据的多版本堆积会让表膨胀。膨胀到什么程度呢我做过一个测试表频繁更新某一行两个小时不到表的大小扩大了一倍。如果你不跑VACUUM那些死行版本就会一直占用磁盘并拖慢索引扫描。这里我给出一个可见性的朴素判断方法执行一个查询前找到该事务的快照然后逐行判断——如果行的xmin早于快照中最小活跃事务ID且已提交则可见如果xmax是某活跃事务ID说明该行正被删除/更新通常不可见。具体判断细节当然更复杂有专门的函数HeapTupleSatisfiesMVCC但理解到这个程度你对线上SQL的行为就已经能做出八九不离十的判断了。3.4 VACUUM与autovacuum多版本下的清道夫VACUUM是PostgreSQL特有的垃圾回收机制。它负责清理死行版本、更新可见性映射、回收空间并维护统计信息。PostgreSQL默认开启autovacuum但默认参数并不一定能适配所有业务。我在项目里设置的几个重要参数是autovacuum_vacuum_scale_factor、autovacuum_vacuum_threshold、autovacuum_vacuum_cost_delay。默认scale_factor0.2的意思是表中20%的行变为死行时才触发autovacuum。对于大表来说20%是一个很大的量级十万行的小表没问题但一张两亿行的订单表要堆积四千万死行才触发表早就膨胀得不成样子了。我的建议是对超大表调低scale_factor比如0.05或0.01同时提高autovacuum_vacuum_cost_limit让回收更积极。另外高频更新的表可以单独设置autovacuum_vacuum_scale_factor 0.01和autovacuum_vacuum_threshold 1000这样可以显著控制膨胀率。不过别走向另一个极端——把autovacuum调得过于激进导致它反复扫描表大量消耗CPU和IO。我见过有的同学把autovacuum_vacuum_cost_delay0直接让VACUUM以全速运行结果业务时段CPU长时间100%核心查询全部变慢。VACUUM要的是长期稳定不是一次清完。我们项目的策略是业务低峰期调高强度高峰期恢复默认让系统自动平衡。还有一个很实用的检查SQL查表膨胀率。用pgstattuple扩展或者简单用pg_relation_size对比实际行数估算。我每天巡检时会看最大的十几张表的死行比例超过30%就考虑手动执行VACUUM (VERBOSE, ANALYZE)。4. 锁机制MVCC之外的另一根支柱4.1 表级锁与行级锁的层级关系MVCC解决读写的冲突但写与写之间的冲突还是要靠锁。PostgreSQL有一整套锁体系表级锁、行级锁、页级锁、咨询锁等其中我们日常打交道最多的是表级锁和行级锁。表级锁有8种ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE。命名看着挺绕但本质是锁粒度从低到高互相之间有冲突矩阵。例如普通的SELECT拿ACCESS SHARE锁UPDATE/DELETE/INSERT拿ROW EXCLUSIVE锁。最为暴力的ALTER TABLE拿ACCESS EXCLUSIVE锁它和所有其他锁都冲突会阻塞几乎所有操作。这解释了一个常见现象为什么你在业务低峰期跑一个ALTER TABLE整个表的读都卡住了。因为ACCESS EXCLUSIVE锁会把普通的SELECT也挡住即使PostgreSQL已经支持了ALTER TABLE ... ADD COLUMN的快速默认值填充但整个表的重写操作仍然是致命的锁源。行级锁主要是FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE、FOR KEY SHARE。这几个加锁强度不同FOR UPDATE是最重的不允许其他事务修改或删除该行FOR SHARE则允许别人读取不允许修改。在并发更新同一行时行锁是事务提交才释放的这就是很多锁等待的根源。4.2 锁等待与死锁的处理逻辑两个事务更新同一行后发起的那个会进入锁等待。锁等待本身不是错误但等太久就是问题。PostgreSQL中lock_timeout参数可以控制等待时长默认是0也就是无限等。我在项目里把关键业务连接的lock_timeout设为5秒配合应用层重试机制防止后台任务把系统拖死。死锁的处理就更有意思了。PostgreSQL的后台进程deadlock_timeout默认1秒会周期性检查等待图如果发现有环路会主动中止其中一个事务抛出类似deadlock detected的错误。受害者事务会被回滚其余事务得以继续。我之前处理过一个死锁案例是两张业务表顺序更新不一致导致的事务A先更新甲表再更新乙表事务B先更新乙表再更新甲表两者交叉等待就形成了环路。解决办法很土但有效——在应用层约定所有事务必须按照字典序依次获取表锁彻底消除环路。这类问题在代码评审阶段就该发现等线上报错已经晚了。4.3 锁监控用一条SQL看清锁全貌遇到锁等待第一件事就是查表。下面这条SQL是我项目里用了无数次的可以一眼看出当前阻塞和被阻塞的会话关系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, blocking_activity.state AS blocking_state, blocking_activity.xact_start AS blocking_xact_start FROM pg_catalog.pg_locks AS blocked_locks JOIN pg_catalog.pg_stat_activity AS blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks AS 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.pid ! blocked_locks.pid JOIN pg_catalog.pg_stat_activity AS blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.granted;这条SQL的思维是找到所有没获得锁的会话grantedfalse再去找持有同一对象锁且已获得锁的会话由后到前反推出阻塞源。一旦定位到阻塞源头判断该会话是活跃还是空闲如果是一个长时间未提交的空闲事务基本可以判定是业务代码漏了commit直接pg_terminate_backend(阻塞pid)就能解锁。线上第一次执行这个SQL时我同时开了好几遍、反复对比从直观可见性的角度你还需要配合pg_blocking_pids(pid)函数。例如想查某会话被谁阻塞直接SELECT pg_blocking_pids(1234);输出就是阻塞它的PID数组非常直观。4.4 咨询锁跨进程协作的利器PostgreSQL提供了咨询锁Advisory Lock它不是绑定在具体数据库行上而是由应用定义的锁。对DBA和开发来说这是实现分布式互斥、控制并发任务执行的宝贝。我项目中有一个场景多台应用服务器同时跑定时任务必须保证同一个时刻只有一台在执行。用咨询锁最优雅——在事务里执行SELECT pg_try_advisory_lock(任务编号)返回true就继续执行返回false就说明别的节点正在跑自动跳过。这个方案比数据库行锁、Redis分布式锁更简单可靠而且不依赖额外组件。不过要注意咨询锁分事务级和会话级pg_advisory_lock是会话级需要手动解锁pg_advisory_xact_lock是事务级事务结束自动释放。我一开始用错了函数导致锁一直挂在会话上差点造成线上任务全部堵死。后来统一改为事务级在事务内完成互斥逻辑干净利落。5. 线上实战一次事务并发事故的完整排查与解决5.1 事故现场业务报错开始到数据库卡死这个项目当时遇到的情况是促销活动一开始订单服务频繁报“Sorry, too many clients already”紧接着数据库CPU飙升大量查询堆积。我登录数据库以后发现pg_stat_activity里state是active的查询并不多大量会话处于idle in transaction状态这就是典型的“事务没结束还把持着锁”。深入查下去发现是项目里有个接口调用了外部支付渠道支付回调迟迟没返回而代码在回调返回之前不提交事务。结果就是每个支付请求都开一个事务占着一行订单的FOR UPDATE锁其他请求更新同一行时全部等待越积越多最终连接池被打满。这暴露了两个问题一是开发错误地将外部IO放在数据库事务内这是非常常见但很要命的模式二是缺少锁等待监控等到爆发才发现。索引监控等都对但没有对事务时长做告警等于裸奔。5.2 处理手段快速止血与治本止血操作很简单找出所有idle in transaction超过阈值比如5分钟的会话直接终止。SELECT pid, now() - xact_start AS duration, usename, state, query FROM pg_stat_activity WHERE state idle in transaction AND xact_start now() - interval 5 minutes ORDER BY duration DESC;确认无误后执行pg_terminate_backend(pid)批量清理。注意这一步操作要谨慎先看PID对应的业务确认是可以牺牲的不然应用端收到异常也麻烦。但在这个场景里外部回调早就超时了留着事务只会让系统更糟。治本措施有三条第一把外部调用移到事务之外先查库、调支付、拿到结果后再开启事务改状态第二给应用层加上事务超时设置比如JDBC层的socketTimeout和transactionTimeout防止捞不到回调时无限等待第三部署锁等待监控脚本每30秒扫描一次锁等待链超过阈值自动告警。5.3 用隔离级别和锁的组合优化业务SQL处理完事故我们又顺手优化了一批业务SQL。一个典型的更新操作比如商品库存扣减UPDATE inventory SET stock stock - 1 WHERE product_id 123 AND stock 0 RETURNING id;这句SQL的正确性依赖两个点stock 0条件防止超卖UPDATE自带行锁防止并发扣减覆盖。只要事务隔离级别是Read Committed以上这个写法就没问题。但项目中有人偷懒用:SELECT stock FROM inventory WHERE product_id 123; -- 应用层判断stock 0 UPDATE inventory SET stock stock - 1 WHERE product_id 123;这是经典的先查后改竞态条件。并发情况下多个请求都读到stock1然后同时通过判断最终库存变成负数。修复方式就是改成原子UPDATE或者加上SELECT ... FOR UPDATE把行先锁住。DB的并发控制是最后一道防线应用层的争抢逻辑才是一切的起点两边都得管。另外大批量更新时不要一个事务里Update几百万行。我见过有人用循环一条一条更新结果事务时间超长VACUUM完全来不及回收。更合理的做法是分批提交每批几百行既保证事务短小又能尽量少地持有锁。这个经验在数据订正、历史数据迁移时特别有用。5.4 参数调优事务并发的物理层面当并发量真的很大时光靠SQL写法已经不够需要从参数层面找空间。我在项目中调优的几个关键参数max_connections默认100对高并发应用太低了。但也不要无脑调到1000因为每个连接都会消耗内存PostgreSQL是进程模型连接越多内存开销越大。更好的方案是用连接池如PgBouncer把前端连接收敛到一两百个后端实际连接保持在几十个。max_prepared_transactions如果不用两阶段提交保持0和低值即可调大只会增加共享内存压力。shared_buffers默认128MB太小一般设置为物理内存的25%左右。但这个参数改了要重启我一般先在测试环境验证线上修改前一定要停业务窗口。deadlock_timeout默认1秒可以略微调大到2秒防止过于频繁的死锁检测给系统添负担但也不要太大否则死锁后响应太慢。lock_timeout建议全局设一个5~10秒的兜底防止某个会话无限等锁。一批参数改完一定要做压力测试看效果别直接上生产。我用pgbench模拟过100并发的场景对比调优前后的TPS和延迟效果最直观的就是shared_buffers和work_mem调整后查询耗时下降明显。但work_mem设太大内存也可能被大量排序操作占满要根据实际情况设一个平衡值比如排序比较重的库可以开到64MB~128MB普通OLTP设32MB就够。6. 事务持久化与高可用联动WAL、复制与并发控制的关系6.1 WAL在事务提交后的角色事务提交时PostgreSQL会把日志先刷到WAL再由后台进程异步将数据页刷到磁盘。这两个刷盘动作是有先后顺序的先WAL后数据崩溃恢复时才能保证一致性。在项目就是靠WAL实现物理复制和PITR时间点恢复的这是PostgreSQL和高可用架构能够平稳配合的根基。我在方案设计时常被问到既然MVCC在数据库内部能保证并发正确性为什么还需要主从复制答案是MVCC解决的是“单节点并发读写的正确性”但如果整个单节点宕机你连数据都没有了还谈什么一致性所以至少一主一从主库负责读写从库只读并提供故障转移能力。事务提交后WAL被传送到备机备机重放WAL最终达到事务级同步。事务的持久性在单机层面靠WAL在集群层面靠同步复制。PostgreSQL的synchronous_commit参数有多个级别remote_apply意味着备机已经应用了这个事务的WAL才返回客户端成功remote_write意味着备机只把WAL写到操作系统缓冲。如果业务能容忍极小概率的数据丢失用remote_write可以提高吞吐核心交易强烈建议remote_apply或至少on。我在一个偏金融类项目里用的是remote_apply事务提交延迟会显著增加但换来的是主库故障切换后备机没有丢任何已提交事务。这就是“事务并发控制”之外的更高维度——集群级的一致性。6.2 从库查询的隔离性问题一个容易忽视的点是从库上查询的快照基于它与主库同步的WAL位置。读到的事务状态有可能比主库稍微滞后但不会出现“部分事务可见”这种撕裂状态。因为PostgreSQL在从库上重放WAL时会保证事务提交记录的原子性——要么整个事务的变更全部可见要么全部不可见。项目中有个数据分析团队直接从备库做报表有一次跑出来的数据跟主库实时值差了几分钟他们以为是bug。其实是同步延迟导致的不是并发控制出错。我给他们重新解释了备库的隔离性边界备库数据是“某个时间点的一致性快照”适合报表和只读分析不适合需要强一致的余额查询。这类查询必须走主库。了解主从复制与MVCC的配合逻辑你才能在设计架构时不犯方向性错误。并发控制不是数据库单机的事而是整条数据链路上每个环节都要考虑的事。6.3 热点行更新序列化与控制在 Rocket 场景的妥协回到前面说的库存扣减如果是秒杀场景同一行被成千上万的请求同时更新即使有MVCC和行锁热点行依然是性能瓶颈。行锁本质是串行化的你不可能让两个事务同时修改同一行。常见优化手段是“分桶”把库存拆成多个子记录比如100件库存拆成10个桶每个桶10件更新时随机选一个桶扣减。这样热点从一行分散成十行冲突大幅下降。代价是查询总库存时要聚合逻辑变得复杂。我用这个方案帮一个电商朋友解决了大促时的锁等待效果立竿见影。另一个手段是用SKIP LOCKED跳过多余的锁冲突让多个并发任务各自取不同的行处理。比如批量任务从一张待处理表里取100行多个worker同时跑每个worker只想拿到未被别人处理的行。这时候用SELECT ... FOR UPDATE SKIP LOCKED LIMIT 100拿不到的行就跳过而不是阻塞等待。这个特性极大提升了并行批处理的吞吐值得每个人掌握。7. 项目中遇到的高频问题与排查脚本速查7.1 高频问题汇总我整理了项目中反复出现的几个问题每个都够写一篇单独的文章但这里用表格给你速查现象最可能原因快速定位方法解决方向连接数打满长事务不提交 / 连接泄漏pg_stat_activity查idle in transaction终止空闲事务、加连接池告警大量锁等待事务中执行外部IO / 长事务持锁pg_locks关联pg_stat_activity移出外部调用、设置lock_timeout死锁报错多表更新顺序不一致查看死锁日志细节统一加锁顺序、重试机制表膨胀严重VACUUM不勤 / 大量更新删除查pg_class.relpages对比行数调autovacuum参数、手动VACUUM查询结果漂移Read Committed下多次快照不同查询隔离级别与事务范围升级隔离级别或保持事务短小复制延迟大备库大量查询/主库WAL量大查pg_stat_replication的replay lag优化备库查询、增加资源事务ID接近回卷autovacuum停摆太久查pg_database.datfrozenxid立即VACUUM冻结这些坑我全部踩过写出来主要是让你少跳几次。别嫌麻烦数据库这东西平时的巡检和自查习惯比任何高深技巧都重要。7.2 我常年维护的巡检SQL清单除了上面锁等待SQL我还会每天跑一个综合巡检。这里把最常用的几条列出来你可以做成脚本定时执行检查活跃查询和长事务SELECT pid, usename, application_name, client_addr, now() - xact_start AS transaction_age, now() - query_start AS query_age, state, wait_event_type, wait_event, left(query, 100) AS query_preview FROM pg_stat_activity WHERE state idle ORDER BY query_age DESC;检查表膨胀程度需要pgstattuple扩展或估算SELECT relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup n_dead_tup 0 THEN round(100.0 * n_dead_tup / (n_live_tup n_dead_tup), 2) ELSE 0 END AS dead_pct, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;检查复制延迟SELECT client_addr, state, sent_lsn, replay_lsn, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag FROM pg_stat_replication;这些SQL放在脚本里每天固定时间跑一遍输出异常即触发告警。三个月跑下来你会对系统行为产生非常强的直觉知道什么数值波动是正常的什么信号是要出大事的前奏。7.3 一次典型的锁等待抢救流程假设警报说业务超时了你登录上去看到大量“waiting for lock”。这个时候别慌按下面的流程走一般五分钟内可以恢复服务SELECT * FROM pg_stat_activity WHERE wait_event_type Lock AND state active找出所有等锁的会话。用上面那条pg_locks关联SQL找出每个等锁会话对应的阻塞源。判断阻塞源是什么类型的会话。如果是idle in transaction基本可以确定是事务没提交直接终止。如果阻塞源是正在跑一个慢查询比如全表扫描那就分析它的query_start看跑了多久。如果是报表任务占道了业务可以终止或者等它跑完。终止阻塞源后观察锁等待数量是否回落通常几秒内业务就会恢复。这一步最关键的是判断“能不能杀”我见过把别人正当业务会话杀掉导致后续错误的情况。所以我一般在终止前先把要杀的PID对应的application_name和客户端IP确认一遍确认不属于核心交易链路再动手。如果你拿不准宁可多等一会儿也别乱杀数据库抢救讲究的是稳妥而不是手快。8. 从单机到复杂链路事务并发不只是数据库的事8.1 数据库事务与分布式事务的边界当一个系统拆分成多个微服务每个服务用自己的数据库时单库事务已经覆盖不了全局。PostgreSQL虽然支持两阶段提交但分布式事务的协调成本非常高很多团队最终选择BASE模型用最终一致性弥补强一致性的不足。我记得有一次设计订单系统一开始想用一个事务同时更新订单库和库存库后来发现根本走不通——跨库事务在PG里可以通过dblink或外部数据包装器实现但性能很差脆弱且难以排障。最后我们改为本库事务保证自身数据一致跨库的状态流转用本地消息表和异步补偿完成。这个思路比硬憋全局事务稳妥得多也符合实际业务弹性要求。这个项目让我明白PostgreSQL的事务并发控制再强大也只是单库层面的一致性工具。设计系统时要清晰划分哪些状态可以用数据库事务保证哪些必须靠分布式协调。这个边界画得越清楚后面的故障就越少。8.2 性能与事务并发控制的平衡艺术调优到最后你会发现并发控制其实是在性能与正确性之间找平衡。MVCC降低了读写互斥但带来了膨胀和回收代价隔离级别提高了正确性但增加了冲突回滚概率行锁保证了写写安全但带来的等待个阻隔需要用算法优化来弥补。没有一种配置是万能的每个系统都要根据读写比、热点分布、业务容忍度来定。我自己的经验是先保证正确性再追求性能。如果业务逻辑有并发盲区哪怕TPS再高上线后也会被数据错误浇灭。反过来说如果你仔细梳理了业务发现很多地方其实不需要严格隔离那就可以用Read Committed加短事务去换吞吐。这个权衡没有标准答案只有case by case的判断。8.3 长事务与自动清理的长期运营长期运营中最隐蔽的敌人是长事务。一个开着事务不结束的会话不仅持有锁还会让VACUUM无法清理它启动之前产生的旧版本导致表膨胀和事务ID老化同时加剧。我在项目里设置了一个硬性规则应用层强制所有事务必须在5秒内完成超过就告警。这里不是限制业务逻辑而是倒逼程序设计把外部调用移出事务让每个事务真正做到“短小精悍”。从根本上看很多并发问题都不是数据库配置导致的而是由事务设计太粗放引起的。如果你也遇到类似问题建议先统计一下线上事务的平均时长和最大时长分布。如果最大时长超过10秒要仔细分析它做了什么是不是把外部IO、网络等待放进来了。清理这些坏事务比调任何参数都有效。9. 最后的一些实操心得这个项目做到尾声回头看整个排查和优化过程最大的体会是PostgreSQL的事务并发控制不是看几篇文档就能掌握的东西必须亲手去查pg_stat_activity去观察xmin分布去复现锁等待才能形成直觉。我强烈建议你在自己的测试环境里也跑一遍上面提到的这些SQL故意开两个事务去模拟更新冲突看看锁是怎么等待、怎么释放的。这种“手搓实验”比看一百遍概念都有用。另外要提的一个小技巧是PostgreSQL的日志配置值得认真对待。打开log_min_duration_statement把超过500毫秒的SQL记录下来打开log_lock_waits on和deadlock_timeout 2s锁等待超过阈值就写到日志。这样即使你不在现场事后翻日志也能复盘事故全过程。这个习惯救了我好几次强烈推荐。如果有条件建议给关键业务集群配置Prometheus监控把pg_locks的数量、pg_stat_activity里各种状态的数量、复制延迟、事务年龄都做成指标配好告警。否则等业务方来喊救命数据库通常已经拖了很久了。最后再分享一个判断标准如果你部署PostgreSQL半年一次死锁报错都没见过未必是系统很健康也可能是你的并发度太低死锁根本没机会触发。这背后的意思很简单——事务并发控制是一个在压力下才会显现真功夫的领域别等线上出事故才去重视平时就应当把它的原理和排查工具吃透。希望这篇复盘能给你省下一些我当初绕过的弯路。
