MySQL四种隔离级别详解：从脏读到串行化

MySQL四种隔离级别详解：从脏读到串行化

• • 引言
  • 1. 并发事务可能引发的三大问题
  • • 1.1 脏读（Dirty Read）
    • 1.2 不可重复读（Non-Repeatable Read）
    • 1.3 幻读（Phantom Read）
  • 2. 四种隔离级别全景图
  • 3. 详细解读四种隔离级别
  • • 3.1 读未提交（READ UNCOMMITTED）
    • 3.2 读已提交（READ COMMITTED）
    • 3.3 可重复读（REPEATABLE READ）
    • 3.4 可串行化（SERIALIZABLE）
  • 4. MySQL特殊之处：可重复读如何解决幻读？
  • 5. 实战：隔离级别选择指南
  • • 5.1 不同场景的选择建议
    • 5.2 性能对比测试
  • 6. 隔离级别的设置方法
  • • 6.1 全局设置
    • 6.2 配置文件设置
    • 6.3 Spring Boot配置
  • 7. 常见问题解答
  • • Q1：为什么MySQL默认用可重复读，而Oracle用读已提交？
    • Q2：可重复读能完全避免幻读吗？
    • Q3：如何选择隔离级别？
  • 总结

引言

在多用户并发访问数据库时，如果没有有效的隔离机制，就会出现各种数据一致性问题。MySQL通过四种隔离级别来平衡数据一致性和并发性能之间的关系。

本文将深入剖析MySQL的四种隔离级别，通过生动的例子和流程图，帮你彻底理解它们之间的区别和适用场景。

1. 并发事务可能引发的三大问题

在了解隔离级别之前，我们需要先认识并发事务可能导致的三种数据不一致现象：

#mermaid-svg-8ASNtWsowPHQck6F{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-8ASNtWsowPHQck6F .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-8ASNtWsowPHQck6F .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-8ASNtWsowPHQck6F .error-icon{fill:#552222;}#mermaid-svg-8ASNtWsowPHQck6F .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-8ASNtWsowPHQck6F .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-8ASNtWsowPHQck6F .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-8ASNtWsowPHQck6F .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-8ASNtWsowPHQck6F .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-8ASNtWsowPHQck6F .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-8ASNtWsowPHQck6F .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-8ASNtWsowPHQck6F .marker{fill:#333333;stroke:#333333;}#mermaid-svg-8ASNtWsowPHQck6F .marker.cross{stroke:#333333;}#mermaid-svg-8ASNtWsowPHQck6F svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-8ASNtWsowPHQck6F p{margin:0;}#mermaid-svg-8ASNtWsowPHQck6F .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-8ASNtWsowPHQck6F .cluster-label text{fill:#333;}#mermaid-svg-8ASNtWsowPHQck6F .cluster-label span{color:#333;}#mermaid-svg-8ASNtWsowPHQck6F .cluster-label span p{background-color:transparent;}#mermaid-svg-8ASNtWsowPHQck6F .label text,#mermaid-svg-8ASNtWsowPHQck6F span{fill:#333;color:#333;}#mermaid-svg-8ASNtWsowPHQck6F .node rect,#mermaid-svg-8ASNtWsowPHQck6F .node circle,#mermaid-svg-8ASNtWsowPHQck6F .node ellipse,#mermaid-svg-8ASNtWsowPHQck6F .node polygon,#mermaid-svg-8ASNtWsowPHQck6F .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-8ASNtWsowPHQck6F .rough-node .label text,#mermaid-svg-8ASNtWsowPHQck6F .node .label text,#mermaid-svg-8ASNtWsowPHQck6F .image-shape .label,#mermaid-svg-8ASNtWsowPHQck6F .icon-shape .label{text-anchor:middle;}#mermaid-svg-8ASNtWsowPHQck6F .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-8ASNtWsowPHQck6F .rough-node .label,#mermaid-svg-8ASNtWsowPHQck6F .node .label,#mermaid-svg-8ASNtWsowPHQck6F .image-shape .label,#mermaid-svg-8ASNtWsowPHQck6F .icon-shape .label{text-align:center;}#mermaid-svg-8ASNtWsowPHQck6F .node.clickable{cursor:pointer;}#mermaid-svg-8ASNtWsowPHQck6F .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-8ASNtWsowPHQck6F .arrowheadPath{fill:#333333;}#mermaid-svg-8ASNtWsowPHQck6F .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-8ASNtWsowPHQck6F .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-8ASNtWsowPHQck6F .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-8ASNtWsowPHQck6F .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-8ASNtWsowPHQck6F .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-8ASNtWsowPHQck6F .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-8ASNtWsowPHQck6F .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-8ASNtWsowPHQck6F .cluster text{fill:#333;}#mermaid-svg-8ASNtWsowPHQck6F .cluster span{color:#333;}#mermaid-svg-8ASNtWsowPHQck6F 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-8ASNtWsowPHQck6F .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-8ASNtWsowPHQck6F rect.text{fill:none;stroke-width:0;}#mermaid-svg-8ASNtWsowPHQck6F .icon-shape,#mermaid-svg-8ASNtWsowPHQck6F .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-8ASNtWsowPHQck6F .icon-shape p,#mermaid-svg-8ASNtWsowPHQck6F .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-8ASNtWsowPHQck6F .icon-shape rect,#mermaid-svg-8ASNtWsowPHQck6F .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-8ASNtWsowPHQck6F .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-8ASNtWsowPHQck6F .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-8ASNtWsowPHQck6F :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

