MySQL MVCC & 并发更新实验笔记

本文记录了使用 HeidiSQL 和 MySQL InnoDB 引擎进行的 MVCC 实验过程，验证版本链行为、事务隔离可见性以及连接行为。

⚠️ 一、数据库并发问题

并发读写会引发以下四类经典问题：

  1. 脏读（Dirty Read） 读取到了其他事务尚未提交的数据。

  2. 不可重复读（Non-Repeatable Read） 在同一事务中多次读取同一行数据，每次结果不同（其他事务修改了这行并提交）。

  3. 幻读（Phantom Read） 同一事务中两次执行相同条件的 SELECT，结果集行数不同（其他事务插入或删除了符合条件的行）。

🔒 二、事务隔离级别（SQL 标准）

隔离级别 脏读 不可重复读 幻读 实现方式
READ UNCOMMITTED ✅ 可能 ✅ 可能 ✅ 可能 无 MVCC
READ COMMITTED ❌ 防止 ✅ 可能 ✅ 可能 部分 MVCC
REPEATABLE READ ❌ 防止 ❌ 防止 ✅ 可能 完整 MVCC
SERIALIZABLE ❌ 防止 ❌ 防止 ❌ 防止 加锁/序列化

InnoDB 默认隔离级别是 REPEATABLE READ，但可通过 SET SESSION TRANSACTION ISOLATION LEVEL ... 修改。

🧠 三、MVCC 核心原理

1. 什么是 MVCC？

MVCC（多版本并发控制）通过为每行数据维护多个版本（undo log），允许读写操作并发进行，提升性能，避免加锁。

2. 如何实现？

每条记录有两个隐藏列：

  • trx_id：最近一次修改它的事务 ID

  • roll_pointer：指向旧版本的指针（undo log）

读取时使用 ReadView 判断哪些版本可见。

3. 快照读与当前读

  • 快照读：

定义：读取的是数据行的一个“历史版本”（通过 undo log 实现），不会加锁，基于事务启动时（可重复读）或查询时（读已提交）生成的 ReadView 来决定可见性
常见语句：SELECT * FROM table WHERE …;

  • 当前读：

定义：读取的是记录的最新版本，并会对记录加锁，以防止其他事务修改
常见语句：
SELECT * FROM table WHERE … FOR UPDATE;
SELECT * FROM table WHERE … LOCK IN SHARE MODE;
INSERT, UPDATE, DELETE

3. ReadView结构字段：

字段名 类型 说明
m_ids List of trx_id 当前系统中活跃事务 ID 列表（尚未提交）
min_trx_id trx_id m_ids 中最小事务 ID（活跃事务中最早的）
max_trx_id trx_id 当前系统中尚未分配的下一个事务 ID（即生成 ReadView 时，后续事务的起点）
creator_trx_id trx_id 当前事务的 ID（即生成这个 ReadView 的事务）

4. ReadView 的判断规则：

判断一条记录版本（版本上的 trx_id简写为 t ）是否可见，规则如下：

  • 如果 t == creator_trx_id
    → 可见（即是当前事务修改的）

  • 如果 t < min_trx_id
    → 可见（比所有活跃事务都早，说明已提交）

  • 如果 t >= max_trx_id
    → 不可见（是未来事务产生的版本）

  • 如果 t ∈ m_ids（即属于未提交事务）
    → 不可见

  • 其他情况（min_trx_id <= t < max_trx_id 且 t ∉ m_ids）
    → 可见（事务在生成 ReadView 前已提交）

📌 四、实验背景(TODO: continue from here)

在使用 InnoDB 引擎、READ COMMITTED 隔离级别下，遇到如下疑问：

  • 为什么 UPDATE 未提交后，SELECT 已能看到更新？

  • 最终版本链的链头是谁决定的？最后 commit 的事务，还是最后 update 的事务？

  • 多个窗口下是否是真正的不同事务？

🧪 五、实验步骤（版本链验证）

1. 创建表与初始数据

1
2
3
4
5
6
7
DROP TABLE IF EXISTS mvcc_test;  
CREATE TABLE mvcc_test (  
  id INT PRIMARY KEY,  
  val VARCHAR(100)  
) ENGINE=InnoDB;  
​  
INSERT INTO mvcc_test VALUES (1, 'V0');

2. 模拟两个并发事务：T1、T2

✅ T1 事务（先更新，后提交）

SET autocommit = 0;
START TRANSACTION;
UPDATE mvcc_test SET val = ‘V1’ WHERE id = 1;
– 不提交，等待 T2 先提交

✅ T2 事务（后更新，先提交）

SET autocommit = 0;
START TRANSACTION;
UPDATE mvcc_test SET val = ‘V2’ WHERE id = 1;
COMMIT;

👉 回到 T1：

COMMIT;

3. T3 验证最终可见值（新事务读取）

START TRANSACTION;
SELECT * FROM mvcc_test WHERE id = 1;
– ✅ 结果为：’V2’

说明：即使 T1 最后提交，链头仍为 V2（T2 的更新）。

🔍 六、观察工具 SQL

查看当前连接 ID

SELECT CONNECTION_ID();

查看当前事务信息

SELECT * FROM information_schema.innodb_trx

查看锁等待情况

SELECT * FROM information_schema.innodb_lock_waits

查看所有连接状态

SHOW PROCESSLIST;

⚠️ 七、HeidiSQL 使用注意事项

操作 说明
多个查询窗口 ❌ 可能复用同一个连接
多开连接 ✅ 使用“新建连接”按钮，确保事务隔离
查看连接 ID 使用 SELECT CONNECTION_ID(); 验证是否是不同连接
Navicat 15 实测不同窗口不会复用同一个连接

🔚 八、结论总结

  • MVCC 的版本链由最后 UPDATE 决定，而非最后 COMMIT。

  • 事务内部能看到自己的未提交更新（包括在 READ COMMITTED 中）。

  • HeidiSQL 多窗口不一定代表多连接，需手动开启新连接以确保事务隔离。

✨ 建议：实验事务逻辑时，始终确认连接 ID、手动控制 autocommit、设置显式隔离级别，避免误判行为。

最后附上，个人博客网站：Southblock’Blog，内容更多，更新，欢迎参观。