MySQL教程 / 第 80 节

第08章：事务与并发控制 ⭐⭐⭐⭐⭐

事务是数据库的核心特性，理解事务和MVCC是成为MySQL专家的必备知识

8.1 事务概述

8.1.1 什么是事务

事务（Transaction）：一组SQL语句的集合，要么全部成功，要么全部失败。

经典案例：银行转账

-- 转账操作必须是一个事务
START TRANSACTION;

-- 步骤1：A账户扣款
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 步骤2：B账户加款
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 提交事务
COMMIT;

如果步骤1成功，步骤2失败，会导致钱丢失！事务保证两个操作要么都成功，要么都失败。

8.2 ACID特性

事务必须满足ACID四大特性：

8.2.1 原子性（Atomicity）

定义： 事务是最小的执行单位，不可分割。要么全部成功，要么全部失败。

实现机制： undo log（回滚日志）

示例：

START TRANSACTION;

INSERT INTO users (username) VALUES ('user1');
INSERT INTO users (username) VALUES ('user2');
-- 这里发生错误
INSERT INTO users (username) VALUES (NULL);  -- 违反NOT NULL约束

ROLLBACK;  -- 回滚，前面的INSERT也会撤销

8.2.2 一致性（Consistency）

定义： 事务执行前后，数据库从一个一致性状态转换到另一个一致性状态。

示例：

-- 转账前：A=1000, B=500, 总额=1500
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;  -- A=900
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;  -- B=600
COMMIT;
-- 转账后：A=900, B=600, 总额=1500（保持一致）

8.2.3 隔离性（Isolation）

定义： 多个事务并发执行时，一个事务的执行不应影响其他事务。

问题： 如果没有隔离性，会出现脏读、不可重复读、幻读等问题。

实现机制： 锁机制 + MVCC（多版本并发控制）

8.2.4 持久性（Durability）

定义： 事务一旦提交，对数据库的改变是永久性的。

实现机制： redo log（重做日志）

示例：

START TRANSACTION;
INSERT INTO users (username) VALUES ('user1');
COMMIT;

-- 即使此时MySQL崩溃，重启后数据仍然存在

8.3 事务的使用

8.3.1 开启事务

-- 方法1
START TRANSACTION;

-- 方法2
BEGIN;

-- 方法3：开启只读事务
START TRANSACTION READ ONLY;

-- 方法4：开启读写事务
START TRANSACTION READ WRITE;

8.3.2 提交事务

COMMIT;

8.3.3 回滚事务

ROLLBACK;

8.3.4 保存点

START TRANSACTION;

INSERT INTO users (username) VALUES ('user1');

-- 设置保存点
SAVEPOINT sp1;

INSERT INTO users (username) VALUES ('user2');

-- 回滚到保存点（只撤销user2）
ROLLBACK TO sp1;

INSERT INTO users (username) VALUES ('user3');

COMMIT;
-- 结果：插入了user1和user3

8.3.5 自动提交

-- 查看自动提交状态
SHOW VARIABLES LIKE 'autocommit';

-- 关闭自动提交
SET autocommit = 0;

-- 此时每个语句都需要手动COMMIT
INSERT INTO users (username) VALUES ('test');
COMMIT;

-- 开启自动提交（默认）
SET autocommit = 1;

8.4 事务隔离级别 ⭐⭐⭐⭐⭐

8.4.1 并发问题

1. 脏读（Dirty Read）

事务A读取了事务B未提交的数据，如果B回滚，A读到的就是脏数据。

-- 时间线：
-- 事务A                          事务B
START TRANSACTION;               START TRANSACTION;
                                 UPDATE users SET age = 30 WHERE id = 1;
SELECT age FROM users WHERE id = 1;  -- 读到30（脏数据）
                                 ROLLBACK;  -- B回滚了
-- A读到的30是无效的

2. 不可重复读（Non-Repeatable Read）

事务A多次读取同一数据，结果不一致（因为事务B修改并提交了）。

-- 时间线：
-- 事务A                          事务B
START TRANSACTION;               
SELECT age FROM users WHERE id = 1;  -- 读到25
                                 START TRANSACTION;
                                 UPDATE users SET age = 30 WHERE id = 1;
                                 COMMIT;