并发问题

读到未提交数据

同一查询结果不同

读到新插入数据

脏读Dirty Read

数据不一致

不可重复读Non-Repeatable Read

幻读Phantom Read

需要隔离级别来规避

1.1 脏读（Dirty Read）

定义：一个事务读取到另一个事务未提交的数据。

— 事务1：正在修改但未提交
BEGIN;
UPDATE account SET balance = 800 WHERE id = 1; — 未提交

— 事务2：读取到了未提交的数据（脏读！）
SELECT balance FROM account WHERE id = 1; — 返回800（如果事务1回滚，这个数据就是无效的）

1.2 不可重复读（Non-Repeatable Read）

定义：同一事务内，多次读取同一数据，结果不一致。

— 事务1：第一次查询
BEGIN;
SELECT balance FROM account WHERE id = 1; — 返回1000

— 事务2：修改并提交
UPDATE account SET balance = 800 WHERE id = 1;
COMMIT;

— 事务1：第二次查询，结果变了（不可重复读）
SELECT balance FROM account WHERE id = 1; — 返回800

1.3 幻读（Phantom Read）

定义：同一事务内，多次查询返回的记录数不同（有新数据插入）。

— 事务1：查询所有账户
BEGIN;
SELECT * FROM account WHERE balance > 500; — 返回10条记录

— 事务2：插入新账户并提交
INSERT INTO account (id, balance) VALUES (100, 600);
COMMIT;

— 事务1：再次查询，返回11条记录（出现了"幻影"行）
SELECT * FROM account WHERE balance > 500; — 返回11条

2. 四种隔离级别全景图

MySQL的四种隔离级别依次增强，解决的并发问题也越来越多：

