MySQL 事务管理
事务是数据库管理系统中的核心概念，用于保证数据的一致性和完整性。本章将详细介绍 MySQL 事务的原理和使用方法。
什么是事务？
事务（Transaction）是一组数据库操作的逻辑单元，这些操作要么全部成功执行，要么全部不执行。事务具有 ACID 四大特性：
ACID 特性
| 特性 | 英文 | 说明 |
|---|---|---|
| 原子性 | Atomicity | 事务中的所有操作要么全部完成，要么全部不完成 |
| 一致性 | Consistency | 事务执行前后，数据库状态保持一致 |
| 隔离性 | Isolation | 多个并发事务之间互不干扰 |
| 持久性 | Durability | 事务完成后，对数据的修改是永久的 |
事务的作用
┌─────────────────────────────────────────────────────────────┐
│ 银行转账示例 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 账户 A 转账 100 元给账户 B │
│ │
│ 事务开始 │
│ │ │
│ ├── 1. 检查账户 A 余额是否充足 │
│ │ │
│ ├── 2. 从账户 A 扣除 100 元 │
│ │ ↓ │
│ │ 如果此时系统崩溃... │
│ │ ↓ │
│ │ 没有事务：账户 A 少了 100 元，账户 B 没收到 │
│ │ 有事务：整个操作回滚，账户 A 余额不变 │
│ │ │
│ ├── 3. 向账户 B 增加 100 元 │
│ │ │
│ └── 事务提交 │
│ │
└─────────────────────────────────────────────────────────────┘
事务控制语句
基本语法
-- 开始事务
START TRANSACTION;
-- 或
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
完整示例
-- 创建测试表
CREATE TABLE accounts (
id INT PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10, 2) NOT NULL
);
INSERT INTO accounts VALUES
(1, '张三', 1000.00),
(2, '李四', 500.00);
-- 转账事务
START TRANSACTION;
-- 检查余额
SELECT balance FROM accounts WHERE id = 1; -- 张三余额 1000
-- 从张三账户扣款
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 向李四账户存款
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 提交事务
COMMIT;
-- 如果中间出现问题，可以回滚
-- ROLLBACK;
保存点（SAVEPOINT）
保存点允许在事务中设置标记，可以回滚到指定位置：
START TRANSACTION;
INSERT INTO accounts (id, name, balance) VALUES (3, '王五', 800.00);
-- 设置保存点
SAVEPOINT point1;
INSERT INTO accounts (id, name, balance) VALUES (4, '赵六', 600.00);
-- 回滚到保存点
ROLLBACK TO SAVEPOINT point1;
-- 此时只有王五被插入，赵六的操作被撤销
-- 继续其他操作
INSERT INTO accounts (id, name, balance) VALUES (5, '孙七', 700.00);
COMMIT;
释放保存点
-- 释放保存点
RELEASE SAVEPOINT point1;
-- 释放后不能再回滚到该保存点
事务隔离级别
隔离级别概述
MySQL InnoDB 支持四种事务隔离级别：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|
| READ UNCOMMITTED | ✓ | ✓ | ✓ | 读未提交，最低级别 |
| READ COMMITTED | ✗ | ✓ | ✓ | 读已提交 |
| REPEATABLE READ | ✗ | ✗ | ✓* | 可重复读（MySQL 默认） |
| SERIALIZABLE | ✗ | ✗ | ✗ | 串行化，最高级别 |
*MySQL InnoDB 在 REPEATABLE READ 级别通过 Next-Key Lock 机制避免了幻读。
并发问题详解
1. 脏读（Dirty Read）
读到了其他事务未提交的数据：
-- 事务 A
START TRANSACTION;
UPDATE accounts SET balance = 2000 WHERE id = 1;
-- 未提交...
-- 事务 B（隔离级别为 READ UNCOMMITTED）
SELECT balance FROM accounts WHERE id = 1; -- 读到 2000
-- 事务 A 回滚
ROLLBACK;
-- 事务 B 读到的是脏数据
2. 不可重复读（Non-repeatable Read）
同一事务中两次读取结果不同（针对修改）：
-- 事务 A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 读到 1000
-- 事务 B
UPDATE accounts SET balance = 2000 WHERE id = 1;
COMMIT;
-- 事务 A 再次读取
SELECT balance FROM accounts WHERE id = 1; -- 读到 2000，两次结果不同
COMMIT;
3. 幻读（Phantom Read）
同一事务中两次读取的行数不同（针对新增/删除）：
-- 事务 A
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 500; -- 返回 2 行
-- 事务 B
INSERT INTO accounts VALUES (6, '新用户', 800.00);
COMMIT;
-- 事务 A 再次查询
SELECT * FROM accounts WHERE balance > 500; -- 返回 3 行，多了一行
COMMIT;
设置隔离级别
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 设置下一条事务的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
各隔离级别详解
READ UNCOMMITTED（读未提交）
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 特点：
-- 1. 允许读取未提交的数据（脏读）
-- 2. 性能最好，但数据一致性最差
-- 3. 极少使用
-- 适用场景：对数据一致性要求不高的统计报表
READ COMMITTED（读已提交）
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 特点：
-- 1. 只能读取已提交的数据
-- 2. 解决了脏读问题
-- 3. 可能出现不可重复读和幻读
-- 每次查询都获取最新快照
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 第一次读取
-- 此时其他事务修改并提交了
SELECT balance FROM accounts WHERE id = 1; -- 第二次读取，结果可能不同
COMMIT;
REPEATABLE READ（可重复读）
MySQL InnoDB 的默认隔离级别：
-- 查看默认隔离级别
SELECT @@transaction_isolation; -- REPEATABLE-READ
-- 特点：
-- 1. 同一事务中多次读取结果相同
-- 2. 解决了脏读和不可重复读
-- 3. InnoDB 通过 MVCC 和 Next-Key Lock 解决了幻读
-- 工作原理：
-- 1. 第一次读取时创建快照
-- 2. 后续读取使用同一个快照
-- 3. 写操作使用 Next-Key Lock 防止幻读
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 读取并创建快照
-- 此时其他事务修改并提交了
SELECT balance FROM accounts WHERE id = 1; -- 仍然读取快照中的值
COMMIT;
SERIALIZABLE（串行化）
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 特点：
-- 1. 最高隔离级别
-- 2. 所有事务串行执行
-- 3. 完全解决所有并发问题
-- 4. 性能最差
-- 工作原理：
-- 普通 SELECT 自动转换为 SELECT ... FOR SHARE
-- 读取时加共享锁，阻塞其他写操作
锁机制简介
锁的类型
-- 共享锁（S锁）- 读锁
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0+
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- 排他锁（X锁）- 写锁
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
行级锁
InnoDB 默认使用行级锁：
-- 更新时自动加排他锁
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 此时 id=1 这一行被锁定
-- 其他事务修改这一行会被阻塞
COMMIT;
间隙锁（Gap Lock）
防止幻读：
-- 假设 accounts 表有 id = 1, 2, 5 的记录
START TRANSACTION;
-- 查询 id > 2 的记录并加锁
SELECT * FROM accounts WHERE id > 2 FOR UPDATE;
-- 此时 id = 3, 4 的间隙也被锁定
-- 其他事务无法插入 id = 3 或 4 的记录
COMMIT;
查看锁信息
-- MySQL 8.0+ 查看锁
SELECT * FROM performance_schema.data_locks;
-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;
-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;
-- 查看锁情况（旧版本）
SHOW ENGINE INNODB STATUS;
MVCC 多版本并发控制
InnoDB 通过 MVCC 实现非阻塞读：
┌─────────────────────────────────────────────────────────────┐
│ MVCC 工作原理 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 每行数据包含： │
│ - DB_TRX_ID：最后修改该行的事务 ID │
│ - DB_ROLL_PTR：回滚指针，指向 undo log │
│ - DB_ROW_ID：隐藏的自增 ID │
│ │
│ 读取流程： │
│ 1. 事务开始时创建 Read View │
│ 2. Read View 记录当前活跃事务列表 │
│ 3. 读取数据时，判断数据版本是否可见 │
│ 4. 如果不可见，通过 undo log 获取历史版本 │
│ │
│ 优势： │
│ - 读操作不加锁，性能高 │
│ - 写操作只锁定修改的行 │
│ - 实现了快照读 │
│ │
└─────────────────────────────────────────────────────────────┘
死锁
死锁产生原因
两个或多个事务互相等待对方释放锁：
-- 事务 A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 锁定 id=1
-- 事务 B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2; -- 锁定 id=2
-- 事务 A 继续执行
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 等待 id=2 的锁
-- 事务 B 继续执行
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- 等待 id=1 的锁
-- 死锁产生！
死锁检测和处理
-- InnoDB 自动检测死锁，回滚其中一个事务
-- 查看死锁信息
SHOW ENGINE INNODB STATUS;
-- 查看锁等待超时设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 默认 50 秒
-- 设置锁等待超时
SET innodb_lock_wait_timeout = 30;
避免死锁
-- 1. 按相同顺序访问表和行
-- 好的做法：总是按 id 升序处理
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 2. 大事务拆分成小事务
-- 3. 尽量使用索引访问数据，减少锁范围
-- 4. 避免长事务
事务最佳实践
1. 控制事务大小
-- 不好的做法：一个大事务
START TRANSACTION;
-- 大量操作...
UPDATE orders SET status = 'completed' WHERE status = 'pending'; -- 影响大量行
-- 更多操作...
COMMIT;
-- 好的做法：分批处理
-- 使用程序控制，每批处理 1000 条
UPDATE orders SET status = 'completed'
WHERE status = 'pending'
LIMIT 1000;
2. 合理设置隔离级别
-- 默认 REPEATABLE READ 适合大多数场景
-- 对于报表统计，可以使用 READ COMMITTED 提高性能
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 对于敏感操作，使用 SERIALIZABLE 确保一致性
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3. 避免长事务
-- 查看长事务
SELECT
trx_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration
FROM information_schema.INNODB_TRX
ORDER BY trx_started;
-- 设置自动提交
SET autocommit = 1;
-- 设置事务超时（MySQL 8.0+）
SET SESSION max_execution_time = 30000; -- 30 秒
4. 使用正确的锁策略
-- 对于需要确保数据一致性的读操作
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 对于只需要读取的操作，不要加锁
SELECT balance FROM accounts WHERE id = 1;
小结
本章我们学习了：
- ACID 特性：原子性、一致性、隔离性、持久性
- 事务控制：BEGIN、COMMIT、ROLLBACK、SAVEPOINT
- 隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE
- 并发问题：脏读、不可重复读、幻读
- 锁机制：共享锁、排他锁、间隙锁
- MVCC：多版本并发控制原理
- 死锁：死锁产生原因和避免方法
练习
- 创建一个转账存储过程，使用事务确保数据一致性
- 测试不同隔离级别下的并发行为
- 模拟并解决一个死锁场景
- 使用 EXPLAIN 分析事务中的锁情况