SELECT age FROM users WHERE id = 1;  -- 读到30（不一致）
COMMIT;

3. 幻读（Phantom Read）

事务A多次查询，结果集的行数不一致（因为事务B插入或删除了数据）。

-- 时间线：
-- 事务A                          事务B
START TRANSACTION;               
SELECT COUNT(*) FROM users WHERE age > 20;  -- 返回10行
                                 START TRANSACTION;
                                 INSERT INTO users (age) VALUES (25);
                                 COMMIT;
SELECT COUNT(*) FROM users WHERE age > 20;  -- 返回11行（幻读）
COMMIT;

8.4.2 四种隔离级别

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	✅ 可能	✅ 可能	✅ 可能	最低级别，性能最好
READ COMMITTED	❌ 不可能	✅ 可能	✅ 可能	Oracle默认
REPEATABLE READ	❌ 不可能	❌ 不可能	✅ 可能	MySQL默认 ⭐
SERIALIZABLE	❌ 不可能	❌ 不可能	❌ 不可能	最高级别，性能最差

8.4.3 查看和设置隔离级别

-- 查看全局隔离级别
SELECT @@global.transaction_isolation;

-- 查看当前会话隔离级别
SELECT @@transaction_isolation;
-- 或
SHOW VARIABLES LIKE 'transaction_isolation';

-- 设置全局隔离级别
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

-- 设置当前会话隔离级别
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- 设置下一个事务的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

8.4.4 隔离级别详解

1. READ UNCOMMITTED（读未提交）

特点： 可以读取未提交的数据（脏读）

演示脏读：

-- 终端1（事务A）
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1;  -- 1000

-- 终端2（事务B）
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE user_id = 1;
-- 注意：没有COMMIT

-- 终端1（事务A）
SELECT balance FROM accounts WHERE user_id = 1;  -- 500（脏读！）

-- 终端2（事务B）
ROLLBACK;  -- 回滚

-- 终端1（事务A）
SELECT balance FROM accounts WHERE user_id = 1;  -- 1000（之前读到的500是脏数据）

使用场景： 几乎不使用

2. READ COMMITTED（读已提交）

特点： 只能读取已提交的数据，避免脏读

演示不可重复读：

-- 终端1（事务A）
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1;  -- 1000

-- 终端2（事务B）
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE user_id = 1;
COMMIT;  -- 提交

-- 终端1（事务A）
SELECT balance FROM accounts WHERE user_id = 1;  -- 500（不可重复读）
COMMIT;

使用场景： Oracle、PostgreSQL默认级别

3. REPEATABLE READ（可重复读）⭐ MySQL默认

特点： 同一事务内多次读取结果一致

演示：

-- 终端1（事务A）
SET SESSION transaction_isolation = 'REPEATABLE-READ';
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1;  -- 1000

-- 终端2（事务B）
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE user_id = 1;
COMMIT;

-- 终端1（事务A）
SELECT balance FROM accounts WHERE user_id = 1;  -- 仍然是1000（可重复读）
COMMIT;

-- 终端1（新事务）
SELECT balance FROM accounts WHERE user_id = 1;  -- 500（新事务读到最新值）

MySQL的REPEATABLE READ解决了幻读问题！（通过Next-Key Lock）

4. SERIALIZABLE（串行化）

特点： 最高隔离级别，完全串行化执行

演示：

-- 终端1（事务A）
SET SESSION transaction_isolation = 'SERIALIZABLE';
START TRANSACTION;
SELECT * FROM users WHERE id = 1;

-- 终端2（事务B）
START TRANSACTION;
UPDATE users SET age = 30 WHERE id = 1;  -- 阻塞，等待事务A提交

-- 终端1（事务A）
COMMIT;  -- 提交后，事务B才能执行

-- 终端2（事务B）
-- UPDATE执行成功
COMMIT;

使用场景： 对数据一致性要求极高的场景

8.5 MVCC多版本并发控制 ⭐⭐⭐⭐⭐

8.5.1 MVCC概述

MVCC（Multi-Version Concurrency Control）：多版本并发控制

核心思想： 为每个事务提供数据的一个快照，不同事务看到不同版本的数据。