#mermaid-svg-xwzyAhsF5X6g6o8V{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-xwzyAhsF5X6g6o8V .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-xwzyAhsF5X6g6o8V .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-xwzyAhsF5X6g6o8V .error-icon{fill:#552222;}#mermaid-svg-xwzyAhsF5X6g6o8V .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-xwzyAhsF5X6g6o8V .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-xwzyAhsF5X6g6o8V .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-xwzyAhsF5X6g6o8V .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-xwzyAhsF5X6g6o8V .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-xwzyAhsF5X6g6o8V .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-xwzyAhsF5X6g6o8V .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-xwzyAhsF5X6g6o8V .marker{fill:#333333;stroke:#333333;}#mermaid-svg-xwzyAhsF5X6g6o8V .marker.cross{stroke:#333333;}#mermaid-svg-xwzyAhsF5X6g6o8V svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-xwzyAhsF5X6g6o8V p{margin:0;}#mermaid-svg-xwzyAhsF5X6g6o8V .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-xwzyAhsF5X6g6o8V .cluster-label text{fill:#333;}#mermaid-svg-xwzyAhsF5X6g6o8V .cluster-label span{color:#333;}#mermaid-svg-xwzyAhsF5X6g6o8V .cluster-label span p{background-color:transparent;}#mermaid-svg-xwzyAhsF5X6g6o8V .label text,#mermaid-svg-xwzyAhsF5X6g6o8V span{fill:#333;color:#333;}#mermaid-svg-xwzyAhsF5X6g6o8V .node rect,#mermaid-svg-xwzyAhsF5X6g6o8V .node circle,#mermaid-svg-xwzyAhsF5X6g6o8V .node ellipse,#mermaid-svg-xwzyAhsF5X6g6o8V .node polygon,#mermaid-svg-xwzyAhsF5X6g6o8V .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-xwzyAhsF5X6g6o8V .rough-node .label text,#mermaid-svg-xwzyAhsF5X6g6o8V .node .label text,#mermaid-svg-xwzyAhsF5X6g6o8V .image-shape .label,#mermaid-svg-xwzyAhsF5X6g6o8V .icon-shape .label{text-anchor:middle;}#mermaid-svg-xwzyAhsF5X6g6o8V .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-xwzyAhsF5X6g6o8V .rough-node .label,#mermaid-svg-xwzyAhsF5X6g6o8V .node .label,#mermaid-svg-xwzyAhsF5X6g6o8V .image-shape .label,#mermaid-svg-xwzyAhsF5X6g6o8V .icon-shape .label{text-align:center;}#mermaid-svg-xwzyAhsF5X6g6o8V .node.clickable{cursor:pointer;}#mermaid-svg-xwzyAhsF5X6g6o8V .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-xwzyAhsF5X6g6o8V .arrowheadPath{fill:#333333;}#mermaid-svg-xwzyAhsF5X6g6o8V .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-xwzyAhsF5X6g6o8V .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-xwzyAhsF5X6g6o8V .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-xwzyAhsF5X6g6o8V .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-xwzyAhsF5X6g6o8V .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-xwzyAhsF5X6g6o8V .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-xwzyAhsF5X6g6o8V .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-xwzyAhsF5X6g6o8V .cluster text{fill:#333;}#mermaid-svg-xwzyAhsF5X6g6o8V .cluster span{color:#333;}#mermaid-svg-xwzyAhsF5X6g6o8V 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-xwzyAhsF5X6g6o8V .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-xwzyAhsF5X6g6o8V rect.text{fill:none;stroke-width:0;}#mermaid-svg-xwzyAhsF5X6g6o8V .icon-shape,#mermaid-svg-xwzyAhsF5X6g6o8V .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-xwzyAhsF5X6g6o8V .icon-shape p,#mermaid-svg-xwzyAhsF5X6g6o8V .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-xwzyAhsF5X6g6o8V .icon-shape rect,#mermaid-svg-xwzyAhsF5X6g6o8V .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-xwzyAhsF5X6g6o8V .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-xwzyAhsF5X6g6o8V .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-xwzyAhsF5X6g6o8V :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

隔离级别阶梯

可能发生: 脏读、不可重复读、幻读

可能发生: 不可重复读、幻读

可能发生: 幻读

所有问题都避免

读未提交READ UNCOMMITTED

无隔离

读已提交READ COMMITTED

避免脏读

可重复读REPEATABLE READ

避免脏读、不可重复读

可串行化SERIALIZABLE

最高隔离

3. 详细解读四种隔离级别

3.1 读未提交（READ UNCOMMITTED）

定义：一个事务可以读取到另一个事务未提交的数据。

— 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

— 事务A
BEGIN;
UPDATE account SET balance = 500 WHERE id = 1; — 修改未提交

— 事务B（并发）
SELECT balance FROM account WHERE id = 1; — 返回500（脏读！）

— 事务A回滚
ROLLBACK; — 事务B读到的500就成了无效数据

实现原理：

