MySQL MVCC解密：多版本并发控制的魔法世界

当多个用户同时读写数据库时，MySQL如何避免数据混乱？ 本文将揭开MVCC的神秘面纱，带你探索这个让数据库高并发运行的魔法引擎！

一、为什么需要MVCC？并发控制的困境

想象图书馆借阅场景：

• 传统方式：一本书只能一个人看（锁机制）
• MVCC方式：复印多份，每人看不同版本（多版本控制）

传统锁机制的痛点：

#mermaid-svg-GoRVg2RTulJe2oVj {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-GoRVg2RTulJe2oVj .error-icon{fill:#552222;}#mermaid-svg-GoRVg2RTulJe2oVj .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-GoRVg2RTulJe2oVj .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-GoRVg2RTulJe2oVj .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-GoRVg2RTulJe2oVj .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-GoRVg2RTulJe2oVj .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-GoRVg2RTulJe2oVj .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-GoRVg2RTulJe2oVj .marker{fill:#333333;stroke:#333333;}#mermaid-svg-GoRVg2RTulJe2oVj .marker.cross{stroke:#333333;}#mermaid-svg-GoRVg2RTulJe2oVj svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-GoRVg2RTulJe2oVj .label{font-family:\"trebuchet ms\",verdana,arial,sans-serif;color:#333;}#mermaid-svg-GoRVg2RTulJe2oVj .cluster-label text{fill:#333;}#mermaid-svg-GoRVg2RTulJe2oVj .cluster-label span{color:#333;}#mermaid-svg-GoRVg2RTulJe2oVj .label text,#mermaid-svg-GoRVg2RTulJe2oVj span{fill:#333;color:#333;}#mermaid-svg-GoRVg2RTulJe2oVj .node rect,#mermaid-svg-GoRVg2RTulJe2oVj .node circle,#mermaid-svg-GoRVg2RTulJe2oVj .node ellipse,#mermaid-svg-GoRVg2RTulJe2oVj .node polygon,#mermaid-svg-GoRVg2RTulJe2oVj .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-GoRVg2RTulJe2oVj .node .label{text-align:center;}#mermaid-svg-GoRVg2RTulJe2oVj .node.clickable{cursor:pointer;}#mermaid-svg-GoRVg2RTulJe2oVj .arrowheadPath{fill:#333333;}#mermaid-svg-GoRVg2RTulJe2oVj .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-GoRVg2RTulJe2oVj .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-GoRVg2RTulJe2oVj .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-GoRVg2RTulJe2oVj .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-GoRVg2RTulJe2oVj .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-GoRVg2RTulJe2oVj .cluster text{fill:#333;}#mermaid-svg-GoRVg2RTulJe2oVj .cluster span{color:#333;}#mermaid-svg-GoRVg2RTulJe2oVj div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-GoRVg2RTulJe2oVj :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}事务A读数据加锁事务B写数据等待锁释放长时间等待系统卡顿

二、MVCC是什么？时间旅行的艺术

MVCC核心概念

MVCC（Multi-Version Concurrency Control）：

通过创建数据快照实现非锁定读，每个事务看到数据库在特定时间点的状态

MVCC vs 锁机制对比

特性 锁机制 MVCC 优势 读阻塞写 ✅ ❌ 读写不冲突 写阻塞读 ✅ ❌ 写不阻塞读 并发性能 低 高 提升5-10倍 实现复杂度 简单 复杂 功能更强大 适用场景 低并发 高并发 现代数据库首选

三、MVCC工作原理全景图

数据行的隐藏字段

每行数据包含三个隐藏列：