优点：

读不加锁，写不阻塞读
提高并发性能
解决脏读、不可重复读问题

适用范围： InnoDB存储引擎，READ COMMITTED和REPEATABLE READ隔离级别

8.5.2 MVCC实现原理

1. 隐藏列

InnoDB为每行记录添加了3个隐藏列：

隐藏列	长度	说明
DB_TRX_ID	6字节	最后修改该行的事务ID
DB_ROLL_PTR	7字节	回滚指针，指向undo log
DB_ROW_ID	6字节	行ID（如果没有主键）

示例：

-- 用户看到的表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- InnoDB实际存储的结构
-- id | name | age | DB_TRX_ID | DB_ROLL_PTR | DB_ROW_ID

2. undo log版本链

每次修改数据时，旧版本会保存到undo log中，形成版本链。

示例：

-- 初始数据：id=1, name='张三', age=25

-- 事务100修改
UPDATE users SET age = 26 WHERE id = 1;
-- 当前版本：id=1, name='张三', age=26, DB_TRX_ID=100
-- undo log：id=1, name='张三', age=25, DB_TRX_ID=初始

-- 事务200修改
UPDATE users SET age = 27 WHERE id = 1;
-- 当前版本：id=1, name='张三', age=27, DB_TRX_ID=200
-- undo log链：
--   版本2：age=26, DB_TRX_ID=100
--   版本1：age=25, DB_TRX_ID=初始

版本链结构：

当前版本 → undo log版本2 → undo log版本1 → ...
age=27      age=26           age=25
TRX_ID=200  TRX_ID=100       TRX_ID=初始

3. Read View（读视图）

Read View：事务开始时创建的一致性视图，决定能看到哪些版本的数据。

Read View包含：

m_ids：当前活跃的事务ID列表
min_trx_id：最小活跃事务ID
max_trx_id：下一个要分配的事务ID
creator_trx_id：当前事务ID

可见性判断规则：

如果 DB_TRX_ID < min_trx_id：
    可见（已提交的旧事务）

如果 DB_TRX_ID >= max_trx_id：
    不可见（未来的事务）

如果 min_trx_id <= DB_TRX_ID < max_trx_id：
    如果 DB_TRX_ID 在 m_ids 中：
        不可见（未提交的事务）
    否则：
        可见（已提交的事务）

如果 DB_TRX_ID == creator_trx_id：
    可见（自己的修改）

8.5.3 MVCC工作流程

场景： 两个事务并发执行

-- 初始数据
INSERT INTO users (id, name, age) VALUES (1, '张三', 25);
-- DB_TRX_ID = 50

-- 时间线：
-- 事务A (TRX_ID=100)              事务B (TRX_ID=200)
START TRANSACTION;
SELECT age FROM users WHERE id=1; -- 读到25
                                  START TRANSACTION;
                                  UPDATE users SET age=30 WHERE id=1;
                                  -- DB_TRX_ID = 200
SELECT age FROM users WHERE id=1; -- 读到25（MVCC）
                                  COMMIT;
SELECT age FROM users WHERE id=1; -- 读到25（REPEATABLE READ）
COMMIT;

-- 新事务
START TRANSACTION;
SELECT age FROM users WHERE id=1; -- 读到30

详细分析：

事务A第一次查询（age=25）
- 创建Read View：m_ids=[100], min_trx_id=100, max_trx_id=101
- 当前版本：DB_TRX_ID=50 < min_trx_id=100 → 可见
- 返回：age=25
事务B更新（age=30）
- 修改数据：DB_TRX_ID=200
- 创建undo log：保存旧版本（age=25, DB_TRX_ID=50）
事务A第二次查询（age=25）
- 使用之前的Read View：m_ids=[100], min_trx_id=100, max_trx_id=101
- 当前版本：DB_TRX_ID=200 >= max_trx_id=101 → 不可见
- 沿着undo log链查找：
  - 版本1：DB_TRX_ID=50 < min_trx_id=100 → 可见