#mermaid-svg-nTlvGEVrRJAKwyF2{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-nTlvGEVrRJAKwyF2 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-nTlvGEVrRJAKwyF2 .error-icon{fill:#552222;}#mermaid-svg-nTlvGEVrRJAKwyF2 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-nTlvGEVrRJAKwyF2 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-nTlvGEVrRJAKwyF2 .marker.cross{stroke:#333333;}#mermaid-svg-nTlvGEVrRJAKwyF2 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-nTlvGEVrRJAKwyF2 p{margin:0;}#mermaid-svg-nTlvGEVrRJAKwyF2 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-nTlvGEVrRJAKwyF2 .cluster-label text{fill:#333;}#mermaid-svg-nTlvGEVrRJAKwyF2 .cluster-label span{color:#333;}#mermaid-svg-nTlvGEVrRJAKwyF2 .cluster-label span p{background-color:transparent;}#mermaid-svg-nTlvGEVrRJAKwyF2 .label text,#mermaid-svg-nTlvGEVrRJAKwyF2 span{fill:#333;color:#333;}#mermaid-svg-nTlvGEVrRJAKwyF2 .node rect,#mermaid-svg-nTlvGEVrRJAKwyF2 .node circle,#mermaid-svg-nTlvGEVrRJAKwyF2 .node ellipse,#mermaid-svg-nTlvGEVrRJAKwyF2 .node polygon,#mermaid-svg-nTlvGEVrRJAKwyF2 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-nTlvGEVrRJAKwyF2 .rough-node .label text,#mermaid-svg-nTlvGEVrRJAKwyF2 .node .label text,#mermaid-svg-nTlvGEVrRJAKwyF2 .image-shape .label,#mermaid-svg-nTlvGEVrRJAKwyF2 .icon-shape .label{text-anchor:middle;}#mermaid-svg-nTlvGEVrRJAKwyF2 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-nTlvGEVrRJAKwyF2 .rough-node .label,#mermaid-svg-nTlvGEVrRJAKwyF2 .node .label,#mermaid-svg-nTlvGEVrRJAKwyF2 .image-shape .label,#mermaid-svg-nTlvGEVrRJAKwyF2 .icon-shape .label{text-align:center;}#mermaid-svg-nTlvGEVrRJAKwyF2 .node.clickable{cursor:pointer;}#mermaid-svg-nTlvGEVrRJAKwyF2 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-nTlvGEVrRJAKwyF2 .arrowheadPath{fill:#333333;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-nTlvGEVrRJAKwyF2 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-nTlvGEVrRJAKwyF2 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-nTlvGEVrRJAKwyF2 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-nTlvGEVrRJAKwyF2 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-nTlvGEVrRJAKwyF2 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-nTlvGEVrRJAKwyF2 .cluster text{fill:#333;}#mermaid-svg-nTlvGEVrRJAKwyF2 .cluster span{color:#333;}#mermaid-svg-nTlvGEVrRJAKwyF2 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-nTlvGEVrRJAKwyF2 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-nTlvGEVrRJAKwyF2 rect.text{fill:none;stroke-width:0;}#mermaid-svg-nTlvGEVrRJAKwyF2 .icon-shape,#mermaid-svg-nTlvGEVrRJAKwyF2 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-nTlvGEVrRJAKwyF2 .icon-shape p,#mermaid-svg-nTlvGEVrRJAKwyF2 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-nTlvGEVrRJAKwyF2 .icon-shape rect,#mermaid-svg-nTlvGEVrRJAKwyF2 .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-nTlvGEVrRJAKwyF2 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-nTlvGEVrRJAKwyF2 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-nTlvGEVrRJAKwyF2 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

读未提交

不加锁直接读

事务A修改数据

直接修改数据行

事务B读取

事务A回滚

数据恢复原值

事务B已读到脏数据

数据不一致

适用场景：几乎不用。只有在对数据一致性要求极低，追求极致性能的场景下才考虑（如日志收集）。

3.2 读已提交（READ COMMITTED）

定义：一个事务只能读取到其他事务已提交的数据。这是SQL Server、Oracle的默认隔离级别。

— 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

— 事务A
BEGIN;
UPDATE account SET balance = 500 WHERE id = 1; — 修改未提交

— 事务B
SELECT balance FROM account WHERE id = 1; — 返回原值1000（避免了脏读）

— 事务A提交
COMMIT;

— 事务B再次查询
SELECT balance FROM account WHERE id = 1; — 返回500（但存在不可重复读）

实现原理：每条SELECT语句都会生成一个新的快照。