#mermaid-svg-Wb6NxEJ2KXdeyfhS {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .error-icon{fill:#552222;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .marker{fill:#333333;stroke:#333333;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .marker.cross{stroke:#333333;}#mermaid-svg-Wb6NxEJ2KXdeyfhS svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-Wb6NxEJ2KXdeyfhS g.classGroup text{fill:#9370DB;fill:#131300;stroke:none;font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:10px;}#mermaid-svg-Wb6NxEJ2KXdeyfhS g.classGroup text .title{font-weight:bolder;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .nodeLabel,#mermaid-svg-Wb6NxEJ2KXdeyfhS .edgeLabel{color:#131300;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .edgeLabel .label rect{fill:#ECECFF;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .label text{fill:#131300;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .edgeLabel .label span{background:#ECECFF;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .classTitle{font-weight:bolder;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .node rect,#mermaid-svg-Wb6NxEJ2KXdeyfhS .node circle,#mermaid-svg-Wb6NxEJ2KXdeyfhS .node ellipse,#mermaid-svg-Wb6NxEJ2KXdeyfhS .node polygon,#mermaid-svg-Wb6NxEJ2KXdeyfhS .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .divider{stroke:#9370DB;stroke:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS g.clickable{cursor:pointer;}#mermaid-svg-Wb6NxEJ2KXdeyfhS g.classGroup rect{fill:#ECECFF;stroke:#9370DB;}#mermaid-svg-Wb6NxEJ2KXdeyfhS g.classGroup line{stroke:#9370DB;stroke-width:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .classLabel .box{stroke:none;stroke-width:0;fill:#ECECFF;opacity:0.5;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .classLabel .label{fill:#9370DB;font-size:10px;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .relation{stroke:#333333;stroke-width:1;fill:none;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .dashed-line{stroke-dasharray:3;}#mermaid-svg-Wb6NxEJ2KXdeyfhS #compositionStart,#mermaid-svg-Wb6NxEJ2KXdeyfhS .composition{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS #compositionEnd,#mermaid-svg-Wb6NxEJ2KXdeyfhS .composition{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS #dependencyStart,#mermaid-svg-Wb6NxEJ2KXdeyfhS .dependency{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS #dependencyStart,#mermaid-svg-Wb6NxEJ2KXdeyfhS .dependency{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS #extensionStart,#mermaid-svg-Wb6NxEJ2KXdeyfhS .extension{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS #extensionEnd,#mermaid-svg-Wb6NxEJ2KXdeyfhS .extension{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS #aggregationStart,#mermaid-svg-Wb6NxEJ2KXdeyfhS .aggregation{fill:#ECECFF!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS #aggregationEnd,#mermaid-svg-Wb6NxEJ2KXdeyfhS .aggregation{fill:#ECECFF!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-Wb6NxEJ2KXdeyfhS .edgeTerminals{font-size:11px;}#mermaid-svg-Wb6NxEJ2KXdeyfhS :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}InnoDBRow+DB_TRX_ID : 6字节+DB_ROLL_PTR : 7字节+DB_ROW_ID : 6字节+用户数据

字段 描述 作用 DB_TRX_ID 最后修改的事务ID 标识数据版本 DB_ROLL_PTR 回滚指针 指向Undo Log记录 DB_ROW_ID 行ID 无主键时自动生成

MVCC核心流程