- 返回：age=25（可重复读）
事务B提交后，事务A第三次查询（age=25）
- 仍使用之前的Read View（REPEATABLE READ）
- 返回：age=25
新事务查询（age=30）
- 创建新的Read View：m_ids=[], min_trx_id=201, max_trx_id=201
- 当前版本：DB_TRX_ID=200 < min_trx_id=201 → 可见
- 返回：age=30

8.5.4 READ COMMITTED vs REPEATABLE READ

READ COMMITTED：

每次SELECT都创建新的Read View
能读到其他事务已提交的数据
不可重复读

REPEATABLE READ：

事务开始时创建Read View，之后一直使用
读到的数据始终一致
可重复读

演示：

-- READ COMMITTED
-- 事务A                          事务B
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT age FROM users WHERE id=1; -- 25（创建Read View 1）
                                  START TRANSACTION;
                                  UPDATE users SET age=30 WHERE id=1;
                                  COMMIT;
SELECT age FROM users WHERE id=1; -- 30（创建Read View 2，读到已提交数据）

-- REPEATABLE READ
-- 事务A                          事务B
SET SESSION transaction_isolation = 'REPEATABLE-READ';
START TRANSACTION;
SELECT age FROM users WHERE id=1; -- 25（创建Read View）
                                  START TRANSACTION;
                                  UPDATE users SET age=30 WHERE id=1;
                                  COMMIT;
SELECT age FROM users WHERE id=1; -- 25（使用同一个Read View）

8.5.5 当前读 vs 快照读

快照读（Snapshot Read）：

普通的SELECT语句
读取的是快照版本（通过MVCC）
不加锁

SELECT * FROM users WHERE id = 1;

当前读（Current Read）：

SELECT … FOR UPDATE
SELECT … LOCK IN SHARE MODE
INSERT、UPDATE、DELETE
读取的是最新版本
加锁

-- 当前读（加排他锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 当前读（加共享锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 当前读
UPDATE users SET age = 30 WHERE id = 1;
DELETE FROM users WHERE id = 1;

演示：

-- 事务A                          事务B
START TRANSACTION;
SELECT age FROM users WHERE id=1; -- 25（快照读）
                                  START TRANSACTION;
                                  UPDATE users SET age=30 WHERE id=1;
                                  COMMIT;
SELECT age FROM users WHERE id=1; -- 25（快照读，MVCC）
SELECT age FROM users WHERE id=1 FOR UPDATE; -- 30（当前读）
COMMIT;

8.5.6 MVCC解决的问题

1. 解决脏读

通过Read View，只能读到已提交的事务
未提交事务的修改不可见

2. 解决不可重复读（REPEATABLE READ）

事务内使用同一个Read View
读到的数据始终一致

3. 提高并发性能

读不加锁
写不阻塞读
多个事务可以并发读取

8.6 实战案例

案例1：转账事务

-- 创建账户表
CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL UNIQUE,
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 0  -- 乐观锁版本号
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO accounts (user_id, balance) VALUES (1, 1000.00), (2, 500.00);

-- 转账：用户1给用户2转账100元
START TRANSACTION;

-- 检查余额
SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE;  -- 当前读，加锁

-- 扣款
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1 AND balance >= 100;

-- 检查是否扣款成功
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    SELECT '余额不足' AS message;
ELSE
    -- 加款
    UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
    COMMIT;
    SELECT '转账成功' AS message;
END IF;

案例2：秒杀系统

-- 创建商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    stock INT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO products (name, stock) VALUES ('iPhone 15', 100);

-- 方法1：悲观锁（FOR UPDATE）
START TRANSACTION;

-- 查询库存（加排他锁）
SELECT stock FROM products WHERE id = 1 FOR UPDATE;

-- 减库存
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;

-- 检查是否成功
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    SELECT '库存不足' AS message;
ELSE
    -- 创建订单
    INSERT INTO orders (product_id, user_id) VALUES (1, 100);
    COMMIT;
    SELECT '秒杀成功' AS message;
END IF;

-- 方法2：乐观锁（版本号）
START TRANSACTION;

-- 查询库存和版本号
SELECT stock, version FROM products WHERE id = 1;
-- 假设：stock=10, version=5

-- 减库存（带版本号检查）
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5 AND stock > 0;