#mermaid-svg-Xe6qYgkoxTER69uG{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-Xe6qYgkoxTER69uG .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-Xe6qYgkoxTER69uG .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-Xe6qYgkoxTER69uG .error-icon{fill:#552222;}#mermaid-svg-Xe6qYgkoxTER69uG .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-Xe6qYgkoxTER69uG .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-Xe6qYgkoxTER69uG .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-Xe6qYgkoxTER69uG .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-Xe6qYgkoxTER69uG .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-Xe6qYgkoxTER69uG .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-Xe6qYgkoxTER69uG .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-Xe6qYgkoxTER69uG .marker{fill:#333333;stroke:#333333;}#mermaid-svg-Xe6qYgkoxTER69uG .marker.cross{stroke:#333333;}#mermaid-svg-Xe6qYgkoxTER69uG svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-Xe6qYgkoxTER69uG p{margin:0;}#mermaid-svg-Xe6qYgkoxTER69uG .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-Xe6qYgkoxTER69uG .cluster-label text{fill:#333;}#mermaid-svg-Xe6qYgkoxTER69uG .cluster-label span{color:#333;}#mermaid-svg-Xe6qYgkoxTER69uG .cluster-label span p{background-color:transparent;}#mermaid-svg-Xe6qYgkoxTER69uG .label text,#mermaid-svg-Xe6qYgkoxTER69uG span{fill:#333;color:#333;}#mermaid-svg-Xe6qYgkoxTER69uG .node rect,#mermaid-svg-Xe6qYgkoxTER69uG .node circle,#mermaid-svg-Xe6qYgkoxTER69uG .node ellipse,#mermaid-svg-Xe6qYgkoxTER69uG .node polygon,#mermaid-svg-Xe6qYgkoxTER69uG .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-Xe6qYgkoxTER69uG .rough-node .label text,#mermaid-svg-Xe6qYgkoxTER69uG .node .label text,#mermaid-svg-Xe6qYgkoxTER69uG .image-shape .label,#mermaid-svg-Xe6qYgkoxTER69uG .icon-shape .label{text-anchor:middle;}#mermaid-svg-Xe6qYgkoxTER69uG .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-Xe6qYgkoxTER69uG .rough-node .label,#mermaid-svg-Xe6qYgkoxTER69uG .node .label,#mermaid-svg-Xe6qYgkoxTER69uG .image-shape .label,#mermaid-svg-Xe6qYgkoxTER69uG .icon-shape .label{text-align:center;}#mermaid-svg-Xe6qYgkoxTER69uG .node.clickable{cursor:pointer;}#mermaid-svg-Xe6qYgkoxTER69uG .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-Xe6qYgkoxTER69uG .arrowheadPath{fill:#333333;}#mermaid-svg-Xe6qYgkoxTER69uG .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-Xe6qYgkoxTER69uG .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-Xe6qYgkoxTER69uG .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-Xe6qYgkoxTER69uG .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-Xe6qYgkoxTER69uG .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-Xe6qYgkoxTER69uG .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-Xe6qYgkoxTER69uG .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-Xe6qYgkoxTER69uG .cluster text{fill:#333;}#mermaid-svg-Xe6qYgkoxTER69uG .cluster span{color:#333;}#mermaid-svg-Xe6qYgkoxTER69uG 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-Xe6qYgkoxTER69uG .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-Xe6qYgkoxTER69uG rect.text{fill:none;stroke-width:0;}#mermaid-svg-Xe6qYgkoxTER69uG .icon-shape,#mermaid-svg-Xe6qYgkoxTER69uG .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-Xe6qYgkoxTER69uG .icon-shape p,#mermaid-svg-Xe6qYgkoxTER69uG .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-Xe6qYgkoxTER69uG .icon-shape rect,#mermaid-svg-Xe6qYgkoxTER69uG .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-Xe6qYgkoxTER69uG .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-Xe6qYgkoxTER69uG .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-Xe6qYgkoxTER69uG :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

读已提交实现

事务开始

第一次查询创建快照1

其他事务提交

数据变更

第二次查询创建快照2

可能看到不同结果

3.3 可重复读（REPEATABLE READ）

定义：同一事务内的多次读取结果一致。这是MySQL的默认隔离级别。

— 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

— 事务A
BEGIN;
SELECT * FROM account WHERE id = 1; — 返回 balance = 1000

— 事务B
UPDATE account SET balance = 500 WHERE id = 1;
COMMIT;

