1. 理解事务先理解PostgreSQL的MVCC世界观1.1 快照隔离不是只读播放器不少从MySQL转过来的朋友刚开始用PostgreSQL时都会有一个困惑明明自己在事务里改了数据为什么另一个连接在同样的隔离级别下却看不到这背后其实是PostgreSQL对事务并发的核心设计——MVCC多版本并发控制。打个比方MySQL的InnoDB更像是一个事后清理派它把旧版本数据放在undo log里当事务提交后异步清理而PostgreSQL是就地留痕派更新一行数据时不会直接抹掉旧值而是像写便签一样在原来的数据页旁边追加一个新版本旧版本继续留在原地直到某个时刻被专门的回收机制清走。两种设计没有绝对优劣但直接决定了你在事务处理中会遇到的所有行为差异。理解这个底层逻辑是本文所有内容的地基。如果你只是记住PG默认读已提交MVCC性能好这几句结论遇到真实场景照样会被坑。我在下面会把MVCC的核心机制拆开讲清楚。1.2 xmin/xmax隐藏字段决定了你看到什么PostgreSQL的每个数据行tuple上都带着几个隐藏的系统字段其中xmin和xmax是事务可见性判断的钥匙。xmin插入这行数据的事务IDxmax删除或更新这行的事务ID如果没有则为空当你执行一条UPDATE时PG并不是修改原有行而是插入一行新版本同时在新行上写xmin为当前事务ID在旧行上写xmax为当前事务ID然后通过版本链把新旧行串起来。一个小技巧是你在pg_stat_activity里看到的事务ID不断增长但其实真正增长的是事务快照的分配节奏。查询时PostgreSQL通过比较当前事务的快照snapshot与每行的xmin/xmax来判断这行对当前事务是否可见。快照是什么简单说就是在我这个事务开始的那个时间点系统里哪些事务已经提交了。注意这里的快照不是MySQL那种把数据拷贝一份出来而是一个包含活跃事务列表的内存结构。PostgreSQL的快照是轻量级的只是记录了当下正在跑的、还没提交的事务ID集合。这意味着读操作从不阻塞写操作写操作也从不阻塞读操作。你在A连接里跑一个大事务更新百万行B连接照样能查只不过查到的是事务开始前的旧版本。这一点和很多传统数据库的锁机制完全不同也是PostgreSQL在高并发读写场景下表现稳定的核心因素。1.3 回滚段不存在垃圾回收是另一套玩法既然旧版本数据还留在数据页里那它们什么时候被清掉这就要说到PostgreSQL和MySQL最直观的区别MySQL有undo log靠purge线程清理旧版本PostgreSQL没有回滚段它靠的是VACUUM机制。当所有活跃事务都不再需要某个旧版本时这个版本就成了dead tuple死元组。VACUUM负责把这些死元组标记为可复用空间然后由后续插入逐步覆盖。自动化清理进程autovacuum会定期干活但如果你的事务处理非常密集autovacuum可能跟不上生成死元组的速度表就会膨胀占用磁盘越来越多查询性能也会因扫描大量无用版本而明显下降。这就引出了事务处理中一个容易被忽视的问题事务不仅影响数据一致性还直接影响数据库的物理存储占用。后面我会专门讲大事务的代价和VACUUM怎么配合。2. 隔离级别PostgreSQL的默认值和MySQL不一样2.1 读已提交每条SQL都有新快照PostgreSQL默认的隔离级别是读已提交READ COMMITTED这一点和MySQL默认的可重复读REPEATABLE READ正好相反。在读已提交级别下事务里的每条SQL语句都会重新获取一次快照。也就是说同一个事务里前后两条SELECT看到的数据可能不一样只要中间有另一个事务提交了数据。看个例子-- 事务A BEGIN; SELECT balance FROM accounts WHERE id 1; -- 结果是100 -- 此时事务B更新了id1的balance为200并提交 SELECT balance FROM accounts WHERE id 1; -- 结果是200 COMMIT;对很多从MySQL过来的人来说这个行为一开始会觉得诡异——不是说事务隔离应该保证一致性吗但在PostgreSQL的语义里读已提交保证的只是每个语句看到的是语句开始前已提交的数据不保证整个事务的读一致性。那这个级别适合什么场景绝大多数OLTP业务都适合。因为每条SQL拿新快照读到的数据总是最新的应用层拿到的数据新鲜度高。而且在这个级别下配合行级锁可以规避大部分并发冲突。2.2 可重复读一个事务从头到尾一个快照如果业务真的需要整个事务内看到一致的数据视图就用可重复读REPEATABLE READ级别。BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id 1; -- 结果是100 -- 此时事务B更新id1的balance为200并提交 SELECT balance FROM accounts WHERE id 1; -- 结果仍是100 COMMIT;原理很简单可重复读级别下快照在事务的第一条SQL执行时创建整个事务期间一直使用这个快照不换新的。但要注意可重复读不是天生的我改了别人就不能改。它只保证你读到的是旧快照不保证你写的时候不会和别人冲突。在可重复读级别下如果两个事务同时更新同一行后提交的那个会直接报错ERROR: could not serialize access due to concurrent update这个报错在MySQL里几乎不会出现但在PostgreSQL里是家常便饭。应用代码里必须把它当作可预期的业务异常来处理通常做法是捕获异常后重试整个事务。2.3 序列化真正意义上的串行最高的序列化SERIALIZABLE级别PostgreSQL用的是SSISerializable Snapshot Isolation算法。听起来高大上其实它的核心思想是允许并发执行但通过检测读写依赖关系确保最终结果等价于某个串行顺序。SSI的检测会在提交时进行如果发现两个事务之间存在危险的读写冲突就会让其中一个以序列化失败告终。这是一种乐观并发控制思路——不提前锁死资源而是事后在提交点裁决。好处是并发度高坏处是序列化失败率在某些高冲突场景下会明显上升。从实战角度看除非你的业务确实需要最严格的隔离保证比如金融转账系统的对账逻辑、库存扣减的超卖防护否则不建议日常业务全部切到SERIALIZABLE级别。因为重试逻辑写起来麻烦而且性能表现不稳定。PostgreSQL官方文档也明确说SERIALIZABLE只应该在你确实需要它的时候才用。3. 事务实操savepoint、DDL事务与大事务的代价3.1 savepoint部分回滚的正确玩法先抛一个场景你在事务里先插入了一条订单主记录然后插入三条明细插第三条时发现数据有问题能不能只撤销第三条保留前两条答案是可以但要用savepoint保存点而不是直接ROLLBACK。一旦执行ROLLBACK整个事务就全部回滚了前两条也白插了。BEGIN; INSERT INTO orders (id, total) VALUES (1001, 500); SAVEPOINT order_items_save; INSERT INTO order_items (order_id, item_id, qty) VALUES (1001, 1, 2); INSERT INTO order_items (order_id, item_id, qty) VALUES (1001, 2, 3); INSERT INTO order_items (order_id, item_id, qty) VALUES (1002, 99, 1); -- 外键校验失败 ROLLBACK TO order_items_save; -- 重新执行正确数据 INSERT INTO order_items (order_id, item_id, qty) VALUES (1001, 3, 1); COMMIT;这个操作在PostgreSQL内部其实是开了子事务subtransaction。子事务的机制不是简单的标记回滚点它在事务ID分配、锁持有、错误捕获等方面都有独立的处理逻辑这也是PostgreSQL比很多数据库在事务灵活性上更强的地方。实际业务中savepoint最常见的应用场景是批量导入数据的逐条处理一条失败不拖垮整批又不丢失前面已经成功的数据。比如用Python的psycopg2库做批量导入时可以在每条记录前设置savepoint失败时回滚到savepoint并记录错误继续下一条。3.2 DDL具有事务性这是PG的巨大优势PostgreSQL有一个让很多从MySQL转过来的开发者眼前一亮的能力DDL语句CREATE TABLE、ALTER TABLE、DROP TABLE等可以放在事务里而且可以回滚。在MySQL的InnoDB中DDL大多是不支持事务回滚的。你执行一个ALTER TABLE ADD COLUMN改错了就是改错了只能再发一条ALTER TABLE去掉如果表很大这个过程可能要锁表很久。而在PostgreSQL里你可以这样BEGIN; ALTER TABLE users ADD COLUMN phone VARCHAR(20); -- 此时发现phone字段长度应该用30或者字段名拼错了 ROLLBACK; -- 整个ALTER操作完全撤销表结构和数据毫无变化这对线上运维和架构调整简直是救命能力。我在实际工作中做迁移脚本时习惯把一组DDL和初始DML数据放在同一个事务里执行配合CI/CD流水线一旦哪一步失败就整体回滚应用代码完全不用感知中间状态。不过要提醒一句虽然DDL可以回滚但长事务期间DDL会持有AccessExclusiveLock锁这是个排他锁会阻塞所有对表的读写操作。所以不要在业务高峰期跑大表的事务性DDL即使它能回滚锁等待带来的业务影响是实打实的。3.3 大事务为什么危险膨胀与vacuum前面说过PostgreSQL的UPDATE会产生旧版本事务提交后旧版本就成了死元组。如果一个大事务跑了很久它产生的死元组数量可能非常庞大而且因为存在时间较长autovacuum可能一直等到事务结束才敢真正清理这些版本。这就带来两个问题第一事务期间产生的死元组不能被清理是因为那些旧版本可能还被更早的快照需要。PG的清理逻辑必须保证任何活跃事务可能看到的数据版本都不能被清掉。所以长事务会拖住VACUUM的清理进度导致表膨胀。第二膨胀不只是磁盘问题。PostgreSQL的查询优化器在做全表扫描时需要读取包含死元组在内的所有页面膨胀率越高顺序扫描的代价越大。有时候你发现一条简单的SELECT count(*)越来越慢一看表的膨胀率已经超过200%这种问题靠加索引是解决不了的。控制大事务的几个实操建议大批量数据更新拆成小批次每批一个事务提交长事务中避免做大量UPDATE尽量改成INSERT加定时清理策略关注pg_stat_user_tables里n_dead_tup和last_autovacuum字段及时人工VACUUM对超级大佬表考虑开启表的fillfactor参数比如fillfactor70在页面中预留30%的空闲空间给行版本更新用能显著降低更新场景下的页面分裂概率4. 和MySQL事务处理的差异迁移者最关心的对照表4.1 隔离级别默认值不同这是迁移时第一个遇到的坑。MySQL InnoDB的默认隔离级别是可重复读REPEATABLE READPostgreSQL的默认级别是读已提交READ COMMITTED。这意味着同样的应用代码不做任何改动直接切换到PostgreSQL后长事务内的读一致性行为会发生变化。典型例子应用在一个事务里先查配置表再做一系列业务逻辑然后在事务末尾再查一次配置表期望两次结果一致。在MySQL默认隔离级别下成立在PostgreSQL默认级别下就可能不成立——如果事务期间有人改了配置表第二次查会拿到新值。解决办法不是盲目把PostgreSQL全局default_transaction_isolation改成repeatable read这样做了之后很多依赖读已提交语义的应用反而会踩并发更新失败的坑而是在确实需要一致读的业务代码里显式声明事务隔离级别比如事务开始的SQL里加上BEGIN ISOLATION LEVEL REPEATABLE READ。4.2 锁机制不同行锁还是间隙锁MySQL InnoDB的行锁和间隙锁机制在可重复读级别下防的是幻读现象用的是Next-Key Lock临键锁。PostgreSQL在可重复读级别下因为MVCC快照本身就保证了一致读根本不需要间隙锁——读取是快照读不会因为其他事务插入新行而产生幻读。但这个设计有一个连带效果PostgreSQL没有间隙锁这种在索引区间上防止插入的锁机制。如果你用SELECT ... FOR UPDATE去锁定一个范围别的连接照样可以在这个范围内插入新行。这在某些先查询范围再插入的业务逻辑里需要特别注意加唯一约束或用咨询锁来补救。4.3 崩溃恢复行为差异redo与WALMySQL的崩溃恢复依赖redo logPostgreSQL依赖WALWrite-Ahead Logging。两者思路相近都是先写日志再写数据但细节上PostgreSQL的WAL日志内容更丰富支持的时间点恢复PITR能力也更强。从事务处理角度看一个实际差异是MySQL在崩溃恢复时对未提交事务会做回滚undoPostgreSQL在崩溃恢复时是不回滚的——它通过WAL里记录的前镜像和后镜像把数据恢复到最后一个已提交事务之后、崩溃瞬间之前的状态期间未提交事务的修改会自然变成死元组后续由VACUUM清理。这就导致一个现象PostgreSQL崩溃恢复速度通常更快因为不需要逐一回滚未完事务但恢复后如果有很多未提交事务遗留下来的垃圾版本需要VACUUM来处理短时间内容量会膨胀一点。4.4 一个常见错误把MySQL的SELECT...FOR UPDATE习惯搬过来很多做库存防超卖的方案在MySQL下的写法是SELECT stock FROM products WHERE id 1 FOR UPDATE; -- 业务判断库存是否足够 -- 执行UPDATE扣减这段代码在MySQL里能正常工作核心是FOR UPDATE触发了当前读锁住了这行数据后续并发事务只能排队等待。到了PostgreSQL里同样的代码也能跑但行为有差异。PostgreSQL的FOR UPDATE是直接锁行锁的是最新版本后续其他事务更新同一行会阻塞等待锁释放。如果前一个事务长时间不COMMIT后一个事务会一直等直到lock_timeout或statement_timeout触发。在可重复读级别下如果两个事务同时FOR UPDATE同一行后执行FOR UPDATE的事务会报序列化失败。我的建议在PostgreSQL里做防超卖优先考虑原子UPDATE比如UPDATE products SET stock stock - 1 WHERE id 1 AND stock 0 RETURNING stock;这条SQL本身就是一个自包含事务数据库层面保证了不会超卖性能也比FOR UPDATE加业务判断更高效。这种写法上的差异迁移时一定得改不改就会出现莫名其妙的锁等待或并发报错。5. 事务异常现场锁等待、死锁与回滚的排查实录5.1 锁等待最容易被忽略的细节你有没有遇到过这种情况一个UPDATE语句挂在那边等了十几分钟也不报错startup日志里也没有明显异常。第一反应确实应该是查锁等待。PostgreSQL里查锁状态直接用系统视图SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state active AND wait_event IS NOT NULL;如果看到wait_event_type是Lockwait_event是transactionid说明当前事务在等待另一个事务释放锁。再进一步查谁锁住了它SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocked.pg_backend_pid blocking.pid WHERE blocked.wait_event_type Lock;我最常忽略的细节是锁等待不一定是另一个事务没提交很多时候是另一个事务忘了提交。有人会在客户端里开一个事务跑完了业务但应用代码没有显式COMMIT连接池里的连接就一直占着开着的状态锁也不释放。查pg_stat_activity时它的state还是idle in transaction这个状态非常容易漏看因为state显示idle看起来像空闲连接但实际上它握着一堆锁。处理办法很简单给idle in transaction设置超时。PostgreSQL配置项idle_in_transaction_session_timeout可以设置超过时间强制断开这类连接。我建议生产环境至少设置成30秒避免这种假空闲拖垮整个业务。5.2 死锁出现在你想不到的时候死锁的经典场景是两个事务分别持有对方需要的锁。PostgreSQL有死锁检测机制默认deadlock_timeout是1秒超过这个时间就主动回滚其中一个事务来打破死锁。但比较隐蔽的死锁往往不是简单的两行交叉锁而是多表更新顺序不一致导致的。举个例子-- 事务A BEGIN; UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; COMMIT; -- 事务B BEGIN; UPDATE accounts SET balance balance - 100 WHERE id 2; UPDATE accounts SET balance balance 100 WHERE id 1; COMMIT;如果两个事务同时跑A锁住了id1B锁住了id2然后A想更新id2B想更新id1就死锁了。这种情况在批量转账、批量结算场景里很容易触发。解决思路只有一个所有事务都按照相同的顺序更新行。比如约定总是先更新id小的行再更新id大的行。这个约定必须在应用层代码里规范死否则数据库的死锁检测虽然能兜底但频繁死锁会让业务方看到大量报错而且事务被回滚后需要重试逻辑配合。5.3 事务开的久不一定是因为逻辑慢排查XactStart时间长的会话时很多人会盯着SQL看怀疑某个SQL本身跑得慢。但有一种情况很误导人事务开得很早pg_stat_activity里xact_start时间是半小时前但你看到的那条SQL刚刚开始执行执行时间其实只有几十毫秒。这种事务时间久但单条SQL快的现象往往是因为应用代码在长事务中间做了外部API调用、等待用户输入、或者处理逻辑本身耗时很久。比如Java的Spring事务里嵌了一个远程HTTP调用调用耗时3秒加上事务内多次查询、数据组装整体事务耗时可能达到几十秒。这种长事务对PostgreSQL的伤害我在前面讲过——拖住VACUUM、锁持有时间长、死元组堆积。所以排查时除了看SQL执行计划还要结合应用的调用链分析事务边界内都干了什么。凡是事务里有网络I/O、外部服务调用的都要坚决拆出去放到事务提交之后再做。这个经验帮我在生产环境解决过好几次数据库突然变慢的故障其实根子都在应用层的事务边界设计不合理。再分享一个我自己的习惯每一条事务处理的SQL都顺手用EXPLAIN ANALYZE验证执行计划每一条疑似锁等待的事务都先看pg_stat_activity里的wait_event_type再动手。事务处理从来不是一个单纯的数据库开关问题它横跨了应用设计、SQL写法、并发模型和运维监控四个层面。把每个层面都摸到底才能在PostgreSQL里写出真正抗高并发的事务代码。 PostgreSQL事务处理全解析：MVCC、隔离级别与锁等待实战
1. 理解事务先理解PostgreSQL的MVCC世界观1.1 快照隔离不是只读播放器不少从MySQL转过来的朋友刚开始用PostgreSQL时都会有一个困惑明明自己在事务里改了数据为什么另一个连接在同样的隔离级别下却看不到这背后其实是PostgreSQL对事务并发的核心设计——MVCC多版本并发控制。打个比方MySQL的InnoDB更像是一个事后清理派它把旧版本数据放在undo log里当事务提交后异步清理而PostgreSQL是就地留痕派更新一行数据时不会直接抹掉旧值而是像写便签一样在原来的数据页旁边追加一个新版本旧版本继续留在原地直到某个时刻被专门的回收机制清走。两种设计没有绝对优劣但直接决定了你在事务处理中会遇到的所有行为差异。理解这个底层逻辑是本文所有内容的地基。如果你只是记住PG默认读已提交MVCC性能好这几句结论遇到真实场景照样会被坑。我在下面会把MVCC的核心机制拆开讲清楚。1.2 xmin/xmax隐藏字段决定了你看到什么PostgreSQL的每个数据行tuple上都带着几个隐藏的系统字段其中xmin和xmax是事务可见性判断的钥匙。xmin插入这行数据的事务IDxmax删除或更新这行的事务ID如果没有则为空当你执行一条UPDATE时PG并不是修改原有行而是插入一行新版本同时在新行上写xmin为当前事务ID在旧行上写xmax为当前事务ID然后通过版本链把新旧行串起来。一个小技巧是你在pg_stat_activity里看到的事务ID不断增长但其实真正增长的是事务快照的分配节奏。查询时PostgreSQL通过比较当前事务的快照snapshot与每行的xmin/xmax来判断这行对当前事务是否可见。快照是什么简单说就是在我这个事务开始的那个时间点系统里哪些事务已经提交了。注意这里的快照不是MySQL那种把数据拷贝一份出来而是一个包含活跃事务列表的内存结构。PostgreSQL的快照是轻量级的只是记录了当下正在跑的、还没提交的事务ID集合。这意味着读操作从不阻塞写操作写操作也从不阻塞读操作。你在A连接里跑一个大事务更新百万行B连接照样能查只不过查到的是事务开始前的旧版本。这一点和很多传统数据库的锁机制完全不同也是PostgreSQL在高并发读写场景下表现稳定的核心因素。1.3 回滚段不存在垃圾回收是另一套玩法既然旧版本数据还留在数据页里那它们什么时候被清掉这就要说到PostgreSQL和MySQL最直观的区别MySQL有undo log靠purge线程清理旧版本PostgreSQL没有回滚段它靠的是VACUUM机制。当所有活跃事务都不再需要某个旧版本时这个版本就成了dead tuple死元组。VACUUM负责把这些死元组标记为可复用空间然后由后续插入逐步覆盖。自动化清理进程autovacuum会定期干活但如果你的事务处理非常密集autovacuum可能跟不上生成死元组的速度表就会膨胀占用磁盘越来越多查询性能也会因扫描大量无用版本而明显下降。这就引出了事务处理中一个容易被忽视的问题事务不仅影响数据一致性还直接影响数据库的物理存储占用。后面我会专门讲大事务的代价和VACUUM怎么配合。2. 隔离级别PostgreSQL的默认值和MySQL不一样2.1 读已提交每条SQL都有新快照PostgreSQL默认的隔离级别是读已提交READ COMMITTED这一点和MySQL默认的可重复读REPEATABLE READ正好相反。在读已提交级别下事务里的每条SQL语句都会重新获取一次快照。也就是说同一个事务里前后两条SELECT看到的数据可能不一样只要中间有另一个事务提交了数据。看个例子-- 事务A BEGIN; SELECT balance FROM accounts WHERE id 1; -- 结果是100 -- 此时事务B更新了id1的balance为200并提交 SELECT balance FROM accounts WHERE id 1; -- 结果是200 COMMIT;对很多从MySQL过来的人来说这个行为一开始会觉得诡异——不是说事务隔离应该保证一致性吗但在PostgreSQL的语义里读已提交保证的只是每个语句看到的是语句开始前已提交的数据不保证整个事务的读一致性。那这个级别适合什么场景绝大多数OLTP业务都适合。因为每条SQL拿新快照读到的数据总是最新的应用层拿到的数据新鲜度高。而且在这个级别下配合行级锁可以规避大部分并发冲突。2.2 可重复读一个事务从头到尾一个快照如果业务真的需要整个事务内看到一致的数据视图就用可重复读REPEATABLE READ级别。BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id 1; -- 结果是100 -- 此时事务B更新id1的balance为200并提交 SELECT balance FROM accounts WHERE id 1; -- 结果仍是100 COMMIT;原理很简单可重复读级别下快照在事务的第一条SQL执行时创建整个事务期间一直使用这个快照不换新的。但要注意可重复读不是天生的我改了别人就不能改。它只保证你读到的是旧快照不保证你写的时候不会和别人冲突。在可重复读级别下如果两个事务同时更新同一行后提交的那个会直接报错ERROR: could not serialize access due to concurrent update这个报错在MySQL里几乎不会出现但在PostgreSQL里是家常便饭。应用代码里必须把它当作可预期的业务异常来处理通常做法是捕获异常后重试整个事务。2.3 序列化真正意义上的串行最高的序列化SERIALIZABLE级别PostgreSQL用的是SSISerializable Snapshot Isolation算法。听起来高大上其实它的核心思想是允许并发执行但通过检测读写依赖关系确保最终结果等价于某个串行顺序。SSI的检测会在提交时进行如果发现两个事务之间存在危险的读写冲突就会让其中一个以序列化失败告终。这是一种乐观并发控制思路——不提前锁死资源而是事后在提交点裁决。好处是并发度高坏处是序列化失败率在某些高冲突场景下会明显上升。从实战角度看除非你的业务确实需要最严格的隔离保证比如金融转账系统的对账逻辑、库存扣减的超卖防护否则不建议日常业务全部切到SERIALIZABLE级别。因为重试逻辑写起来麻烦而且性能表现不稳定。PostgreSQL官方文档也明确说SERIALIZABLE只应该在你确实需要它的时候才用。3. 事务实操savepoint、DDL事务与大事务的代价3.1 savepoint部分回滚的正确玩法先抛一个场景你在事务里先插入了一条订单主记录然后插入三条明细插第三条时发现数据有问题能不能只撤销第三条保留前两条答案是可以但要用savepoint保存点而不是直接ROLLBACK。一旦执行ROLLBACK整个事务就全部回滚了前两条也白插了。BEGIN; INSERT INTO orders (id, total) VALUES (1001, 500); SAVEPOINT order_items_save; INSERT INTO order_items (order_id, item_id, qty) VALUES (1001, 1, 2); INSERT INTO order_items (order_id, item_id, qty) VALUES (1001, 2, 3); INSERT INTO order_items (order_id, item_id, qty) VALUES (1002, 99, 1); -- 外键校验失败 ROLLBACK TO order_items_save; -- 重新执行正确数据 INSERT INTO order_items (order_id, item_id, qty) VALUES (1001, 3, 1); COMMIT;这个操作在PostgreSQL内部其实是开了子事务subtransaction。子事务的机制不是简单的标记回滚点它在事务ID分配、锁持有、错误捕获等方面都有独立的处理逻辑这也是PostgreSQL比很多数据库在事务灵活性上更强的地方。实际业务中savepoint最常见的应用场景是批量导入数据的逐条处理一条失败不拖垮整批又不丢失前面已经成功的数据。比如用Python的psycopg2库做批量导入时可以在每条记录前设置savepoint失败时回滚到savepoint并记录错误继续下一条。3.2 DDL具有事务性这是PG的巨大优势PostgreSQL有一个让很多从MySQL转过来的开发者眼前一亮的能力DDL语句CREATE TABLE、ALTER TABLE、DROP TABLE等可以放在事务里而且可以回滚。在MySQL的InnoDB中DDL大多是不支持事务回滚的。你执行一个ALTER TABLE ADD COLUMN改错了就是改错了只能再发一条ALTER TABLE去掉如果表很大这个过程可能要锁表很久。而在PostgreSQL里你可以这样BEGIN; ALTER TABLE users ADD COLUMN phone VARCHAR(20); -- 此时发现phone字段长度应该用30或者字段名拼错了 ROLLBACK; -- 整个ALTER操作完全撤销表结构和数据毫无变化这对线上运维和架构调整简直是救命能力。我在实际工作中做迁移脚本时习惯把一组DDL和初始DML数据放在同一个事务里执行配合CI/CD流水线一旦哪一步失败就整体回滚应用代码完全不用感知中间状态。不过要提醒一句虽然DDL可以回滚但长事务期间DDL会持有AccessExclusiveLock锁这是个排他锁会阻塞所有对表的读写操作。所以不要在业务高峰期跑大表的事务性DDL即使它能回滚锁等待带来的业务影响是实打实的。3.3 大事务为什么危险膨胀与vacuum前面说过PostgreSQL的UPDATE会产生旧版本事务提交后旧版本就成了死元组。如果一个大事务跑了很久它产生的死元组数量可能非常庞大而且因为存在时间较长autovacuum可能一直等到事务结束才敢真正清理这些版本。这就带来两个问题第一事务期间产生的死元组不能被清理是因为那些旧版本可能还被更早的快照需要。PG的清理逻辑必须保证任何活跃事务可能看到的数据版本都不能被清掉。所以长事务会拖住VACUUM的清理进度导致表膨胀。第二膨胀不只是磁盘问题。PostgreSQL的查询优化器在做全表扫描时需要读取包含死元组在内的所有页面膨胀率越高顺序扫描的代价越大。有时候你发现一条简单的SELECT count(*)越来越慢一看表的膨胀率已经超过200%这种问题靠加索引是解决不了的。控制大事务的几个实操建议大批量数据更新拆成小批次每批一个事务提交长事务中避免做大量UPDATE尽量改成INSERT加定时清理策略关注pg_stat_user_tables里n_dead_tup和last_autovacuum字段及时人工VACUUM对超级大佬表考虑开启表的fillfactor参数比如fillfactor70在页面中预留30%的空闲空间给行版本更新用能显著降低更新场景下的页面分裂概率4. 和MySQL事务处理的差异迁移者最关心的对照表4.1 隔离级别默认值不同这是迁移时第一个遇到的坑。MySQL InnoDB的默认隔离级别是可重复读REPEATABLE READPostgreSQL的默认级别是读已提交READ COMMITTED。这意味着同样的应用代码不做任何改动直接切换到PostgreSQL后长事务内的读一致性行为会发生变化。典型例子应用在一个事务里先查配置表再做一系列业务逻辑然后在事务末尾再查一次配置表期望两次结果一致。在MySQL默认隔离级别下成立在PostgreSQL默认级别下就可能不成立——如果事务期间有人改了配置表第二次查会拿到新值。解决办法不是盲目把PostgreSQL全局default_transaction_isolation改成repeatable read这样做了之后很多依赖读已提交语义的应用反而会踩并发更新失败的坑而是在确实需要一致读的业务代码里显式声明事务隔离级别比如事务开始的SQL里加上BEGIN ISOLATION LEVEL REPEATABLE READ。4.2 锁机制不同行锁还是间隙锁MySQL InnoDB的行锁和间隙锁机制在可重复读级别下防的是幻读现象用的是Next-Key Lock临键锁。PostgreSQL在可重复读级别下因为MVCC快照本身就保证了一致读根本不需要间隙锁——读取是快照读不会因为其他事务插入新行而产生幻读。但这个设计有一个连带效果PostgreSQL没有间隙锁这种在索引区间上防止插入的锁机制。如果你用SELECT ... FOR UPDATE去锁定一个范围别的连接照样可以在这个范围内插入新行。这在某些先查询范围再插入的业务逻辑里需要特别注意加唯一约束或用咨询锁来补救。4.3 崩溃恢复行为差异redo与WALMySQL的崩溃恢复依赖redo logPostgreSQL依赖WALWrite-Ahead Logging。两者思路相近都是先写日志再写数据但细节上PostgreSQL的WAL日志内容更丰富支持的时间点恢复PITR能力也更强。从事务处理角度看一个实际差异是MySQL在崩溃恢复时对未提交事务会做回滚undoPostgreSQL在崩溃恢复时是不回滚的——它通过WAL里记录的前镜像和后镜像把数据恢复到最后一个已提交事务之后、崩溃瞬间之前的状态期间未提交事务的修改会自然变成死元组后续由VACUUM清理。这就导致一个现象PostgreSQL崩溃恢复速度通常更快因为不需要逐一回滚未完事务但恢复后如果有很多未提交事务遗留下来的垃圾版本需要VACUUM来处理短时间内容量会膨胀一点。4.4 一个常见错误把MySQL的SELECT...FOR UPDATE习惯搬过来很多做库存防超卖的方案在MySQL下的写法是SELECT stock FROM products WHERE id 1 FOR UPDATE; -- 业务判断库存是否足够 -- 执行UPDATE扣减这段代码在MySQL里能正常工作核心是FOR UPDATE触发了当前读锁住了这行数据后续并发事务只能排队等待。到了PostgreSQL里同样的代码也能跑但行为有差异。PostgreSQL的FOR UPDATE是直接锁行锁的是最新版本后续其他事务更新同一行会阻塞等待锁释放。如果前一个事务长时间不COMMIT后一个事务会一直等直到lock_timeout或statement_timeout触发。在可重复读级别下如果两个事务同时FOR UPDATE同一行后执行FOR UPDATE的事务会报序列化失败。我的建议在PostgreSQL里做防超卖优先考虑原子UPDATE比如UPDATE products SET stock stock - 1 WHERE id 1 AND stock 0 RETURNING stock;这条SQL本身就是一个自包含事务数据库层面保证了不会超卖性能也比FOR UPDATE加业务判断更高效。这种写法上的差异迁移时一定得改不改就会出现莫名其妙的锁等待或并发报错。5. 事务异常现场锁等待、死锁与回滚的排查实录5.1 锁等待最容易被忽略的细节你有没有遇到过这种情况一个UPDATE语句挂在那边等了十几分钟也不报错startup日志里也没有明显异常。第一反应确实应该是查锁等待。PostgreSQL里查锁状态直接用系统视图SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state active AND wait_event IS NOT NULL;如果看到wait_event_type是Lockwait_event是transactionid说明当前事务在等待另一个事务释放锁。再进一步查谁锁住了它SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocked.pg_backend_pid blocking.pid WHERE blocked.wait_event_type Lock;我最常忽略的细节是锁等待不一定是另一个事务没提交很多时候是另一个事务忘了提交。有人会在客户端里开一个事务跑完了业务但应用代码没有显式COMMIT连接池里的连接就一直占着开着的状态锁也不释放。查pg_stat_activity时它的state还是idle in transaction这个状态非常容易漏看因为state显示idle看起来像空闲连接但实际上它握着一堆锁。处理办法很简单给idle in transaction设置超时。PostgreSQL配置项idle_in_transaction_session_timeout可以设置超过时间强制断开这类连接。我建议生产环境至少设置成30秒避免这种假空闲拖垮整个业务。5.2 死锁出现在你想不到的时候死锁的经典场景是两个事务分别持有对方需要的锁。PostgreSQL有死锁检测机制默认deadlock_timeout是1秒超过这个时间就主动回滚其中一个事务来打破死锁。但比较隐蔽的死锁往往不是简单的两行交叉锁而是多表更新顺序不一致导致的。举个例子-- 事务A BEGIN; UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; COMMIT; -- 事务B BEGIN; UPDATE accounts SET balance balance - 100 WHERE id 2; UPDATE accounts SET balance balance 100 WHERE id 1; COMMIT;如果两个事务同时跑A锁住了id1B锁住了id2然后A想更新id2B想更新id1就死锁了。这种情况在批量转账、批量结算场景里很容易触发。解决思路只有一个所有事务都按照相同的顺序更新行。比如约定总是先更新id小的行再更新id大的行。这个约定必须在应用层代码里规范死否则数据库的死锁检测虽然能兜底但频繁死锁会让业务方看到大量报错而且事务被回滚后需要重试逻辑配合。5.3 事务开的久不一定是因为逻辑慢排查XactStart时间长的会话时很多人会盯着SQL看怀疑某个SQL本身跑得慢。但有一种情况很误导人事务开得很早pg_stat_activity里xact_start时间是半小时前但你看到的那条SQL刚刚开始执行执行时间其实只有几十毫秒。这种事务时间久但单条SQL快的现象往往是因为应用代码在长事务中间做了外部API调用、等待用户输入、或者处理逻辑本身耗时很久。比如Java的Spring事务里嵌了一个远程HTTP调用调用耗时3秒加上事务内多次查询、数据组装整体事务耗时可能达到几十秒。这种长事务对PostgreSQL的伤害我在前面讲过——拖住VACUUM、锁持有时间长、死元组堆积。所以排查时除了看SQL执行计划还要结合应用的调用链分析事务边界内都干了什么。凡是事务里有网络I/O、外部服务调用的都要坚决拆出去放到事务提交之后再做。这个经验帮我在生产环境解决过好几次数据库突然变慢的故障其实根子都在应用层的事务边界设计不合理。再分享一个我自己的习惯每一条事务处理的SQL都顺手用EXPLAIN ANALYZE验证执行计划每一条疑似锁等待的事务都先看pg_stat_activity里的wait_event_type再动手。事务处理从来不是一个单纯的数据库开关问题它横跨了应用设计、SQL写法、并发模型和运维监控四个层面。把每个层面都摸到底才能在PostgreSQL里写出真正抗高并发的事务代码。