-- 检查是否成功
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    SELECT '秒杀失败，请重试' AS message;
ELSE
    -- 创建订单
    INSERT INTO orders (product_id, user_id) VALUES (1, 100);
    COMMIT;
    SELECT '秒杀成功' AS message;
END IF;

案例3：防止超卖

-- 错误示例：可能超卖
START TRANSACTION;
SELECT stock FROM products WHERE id = 1;  -- 快照读，stock=10
-- 此时其他事务可能已经减库存
UPDATE products SET stock = stock - 1 WHERE id = 1;  -- 可能变成负数
COMMIT;

-- 正确示例1：使用FOR UPDATE
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;  -- 当前读，加锁
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;

-- 正确示例2：直接UPDATE带条件
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

案例4：分布式事务（两阶段提交）

-- 场景：跨数据库转账

-- 数据库1：扣款
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
-- 准备提交（不真正提交）
XA PREPARE 'trx1';

-- 数据库2：加款
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 准备提交
XA PREPARE 'trx2';

-- 如果两个都准备成功，则提交
XA COMMIT 'trx1';
XA COMMIT 'trx2';

-- 如果任何一个失败，则回滚
XA ROLLBACK 'trx1';
XA ROLLBACK 'trx2';

8.7 性能优化建议

8.7.1 选择合适的隔离级别

-- 对一致性要求不高的场景
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- 对一致性要求高的场景（默认）
SET SESSION transaction_isolation = 'REPEATABLE-READ';

-- 极高一致性要求
SET SESSION transaction_isolation = 'SERIALIZABLE';

8.7.2 缩短事务时间

-- ❌ 错误：事务时间过长
START TRANSACTION;
SELECT * FROM users;  -- 查询
-- 执行复杂的业务逻辑（耗时）
-- 调用外部API（耗时）
UPDATE users SET status = 1 WHERE id = 1;
COMMIT;

-- ✅ 正确：只在必要时使用事务
-- 先执行业务逻辑
-- 再开启事务
START TRANSACTION;
UPDATE users SET status = 1 WHERE id = 1;
COMMIT;

8.7.3 避免大事务

-- ❌ 错误：一次更新100万行
START TRANSACTION;
UPDATE users SET status = 1 WHERE create_time < '2024-01-01';  -- 100万行
COMMIT;

-- ✅ 正确：分批更新
WHILE (SELECT COUNT(*) FROM users WHERE create_time < '2024-01-01' AND status = 0) > 0 DO
    START TRANSACTION;
    UPDATE users SET status = 1
    WHERE create_time < '2024-01-01' AND status = 0
    LIMIT 1000;
    COMMIT;
    SELECT SLEEP(0.1);  -- 暂停一下
END WHILE;

8.7.4 使用乐观锁代替悲观锁

-- 悲观锁：并发低
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR UPDATE;  -- 加锁
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- 乐观锁：并发高
START TRANSACTION;
SELECT stock, version FROM products WHERE id = 1;
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = #{old_version};
IF ROW_COUNT() = 0 THEN
    ROLLBACK;  -- 版本冲突，重试
ELSE
    COMMIT;
END IF;

8.8 小结

本章学习了事务与并发控制：

✅ 事务的ACID特性
✅ 事务的使用（BEGIN、COMMIT、ROLLBACK）
✅ 四种事务隔离级别
✅ 并发问题（脏读、不可重复读、幻读）
✅ MVCC多版本并发控制原理 ⭐⭐⭐⭐⭐
✅ 当前读 vs 快照读
✅ 实战案例（转账、秒杀、防超卖）

重点掌握：

MySQL默认隔离级别是REPEATABLE READ
MVCC通过undo log版本链和Read View实现
快照读不加锁，当前读加锁
使用FOR UPDATE实现悲观锁
使用版本号实现乐观锁

面试重点：

MVCC的实现原理
四种隔离级别的区别
如何解决幻读问题
当前读和快照读的区别
如何防止超卖

下一章预告： 锁机制详解

练习题

实现一个转账功能，要求使用事务保证数据一致性
演示四种隔离级别的区别
解释MVCC的工作原理
实现一个秒杀系统，防止超卖
对比悲观锁和乐观锁的使用场景

继续学习： 第09章：锁机制