— 事务A再次查询
SELECT * FROM account WHERE id = 1; — 仍返回 balance = 1000（可重复读）
SELECT * FROM account WHERE id = 1; — 再次查询还是1000

— 事务A提交后再查询
COMMIT;
SELECT * FROM account WHERE id = 1; — 此时才看到500

实现原理：MVCC多版本并发控制

#mermaid-svg-YGNS5ZkdTu4sLC1j{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-YGNS5ZkdTu4sLC1j .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-YGNS5ZkdTu4sLC1j .error-icon{fill:#552222;}#mermaid-svg-YGNS5ZkdTu4sLC1j .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-YGNS5ZkdTu4sLC1j .marker{fill:#333333;stroke:#333333;}#mermaid-svg-YGNS5ZkdTu4sLC1j .marker.cross{stroke:#333333;}#mermaid-svg-YGNS5ZkdTu4sLC1j svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-YGNS5ZkdTu4sLC1j p{margin:0;}#mermaid-svg-YGNS5ZkdTu4sLC1j .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-YGNS5ZkdTu4sLC1j .cluster-label text{fill:#333;}#mermaid-svg-YGNS5ZkdTu4sLC1j .cluster-label span{color:#333;}#mermaid-svg-YGNS5ZkdTu4sLC1j .cluster-label span p{background-color:transparent;}#mermaid-svg-YGNS5ZkdTu4sLC1j .label text,#mermaid-svg-YGNS5ZkdTu4sLC1j span{fill:#333;color:#333;}#mermaid-svg-YGNS5ZkdTu4sLC1j .node rect,#mermaid-svg-YGNS5ZkdTu4sLC1j .node circle,#mermaid-svg-YGNS5ZkdTu4sLC1j .node ellipse,#mermaid-svg-YGNS5ZkdTu4sLC1j .node polygon,#mermaid-svg-YGNS5ZkdTu4sLC1j .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-YGNS5ZkdTu4sLC1j .rough-node .label text,#mermaid-svg-YGNS5ZkdTu4sLC1j .node .label text,#mermaid-svg-YGNS5ZkdTu4sLC1j .image-shape .label,#mermaid-svg-YGNS5ZkdTu4sLC1j .icon-shape .label{text-anchor:middle;}#mermaid-svg-YGNS5ZkdTu4sLC1j .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-YGNS5ZkdTu4sLC1j .rough-node .label,#mermaid-svg-YGNS5ZkdTu4sLC1j .node .label,#mermaid-svg-YGNS5ZkdTu4sLC1j .image-shape .label,#mermaid-svg-YGNS5ZkdTu4sLC1j .icon-shape .label{text-align:center;}#mermaid-svg-YGNS5ZkdTu4sLC1j .node.clickable{cursor:pointer;}#mermaid-svg-YGNS5ZkdTu4sLC1j .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-YGNS5ZkdTu4sLC1j .arrowheadPath{fill:#333333;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-YGNS5ZkdTu4sLC1j .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-YGNS5ZkdTu4sLC1j .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-YGNS5ZkdTu4sLC1j .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-YGNS5ZkdTu4sLC1j .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-YGNS5ZkdTu4sLC1j .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-YGNS5ZkdTu4sLC1j .cluster text{fill:#333;}#mermaid-svg-YGNS5ZkdTu4sLC1j .cluster span{color:#333;}#mermaid-svg-YGNS5ZkdTu4sLC1j 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-YGNS5ZkdTu4sLC1j .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-YGNS5ZkdTu4sLC1j rect.text{fill:none;stroke-width:0;}#mermaid-svg-YGNS5ZkdTu4sLC1j .icon-shape,#mermaid-svg-YGNS5ZkdTu4sLC1j .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-YGNS5ZkdTu4sLC1j .icon-shape p,#mermaid-svg-YGNS5ZkdTu4sLC1j .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-YGNS5ZkdTu4sLC1j .icon-shape rect,#mermaid-svg-YGNS5ZkdTu4sLC1j .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-YGNS5ZkdTu4sLC1j .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-YGNS5ZkdTu4sLC1j .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-YGNS5ZkdTu4sLC1j :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

MVCC实现可重复读

通过版本链访问v1

事务1开始创建视图