#mermaid-svg-yMPifi80ZHWiKRNB {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-yMPifi80ZHWiKRNB .error-icon{fill:#552222;}#mermaid-svg-yMPifi80ZHWiKRNB .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-yMPifi80ZHWiKRNB .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-yMPifi80ZHWiKRNB .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-yMPifi80ZHWiKRNB .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-yMPifi80ZHWiKRNB .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-yMPifi80ZHWiKRNB .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-yMPifi80ZHWiKRNB .marker{fill:#333333;stroke:#333333;}#mermaid-svg-yMPifi80ZHWiKRNB .marker.cross{stroke:#333333;}#mermaid-svg-yMPifi80ZHWiKRNB svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-yMPifi80ZHWiKRNB .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-yMPifi80ZHWiKRNB text.actor>tspan{fill:black;stroke:none;}#mermaid-svg-yMPifi80ZHWiKRNB .actor-line{stroke:grey;}#mermaid-svg-yMPifi80ZHWiKRNB .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-yMPifi80ZHWiKRNB .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-yMPifi80ZHWiKRNB #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-yMPifi80ZHWiKRNB .sequenceNumber{fill:white;}#mermaid-svg-yMPifi80ZHWiKRNB #sequencenumber{fill:#333;}#mermaid-svg-yMPifi80ZHWiKRNB #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-yMPifi80ZHWiKRNB .messageText{fill:#333;stroke:#333;}#mermaid-svg-yMPifi80ZHWiKRNB .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-yMPifi80ZHWiKRNB .labelText,#mermaid-svg-yMPifi80ZHWiKRNB .labelText>tspan{fill:black;stroke:none;}#mermaid-svg-yMPifi80ZHWiKRNB .loopText,#mermaid-svg-yMPifi80ZHWiKRNB .loopText>tspan{fill:black;stroke:none;}#mermaid-svg-yMPifi80ZHWiKRNB .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-yMPifi80ZHWiKRNB .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-yMPifi80ZHWiKRNB .noteText,#mermaid-svg-yMPifi80ZHWiKRNB .noteText>tspan{fill:black;stroke:none;}#mermaid-svg-yMPifi80ZHWiKRNB .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-yMPifi80ZHWiKRNB .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-yMPifi80ZHWiKRNB .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-yMPifi80ZHWiKRNB .actorPopupMenu{position:absolute;}#mermaid-svg-yMPifi80ZHWiKRNB .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#mermaid-svg-yMPifi80ZHWiKRNB .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-yMPifi80ZHWiKRNB .actor-man circle,#mermaid-svg-yMPifi80ZHWiKRNB line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-yMPifi80ZHWiKRNB :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}事务100事务101数据库开始事务(ReadView)修改行X(创建新版本)返回成功读取行X返回旧版本(事务100可见)提交事务提交事务事务100事务101数据库

四、InnoDB的MVCC实现机制

1. Undo Log版本链

#mermaid-svg-azvTsS0V9QmAspIT {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-azvTsS0V9QmAspIT .error-icon{fill:#552222;}#mermaid-svg-azvTsS0V9QmAspIT .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-azvTsS0V9QmAspIT .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-azvTsS0V9QmAspIT .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-azvTsS0V9QmAspIT .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-azvTsS0V9QmAspIT .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-azvTsS0V9QmAspIT .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-azvTsS0V9QmAspIT .marker{fill:#333333;stroke:#333333;}#mermaid-svg-azvTsS0V9QmAspIT .marker.cross{stroke:#333333;}#mermaid-svg-azvTsS0V9QmAspIT svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-azvTsS0V9QmAspIT .label{font-family:\"trebuchet ms\",verdana,arial,sans-serif;color:#333;}#mermaid-svg-azvTsS0V9QmAspIT .cluster-label text{fill:#333;}#mermaid-svg-azvTsS0V9QmAspIT .cluster-label span{color:#333;}#mermaid-svg-azvTsS0V9QmAspIT .label text,#mermaid-svg-azvTsS0V9QmAspIT span{fill:#333;color:#333;}#mermaid-svg-azvTsS0V9QmAspIT .node rect,#mermaid-svg-azvTsS0V9QmAspIT .node circle,#mermaid-svg-azvTsS0V9QmAspIT .node ellipse,#mermaid-svg-azvTsS0V9QmAspIT .node polygon,#mermaid-svg-azvTsS0V9QmAspIT .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-azvTsS0V9QmAspIT .node .label{text-align:center;}#mermaid-svg-azvTsS0V9QmAspIT .node.clickable{cursor:pointer;}#mermaid-svg-azvTsS0V9QmAspIT .arrowheadPath{fill:#333333;}#mermaid-svg-azvTsS0V9QmAspIT .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-azvTsS0V9QmAspIT .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-azvTsS0V9QmAspIT .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-azvTsS0V9QmAspIT .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-azvTsS0V9QmAspIT .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-azvTsS0V9QmAspIT .cluster text{fill:#333;}#mermaid-svg-azvTsS0V9QmAspIT .cluster span{color:#333;}#mermaid-svg-azvTsS0V9QmAspIT div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-azvTsS0V9QmAspIT :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}当前版本旧版本更旧版本初始版本

2. ReadView（读视图）

