MySQL事务执行链的庖丁解牛

MySQL 事务执行链不是“BEGIN → SQL → COMMIT”，而是一条跨越连接层、SQL 层、存储引擎层、日志系统、锁管理器的精密协作路径。

一、事务执行的 5 个阶段（以START TRANSACTION到COMMIT为例）

1. 事务启动 → 2. 语句执行 → 3. 写日志 → 4. 提交 → 5. 清理

阶段 1：事务启动（START TRANSACTION/ 第一条 SQL）

• 分配事务 ID（trx_id）：从全局递增的trx_sys->max_trx_id获取；
• 分配回滚段（Rollback Segment）：用于存储 undo 日志；
• 事务对象入活跃列表：trx_sys->trx_list，供 MVCC 和锁管理使用。

📌 此时无任何磁盘 I/O，纯内存操作。

阶段 2：DML 语句执行（如UPDATE users SET name='A' WHERE id=1）

（1）SQL 层解析与优化

• 语法分析 → 生成 AST；
• 优化器选择执行计划（如走主键索引）；
• 生成执行器迭代器（handler接口调用）。

（2）存储引擎层（InnoDB）操作

• 加锁：
  • 主键id=1→ 加X 锁（排他锁）；
  • 若无索引全表扫描 → 加大量行锁（危险！）。
• 查数据：
  • 先查 Buffer Pool（内存）；
  • 未命中 → 从磁盘加载页到 Buffer Pool。
• 写 undo log（内存）：
  • 记录修改前的值（name='Old'）；
  • 用于回滚和 MVCC（旧版本可见性）。
• 修改数据页：
  • 在 Buffer Pool 中修改name='A'；
  • 标记页为“脏页”（dirty page），但不刷盘。

🔑关键：此时修改仅在内存，事务未提交，其他事务不可见（通过 MVCC 隔离）。

阶段 3：日志写入（WAL 机制）

InnoDB 遵循Write-Ahead Logging（WAL）：
日志必须先于数据持久化。

• redo log 写入（关键！）：
  • 将“将页 X 的 offset Y 改为 Z”记录到redo log buffer（内存）；
  • 根据innodb_flush_log_at_trx_commit决定刷盘策略：
    • =1（默认）：每次事务提交，调用fsync()强刷 redo log 到磁盘；
    • =2：写 OS Cache，1 秒刷盘（宕机可能丢 1 秒数据）；
    • =0：每秒刷盘（高性能，高风险）。
• undo log 暂不刷盘：随脏页后台刷盘。

💡性能核心：

• innodb_flush_log_at_trx_commit=1保证 ACID，但每次提交触发磁盘 I/O（1 次fsync≈ 1–10ms）；
• 高并发下，redo log 刷盘是主要瓶颈。

阶段 4：事务提交（COMMIT）

（1）原子提交协议（两阶段提交，2PC）

• Prepare 阶段：
  • 将事务状态设为PREPARED；
  • 强制刷 redo log 到磁盘（fsync）；
  • 此时崩溃，重启后可回滚或提交（见恢复机制）。
• Commit 阶段：
  • 写binlog（若开启，用于主从复制）；
  • 将事务状态设为COMMITTED；
  • 释放所有行锁；
  • 将事务从活跃列表移除。

⚠️崩溃安全：

• 若在 Prepare 后、Commit 前崩溃 → 重启时通过 redo log + binlog自动提交；
• 若在 Prepare 前崩溃 → 重启时回滚。

（2）提交后行为

• 脏页不立即刷盘：由后台线程（Page Cleaner）异步刷；
• undo log 标记为可 purge：由 purge 线程后台清理。

阶段 5：事务清理

• 锁释放：所有行锁、表锁释放；
• MVCC 可见性更新：新事务可看到此修改；
• undo log 回收：当无活跃事务需此版本，purge 线程删除。

二、关键组件交互图

+-----------------+ +---------------------+ +------------------+ | SQL Layer | | InnoDB Storage | | Disk Subsystem | | (Parser, Optimizer)| | (Lock, Buffer Pool, | | (Redo Log, Data)| +-----------------+ | Undo/Redo, MVCC) | +------------------+ | +----------+----------+ ^ | | | | 1. 执行计划 | 2. 加锁、查数据 | |----------------------->| | | | 3. 写 undo log (内存) | | | 4. 修改脏页 (内存) | | | 5. 写 redo log buffer | | |------------------------>| | | 6. COMMIT: fsync redo | |<-----------------------| 7. 释放锁、移出活跃列表 | +-----------------+ +---------------------+ +------------------+

三、可验证实验

1.观察锁行为

-- 会话1STARTTRANSACTION;UPDATEusersSETname='A'WHEREid=1;-- 未 commit-- 会话2UPDATEusersSETname='B'WHEREid=1;-- 阻塞，等待 X 锁释放

2.验证 redo log 刷盘

# my.cnf innodb_flush_log_at_trx_commit = 1 # 安全 # innodb_flush_log_at_trx_commit = 2 # 性能

• 用iostat -x 1监控磁盘await，trx_commit=1时每次提交有 I/O spike。

3.崩溃恢复测试

• 启动事务 → 执行 UPDATE → kill -9 mysqld；
• 重启后，数据要么全回滚，要么全提交（无中间状态）。

四、性能与调优杠杆点

五、常见误区

• ❌ “COMMIT 后数据立即写磁盘” → 实际脏页异步刷盘，但 redo log 已持久化；
• ❌ “事务越小越好” → 过小事务导致redo log 刷盘频率过高（每条 SQL 一次fsync）；
• ✅最佳实践：
  • 事务包含完整业务单元（如“扣库存+创建订单”）；
  • 避免在事务中处理用户交互（防止长持有锁）。

总结

MySQL 事务执行链的核心是：
内存修改 + redo log 原子持久化 + 锁/MVCC 隔离。

• ACID 的 A（原子性）：靠 redo log + 2PC 保证；
• C（一致性）：靠应用逻辑 + 约束；
• I（隔离性）：靠锁 + MVCC；
• D（持久性）：靠fsyncredo log。

理解这条链，
你就能在“慢事务”“死锁”“宕机恢复”问题中，
精准定位瓶颈，而非盲目调参。