读取数据版本v1

事务2修改并提交

创建新版本v2

事务1再次读取

数据行

版本链

v1:1000trx_id=100

v2:500trx_id=101

关键机制：

• 事务开始时会创建一个一致性视图（read view）
• 整个事务期间都使用这个视图访问数据
• 通过undo log版本链，总能找到符合视图版本的数据

3.4 可串行化（SERIALIZABLE）

定义：最高的隔离级别，强制事务串行执行，完全避免并发问题。

— 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

— 事务A
BEGIN;
SELECT * FROM account WHERE id = 1; — 加锁

— 事务B（被阻塞！）
UPDATE account SET balance = 500 WHERE id = 1; — 等待事务A释放锁
— 直到事务A提交或回滚，事务B才能执行

实现原理：

#mermaid-svg-FT1YWeQADmluWAS9{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-FT1YWeQADmluWAS9 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-FT1YWeQADmluWAS9 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-FT1YWeQADmluWAS9 .error-icon{fill:#552222;}#mermaid-svg-FT1YWeQADmluWAS9 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-FT1YWeQADmluWAS9 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-FT1YWeQADmluWAS9 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-FT1YWeQADmluWAS9 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-FT1YWeQADmluWAS9 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-FT1YWeQADmluWAS9 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-FT1YWeQADmluWAS9 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-FT1YWeQADmluWAS9 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-FT1YWeQADmluWAS9 .marker.cross{stroke:#333333;}#mermaid-svg-FT1YWeQADmluWAS9 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-FT1YWeQADmluWAS9 p{margin:0;}#mermaid-svg-FT1YWeQADmluWAS9 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-FT1YWeQADmluWAS9 .cluster-label text{fill:#333;}#mermaid-svg-FT1YWeQADmluWAS9 .cluster-label span{color:#333;}#mermaid-svg-FT1YWeQADmluWAS9 .cluster-label span p{background-color:transparent;}#mermaid-svg-FT1YWeQADmluWAS9 .label text,#mermaid-svg-FT1YWeQADmluWAS9 span{fill:#333;color:#333;}#mermaid-svg-FT1YWeQADmluWAS9 .node rect,#mermaid-svg-FT1YWeQADmluWAS9 .node circle,#mermaid-svg-FT1YWeQADmluWAS9 .node ellipse,#mermaid-svg-FT1YWeQADmluWAS9 .node polygon,#mermaid-svg-FT1YWeQADmluWAS9 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-FT1YWeQADmluWAS9 .rough-node .label text,#mermaid-svg-FT1YWeQADmluWAS9 .node .label text,#mermaid-svg-FT1YWeQADmluWAS9 .image-shape .label,#mermaid-svg-FT1YWeQADmluWAS9 .icon-shape .label{text-anchor:middle;}#mermaid-svg-FT1YWeQADmluWAS9 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-FT1YWeQADmluWAS9 .rough-node .label,#mermaid-svg-FT1YWeQADmluWAS9 .node .label,#mermaid-svg-FT1YWeQADmluWAS9 .image-shape .label,#mermaid-svg-FT1YWeQADmluWAS9 .icon-shape .label{text-align:center;}#mermaid-svg-FT1YWeQADmluWAS9 .node.clickable{cursor:pointer;}#mermaid-svg-FT1YWeQADmluWAS9 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-FT1YWeQADmluWAS9 .arrowheadPath{fill:#333333;}#mermaid-svg-FT1YWeQADmluWAS9 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-FT1YWeQADmluWAS9 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-FT1YWeQADmluWAS9 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-FT1YWeQADmluWAS9 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-FT1YWeQADmluWAS9 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-FT1YWeQADmluWAS9 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-FT1YWeQADmluWAS9 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-FT1YWeQADmluWAS9 .cluster text{fill:#333;}#mermaid-svg-FT1YWeQADmluWAS9 .cluster span{color:#333;}#mermaid-svg-FT1YWeQADmluWAS9 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-FT1YWeQADmluWAS9 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-FT1YWeQADmluWAS9 rect.text{fill:none;stroke-width:0;}#mermaid-svg-FT1YWeQADmluWAS9 .icon-shape,#mermaid-svg-FT1YWeQADmluWAS9 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-FT1YWeQADmluWAS9 .icon-shape p,#mermaid-svg-FT1YWeQADmluWAS9 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-FT1YWeQADmluWAS9 .icon-shape rect,#mermaid-svg-FT1YWeQADmluWAS9 .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-FT1YWeQADmluWAS9 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-FT1YWeQADmluWAS9 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-FT1YWeQADmluWAS9 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

