学院网站的系统建设方式,重庆百度总代理,一个服务器做一样的网站,搭建棋牌工具#x1f50d; 事务隔离级别与 MVCC 机制深度剖析
#x1f9e0; 前言
在高并发场景下#xff0c;数据库事务是保证数据一致性的基石。但在 MySQL InnoDB 中#xff0c;事务的隔离级别、锁策略、MVCC#xff08;多版本并发控制#xff09;之间的配合#xff0c;常常是面… 事务隔离级别与 MVCC 机制深度剖析 前言
在高并发场景下数据库事务是保证数据一致性的基石。但在 MySQL InnoDB 中事务的隔离级别、锁策略、MVCC多版本并发控制之间的配合常常是面试与生产调优的重点。
本文目标 深入理解 事务隔离级别 与 MVCC 工作原理 通过 SQL 实验 验证脏读、不可重复读、幻读 结合 InnoDB 源码机制 解释 MVCC 如何实现高并发读 提供 调优与排查建议 文章目录 事务隔离级别与 MVCC 机制深度剖析 前言一、事务隔离数据库的基石 事务核心特性ACID⚠️ 隔离性的挑战二、隔离级别与异常现象 四大隔离级别对比 异常现象精确定义三、SQL复现异常现象实验⚙️ 实验环境设置 实验1脏读复现 实验2不可重复读复现 实验3幻读复现四、MVCC原理剖析 MVCC核心组件⚙️ 版本链结构 Read View可见性规则⏱️ MVCC时序示例五、InnoDB MVCC实现细节 InnoDB MVCC架构⚙️ 避免幻读的魔法Next-Key Lock 幻读防护示例六、undo与redo日志机制 日志系统架构⚙️ redo log写入流程 undo log生命周期七、隔离级别实现差异 RC与RR的可见性差异⚠️ Gap Lock触发场景八、实战调优指南 隔离级别选型建议⚡️ 高并发优化策略九、排查与诊断 事务问题排查清单⚠️ 关键日志解读INNODB STATUS十、总结 核心知识图谱 事务优化黄金法则一、事务隔离数据库的基石 事务核心特性ACID
#mermaid-svg-AlPK5lCuKcOWNEqZ {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-AlPK5lCuKcOWNEqZ .error-icon{fill:#552222;}#mermaid-svg-AlPK5lCuKcOWNEqZ .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-AlPK5lCuKcOWNEqZ .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-AlPK5lCuKcOWNEqZ .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-AlPK5lCuKcOWNEqZ .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-AlPK5lCuKcOWNEqZ .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-AlPK5lCuKcOWNEqZ .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-AlPK5lCuKcOWNEqZ .marker{fill:#333333;stroke:#333333;}#mermaid-svg-AlPK5lCuKcOWNEqZ .marker.cross{stroke:#333333;}#mermaid-svg-AlPK5lCuKcOWNEqZ svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-AlPK5lCuKcOWNEqZ .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-AlPK5lCuKcOWNEqZ .cluster-label text{fill:#333;}#mermaid-svg-AlPK5lCuKcOWNEqZ .cluster-label span{color:#333;}#mermaid-svg-AlPK5lCuKcOWNEqZ .label text,#mermaid-svg-AlPK5lCuKcOWNEqZ span{fill:#333;color:#333;}#mermaid-svg-AlPK5lCuKcOWNEqZ .node rect,#mermaid-svg-AlPK5lCuKcOWNEqZ .node circle,#mermaid-svg-AlPK5lCuKcOWNEqZ .node ellipse,#mermaid-svg-AlPK5lCuKcOWNEqZ .node polygon,#mermaid-svg-AlPK5lCuKcOWNEqZ .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-AlPK5lCuKcOWNEqZ .node .label{text-align:center;}#mermaid-svg-AlPK5lCuKcOWNEqZ .node.clickable{cursor:pointer;}#mermaid-svg-AlPK5lCuKcOWNEqZ .arrowheadPath{fill:#333333;}#mermaid-svg-AlPK5lCuKcOWNEqZ .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-AlPK5lCuKcOWNEqZ .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-AlPK5lCuKcOWNEqZ .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-AlPK5lCuKcOWNEqZ .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-AlPK5lCuKcOWNEqZ .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-AlPK5lCuKcOWNEqZ .cluster text{fill:#333;}#mermaid-svg-AlPK5lCuKcOWNEqZ .cluster span{color:#333;}#mermaid-svg-AlPK5lCuKcOWNEqZ 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-AlPK5lCuKcOWNEqZ :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}ACID原子性一致性隔离性持久性全部成功或全部失败数据完整性约束并发事务互不干扰提交后永不丢失⚠️ 隔离性的挑战
挑战描述解决方案脏读读到未提交数据隔离级别控制不可重复读同事务内读取结果不同MVCC/锁幻读同查询返回不同行数Gap Lock更新丢失覆盖他人提交乐观锁/悲观锁
二、隔离级别与异常现象 四大隔离级别对比
隔离级别脏读不可重复读幻读实现机制READ UNCOMMITTED✓✓✓无锁READ COMMITTED✗✓✓MVCC/锁REPEATABLE READ✗✗✓*MVCCNext-Key LockSERIALIZABLE✗✗✗全表锁异常现象精确定义
脏读Dirty Read 事务A读取到事务B未提交的修改 不可重复读Non-repeatableRead 事务A内两次读取同一数据结果不同被其他事务修改幻读PhantomRead 事务A内两次相同查询返回不同行数被其他事务增删
三、SQL复现异常现象实验
⚙️ 实验环境设置
-- 创建测试表
CREATE TABLE account (id INT PRIMARY KEY,name VARCHAR(20),balance DECIMAL(10, 2)
);INSERT INTO account VALUES (1, Alice, 1000), (2, Bob, 2000);-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;实验1脏读复现
-- 事务A未提交修改
START TRANSACTION;
UPDATE account SET balance balance 100 WHERE id 1;-- 事务B读取未提交数据
START TRANSACTION;
SELECT balance FROM account WHERE id 1; -- 读到1100未提交-- 事务A回滚
ROLLBACK;-- 事务B读取到不存在的数据实验2不可重复读复现
-- 设置隔离级别为READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;-- 事务A
START TRANSACTION;
SELECT balance FROM account WHERE id 1; -- 返回1000-- 事务B修改并提交
START TRANSACTION;
UPDATE account SET balance 1500 WHERE id 1;
COMMIT;-- 事务A再次读取
SELECT balance FROM account WHERE id 1; -- 返回1500结果改变实验3幻读复现
-- 设置隔离级别为REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 事务A
START TRANSACTION;
SELECT * FROM account WHERE balance 1000; -- 返回Bob(2000)-- 事务B插入新记录并提交
START TRANSACTION;
INSERT INTO account VALUES (3, Charlie, 3000);
COMMIT;-- 事务A再次查询
SELECT * FROM account WHERE balance 1000; -- 仍只返回Bob无幻读-- 但更新时会发现新行MySQL特有行为
UPDATE account SET name CONCAT(name, *)
WHERE balance 1000; -- 影响3行包括Charlie四、MVCC原理剖析 MVCC核心组件
#mermaid-svg-jNK5nihxMODuOqJN {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-jNK5nihxMODuOqJN .error-icon{fill:#552222;}#mermaid-svg-jNK5nihxMODuOqJN .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-jNK5nihxMODuOqJN .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-jNK5nihxMODuOqJN .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-jNK5nihxMODuOqJN .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-jNK5nihxMODuOqJN .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-jNK5nihxMODuOqJN .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-jNK5nihxMODuOqJN .marker{fill:#333333;stroke:#333333;}#mermaid-svg-jNK5nihxMODuOqJN .marker.cross{stroke:#333333;}#mermaid-svg-jNK5nihxMODuOqJN svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-jNK5nihxMODuOqJN .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-jNK5nihxMODuOqJN .cluster-label text{fill:#333;}#mermaid-svg-jNK5nihxMODuOqJN .cluster-label span{color:#333;}#mermaid-svg-jNK5nihxMODuOqJN .label text,#mermaid-svg-jNK5nihxMODuOqJN span{fill:#333;color:#333;}#mermaid-svg-jNK5nihxMODuOqJN .node rect,#mermaid-svg-jNK5nihxMODuOqJN .node circle,#mermaid-svg-jNK5nihxMODuOqJN .node ellipse,#mermaid-svg-jNK5nihxMODuOqJN .node polygon,#mermaid-svg-jNK5nihxMODuOqJN .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-jNK5nihxMODuOqJN .node .label{text-align:center;}#mermaid-svg-jNK5nihxMODuOqJN .node.clickable{cursor:pointer;}#mermaid-svg-jNK5nihxMODuOqJN .arrowheadPath{fill:#333333;}#mermaid-svg-jNK5nihxMODuOqJN .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-jNK5nihxMODuOqJN .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-jNK5nihxMODuOqJN .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-jNK5nihxMODuOqJN .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-jNK5nihxMODuOqJN .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-jNK5nihxMODuOqJN .cluster text{fill:#333;}#mermaid-svg-jNK5nihxMODuOqJN .cluster span{color:#333;}#mermaid-svg-jNK5nihxMODuOqJN 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-jNK5nihxMODuOqJN :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}MVCC事务ID版本链Read Viewtrx_idundo log构建可见性判断⚙️ 版本链结构
#mermaid-svg-sS6a1BmL8LRmRwGE {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-sS6a1BmL8LRmRwGE .error-icon{fill:#552222;}#mermaid-svg-sS6a1BmL8LRmRwGE .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-sS6a1BmL8LRmRwGE .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-sS6a1BmL8LRmRwGE .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-sS6a1BmL8LRmRwGE .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-sS6a1BmL8LRmRwGE .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-sS6a1BmL8LRmRwGE .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-sS6a1BmL8LRmRwGE .marker{fill:#333333;stroke:#333333;}#mermaid-svg-sS6a1BmL8LRmRwGE .marker.cross{stroke:#333333;}#mermaid-svg-sS6a1BmL8LRmRwGE svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-sS6a1BmL8LRmRwGE .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-sS6a1BmL8LRmRwGE .cluster-label text{fill:#333;}#mermaid-svg-sS6a1BmL8LRmRwGE .cluster-label span{color:#333;}#mermaid-svg-sS6a1BmL8LRmRwGE .label text,#mermaid-svg-sS6a1BmL8LRmRwGE span{fill:#333;color:#333;}#mermaid-svg-sS6a1BmL8LRmRwGE .node rect,#mermaid-svg-sS6a1BmL8LRmRwGE .node circle,#mermaid-svg-sS6a1BmL8LRmRwGE .node ellipse,#mermaid-svg-sS6a1BmL8LRmRwGE .node polygon,#mermaid-svg-sS6a1BmL8LRmRwGE .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-sS6a1BmL8LRmRwGE .node .label{text-align:center;}#mermaid-svg-sS6a1BmL8LRmRwGE .node.clickable{cursor:pointer;}#mermaid-svg-sS6a1BmL8LRmRwGE .arrowheadPath{fill:#333333;}#mermaid-svg-sS6a1BmL8LRmRwGE .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-sS6a1BmL8LRmRwGE .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-sS6a1BmL8LRmRwGE .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-sS6a1BmL8LRmRwGE .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-sS6a1BmL8LRmRwGE .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-sS6a1BmL8LRmRwGE .cluster text{fill:#333;}#mermaid-svg-sS6a1BmL8LRmRwGE .cluster span{color:#333;}#mermaid-svg-sS6a1BmL8LRmRwGE 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-sS6a1BmL8LRmRwGE :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}当前版本旧版本1旧版本2旧版本3Read View可见性规则
boolean isVisible(TransactionRecord record) {if (record.trx_id min_trx_id) return true; // 已提交if (record.trx_id max_trx_id) return false; // 未开始if (trx_ids.contains(record.trx_id)) return false; // 未提交return true; // 已提交
}
⏱️ MVCC时序示例
#mermaid-svg-Xmr5R7DB1cdlo5hj {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-Xmr5R7DB1cdlo5hj .error-icon{fill:#552222;}#mermaid-svg-Xmr5R7DB1cdlo5hj .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-Xmr5R7DB1cdlo5hj .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-Xmr5R7DB1cdlo5hj .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-Xmr5R7DB1cdlo5hj .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-Xmr5R7DB1cdlo5hj .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-Xmr5R7DB1cdlo5hj .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-Xmr5R7DB1cdlo5hj .marker{fill:#333333;stroke:#333333;}#mermaid-svg-Xmr5R7DB1cdlo5hj .marker.cross{stroke:#333333;}#mermaid-svg-Xmr5R7DB1cdlo5hj svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-Xmr5R7DB1cdlo5hj .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-Xmr5R7DB1cdlo5hj text.actortspan{fill:black;stroke:none;}#mermaid-svg-Xmr5R7DB1cdlo5hj .actor-line{stroke:grey;}#mermaid-svg-Xmr5R7DB1cdlo5hj .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-Xmr5R7DB1cdlo5hj .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-Xmr5R7DB1cdlo5hj #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-Xmr5R7DB1cdlo5hj .sequenceNumber{fill:white;}#mermaid-svg-Xmr5R7DB1cdlo5hj #sequencenumber{fill:#333;}#mermaid-svg-Xmr5R7DB1cdlo5hj #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-Xmr5R7DB1cdlo5hj .messageText{fill:#333;stroke:#333;}#mermaid-svg-Xmr5R7DB1cdlo5hj .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-Xmr5R7DB1cdlo5hj .labelText,#mermaid-svg-Xmr5R7DB1cdlo5hj .labelTexttspan{fill:black;stroke:none;}#mermaid-svg-Xmr5R7DB1cdlo5hj .loopText,#mermaid-svg-Xmr5R7DB1cdlo5hj .loopTexttspan{fill:black;stroke:none;}#mermaid-svg-Xmr5R7DB1cdlo5hj .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-Xmr5R7DB1cdlo5hj .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-Xmr5R7DB1cdlo5hj .noteText,#mermaid-svg-Xmr5R7DB1cdlo5hj .noteTexttspan{fill:black;stroke:none;}#mermaid-svg-Xmr5R7DB1cdlo5hj .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-Xmr5R7DB1cdlo5hj .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-Xmr5R7DB1cdlo5hj .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-Xmr5R7DB1cdlo5hj .actorPopupMenu{position:absolute;}#mermaid-svg-Xmr5R7DB1cdlo5hj .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-Xmr5R7DB1cdlo5hj .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-Xmr5R7DB1cdlo5hj .actor-man circle,#mermaid-svg-Xmr5R7DB1cdlo5hj line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-Xmr5R7DB1cdlo5hj :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}事务100事务101数据库START (trx_id100)UPDATE row SET value200START (trx_id101)SELECT value - 创建ReadView[100]读取旧版本value100COMMITSELECT value - 仍读旧版本100事务100事务101数据库五、InnoDB MVCC实现细节 InnoDB MVCC架构
#mermaid-svg-IIR89VeTIRhITxH6 {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-IIR89VeTIRhITxH6 .error-icon{fill:#552222;}#mermaid-svg-IIR89VeTIRhITxH6 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-IIR89VeTIRhITxH6 .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-IIR89VeTIRhITxH6 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-IIR89VeTIRhITxH6 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-IIR89VeTIRhITxH6 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-IIR89VeTIRhITxH6 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-IIR89VeTIRhITxH6 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-IIR89VeTIRhITxH6 .marker.cross{stroke:#333333;}#mermaid-svg-IIR89VeTIRhITxH6 svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-IIR89VeTIRhITxH6 .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-IIR89VeTIRhITxH6 .cluster-label text{fill:#333;}#mermaid-svg-IIR89VeTIRhITxH6 .cluster-label span{color:#333;}#mermaid-svg-IIR89VeTIRhITxH6 .label text,#mermaid-svg-IIR89VeTIRhITxH6 span{fill:#333;color:#333;}#mermaid-svg-IIR89VeTIRhITxH6 .node rect,#mermaid-svg-IIR89VeTIRhITxH6 .node circle,#mermaid-svg-IIR89VeTIRhITxH6 .node ellipse,#mermaid-svg-IIR89VeTIRhITxH6 .node polygon,#mermaid-svg-IIR89VeTIRhITxH6 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-IIR89VeTIRhITxH6 .node .label{text-align:center;}#mermaid-svg-IIR89VeTIRhITxH6 .node.clickable{cursor:pointer;}#mermaid-svg-IIR89VeTIRhITxH6 .arrowheadPath{fill:#333333;}#mermaid-svg-IIR89VeTIRhITxH6 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-IIR89VeTIRhITxH6 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-IIR89VeTIRhITxH6 .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-IIR89VeTIRhITxH6 .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-IIR89VeTIRhITxH6 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-IIR89VeTIRhITxH6 .cluster text{fill:#333;}#mermaid-svg-IIR89VeTIRhITxH6 .cluster span{color:#333;}#mermaid-svg-IIR89VeTIRhITxH6 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-IIR89VeTIRhITxH6 :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}InnoDB聚簇索引Undo LogRead View存储当前数据存储历史版本判断可见性⚙️ 避免幻读的魔法Next-Key Lock
#mermaid-svg-3INeP8fdrsZDfL18 {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-3INeP8fdrsZDfL18 .error-icon{fill:#552222;}#mermaid-svg-3INeP8fdrsZDfL18 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-3INeP8fdrsZDfL18 .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-3INeP8fdrsZDfL18 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-3INeP8fdrsZDfL18 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-3INeP8fdrsZDfL18 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-3INeP8fdrsZDfL18 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-3INeP8fdrsZDfL18 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-3INeP8fdrsZDfL18 .marker.cross{stroke:#333333;}#mermaid-svg-3INeP8fdrsZDfL18 svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-3INeP8fdrsZDfL18 .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-3INeP8fdrsZDfL18 .cluster-label text{fill:#333;}#mermaid-svg-3INeP8fdrsZDfL18 .cluster-label span{color:#333;}#mermaid-svg-3INeP8fdrsZDfL18 .label text,#mermaid-svg-3INeP8fdrsZDfL18 span{fill:#333;color:#333;}#mermaid-svg-3INeP8fdrsZDfL18 .node rect,#mermaid-svg-3INeP8fdrsZDfL18 .node circle,#mermaid-svg-3INeP8fdrsZDfL18 .node ellipse,#mermaid-svg-3INeP8fdrsZDfL18 .node polygon,#mermaid-svg-3INeP8fdrsZDfL18 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-3INeP8fdrsZDfL18 .node .label{text-align:center;}#mermaid-svg-3INeP8fdrsZDfL18 .node.clickable{cursor:pointer;}#mermaid-svg-3INeP8fdrsZDfL18 .arrowheadPath{fill:#333333;}#mermaid-svg-3INeP8fdrsZDfL18 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-3INeP8fdrsZDfL18 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-3INeP8fdrsZDfL18 .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-3INeP8fdrsZDfL18 .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-3INeP8fdrsZDfL18 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-3INeP8fdrsZDfL18 .cluster text{fill:#333;}#mermaid-svg-3INeP8fdrsZDfL18 .cluster span{color:#333;}#mermaid-svg-3INeP8fdrsZDfL18 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-3INeP8fdrsZDfL18 :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}记录锁锁定现有行间隙锁锁定行间空隙Next-Key Lock记录锁间隙锁幻读防护示例
-- 事务AREPEATABLE READ
START TRANSACTION;
SELECT * FROM account WHERE balance 1000 FOR UPDATE; -- 加Next-Key Lock-- 事务B尝试插入
INSERT INTO account VALUES (3, Charlie, 3000); -- 阻塞等待锁六、undo与redo日志机制 日志系统架构
#mermaid-svg-Mo1EE4OSje8RkjWu {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-Mo1EE4OSje8RkjWu .error-icon{fill:#552222;}#mermaid-svg-Mo1EE4OSje8RkjWu .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-Mo1EE4OSje8RkjWu .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-Mo1EE4OSje8RkjWu .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-Mo1EE4OSje8RkjWu .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-Mo1EE4OSje8RkjWu .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-Mo1EE4OSje8RkjWu .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-Mo1EE4OSje8RkjWu .marker{fill:#333333;stroke:#333333;}#mermaid-svg-Mo1EE4OSje8RkjWu .marker.cross{stroke:#333333;}#mermaid-svg-Mo1EE4OSje8RkjWu svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-Mo1EE4OSje8RkjWu .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-Mo1EE4OSje8RkjWu .cluster-label text{fill:#333;}#mermaid-svg-Mo1EE4OSje8RkjWu .cluster-label span{color:#333;}#mermaid-svg-Mo1EE4OSje8RkjWu .label text,#mermaid-svg-Mo1EE4OSje8RkjWu span{fill:#333;color:#333;}#mermaid-svg-Mo1EE4OSje8RkjWu .node rect,#mermaid-svg-Mo1EE4OSje8RkjWu .node circle,#mermaid-svg-Mo1EE4OSje8RkjWu .node ellipse,#mermaid-svg-Mo1EE4OSje8RkjWu .node polygon,#mermaid-svg-Mo1EE4OSje8RkjWu .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-Mo1EE4OSje8RkjWu .node .label{text-align:center;}#mermaid-svg-Mo1EE4OSje8RkjWu .node.clickable{cursor:pointer;}#mermaid-svg-Mo1EE4OSje8RkjWu .arrowheadPath{fill:#333333;}#mermaid-svg-Mo1EE4OSje8RkjWu .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-Mo1EE4OSje8RkjWu .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-Mo1EE4OSje8RkjWu .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-Mo1EE4OSje8RkjWu .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-Mo1EE4OSje8RkjWu .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-Mo1EE4OSje8RkjWu .cluster text{fill:#333;}#mermaid-svg-Mo1EE4OSje8RkjWu .cluster span{color:#333;}#mermaid-svg-Mo1EE4OSje8RkjWu 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-Mo1EE4OSje8RkjWu :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}事务修改数据页写undo log写redo log回滚/MVCC崩溃恢复⚙️ redo log写入流程
#mermaid-svg-54wITkepSuVBnMUd {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-54wITkepSuVBnMUd .error-icon{fill:#552222;}#mermaid-svg-54wITkepSuVBnMUd .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-54wITkepSuVBnMUd .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-54wITkepSuVBnMUd .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-54wITkepSuVBnMUd .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-54wITkepSuVBnMUd .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-54wITkepSuVBnMUd .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-54wITkepSuVBnMUd .marker{fill:#333333;stroke:#333333;}#mermaid-svg-54wITkepSuVBnMUd .marker.cross{stroke:#333333;}#mermaid-svg-54wITkepSuVBnMUd svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-54wITkepSuVBnMUd .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-54wITkepSuVBnMUd text.actortspan{fill:black;stroke:none;}#mermaid-svg-54wITkepSuVBnMUd .actor-line{stroke:grey;}#mermaid-svg-54wITkepSuVBnMUd .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-54wITkepSuVBnMUd .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-54wITkepSuVBnMUd #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-54wITkepSuVBnMUd .sequenceNumber{fill:white;}#mermaid-svg-54wITkepSuVBnMUd #sequencenumber{fill:#333;}#mermaid-svg-54wITkepSuVBnMUd #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-54wITkepSuVBnMUd .messageText{fill:#333;stroke:#333;}#mermaid-svg-54wITkepSuVBnMUd .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-54wITkepSuVBnMUd .labelText,#mermaid-svg-54wITkepSuVBnMUd .labelTexttspan{fill:black;stroke:none;}#mermaid-svg-54wITkepSuVBnMUd .loopText,#mermaid-svg-54wITkepSuVBnMUd .loopTexttspan{fill:black;stroke:none;}#mermaid-svg-54wITkepSuVBnMUd .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-54wITkepSuVBnMUd .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-54wITkepSuVBnMUd .noteText,#mermaid-svg-54wITkepSuVBnMUd .noteTexttspan{fill:black;stroke:none;}#mermaid-svg-54wITkepSuVBnMUd .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-54wITkepSuVBnMUd .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-54wITkepSuVBnMUd .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-54wITkepSuVBnMUd .actorPopupMenu{position:absolute;}#mermaid-svg-54wITkepSuVBnMUd .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-54wITkepSuVBnMUd .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-54wITkepSuVBnMUd .actor-man circle,#mermaid-svg-54wITkepSuVBnMUd line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-54wITkepSuVBnMUd :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}事务Log BufferOS CacheDisk1. 写入redo记录2. 刷到OS缓存3. 刷盘持久化4. 确认提交事务Log BufferOS CacheDiskundo log生命周期
#mermaid-svg-VJoKfqNjIDrYgdJe {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-VJoKfqNjIDrYgdJe .error-icon{fill:#552222;}#mermaid-svg-VJoKfqNjIDrYgdJe .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-VJoKfqNjIDrYgdJe .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-VJoKfqNjIDrYgdJe .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-VJoKfqNjIDrYgdJe .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-VJoKfqNjIDrYgdJe .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-VJoKfqNjIDrYgdJe .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-VJoKfqNjIDrYgdJe .marker{fill:#333333;stroke:#333333;}#mermaid-svg-VJoKfqNjIDrYgdJe .marker.cross{stroke:#333333;}#mermaid-svg-VJoKfqNjIDrYgdJe svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-VJoKfqNjIDrYgdJe .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-VJoKfqNjIDrYgdJe .cluster-label text{fill:#333;}#mermaid-svg-VJoKfqNjIDrYgdJe .cluster-label span{color:#333;}#mermaid-svg-VJoKfqNjIDrYgdJe .label text,#mermaid-svg-VJoKfqNjIDrYgdJe span{fill:#333;color:#333;}#mermaid-svg-VJoKfqNjIDrYgdJe .node rect,#mermaid-svg-VJoKfqNjIDrYgdJe .node circle,#mermaid-svg-VJoKfqNjIDrYgdJe .node ellipse,#mermaid-svg-VJoKfqNjIDrYgdJe .node polygon,#mermaid-svg-VJoKfqNjIDrYgdJe .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-VJoKfqNjIDrYgdJe .node .label{text-align:center;}#mermaid-svg-VJoKfqNjIDrYgdJe .node.clickable{cursor:pointer;}#mermaid-svg-VJoKfqNjIDrYgdJe .arrowheadPath{fill:#333333;}#mermaid-svg-VJoKfqNjIDrYgdJe .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-VJoKfqNjIDrYgdJe .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-VJoKfqNjIDrYgdJe .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-VJoKfqNjIDrYgdJe .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-VJoKfqNjIDrYgdJe .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-VJoKfqNjIDrYgdJe .cluster text{fill:#333;}#mermaid-svg-VJoKfqNjIDrYgdJe .cluster span{color:#333;}#mermaid-svg-VJoKfqNjIDrYgdJe 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-VJoKfqNjIDrYgdJe :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}事务开始生成undo log事务提交放入undo历史链表purge线程清理七、隔离级别实现差异 RC与RR的可见性差异
特性READ COMMITTEDREPEATABLE READRead View创建每条语句创建事务首条语句创建可见性最新已提交版本事务开始时快照锁范围仅记录锁Next-Key Lock幻读防护无有
⚠️ Gap Lock触发场景
-- 以下操作会触发Gap Lock
SELECT * FROM table WHERE id 100 FOR UPDATE;
DELETE FROM table WHERE salary BETWEEN 5000 AND 10000;
UPDATE employees SET status inactive WHERE department_id 3;八、实战调优指南 隔离级别选型建议
#mermaid-svg-Rg0SizTyOeHXFNl8 {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-Rg0SizTyOeHXFNl8 .error-icon{fill:#552222;}#mermaid-svg-Rg0SizTyOeHXFNl8 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-Rg0SizTyOeHXFNl8 .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-Rg0SizTyOeHXFNl8 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-Rg0SizTyOeHXFNl8 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-Rg0SizTyOeHXFNl8 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-Rg0SizTyOeHXFNl8 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-Rg0SizTyOeHXFNl8 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-Rg0SizTyOeHXFNl8 .marker.cross{stroke:#333333;}#mermaid-svg-Rg0SizTyOeHXFNl8 svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-Rg0SizTyOeHXFNl8 .pieCircle{stroke:black;stroke-width:2px;opacity:0.7;}#mermaid-svg-Rg0SizTyOeHXFNl8 .pieTitleText{text-anchor:middle;font-size:25px;fill:black;font-family:"trebuchet ms",verdana,arial,sans-serif;}#mermaid-svg-Rg0SizTyOeHXFNl8 .slice{font-family:"trebuchet ms",verdana,arial,sans-serif;fill:#333;font-size:17px;}#mermaid-svg-Rg0SizTyOeHXFNl8 .legend text{fill:black;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:17px;}#mermaid-svg-Rg0SizTyOeHXFNl8 :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}65%30%4%1%生产环境隔离级别使用占比REPEATABLE READREAD COMMITTEDSERIALIZABLEREAD UNCOMMITTED⚡️ 高并发优化策略
短事务原则
-- 反例长事务
START TRANSACTION;
SELECT ... -- 耗时操作
UPDATE ... -- 业务逻辑
COMMIT; -- 长时间持有锁-- 正例拆分事务
UPDATE ... -- 快速操作1
UPDATE ... -- 快速操作2索引优化
全表扫描会锁全表索引减少锁范围
监控长事务
-- 查看运行中事务
SELECT * FROM information_schema.INNODB_TRX;-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;九、排查与诊断 事务问题排查清单 确认隔离级别 SELECT transaction_isolation; 检查长事务 SELECT * FROM information_schema.INNODB_TRX; 分析锁等待 SHOW ENGINE INNODB STATUS; SELECT * FROM sys.innodb_lock_waits; 监控性能指标 sql – 锁等待次数 SHOW GLOBAL STATUS LIKE Innodb_row_lock%; – 事务吞吐量 SHOW GLOBAL STATUS LIKE Com_commit; SHOW GLOBAL STATUS LIKE Com_rollback;
⚠️ 关键日志解读INNODB STATUS
---TRANSACTION 123456, ACTIVE 10 sec2 lock struct(s), heap size 1136, 1 row lock(s)MySQL thread id 789, OS thread handle 12345, query id 9876解读
- 事务123456已运行10秒
- 持有1个行锁
- 线程ID 789查询ID 9876十、总结 核心知识图谱
#mermaid-svg-X7yu8Rkzf6vMZto4 {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-X7yu8Rkzf6vMZto4 .error-icon{fill:#552222;}#mermaid-svg-X7yu8Rkzf6vMZto4 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-X7yu8Rkzf6vMZto4 .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-X7yu8Rkzf6vMZto4 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-X7yu8Rkzf6vMZto4 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-X7yu8Rkzf6vMZto4 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-X7yu8Rkzf6vMZto4 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-X7yu8Rkzf6vMZto4 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-X7yu8Rkzf6vMZto4 .marker.cross{stroke:#333333;}#mermaid-svg-X7yu8Rkzf6vMZto4 svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-X7yu8Rkzf6vMZto4 .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-X7yu8Rkzf6vMZto4 .cluster-label text{fill:#333;}#mermaid-svg-X7yu8Rkzf6vMZto4 .cluster-label span{color:#333;}#mermaid-svg-X7yu8Rkzf6vMZto4 .label text,#mermaid-svg-X7yu8Rkzf6vMZto4 span{fill:#333;color:#333;}#mermaid-svg-X7yu8Rkzf6vMZto4 .node rect,#mermaid-svg-X7yu8Rkzf6vMZto4 .node circle,#mermaid-svg-X7yu8Rkzf6vMZto4 .node ellipse,#mermaid-svg-X7yu8Rkzf6vMZto4 .node polygon,#mermaid-svg-X7yu8Rkzf6vMZto4 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-X7yu8Rkzf6vMZto4 .node .label{text-align:center;}#mermaid-svg-X7yu8Rkzf6vMZto4 .node.clickable{cursor:pointer;}#mermaid-svg-X7yu8Rkzf6vMZto4 .arrowheadPath{fill:#333333;}#mermaid-svg-X7yu8Rkzf6vMZto4 .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-X7yu8Rkzf6vMZto4 .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-X7yu8Rkzf6vMZto4 .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-X7yu8Rkzf6vMZto4 .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-X7yu8Rkzf6vMZto4 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-X7yu8Rkzf6vMZto4 .cluster text{fill:#333;}#mermaid-svg-X7yu8Rkzf6vMZto4 .cluster span{color:#333;}#mermaid-svg-X7yu8Rkzf6vMZto4 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-X7yu8Rkzf6vMZto4 :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}事务隔离隔离级别MVCC机制锁机制RC/RR/SR版本链/Read View记录锁/Gap锁异常控制无锁读并发控制事务优化黄金法则
1.短事务优先事务执行时间控制在100ms内 2.合理索引减少锁范围避免全表扫描 3.监控预警设置长事务阈值1s告警 4.避免热点热点数据采用队列串行化 5.版本控制高并发更新使用乐观锁
