下午三点线上业务突然变慢数据库CPU不高但锁等待的监控面板却拉满了。我打开活动会话一看一个跑了十几分钟的分析查询把后面一大串写事务全堵住了。这时候你才真正理解并发控制不是教科书里的概念而是每分每秒在和生产环境较劲的事情。MVCCMulti-Version Concurrency Control多版本并发控制就是在这种背景下被广泛应用的主流方案。PostgreSQL、Oracle、MySQLInnoDB三家的实现路径各不相同但设计目标是一致的让读不阻塞写让写不阻塞读同时还要保证事务隔离级别的语义。这篇文章会把三者的MVCC机制从头到尾拆一遍从快照结构到版本存储从垃圾清理到隔离级别差异并附上我在实际运维和面试中见过的高频问题。如果你是后端开发、DBA或者正准备数据库相关的面试这篇内容应该能帮你省不少翻文档的时间。1. MVCC要解决的问题读和写为什么天生互相阻挠在展开三个数据库的细节之前有必要先把MVCC解决的核心矛盾讲清楚。很多人对MVCC的理解停留在多版本三个字上但很少追问到底为什么需要多版本1.1 从锁的视角看并发为什么只有读写锁还不够传统的关系型数据库用锁来保证并发安全。最简单粗暴的方案是任何操作都加互斥锁读和写完全串行。这样一致性倒是有了但并发能力等于零。实际产品里没人这么干于是有了读写锁读锁之间可以共享写锁独占读写之间互斥。问题就出在读写互斥上。一个事务正在更新某一行时其他事务想读这一行只能等待写事务提交或回滚。如果写事务持锁时间很久——比如它先更新了一行又去等待另一张表上的锁结果被别的会话卡住——那么所有读事务就全被堵住了。生产环境里我见过不少案例真正的瓶颈不是SQL慢而是锁等待链条太长一个事务被堵导致整条业务链路都跟着停摆。1.2 快照隔离让读事务看到过去的数据库MVCC思路的核心是让每个读事务在开始读取时获得一个数据库在某一个时间点上的快照。之后读事务再去查询数据不需要关心当前数据是否正在被别的写事务修改它只要按照快照时点的可见性规则去找到那个时间点上应该看到的数据版本即可。这句话可以类比成图书馆的旧版本书架。书架上有人正在往里面加新书但这个读者手里拿着一张写成时间索引的目录清单他只需要按清单去找对应版本的书不需要等加书的人走开。写者也不需要因为有人正在看目录就停下加书的动作。这个设计把读写互斥变成了读写并发。数据库需要做的事就是保存同一个逻辑行的多个历史版本并且在查询时依据快照判断应该读取哪个版本。这就是多版本Multi-Version这个名字的由来。1.3 MVCC的通用骨架行版本、可见性、清理抛开具体实现MVCC在几乎所有数据库里都有三个共性组件版本生成每次写操作插入、更新、删除都会产生一个新的行版本旧版本不会立即物理删除而是保留一段时间以便正在运行中的读事务还能访问到。可见性判定每个事务需要能判断我看到的是哪一个版本。系统通常会给每个事务分配一个递增的标识事务ID或SCN并维护一个活跃事务列表用来判定一个行版本是否已提交、是否对当前事务可见。版本清理旧版本不可能永久保留必然需要一种机制清理那些已经没有任何活跃事务会用到的旧版本。清理机制和触发策略在三家数据库里差异非常大。这三个组件就是整篇文章的脉络。接下来分别看PostgreSQL、Oracle、MySQL InnoDB各自的实现。2. PostgreSQL的多版本实现老版本躺在数据页里PostgreSQL的MVCC有一个非常鲜明的特点多个版本的行数据直接存放在堆表页面data page里。换句话说新版本插入时老版本并不会立刻挪走它就留在原来的数据页里新版本写到同一个页面如果放得下或者写到另一个页面。2.1 堆表里的xmin与xmax版本号的直觉理解PostgreSQL的每个表在物理层面都有几个系统列其中最核心的两个是xmin和xmax用户平时不会直接查询它们但它们决定了每行版本的出生时间和过期时间。xmin插入这个行版本的事务IDtransaction id。xmax删除或者更新这个行版本的事务ID。如果没有被删除xmax为0System Information里通常显示为无效值。PostgreSQL的事务ID是一个32位递增计数器它的分配顺序决定了事务的先后关系。当一个UPDATE执行时PostgreSQL做的事情是先把旧元组的xmax标记为当前事务ID表示这个旧版本在这个事务里失效了然后插入一条全新的元组新元组的xmin就是当前事务ID。删除DELETE则更简单只需要把旧元组的xmax置为当前事务ID表示这一行已经不可见。这种实现方式背后有个关键代价一次UPDATE至少产生一个死元组dead tuple。如果一张表被高频更新死元组数量会快速增长数据页面会膨胀表占用的磁盘空间会越来越大。这也是PostgreSQL后来引入HOTHeap-Only Tuple更新的原因如果更新不涉及索引列新版本可以放在同一页面且不需要更新索引项能大幅减少索引膨胀。2.2 可见性判断与快照机制PostgreSQL的可见性判断是以快照Snapshot为核心的。快照本质上是事务启动时或语句启动时打出来的一个活跃事务视图。它主要包含xmin快照开始时系统里最早活跃事务的事务ID。xmax快照开始时下一个未分配的事务ID。xip_list快照创建时所有活跃事务的ID列表。当一个读操作需要判断某个元组是否可见时PostgreSQL按如下逻辑判断简化版如果元组的xmin 当前快照的xmax说明该版本是在快照之后才插入的不可见。如果元组的xmin在xip_list活跃事务列表中说明插入该版本的事务尚未提交不可见但如果是当前事务自己插入的可见。如果元组的xmax在xip_list活跃事务列表中说明删除/更新该版本的事务尚未提交该版本仍然可见。如果元组的xmax已经提交说明该版本已被删除不可见。这里有一个很重要的实践细节PostgreSQL默认隔离级别是读已提交Read Committed在这个隔离级别下每条SQL语句开始时会重新获取一个新的快照因此同一个事务内的多次查询能看到不同的数据。而在可重复读Repeatable Read隔离级别下快照在事务的第一条SQL执行时创建之后整个事务内的所有查询都复用这个快照看到的是一个完全固定的视野。2.3 VACUUMPostgreSQL躲不开的野外生存课PostgreSQL不像Oracle和MySQL那样有一个独立的后台进程持续回收旧版本它依赖VACUUM。VACUUM的常规目标是清理那些已经不被任何快照引用的死元组并复用它们占用的页面空间。自动VACUUM在默认配置下是开启的autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor控制触发频率。默认阈值是50个元组加表规模的20%也就是说一张千万级表要等死元组积累到200万左右才会触发一次自动清理。对于高频更新的表这个频率往往不够膨胀问题如果不人工干预会演变成性能灾难。我在生产环境里最常踩的坑是一个长事务比如打开的数据库连接里有一个空闲事务或者一个没提交的PL/pgSQL函数挂在那里VACUUM即使跑起来也什么都不敢删。因为长事务持有的事务ID处在整个快照体系里最古老的位置所有比它年轻的行版本在它眼里都可能是可见的VACUUM只能全数跳过。长事务只要不结束死元组就永远无法清理表膨胀就是板上钉钉。应对手段无非是监控长事务、给空闲连接设置超时、必要时用pg_terminate_backend掐掉僵尸会话。3. Oracle的MVCCSCN与回滚段的默契Oracle的MVCC和PostgreSQL走了一条完全不同的路线它不把旧版本数据放在数据块里而是把修改前的旧影像前镜像前像写入回滚段undo segment。数据块里永远只保留当前已提交或正在修改的最新版本查询时如果要读旧版本就去回滚段里把前像找出来拼回去。3.1 SCN和一致性读的关系SCNSystem Change Number是Oracle里的全局递增时间戳。每次对数据库的修改都会产生一个SCN事务提交时也会产生一个SCN。SCN贯穿整个MySQL读一致性的核心逻辑。当一个查询开始时Oracle会获取一个SCN作为查询的一致性点。数据库在执行SQL时会检查数据块上记录的SCN块的SCN用块头部的maxquery_scn等字段标记细分有block cleanout机制如果当前数据块的SCN大于查询的一致性点说明这个块里保存的数据是查询之后被修改的不能直接读取需要借助回滚段构造出旧版本数据。这就是Oracle经典的**一致性读consistent read**机制。你可以把它理解成Oracle在查询执行过程中一旦发现要读的数据块太新就通过回滚段把它倒带到查询开始的那个时间点。整个过程对应用透明但代价是大量的一致性读会产生大量回滚段读和逻辑I/O严重时可能读到CPU飙高。3.2 ITL和回滚段如何恢复旧版本在Oracle中一个行更准确说是行所在的data block被修改时会在数据块的**ITLInterested Transaction List事务槽**中登记事务信息包括事务ID、回滚段地址、SCN等。回滚段由一系列undo块undo extent组成记录原始版本的数据。每当我们给定一个目标SCNOracle会按如下顺序找版本如果数据块当前版本是修改前已提交且SCN 目标SCN直接使用当前版本。如果发现当前版本是由一个晚于目标SCN的事务修改的就通过ITL里的回滚指针undo记录在回滚段里找到这个事务修改前的旧影像如果这个旧版本依然晚于目标SCN就继续沿着之前的事务链继续找直到找到一个SCN 目标SCN或者找到原始版本的版本。这里有一个容易误解的点Oracle的数据块并不像InnoDB或PostgreSQL那样通过事务ID对比做复杂的可见性遍历而是大量依赖undo链的倒带操作。数据库在做DML时修改数据块的同时一定会先写undo记录把修改前的整行或关键字段保存下来。3.3 ORA-01555与闪回查询都是undo惹的福回滚段的作用不只是提供一致性读它还是回滚ROLLBACK和闪回类功能的基础。由于回滚段保存了旧版本数据Oracle可以基于这些undo数据实现闪回查询Flashback Query让用户查询过去任意时间点在保留下限内的数据。而回滚段最著名的坑就是ORA-01555snapshot too old。这个错误出现的原因是某个查询需要读取一段很老的undo数据但这段undo已经被覆盖回收了数据库无法再构造出查询开始时的一致版本。在我维护过的Oracle实例上ORA-01555最常见的触发场景是一个大的查询跑了很久或者深分页的PL/SQL循环持续执行系统里同时有大量的高频DML在快速覆盖undo表空间。解决办法通常围绕几点展开优化SQL缩短查询时间调大undo表空间和UNDO_RETENTION参数如果是因为一次UPDATE/DELETE大量数据导致undo瞬间增长则需要把大事务拆分成小批量提交。4. MySQL InnoDB的MVCC隐藏列、undo和Read ViewMySQLInnoDB的MVCC实现介于PostgreSQL和Oracle之间行数据本身存放在聚簇索引主键索引里旧版本不会像PostgreSQL那样整行留在堆表里而是通过undo log保存变化前的数据行上用一个回滚指针指向undo log中的版本链。查询时通过Read View读视图来判断版本可见性。4.1 聚簇索引上的两个隐藏列版本链的起点InnoDB的每行记录聚簇索引叶子节点都会携带三个隐藏字段实际上还有更多内部字段这里只关注与MVCC直接相关的DB_TRX_ID最后修改该行的事务ID。每发生一次UPDATE这个字段就会更新为新的事务IDDELETE会做标记但行本身仍保留。DB_ROLL_PTR回滚指针指向undo log中该行旧版本的记录。通过它可以把undo log串成一条版本链。DB_ROW_ID如果没有显式主键InnoDB会隐式生成一个单调递增的行ID用于聚簇索引的索引键。当一个UPDATE发生时InnoDB并不会真的在聚簇索引里原地修改这一行而是在undo log中写入一条前镜像记录然后把行头上的DB_ROLL_PTR指向这条新的undo记录同时把DB_TRX_ID改成当前事务ID。也就是说聚簇索引的行内容是最新版本旧版本的信息全部存放在undo log的版本链里。4.2 Read View与可见性判定读已提交和可重复读的分界Read View是InnoDB判断版本可见性的核心数据结构。它有几个关键字段m_low_limit_id创建Read View时系统尚未分配的下一个事务ID也就是max_trx_id所有该值的事务ID都被视为不可见。m_up_limit_idRead View创建时活跃事务列表中最小的ID。所有该值且不活跃的事务被视为已提交。m_ids创建Read View时系统中所有活跃事务的ID列表。m_creator_trx_id创建该Read View的事务自身ID。一条版本记录的DB_TRX_ID小于m_up_limit_id或者等于当前事务ID通常可以判定为可见如果DB_TRX_ID在活跃事务列表中则不可见需要沿着DB_ROLL_PTR顺着版本链继续往前找直到找到一个可见版本或到达链表末尾。这里有一个面试高频考点读已提交和可重复读的区别在InnoDB中主要体现在Read View的创建时机上。读已提交每次SELECT都会新建一个Read View所以同一个事务里两次SELECT可能看到不同的数据。可重复读在第一个一致性读即第一个SELECT时创建Read View之后事务内所有普通SELECT都复用这一个Read View。从这个角度看可重复读就是拿着同一张底片看世界。注意MySQL在可重复读隔离级别下并不是完全靠MVCC解决所有写冲突的。当前读SELECT ... FOR UPDATE、UPDATE、DELETE在可重复读下会使用临键锁next-key lock通过锁住范围来防止幻读这一点和PostgreSQL的可重复读有本质差异后面第5节再细说。4.3 二级索引的特殊处境与purge线程InnoDB的二级索引非聚簇索引并不包含DB_TRX_ID和DB_ROLL_PTR字段。所以当查询走的路径是二级索引时InnoDB必须通过二级索引行上的主键值回表ref到聚簇索引再读取隐藏列来判断可见性。这个回表可见性判断的组合在高并发场景下会增加大量随机I/O。InnoDB做了一个优化二级索引页中每个叶节点页面有一个名为PAGE_MAX_TRX_ID的字段标记页面上最新被修改过的事务ID。如果PAGE_MAX_TRX_ID小于当前Read View的m_low_limit_id说明页面上所有记录都在Read View创建之前就稳定存在可以直接判断可见免去回表。这就是半可见semi-consistent read的级别或称为页级可见性优化。Undo log的清理由后台purge线程负责。InnoDB会根据Read View和系统状态把一条版本链上没有任何活跃事务再引用的历史版本物理删除。如果长事务持续打开版本链上的旧版本就无法被purgeundo log会持续增长history list会变长。我见过不少MySQL实例卡顿根因就是有长期未提交的事务undo膨胀导致磁盘占用上升、回滚段活跃最后整个实例性能恶化。日常巡检务必关注information_schema.innodb_trx里的长时间运行事务。5. 三张表看清三种设计同一目标不同取舍讨论到这儿三家数据库的MVCC大致图景应该清楚了。这一节用表格把关键差异集中起来再针对几个容易被忽略的点展开聊。5.1 版本数据与索引的物理布局对比维度PostgreSQLOracleMySQL InnoDB版本数据存放位置堆表数据页内新旧版本共存于数据页回滚段undo segmentundo log 聚簇索引行内回滚指针新版本修改时的处理UPDATE生成新元组旧元组标记xmax数据块保留最新版本旧影像写入undo块聚簇索引更新隐藏列旧影像写入undo log索引是否包含版本信息索引叶节点不包含xmin/xmax回表查看堆元组索引不包含SCN等版本信息一致性读靠undo聚簇索引包含隐藏列二级索引需回表判断删除的处理标记xmax删除位修改ITL并记录undo删除前影像标记DB_TRX_IDdelete_bit不立即物理删除典型清理依赖VACUUMundo自动管理 block cleanoutpurge线程这张表最值得关注的差异点是只有PostgreSQL把多版本直接堆在数据页面里这意味着PostgreSQL的数据页要承受版本堆积索引访问还需要回表读堆。YouPay注意Oracle和MySQL的旧版本都不放在索引页里而是在undo里所以索引页相对干净。这也是为什么PostgreSQL对VACUUM和表膨胀要求那么高的根源。5.2 并发控制语义对比维度PostgreSQLOracleMySQL InnoDB默认隔离级别读已提交读已提交可重复读可重复读的实现事务第一个SQL时创建快照后续查询复用事务级一致性读通过SCN第一个一致性读时创建Read View是否用锁防止幻读不使用gap lock靠快照保证读稳定不使用gap lock靠快照保证读稳定当前读使用next-key lock防幻读写写冲突处理更新冲突检测并发更新同一行时后提交者可能报序列化错误更新行锁后操作者阻塞等待更新行锁后提交者可能死锁依赖锁等待超时读快照是否自动更新RR下固定RC下每语句重置RR序列化固定RC下每语句重置RR下固定RC下每语句重置这里必须特别澄清一个容易混淆的点。很多人说MySQL的可重复读会使用next-key lock来防止幻读而PostgreSQL不会这句话并不完整。PostgreSQL的快照读天然就不会看到新增的行因为查询使用的快照在事务开始时就已经固定了。所以从读的角度PG靠快照也可以避免幻读。但在当前读的场景比如SELECT ... FOR UPDATEMySQL会锁死读到的以及范围内的记录其他事务无法插入新行PostgreSQL则是靠更新冲突检测来兜底而不是靠间隙锁。这个差异会直接影响某些并发写入业务的结果。5.3 清理机制与运维负担对比维度PostgreSQLOracleMySQL InnoDB清理主体autovacuum 手动VACUUM回滚段自动管理undo自动调优purge线程是否受长事务影响是长事务会完全阻塞死元组清理是长事务会导致undo retention无法回收是history list会持续增长主要运维风险表膨胀、索引膨胀、冻结事务IDundo暴涨、ORA-01555undo暴涨、磁盘空间、锁膨胀是否需要人工干预频繁需要配置autovacuum参数和监控中通常调undo相关参数即可中需监控长事务和purge进度从运维负担角度看三者的核心风险其实都指向同一个痛点长事务。长事务不结束MVCC的版本就永远不能安全清理。区别只是清理机制的名字和地点不一样。我每次在培训里都强调业务代码里开事务后忘了COMMIT或者ORM连接池配置不当导致事务悬挂这远比慢SQL更可怕因为它会同时拖垮MVCC、锁和磁盘空间三个维度。6. 从原理到实战排错和面试都能用上的点原理讲完落到实际。这个部分整理几个我常用的SQL模板、高频面试考点以及选型层面的考虑。6.1 面试最常踩的三个考点我把近期面试候选人时反复被问到的问题梳理了一下三个高频点第一Read View/快照的创建时机。MySQL的Repeated Read下BEGIN不代表建立快照第一个一致性读SELECT才建立Read View。Oracle也一样事务级一致性读是在第一条查询语句执行时获取SCN。PostgreSQL则略有不同RR级别第一条语句获取快照通常读操作开始的那一刻就确定了。这个细节很多人答错。第二可重复读隔离级别下为什么MySQL能防幻读而Oracle和PostgreSQL主要靠快照严格来说三者都能保证同一个事务内快照读的稳定性。MySQL的独特之处在于它额外使用next-key lock保护当前读。而Oracle/PG没有gap lock如果需要防止业务逻辑级别的幻读必须自己加锁或使用更高隔离级别如PG的串行化隔离和SSI实现。第三ORA-01555和MySQL undo膨胀的根因。很多候选人只知道报错信息不知道是MVCC版本回收机制跟不上导致的。能讲出长查询 并发DML侵蚀undo这个组合才算真正理解。6.2 实战中三种数据库的膨胀与长事务坑这里的实战策略我建议直接照抄PostgreSQL除默认autovacuum外一定要监控pg_stat_activity里state idle in transaction的会话关注pg_stat_user_tables的dead_tuples比例对高频更新表可手动调小autovacuum_vacuum_scale_factor甚至把它改为固定阈值模式比如autovacuum_vacuum_threshold 100。Oracle关注DBA_UNDO_EXTENTS的使用情况和V$UNDOSTAT检查UNDO_RETENTION是否匹配业务高峰程序里避免把大目标表一次性UPDATE改成COMMIT循环。MySQL看history list length在SHOW ENGINE INNODB STATUS里有没有异常增长定期查information_schema.innodb_trx的长事务控制大事务的粒度别让一个事务处理几十万行。6.3 选型建议什么时候你该在意MVCC的实现差异日常开发里选数据库通常不单看MVCC但MVCC差异有时会影响系统设计策略。如果业务以高并发读多写少、需要大量基于历史时间点的分析查询为特点Oracle的闪回查询和MVCC特性天然占优前提是要有能力维护好undo体系。如果你倾向于数据强一致、复杂查询、且不介意对表做定期维护PostgreSQL的MVCC给了你在RR下干净利落的快照读体验配合丰富的扩展能力比如分区、FDW很舒服但要接受VACUUM这一课。如果团队把高并发写入、分库分表、与MySQL生态深度绑定放在第一位InnoDB的MVCC是三个里对二级索引最不友好的但在事务较短、锁冲突可控的场景下稳定性经过大量互联网业务验证完全够用。7. 一点个人的实践体会研究和维护这三种数据库的MVCC机制这么几年我最深的感受是数据库的版本清理机制才是决定长期运维质量的分水岭。面试时大家都讲得头头是道但真正上线后就比谁更敬畏长事务、谁更重视监控。我个人的习惯是在新项目启动时提前把三个监控指标固化成看板PostgreSQL的死元组比例和最长空闲事务时间Oracle的undo膨胀速率和ORA-01555计数MySQL的history list长度和最长运行事务。规则很简单——一旦超过基线立即告警。这三组数字背后的MVCC原理就是这篇文章讲的这些内容。搞清楚了它们数据库并发问题在你眼里就不再是黑盒而是每个版本、每个事务ID、每段undo都能顺着链条找到答案的清晰逻辑。
MVCC原理深挖：PostgreSQL、Oracle与MySQL InnoDB实现对比
下午三点线上业务突然变慢数据库CPU不高但锁等待的监控面板却拉满了。我打开活动会话一看一个跑了十几分钟的分析查询把后面一大串写事务全堵住了。这时候你才真正理解并发控制不是教科书里的概念而是每分每秒在和生产环境较劲的事情。MVCCMulti-Version Concurrency Control多版本并发控制就是在这种背景下被广泛应用的主流方案。PostgreSQL、Oracle、MySQLInnoDB三家的实现路径各不相同但设计目标是一致的让读不阻塞写让写不阻塞读同时还要保证事务隔离级别的语义。这篇文章会把三者的MVCC机制从头到尾拆一遍从快照结构到版本存储从垃圾清理到隔离级别差异并附上我在实际运维和面试中见过的高频问题。如果你是后端开发、DBA或者正准备数据库相关的面试这篇内容应该能帮你省不少翻文档的时间。1. MVCC要解决的问题读和写为什么天生互相阻挠在展开三个数据库的细节之前有必要先把MVCC解决的核心矛盾讲清楚。很多人对MVCC的理解停留在多版本三个字上但很少追问到底为什么需要多版本1.1 从锁的视角看并发为什么只有读写锁还不够传统的关系型数据库用锁来保证并发安全。最简单粗暴的方案是任何操作都加互斥锁读和写完全串行。这样一致性倒是有了但并发能力等于零。实际产品里没人这么干于是有了读写锁读锁之间可以共享写锁独占读写之间互斥。问题就出在读写互斥上。一个事务正在更新某一行时其他事务想读这一行只能等待写事务提交或回滚。如果写事务持锁时间很久——比如它先更新了一行又去等待另一张表上的锁结果被别的会话卡住——那么所有读事务就全被堵住了。生产环境里我见过不少案例真正的瓶颈不是SQL慢而是锁等待链条太长一个事务被堵导致整条业务链路都跟着停摆。1.2 快照隔离让读事务看到过去的数据库MVCC思路的核心是让每个读事务在开始读取时获得一个数据库在某一个时间点上的快照。之后读事务再去查询数据不需要关心当前数据是否正在被别的写事务修改它只要按照快照时点的可见性规则去找到那个时间点上应该看到的数据版本即可。这句话可以类比成图书馆的旧版本书架。书架上有人正在往里面加新书但这个读者手里拿着一张写成时间索引的目录清单他只需要按清单去找对应版本的书不需要等加书的人走开。写者也不需要因为有人正在看目录就停下加书的动作。这个设计把读写互斥变成了读写并发。数据库需要做的事就是保存同一个逻辑行的多个历史版本并且在查询时依据快照判断应该读取哪个版本。这就是多版本Multi-Version这个名字的由来。1.3 MVCC的通用骨架行版本、可见性、清理抛开具体实现MVCC在几乎所有数据库里都有三个共性组件版本生成每次写操作插入、更新、删除都会产生一个新的行版本旧版本不会立即物理删除而是保留一段时间以便正在运行中的读事务还能访问到。可见性判定每个事务需要能判断我看到的是哪一个版本。系统通常会给每个事务分配一个递增的标识事务ID或SCN并维护一个活跃事务列表用来判定一个行版本是否已提交、是否对当前事务可见。版本清理旧版本不可能永久保留必然需要一种机制清理那些已经没有任何活跃事务会用到的旧版本。清理机制和触发策略在三家数据库里差异非常大。这三个组件就是整篇文章的脉络。接下来分别看PostgreSQL、Oracle、MySQL InnoDB各自的实现。2. PostgreSQL的多版本实现老版本躺在数据页里PostgreSQL的MVCC有一个非常鲜明的特点多个版本的行数据直接存放在堆表页面data page里。换句话说新版本插入时老版本并不会立刻挪走它就留在原来的数据页里新版本写到同一个页面如果放得下或者写到另一个页面。2.1 堆表里的xmin与xmax版本号的直觉理解PostgreSQL的每个表在物理层面都有几个系统列其中最核心的两个是xmin和xmax用户平时不会直接查询它们但它们决定了每行版本的出生时间和过期时间。xmin插入这个行版本的事务IDtransaction id。xmax删除或者更新这个行版本的事务ID。如果没有被删除xmax为0System Information里通常显示为无效值。PostgreSQL的事务ID是一个32位递增计数器它的分配顺序决定了事务的先后关系。当一个UPDATE执行时PostgreSQL做的事情是先把旧元组的xmax标记为当前事务ID表示这个旧版本在这个事务里失效了然后插入一条全新的元组新元组的xmin就是当前事务ID。删除DELETE则更简单只需要把旧元组的xmax置为当前事务ID表示这一行已经不可见。这种实现方式背后有个关键代价一次UPDATE至少产生一个死元组dead tuple。如果一张表被高频更新死元组数量会快速增长数据页面会膨胀表占用的磁盘空间会越来越大。这也是PostgreSQL后来引入HOTHeap-Only Tuple更新的原因如果更新不涉及索引列新版本可以放在同一页面且不需要更新索引项能大幅减少索引膨胀。2.2 可见性判断与快照机制PostgreSQL的可见性判断是以快照Snapshot为核心的。快照本质上是事务启动时或语句启动时打出来的一个活跃事务视图。它主要包含xmin快照开始时系统里最早活跃事务的事务ID。xmax快照开始时下一个未分配的事务ID。xip_list快照创建时所有活跃事务的ID列表。当一个读操作需要判断某个元组是否可见时PostgreSQL按如下逻辑判断简化版如果元组的xmin 当前快照的xmax说明该版本是在快照之后才插入的不可见。如果元组的xmin在xip_list活跃事务列表中说明插入该版本的事务尚未提交不可见但如果是当前事务自己插入的可见。如果元组的xmax在xip_list活跃事务列表中说明删除/更新该版本的事务尚未提交该版本仍然可见。如果元组的xmax已经提交说明该版本已被删除不可见。这里有一个很重要的实践细节PostgreSQL默认隔离级别是读已提交Read Committed在这个隔离级别下每条SQL语句开始时会重新获取一个新的快照因此同一个事务内的多次查询能看到不同的数据。而在可重复读Repeatable Read隔离级别下快照在事务的第一条SQL执行时创建之后整个事务内的所有查询都复用这个快照看到的是一个完全固定的视野。2.3 VACUUMPostgreSQL躲不开的野外生存课PostgreSQL不像Oracle和MySQL那样有一个独立的后台进程持续回收旧版本它依赖VACUUM。VACUUM的常规目标是清理那些已经不被任何快照引用的死元组并复用它们占用的页面空间。自动VACUUM在默认配置下是开启的autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor控制触发频率。默认阈值是50个元组加表规模的20%也就是说一张千万级表要等死元组积累到200万左右才会触发一次自动清理。对于高频更新的表这个频率往往不够膨胀问题如果不人工干预会演变成性能灾难。我在生产环境里最常踩的坑是一个长事务比如打开的数据库连接里有一个空闲事务或者一个没提交的PL/pgSQL函数挂在那里VACUUM即使跑起来也什么都不敢删。因为长事务持有的事务ID处在整个快照体系里最古老的位置所有比它年轻的行版本在它眼里都可能是可见的VACUUM只能全数跳过。长事务只要不结束死元组就永远无法清理表膨胀就是板上钉钉。应对手段无非是监控长事务、给空闲连接设置超时、必要时用pg_terminate_backend掐掉僵尸会话。3. Oracle的MVCCSCN与回滚段的默契Oracle的MVCC和PostgreSQL走了一条完全不同的路线它不把旧版本数据放在数据块里而是把修改前的旧影像前镜像前像写入回滚段undo segment。数据块里永远只保留当前已提交或正在修改的最新版本查询时如果要读旧版本就去回滚段里把前像找出来拼回去。3.1 SCN和一致性读的关系SCNSystem Change Number是Oracle里的全局递增时间戳。每次对数据库的修改都会产生一个SCN事务提交时也会产生一个SCN。SCN贯穿整个MySQL读一致性的核心逻辑。当一个查询开始时Oracle会获取一个SCN作为查询的一致性点。数据库在执行SQL时会检查数据块上记录的SCN块的SCN用块头部的maxquery_scn等字段标记细分有block cleanout机制如果当前数据块的SCN大于查询的一致性点说明这个块里保存的数据是查询之后被修改的不能直接读取需要借助回滚段构造出旧版本数据。这就是Oracle经典的**一致性读consistent read**机制。你可以把它理解成Oracle在查询执行过程中一旦发现要读的数据块太新就通过回滚段把它倒带到查询开始的那个时间点。整个过程对应用透明但代价是大量的一致性读会产生大量回滚段读和逻辑I/O严重时可能读到CPU飙高。3.2 ITL和回滚段如何恢复旧版本在Oracle中一个行更准确说是行所在的data block被修改时会在数据块的**ITLInterested Transaction List事务槽**中登记事务信息包括事务ID、回滚段地址、SCN等。回滚段由一系列undo块undo extent组成记录原始版本的数据。每当我们给定一个目标SCNOracle会按如下顺序找版本如果数据块当前版本是修改前已提交且SCN 目标SCN直接使用当前版本。如果发现当前版本是由一个晚于目标SCN的事务修改的就通过ITL里的回滚指针undo记录在回滚段里找到这个事务修改前的旧影像如果这个旧版本依然晚于目标SCN就继续沿着之前的事务链继续找直到找到一个SCN 目标SCN或者找到原始版本的版本。这里有一个容易误解的点Oracle的数据块并不像InnoDB或PostgreSQL那样通过事务ID对比做复杂的可见性遍历而是大量依赖undo链的倒带操作。数据库在做DML时修改数据块的同时一定会先写undo记录把修改前的整行或关键字段保存下来。3.3 ORA-01555与闪回查询都是undo惹的福回滚段的作用不只是提供一致性读它还是回滚ROLLBACK和闪回类功能的基础。由于回滚段保存了旧版本数据Oracle可以基于这些undo数据实现闪回查询Flashback Query让用户查询过去任意时间点在保留下限内的数据。而回滚段最著名的坑就是ORA-01555snapshot too old。这个错误出现的原因是某个查询需要读取一段很老的undo数据但这段undo已经被覆盖回收了数据库无法再构造出查询开始时的一致版本。在我维护过的Oracle实例上ORA-01555最常见的触发场景是一个大的查询跑了很久或者深分页的PL/SQL循环持续执行系统里同时有大量的高频DML在快速覆盖undo表空间。解决办法通常围绕几点展开优化SQL缩短查询时间调大undo表空间和UNDO_RETENTION参数如果是因为一次UPDATE/DELETE大量数据导致undo瞬间增长则需要把大事务拆分成小批量提交。4. MySQL InnoDB的MVCC隐藏列、undo和Read ViewMySQLInnoDB的MVCC实现介于PostgreSQL和Oracle之间行数据本身存放在聚簇索引主键索引里旧版本不会像PostgreSQL那样整行留在堆表里而是通过undo log保存变化前的数据行上用一个回滚指针指向undo log中的版本链。查询时通过Read View读视图来判断版本可见性。4.1 聚簇索引上的两个隐藏列版本链的起点InnoDB的每行记录聚簇索引叶子节点都会携带三个隐藏字段实际上还有更多内部字段这里只关注与MVCC直接相关的DB_TRX_ID最后修改该行的事务ID。每发生一次UPDATE这个字段就会更新为新的事务IDDELETE会做标记但行本身仍保留。DB_ROLL_PTR回滚指针指向undo log中该行旧版本的记录。通过它可以把undo log串成一条版本链。DB_ROW_ID如果没有显式主键InnoDB会隐式生成一个单调递增的行ID用于聚簇索引的索引键。当一个UPDATE发生时InnoDB并不会真的在聚簇索引里原地修改这一行而是在undo log中写入一条前镜像记录然后把行头上的DB_ROLL_PTR指向这条新的undo记录同时把DB_TRX_ID改成当前事务ID。也就是说聚簇索引的行内容是最新版本旧版本的信息全部存放在undo log的版本链里。4.2 Read View与可见性判定读已提交和可重复读的分界Read View是InnoDB判断版本可见性的核心数据结构。它有几个关键字段m_low_limit_id创建Read View时系统尚未分配的下一个事务ID也就是max_trx_id所有该值的事务ID都被视为不可见。m_up_limit_idRead View创建时活跃事务列表中最小的ID。所有该值且不活跃的事务被视为已提交。m_ids创建Read View时系统中所有活跃事务的ID列表。m_creator_trx_id创建该Read View的事务自身ID。一条版本记录的DB_TRX_ID小于m_up_limit_id或者等于当前事务ID通常可以判定为可见如果DB_TRX_ID在活跃事务列表中则不可见需要沿着DB_ROLL_PTR顺着版本链继续往前找直到找到一个可见版本或到达链表末尾。这里有一个面试高频考点读已提交和可重复读的区别在InnoDB中主要体现在Read View的创建时机上。读已提交每次SELECT都会新建一个Read View所以同一个事务里两次SELECT可能看到不同的数据。可重复读在第一个一致性读即第一个SELECT时创建Read View之后事务内所有普通SELECT都复用这一个Read View。从这个角度看可重复读就是拿着同一张底片看世界。注意MySQL在可重复读隔离级别下并不是完全靠MVCC解决所有写冲突的。当前读SELECT ... FOR UPDATE、UPDATE、DELETE在可重复读下会使用临键锁next-key lock通过锁住范围来防止幻读这一点和PostgreSQL的可重复读有本质差异后面第5节再细说。4.3 二级索引的特殊处境与purge线程InnoDB的二级索引非聚簇索引并不包含DB_TRX_ID和DB_ROLL_PTR字段。所以当查询走的路径是二级索引时InnoDB必须通过二级索引行上的主键值回表ref到聚簇索引再读取隐藏列来判断可见性。这个回表可见性判断的组合在高并发场景下会增加大量随机I/O。InnoDB做了一个优化二级索引页中每个叶节点页面有一个名为PAGE_MAX_TRX_ID的字段标记页面上最新被修改过的事务ID。如果PAGE_MAX_TRX_ID小于当前Read View的m_low_limit_id说明页面上所有记录都在Read View创建之前就稳定存在可以直接判断可见免去回表。这就是半可见semi-consistent read的级别或称为页级可见性优化。Undo log的清理由后台purge线程负责。InnoDB会根据Read View和系统状态把一条版本链上没有任何活跃事务再引用的历史版本物理删除。如果长事务持续打开版本链上的旧版本就无法被purgeundo log会持续增长history list会变长。我见过不少MySQL实例卡顿根因就是有长期未提交的事务undo膨胀导致磁盘占用上升、回滚段活跃最后整个实例性能恶化。日常巡检务必关注information_schema.innodb_trx里的长时间运行事务。5. 三张表看清三种设计同一目标不同取舍讨论到这儿三家数据库的MVCC大致图景应该清楚了。这一节用表格把关键差异集中起来再针对几个容易被忽略的点展开聊。5.1 版本数据与索引的物理布局对比维度PostgreSQLOracleMySQL InnoDB版本数据存放位置堆表数据页内新旧版本共存于数据页回滚段undo segmentundo log 聚簇索引行内回滚指针新版本修改时的处理UPDATE生成新元组旧元组标记xmax数据块保留最新版本旧影像写入undo块聚簇索引更新隐藏列旧影像写入undo log索引是否包含版本信息索引叶节点不包含xmin/xmax回表查看堆元组索引不包含SCN等版本信息一致性读靠undo聚簇索引包含隐藏列二级索引需回表判断删除的处理标记xmax删除位修改ITL并记录undo删除前影像标记DB_TRX_IDdelete_bit不立即物理删除典型清理依赖VACUUMundo自动管理 block cleanoutpurge线程这张表最值得关注的差异点是只有PostgreSQL把多版本直接堆在数据页面里这意味着PostgreSQL的数据页要承受版本堆积索引访问还需要回表读堆。YouPay注意Oracle和MySQL的旧版本都不放在索引页里而是在undo里所以索引页相对干净。这也是为什么PostgreSQL对VACUUM和表膨胀要求那么高的根源。5.2 并发控制语义对比维度PostgreSQLOracleMySQL InnoDB默认隔离级别读已提交读已提交可重复读可重复读的实现事务第一个SQL时创建快照后续查询复用事务级一致性读通过SCN第一个一致性读时创建Read View是否用锁防止幻读不使用gap lock靠快照保证读稳定不使用gap lock靠快照保证读稳定当前读使用next-key lock防幻读写写冲突处理更新冲突检测并发更新同一行时后提交者可能报序列化错误更新行锁后操作者阻塞等待更新行锁后提交者可能死锁依赖锁等待超时读快照是否自动更新RR下固定RC下每语句重置RR序列化固定RC下每语句重置RR下固定RC下每语句重置这里必须特别澄清一个容易混淆的点。很多人说MySQL的可重复读会使用next-key lock来防止幻读而PostgreSQL不会这句话并不完整。PostgreSQL的快照读天然就不会看到新增的行因为查询使用的快照在事务开始时就已经固定了。所以从读的角度PG靠快照也可以避免幻读。但在当前读的场景比如SELECT ... FOR UPDATEMySQL会锁死读到的以及范围内的记录其他事务无法插入新行PostgreSQL则是靠更新冲突检测来兜底而不是靠间隙锁。这个差异会直接影响某些并发写入业务的结果。5.3 清理机制与运维负担对比维度PostgreSQLOracleMySQL InnoDB清理主体autovacuum 手动VACUUM回滚段自动管理undo自动调优purge线程是否受长事务影响是长事务会完全阻塞死元组清理是长事务会导致undo retention无法回收是history list会持续增长主要运维风险表膨胀、索引膨胀、冻结事务IDundo暴涨、ORA-01555undo暴涨、磁盘空间、锁膨胀是否需要人工干预频繁需要配置autovacuum参数和监控中通常调undo相关参数即可中需监控长事务和purge进度从运维负担角度看三者的核心风险其实都指向同一个痛点长事务。长事务不结束MVCC的版本就永远不能安全清理。区别只是清理机制的名字和地点不一样。我每次在培训里都强调业务代码里开事务后忘了COMMIT或者ORM连接池配置不当导致事务悬挂这远比慢SQL更可怕因为它会同时拖垮MVCC、锁和磁盘空间三个维度。6. 从原理到实战排错和面试都能用上的点原理讲完落到实际。这个部分整理几个我常用的SQL模板、高频面试考点以及选型层面的考虑。6.1 面试最常踩的三个考点我把近期面试候选人时反复被问到的问题梳理了一下三个高频点第一Read View/快照的创建时机。MySQL的Repeated Read下BEGIN不代表建立快照第一个一致性读SELECT才建立Read View。Oracle也一样事务级一致性读是在第一条查询语句执行时获取SCN。PostgreSQL则略有不同RR级别第一条语句获取快照通常读操作开始的那一刻就确定了。这个细节很多人答错。第二可重复读隔离级别下为什么MySQL能防幻读而Oracle和PostgreSQL主要靠快照严格来说三者都能保证同一个事务内快照读的稳定性。MySQL的独特之处在于它额外使用next-key lock保护当前读。而Oracle/PG没有gap lock如果需要防止业务逻辑级别的幻读必须自己加锁或使用更高隔离级别如PG的串行化隔离和SSI实现。第三ORA-01555和MySQL undo膨胀的根因。很多候选人只知道报错信息不知道是MVCC版本回收机制跟不上导致的。能讲出长查询 并发DML侵蚀undo这个组合才算真正理解。6.2 实战中三种数据库的膨胀与长事务坑这里的实战策略我建议直接照抄PostgreSQL除默认autovacuum外一定要监控pg_stat_activity里state idle in transaction的会话关注pg_stat_user_tables的dead_tuples比例对高频更新表可手动调小autovacuum_vacuum_scale_factor甚至把它改为固定阈值模式比如autovacuum_vacuum_threshold 100。Oracle关注DBA_UNDO_EXTENTS的使用情况和V$UNDOSTAT检查UNDO_RETENTION是否匹配业务高峰程序里避免把大目标表一次性UPDATE改成COMMIT循环。MySQL看history list length在SHOW ENGINE INNODB STATUS里有没有异常增长定期查information_schema.innodb_trx的长事务控制大事务的粒度别让一个事务处理几十万行。6.3 选型建议什么时候你该在意MVCC的实现差异日常开发里选数据库通常不单看MVCC但MVCC差异有时会影响系统设计策略。如果业务以高并发读多写少、需要大量基于历史时间点的分析查询为特点Oracle的闪回查询和MVCC特性天然占优前提是要有能力维护好undo体系。如果你倾向于数据强一致、复杂查询、且不介意对表做定期维护PostgreSQL的MVCC给了你在RR下干净利落的快照读体验配合丰富的扩展能力比如分区、FDW很舒服但要接受VACUUM这一课。如果团队把高并发写入、分库分表、与MySQL生态深度绑定放在第一位InnoDB的MVCC是三个里对二级索引最不友好的但在事务较短、锁冲突可控的场景下稳定性经过大量互联网业务验证完全够用。7. 一点个人的实践体会研究和维护这三种数据库的MVCC机制这么几年我最深的感受是数据库的版本清理机制才是决定长期运维质量的分水岭。面试时大家都讲得头头是道但真正上线后就比谁更敬畏长事务、谁更重视监控。我个人的习惯是在新项目启动时提前把三个监控指标固化成看板PostgreSQL的死元组比例和最长空闲事务时间Oracle的undo膨胀速率和ORA-01555计数MySQL的history list长度和最长运行事务。规则很简单——一旦超过基线立即告警。这三组数字背后的MVCC原理就是这篇文章讲的这些内容。搞清楚了它们数据库并发问题在你眼里就不再是黑盒而是每个版本、每个事务ID、每段undo都能顺着链条找到答案的清晰逻辑。