可串行化

获取读锁

尝试获取写锁

锁被占用

释放锁

事务A

数据行

事务B

事务B等待

事务A提交

事务B获得锁

4. MySQL特殊之处：可重复读如何解决幻读？

需要注意的是，MySQL的可重复读级别通过间隙锁（Gap Lock）解决了幻读问题，这是与其他数据库（如Oracle）不同的地方。

— MySQL可重复读级别下的幻读预防
— 事务A
BEGIN;
SELECT * FROM account WHERE balance > 500 FOR UPDATE;
— 不仅锁住现有行，还锁住满足条件的间隙

— 事务B（被阻塞！）
INSERT INTO account (id, balance) VALUES (100, 600);
— 无法插入，因为间隙被锁

— 事务A提交后，事务B才能执行
COMMIT;

间隙锁机制：

渲染错误: Mermaid 渲染失败: Parse error on line 7: …] –>|锁住| F[(300,800)之间的间隙] E — ———————–^ Expecting 'CYLINDEREND', 'TAGEND', 'UNICODE_TEXT', 'TEXT', 'TAGSTART', got 'PE'

5. 实战：隔离级别选择指南

5.1 不同场景的选择建议

@Service
public class TransactionService {

/**
* 场景1：报表统计
* 特性：大量查询，允许脏读，追求性能
*/
@Transactional(isolation = Isolation.READ_UNCOMMITTED)
public List<Report> generateReport() {
// 快速生成统计报表
// 即使数据稍有偏差也可以接受
}

/**
* 场景2：订单查询（MySQL默认）
* 特性：需要一致性视图，防止金额变化
*/
@Transactional(isolation = Isolation.REPEATABLE_READ)
public Order getOrderDetail(Long orderId) {
Order order = orderMapper.selectById(orderId);
// 多次查询状态应该一致
List<OrderItem> items = orderItemMapper.selectByOrderId(orderId);
return order;
}

/**
* 场景3：库存扣减
* 特性：需要最高隔离级别防止超卖
*/
@Transactional(isolation = Isolation.SERIALIZABLE)
public boolean deductStock(Long productId, Integer count) {
// 串行化执行，完全避免并发问题
// 但性能较差，适合低并发场景
return stockMapper.deduct(productId, count) > 0;
}
}

5.2 性能对比测试

在1000万数据量下，不同隔离级别的性能表现：

6. 隔离级别的设置方法

6.1 全局设置

— 查看当前隔离级别
SELECT @@global.transaction_isolation;
SELECT @@session.transaction_isolation;

— 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

— 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

6.2 配置文件设置

# my.cnf 或 my.ini
[mysqld]
# 默认隔离级别
transaction-isolation = REPEATABLE-READ
# 或设置为 READ-COMMITTED
# transaction-isolation = READ-COMMITTED

6.3 Spring Boot配置

spring:
datasource:
# 设置连接池的默认隔离级别
hikari:
transaction-isolation: TRANSACTION_REPEATABLE_READ

7. 常见问题解答

Q1：为什么MySQL默认用可重复读，而Oracle用读已提交？

答：历史原因。MySQL主从复制早期基于语句复制，可重复读能保证复制的一致性。另外，MySQL通过间隙锁在可重复读级别解决了幻读，使得这个级别足够安全。

Q2：可重复读能完全避免幻读吗？

答：在MySQL中，通过间隙锁机制，可重复读级别可以避免幻读。但在快照读（普通SELECT）下，不会出现幻读；在当前读（SELECT FOR UPDATE）下，通过间隙锁防止幻读。

Q3：如何选择隔离级别？

答：遵循"够用就好"原则：

• 金融交易：可串行化
• 电商核心：可重复读
• 报表分析：读已提交
• 日志收集：读未提交

总结

记住：隔离级别越高，数据越安全，但性能越差。在实际开发中，应根据业务场景选择最适合的隔离级别。