#mermaid-svg-wIZxevtVBsj3OsAT {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-wIZxevtVBsj3OsAT .error-icon{fill:#552222;}#mermaid-svg-wIZxevtVBsj3OsAT .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-wIZxevtVBsj3OsAT .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-wIZxevtVBsj3OsAT .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-wIZxevtVBsj3OsAT .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-wIZxevtVBsj3OsAT .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-wIZxevtVBsj3OsAT .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-wIZxevtVBsj3OsAT .marker{fill:#333333;stroke:#333333;}#mermaid-svg-wIZxevtVBsj3OsAT .marker.cross{stroke:#333333;}#mermaid-svg-wIZxevtVBsj3OsAT svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-wIZxevtVBsj3OsAT g.classGroup text{fill:#9370DB;fill:#131300;stroke:none;font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:10px;}#mermaid-svg-wIZxevtVBsj3OsAT g.classGroup text .title{font-weight:bolder;}#mermaid-svg-wIZxevtVBsj3OsAT .nodeLabel,#mermaid-svg-wIZxevtVBsj3OsAT .edgeLabel{color:#131300;}#mermaid-svg-wIZxevtVBsj3OsAT .edgeLabel .label rect{fill:#ECECFF;}#mermaid-svg-wIZxevtVBsj3OsAT .label text{fill:#131300;}#mermaid-svg-wIZxevtVBsj3OsAT .edgeLabel .label span{background:#ECECFF;}#mermaid-svg-wIZxevtVBsj3OsAT .classTitle{font-weight:bolder;}#mermaid-svg-wIZxevtVBsj3OsAT .node rect,#mermaid-svg-wIZxevtVBsj3OsAT .node circle,#mermaid-svg-wIZxevtVBsj3OsAT .node ellipse,#mermaid-svg-wIZxevtVBsj3OsAT .node polygon,#mermaid-svg-wIZxevtVBsj3OsAT .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-wIZxevtVBsj3OsAT .divider{stroke:#9370DB;stroke:1;}#mermaid-svg-wIZxevtVBsj3OsAT g.clickable{cursor:pointer;}#mermaid-svg-wIZxevtVBsj3OsAT g.classGroup rect{fill:#ECECFF;stroke:#9370DB;}#mermaid-svg-wIZxevtVBsj3OsAT g.classGroup line{stroke:#9370DB;stroke-width:1;}#mermaid-svg-wIZxevtVBsj3OsAT .classLabel .box{stroke:none;stroke-width:0;fill:#ECECFF;opacity:0.5;}#mermaid-svg-wIZxevtVBsj3OsAT .classLabel .label{fill:#9370DB;font-size:10px;}#mermaid-svg-wIZxevtVBsj3OsAT .relation{stroke:#333333;stroke-width:1;fill:none;}#mermaid-svg-wIZxevtVBsj3OsAT .dashed-line{stroke-dasharray:3;}#mermaid-svg-wIZxevtVBsj3OsAT #compositionStart,#mermaid-svg-wIZxevtVBsj3OsAT .composition{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-wIZxevtVBsj3OsAT #compositionEnd,#mermaid-svg-wIZxevtVBsj3OsAT .composition{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-wIZxevtVBsj3OsAT #dependencyStart,#mermaid-svg-wIZxevtVBsj3OsAT .dependency{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-wIZxevtVBsj3OsAT #dependencyStart,#mermaid-svg-wIZxevtVBsj3OsAT .dependency{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-wIZxevtVBsj3OsAT #extensionStart,#mermaid-svg-wIZxevtVBsj3OsAT .extension{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-wIZxevtVBsj3OsAT #extensionEnd,#mermaid-svg-wIZxevtVBsj3OsAT .extension{fill:#333333!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-wIZxevtVBsj3OsAT #aggregationStart,#mermaid-svg-wIZxevtVBsj3OsAT .aggregation{fill:#ECECFF!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-wIZxevtVBsj3OsAT #aggregationEnd,#mermaid-svg-wIZxevtVBsj3OsAT .aggregation{fill:#ECECFF!important;stroke:#333333!important;stroke-width:1;}#mermaid-svg-wIZxevtVBsj3OsAT .edgeTerminals{font-size:11px;}#mermaid-svg-wIZxevtVBsj3OsAT :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}ReadView+m_low_limit_id : 最小活动事务ID+m_up_limit_id : 最大活动事务ID+m_creator_trx_id : 创建者事务ID+m_ids : 活动事务列表+is_visible(trx_id)

3. 可见性判断算法

def is_visible(trx_id, read_view): if trx_id < read_view.m_low_limit_id: return True # 事务已提交 elif trx_id >= read_view.m_up_limit_id: return False # 事务后开始 elif trx_id in read_view.m_ids: return False # 事务仍活跃 else: return True # 事务已提交

五、MVCC与事务隔离级别

不同隔离级别的MVCC行为

隔离级别 SELECT行为 实现机制 读未提交 读取最新数据 无视MVCC 读已提交 每次读取新快照 每次创建新ReadView 可重复读 保持首次读取快照 使用首次ReadView 串行化 加锁读取 不使用MVCC

幻读问题解决方案

#mermaid-svg-Y5lv1ONg7YCq7DOg {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-Y5lv1ONg7YCq7DOg .error-icon{fill:#552222;}#mermaid-svg-Y5lv1ONg7YCq7DOg .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-Y5lv1ONg7YCq7DOg .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-Y5lv1ONg7YCq7DOg .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-Y5lv1ONg7YCq7DOg .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-Y5lv1ONg7YCq7DOg .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-Y5lv1ONg7YCq7DOg .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-Y5lv1ONg7YCq7DOg .marker{fill:#333333;stroke:#333333;}#mermaid-svg-Y5lv1ONg7YCq7DOg .marker.cross{stroke:#333333;}#mermaid-svg-Y5lv1ONg7YCq7DOg svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-Y5lv1ONg7YCq7DOg .label{font-family:\"trebuchet ms\",verdana,arial,sans-serif;color:#333;}#mermaid-svg-Y5lv1ONg7YCq7DOg .cluster-label text{fill:#333;}#mermaid-svg-Y5lv1ONg7YCq7DOg .cluster-label span{color:#333;}#mermaid-svg-Y5lv1ONg7YCq7DOg .label text,#mermaid-svg-Y5lv1ONg7YCq7DOg span{fill:#333;color:#333;}#mermaid-svg-Y5lv1ONg7YCq7DOg .node rect,#mermaid-svg-Y5lv1ONg7YCq7DOg .node circle,#mermaid-svg-Y5lv1ONg7YCq7DOg .node ellipse,#mermaid-svg-Y5lv1ONg7YCq7DOg .node polygon,#mermaid-svg-Y5lv1ONg7YCq7DOg .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-Y5lv1ONg7YCq7DOg .node .label{text-align:center;}#mermaid-svg-Y5lv1ONg7YCq7DOg .node.clickable{cursor:pointer;}#mermaid-svg-Y5lv1ONg7YCq7DOg .arrowheadPath{fill:#333333;}#mermaid-svg-Y5lv1ONg7YCq7DOg .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-Y5lv1ONg7YCq7DOg .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-Y5lv1ONg7YCq7DOg .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-Y5lv1ONg7YCq7DOg .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-Y5lv1ONg7YCq7DOg .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-Y5lv1ONg7YCq7DOg .cluster text{fill:#333;}#mermaid-svg-Y5lv1ONg7YCq7DOg .cluster span{color:#333;}#mermaid-svg-Y5lv1ONg7YCq7DOg div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-Y5lv1ONg7YCq7DOg :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}可重复读读已提交事务A查询age>30返回3行事务B插入age=35提交再次查询age>30仍返回3行返回4行

六、MVCC实战演示

场景设置

-- 创建测试表CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), balance DECIMAL(10,2)) ENGINE=InnoDB;-- 插入初始数据INSERT INTO users VALUES (1, \'Alice\', 1000);

实验1：读写并发不阻塞

-- 事务1（读）START TRANSACTION;SELECT * FROM users; -- 看到Alice余额1000-- 事务2（写）START TRANSACTION;UPDATE users SET balance = 900 WHERE id = 1;-- 事务1再次查询（可重复读）SELECT * FROM users; -- 仍看到1000（MVCC快照）

实验2：版本链追溯

-- 查看隐藏字段（需特殊工具）SELECT id, name, balance, DB_TRX_ID AS trx_id, DB_ROLL_PTR AS roll_ptrFROM users;-- 输出：+----+-------+---------+--------+------------------+| id | name | balance | trx_id | roll_ptr |+----+-------+---------+--------+------------------+| 1 | Alice | 900.00 | 101 | 0x0000000123456 |+----+-------+---------+--------+------------------+

实验3：Undo Log查看

-- 查看Undo信息（需管理员权限）SELECT * FROM information_schema.INNODB_TRX WHERE trx_id = 101\\G-- 输出包含：trx_undo_rec: {old_balance: 1000.00}

七、MVCC的优缺点分析

优势 ✅

优势 影响 量化提升 非阻塞读 读写不冲突 读性能提升5-10倍 避免死锁 减少锁竞争 死锁率降低80% 快速回滚 使用Undo Log 回滚速度提升100倍 高并发 支持更多连接 并发连接提升3-5倍

局限 ❌

局限 原因 解决方案 额外存储 需要Undo Log 定期清理 版本链过长 长事务导致 避免长事务 写冲突 最后提交者胜出 应用层重试 空间放大 旧版本未清理 优化purge机制

八、MVCC性能优化策略

1. 控制版本链长度

-- 监控版本链SHOW ENGINE INNODB STATUS\\G-- 查找 HISTORY LIST LENGTH-- 优化建议：SET GLOBAL innodb_purge_threads = 4; -- 增加清理线程SET GLOBAL innodb_max_purge_lag = 100000; -- 控制清理延迟

2. 避免长事务

-- 查询长事务SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 5;-- 设置超时SET GLOBAL max_execution_time = 5000; -- 5秒超时

3. 合理设计索引

-- 减少全表扫描CREATE INDEX idx_age ON users(age);-- 索引覆盖查询SELECT id FROM users WHERE age > 30; -- 使用索引避免访问数据行

九、MVCC在分布式数据库中的应用

TiDB的MVCC实现

#mermaid-svg-czYBiGggRGr0a7dS {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-czYBiGggRGr0a7dS .error-icon{fill:#552222;}#mermaid-svg-czYBiGggRGr0a7dS .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-czYBiGggRGr0a7dS .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-czYBiGggRGr0a7dS .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-czYBiGggRGr0a7dS .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-czYBiGggRGr0a7dS .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-czYBiGggRGr0a7dS .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-czYBiGggRGr0a7dS .marker{fill:#333333;stroke:#333333;}#mermaid-svg-czYBiGggRGr0a7dS .marker.cross{stroke:#333333;}#mermaid-svg-czYBiGggRGr0a7dS svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-czYBiGggRGr0a7dS .label{font-family:\"trebuchet ms\",verdana,arial,sans-serif;color:#333;}#mermaid-svg-czYBiGggRGr0a7dS .cluster-label text{fill:#333;}#mermaid-svg-czYBiGggRGr0a7dS .cluster-label span{color:#333;}#mermaid-svg-czYBiGggRGr0a7dS .label text,#mermaid-svg-czYBiGggRGr0a7dS span{fill:#333;color:#333;}#mermaid-svg-czYBiGggRGr0a7dS .node rect,#mermaid-svg-czYBiGggRGr0a7dS .node circle,#mermaid-svg-czYBiGggRGr0a7dS .node ellipse,#mermaid-svg-czYBiGggRGr0a7dS .node polygon,#mermaid-svg-czYBiGggRGr0a7dS .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-czYBiGggRGr0a7dS .node .label{text-align:center;}#mermaid-svg-czYBiGggRGr0a7dS .node.clickable{cursor:pointer;}#mermaid-svg-czYBiGggRGr0a7dS .arrowheadPath{fill:#333333;}#mermaid-svg-czYBiGggRGr0a7dS .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-czYBiGggRGr0a7dS .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-czYBiGggRGr0a7dS .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-czYBiGggRGr0a7dS .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-czYBiGggRGr0a7dS .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-czYBiGggRGr0a7dS .cluster text{fill:#333;}#mermaid-svg-czYBiGggRGr0a7dS .cluster span{color:#333;}#mermaid-svg-czYBiGggRGr0a7dS div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-czYBiGggRGr0a7dS :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}事务开始获取全局时间戳读取数据提交时校验写入新版本

多版本存储格式

数据库 实现方式 特点 MySQL Undo Log 集中存储旧版本 PostgreSQL Heap Only Tuple 表内多版本存储 Oracle Undo Tablespace 专用回滚表空间 TiDB Percolator模型 分布式时间戳

十、总结：MVCC核心要点

MVCC工作原理全景

#mermaid-svg-catFJki3S4g1zDgk {font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-catFJki3S4g1zDgk .error-icon{fill:#552222;}#mermaid-svg-catFJki3S4g1zDgk .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-catFJki3S4g1zDgk .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-catFJki3S4g1zDgk .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-catFJki3S4g1zDgk .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-catFJki3S4g1zDgk .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-catFJki3S4g1zDgk .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-catFJki3S4g1zDgk .marker{fill:#333333;stroke:#333333;}#mermaid-svg-catFJki3S4g1zDgk .marker.cross{stroke:#333333;}#mermaid-svg-catFJki3S4g1zDgk svg{font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-catFJki3S4g1zDgk .label{font-family:\"trebuchet ms\",verdana,arial,sans-serif;color:#333;}#mermaid-svg-catFJki3S4g1zDgk .cluster-label text{fill:#333;}#mermaid-svg-catFJki3S4g1zDgk .cluster-label span{color:#333;}#mermaid-svg-catFJki3S4g1zDgk .label text,#mermaid-svg-catFJki3S4g1zDgk span{fill:#333;color:#333;}#mermaid-svg-catFJki3S4g1zDgk .node rect,#mermaid-svg-catFJki3S4g1zDgk .node circle,#mermaid-svg-catFJki3S4g1zDgk .node ellipse,#mermaid-svg-catFJki3S4g1zDgk .node polygon,#mermaid-svg-catFJki3S4g1zDgk .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-catFJki3S4g1zDgk .node .label{text-align:center;}#mermaid-svg-catFJki3S4g1zDgk .node.clickable{cursor:pointer;}#mermaid-svg-catFJki3S4g1zDgk .arrowheadPath{fill:#333333;}#mermaid-svg-catFJki3S4g1zDgk .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-catFJki3S4g1zDgk .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-catFJki3S4g1zDgk .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-catFJki3S4g1zDgk .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-catFJki3S4g1zDgk .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-catFJki3S4g1zDgk .cluster text{fill:#333;}#mermaid-svg-catFJki3S4g1zDgk .cluster span{color:#333;}#mermaid-svg-catFJki3S4g1zDgk div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\"trebuchet ms\",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-catFJki3S4g1zDgk :root{--mermaid-font-family:\"trebuchet ms\",verdana,arial,sans-serif;}读取修改事务开始创建ReadView数据访问版本链遍历可见性检查创建新版本写入Undo Log

MVCC三大核心价值

1. 高并发：读写操作互不阻塞
2. 一致性：事务看到一致的数据快照
3. 高性能：避免锁带来的性能损耗

最佳实践指南

原则 实施建议 控制事务时长 单事务<1秒 合理选择隔离级别 默认使用RR 定期监控 检查版本链长度 避免全表扫描 优化查询索引 及时清理 配置Purge机制

终极思考：
💡 MVCC是时间换空间的经典设计 - 用存储空间换取并发性能
⚠️ 版本链是双刃剑 - 过长会严重影响性能
🚀 理解MVCC是数据库优化的基石 - 掌握它才能发挥MySQL真正实力

行动指南：立即执行以下命令检查你的数据库MVCC状态：

SHOW ENGINE INNODB STATUS\\G-- 在输出中查找 TRANSACTIONS 部分

讨论话题：你在实际项目中遇到过MVCC引起的意外行为吗？是如何解决的？欢迎在评论区分享你的经验！