数据库事务讨论到后面,绕不开两个高频点:
MVCC 为什么能让很多读操作不加锁也保持一致视图很多人会背"脏读、不可重复读、幻读",但一到线上排障就说不清:为什么这条查询没加锁也能读,为什么更新又会互相等待。核心就在 MVCC 和隔离级别。
事务隔离级别的目标,是在并发读写时平衡三件事:
隔离越强,数据越稳定,但并发代价通常也越高。
| 隔离级别 | 能否脏读 | 能否不可重复读 | 能否幻读 | 并发性能 |
|---|---|---|---|---|
READ UNCOMMITTED | 可能 | 可能 | 可能 | 最高 |
READ COMMITTED | 不会 | 可能 | 可能 | 高 |
REPEATABLE READ | 不会 | 不会 | 快照读下不会* | 中 |
SERIALIZABLE | 不会 | 不会 | 不会 | 最低 |
在 MySQL InnoDB 中,默认隔离级别通常是 REPEATABLE READ。
* MySQL 的 RR 下,一致性读(快照读)通过 MVCC 避免幻读,当前读通过间隙锁防护;两类读混用时仍需注意,见后文案例。
需要注意,表格是理论层总结,真实行为还要结合数据库实现细节来看,尤其是在 MySQL 下,MVCC 和间隙锁会让很多人对幻读的理解产生偏差。
READ COMMITTED (读已提交)
REPEATABLE READ (可重复读)
SERIALIZABLE (可串行化)
-- 查看当前会话隔离级别
SELECT @@transaction_isolation;
-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;定义:一个事务读到了另一个事务尚未提交的数据。如果对方事务回滚,那么当前事务读到的其实是无效数据。
示例场景:
-- 事务A:转账操作
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- 扣款
-- 此时事务A还未提交
-- 事务B:查询余额(READ UNCOMMITTED级别)
SELECT balance FROM accounts WHERE user_id = 1;
-- 读到了900(未提交的值)
-- 事务A:回滚
ROLLBACK; -- 实际余额还是1000
-- 事务B基于错误的余额做业务决策,导致问题危害:
解决方案:使用 READ COMMITTED 及以上隔离级别。
定义:同一事务中,对同一行数据前后两次读取结果不同,通常是因为中间有其他事务提交了更新。
示例场景:
-- 事务A:查询余额两次
BEGIN;
SELECT balance FROM accounts WHERE user_id = 1; -- 第一次读到1000
-- 事务B:更新并提交
BEGIN;
UPDATE accounts SET balance = 900 WHERE user_id = 1;
COMMIT;
-- 事务A:再次查询同一行
SELECT balance FROM accounts WHERE user_id = 1; -- 第二次读到900
COMMIT;危害:
解决方案:使用 REPEATABLE READ 及以上隔离级别,通过MVCC保证一致性读。
定义:同一事务里,前后两次按条件查询时,结果集的记录数量发生变化。它强调的是"范围内新增或减少了记录",而不是单行值变化。
示例场景:
-- 事务A:统计订单
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 第一次:10条
-- 事务B:插入新订单并提交
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
COMMIT;
-- 事务A:再次统计
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 第二次:11条
COMMIT;危害:
解决方案:
SERIALIZABLE 隔离级别REPEATABLE READ 通过间隙锁和MVCC解决幻读| 问题类型 | 关注点 | 出现的隔离级别 | 解决方案 |
|---|---|---|---|
| 脏读 | 读到未提交数据 | READ UNCOMMITTED | 使用READ COMMITTED及以上 |
| 不可重复读 | 同一行数据前后不一致 | READ COMMITTED | 使用REPEATABLE READ及以上 |
| 幻读 | 结果集记录数量变化 | READ COMMITTED、REPEATABLE READ(理论上) | 使用SERIALIZABLE或MySQL的RR |
MVCC(Multi-Version Concurrency Control,多版本并发控制)是一种并发控制机制,核心思想是:
核心价值:
适用场景:
在 InnoDB 里,MVCC 主要依赖三个核心组件:
┌─────────────────────────────────────────┐
│ MVCC 核心组件关系 │
├─────────────────────────────────────────┤
│ │
│ ┌──────────┐ ┌──────────┐ │
│ │ 聚簇索引 │─────▶│ 隐藏字段 │ │
│ │ (数据行) │ │ - DB_TRX_ID│ │
│ └──────────┘ │ - DB_ROLL_PT│ │
│ │ │ - DB_ROW_ID│ │
│ │ └──────────┘ │
│ ▼ │ │
│ ┌──────────┐ │ │
│ │ Undo Log │◀────────────┘ │
│ │ (版本链) │ │
│ └──────────┘ │
│ │ │
│ ▼ │
│ ┌──────────┐ │
│ │Read View │ │
│ │(可见性判断)│ │
│ └──────────┘ │
│ │
└─────────────────────────────────────────┘InnoDB为每行数据自动添加三个隐藏字段:
| 字段名 | 大小 | 说明 |
|---|---|---|
DB_TRX_ID | 6字节 | 最近修改该行数据的事务ID |
DB_ROLL_PTR | 7字节 | 回滚指针,指向该行的上一个版本(在Undo Log中) |
DB_ROW_ID | 6字节 | 隐藏主键(如果表没有显式主键) |
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);
INSERT INTO users VALUES (1, '张三', 25);数据行实际存储:
┌────┬──────┬─────┬───────────┬──────────────┬────────────┐
│ id │ name │ age │ DB_TRX_ID │ DB_ROLL_PTR │ DB_ROW_ID │
├────┼──────┼─────┼───────────┼──────────────┼────────────┤
│ 1 │ 张三 │ 25 │ 1001 │ NULL │ (无需) │
└────┴──────┴─────┴───────────┴──────────────┴────────────┘DB_TRX_ID (事务ID):
DB_ROLL_PTR (回滚指针):
DB_ROW_ID (隐藏主键):
Undo Log(回滚日志)是InnoDB实现MVCC和事务回滚的核心机制。
主要作用:
存储位置:
每次更新数据时,InnoDB会:
示例:更新操作形成版本链
-- 初始插入(事务ID=1001)
INSERT INTO users VALUES (1, '张三', 25);
-- 第一次更新(事务ID=1002)
UPDATE users SET age = 26 WHERE id = 1;
-- 第二次更新(事务ID=1003)
UPDATE users SET name = '李四', age = 27 WHERE id = 1;版本链结构:
当前数据行(最新版本)
┌────┬──────┬─────┬───────────┬──────────────┐
│ id │ name │ age │ DB_TRX_ID │ DB_ROLL_PTR │
│ 1 │ 李四 │ 27 │ 1003 │ ↓ │
└────┴──────┴─────┴───────────┴──────┼───────┘
│
┌────────────────────────────┘
▼
Undo Log (版本1)
┌────┬──────┬─────┬───────────┬──────────────┐
│ id │ name │ age │ DB_TRX_ID │ DB_ROLL_PTR │
│ 1 │ 张三 │ 26 │ 1002 │ ↓ │
└────┴──────┴─────┴───────────┴──────┼───────┘
│
┌────────────────────────────┘
▼
Undo Log (版本0)
┌────┬──────┬─────┬───────────┬──────────────┐
│ id │ name │ age │ DB_TRX_ID │ DB_ROLL_PTR │
│ 1 │ 张三 │ 25 │ 1001 │ NULL │
└────┴──────┴─────┴───────────┴──────────────┘Insert Undo Log:
Update Undo Log:
purge线程负责清理不再需要的Undo Log:
长事务的危害:
-- 长事务开始
BEGIN;
SELECT * FROM users WHERE id = 1; -- 事务ID=2000
-- 其他事务持续更新(产生大量Undo Log)
-- UPDATE、INSERT、DELETE操作...
-- 长事务持续运行(不提交也不回滚)
-- 所有Undo Log都无法清理,导致:
-- 1. Undo Log空间膨胀
-- 2. 版本链过长,查询性能下降
-- 3. 系统整体性能下降Read View是MVCC的核心,用于判断某个版本对当前事务是否可见。
Read View 包含的关键字段:
| 字段名 | 说明 |
|---|---|
m_ids | 生成Read View时,当前活跃的事务ID列表(未提交) |
min_trx_id | m_ids中最小的事务ID |
max_trx_id | 系统应分配的下一个事务ID(不是m_ids最大值) |
creator_trx_id | 创建该Read View的事务ID |
对于某行数据的某个版本,判断其对当前事务是否可见:
规则1:自己修改的,一定可见
if (DB_TRX_ID == creator_trx_id) {
// 自己修改的,可见
return VISIBLE;
}规则2:已提交且在Read View生成前提交的,可见
if (DB_TRX_ID < min_trx_id) {
// 在Read View生成前已提交,可见
return VISIBLE;
}规则3:在Read View生成后才开始的事务,不可见
if (DB_TRX_ID >= max_trx_id) {
// 在Read View生成后才开始的事务修改的版本,不可见
return NOT_VISIBLE;
}规则4:活跃事务列表中,不可见
if (m_ids.contains(DB_TRX_ID)) {
// 还未提交,不可见
return NOT_VISIBLE;
}规则5:其他情况,可见
// min_trx_id <= DB_TRX_ID < max_trx_id 且不在 m_ids 中,
// 即生成Read View时已经提交,可见
return VISIBLE;不同隔离级别,Read View生成时机不同:
READ COMMITTED:
REPEATABLE READ:
对比示例:
-- 事务A (RC级别)
BEGIN; -- 事务ID=100
SELECT * FROM users WHERE id = 1; -- 生成Read View-1
-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 事务ID=101
COMMIT;
-- 事务A再次查询
SELECT * FROM users WHERE id = 1; -- 生成Read View-2(能看到age=30)
COMMIT;-- 事务A (RR级别)
BEGIN; -- 事务ID=100
SELECT * FROM users WHERE id = 1; -- 生成Read View-1
-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 事务ID=101
COMMIT;
-- 事务A再次查询
SELECT * FROM users WHERE id = 1; -- 使用Read View-1(看不到age=30)
COMMIT;开始查询
│
▼
生成/复用Read View
│
▼
读取数据行(DB_TRX_ID)
│
▼
判断可见性
│
├─▶ DB_TRX_ID == creator_trx_id? ──▶ YES ──▶ 可见
│ │
│ NO
│ ▼
├─▶ DB_TRX_ID < min_trx_id? ──────▶ YES ──▶ 可见
│ │
│ NO
│ ▼
├─▶ DB_TRX_ID >= max_trx_id? ─────▶ YES ──▶ 不可见,沿版本链回溯
│ │
│ NO
│ ▼
├─▶ DB_TRX_ID in m_ids? ──────────▶ YES ──▶ 不可见
│ │
│ NO
│ ▼
└─▶ 可见(已提交) ──────────────────▶ 返回该版本
如果不可见,通过DB_ROLL_PTR找上一版本,重复判断Redo Log(重做日志)是InnoDB保证事务持久性的核心机制。
主要作用:
存储位置:
ib_logfile0 和 ib_logfile1(默认)┌─────────────────────────────────────────────┐
│ Redo Log 工作流程 │
├─────────────────────────────────────────────┤
│ │
│ 1. 事务提交 │
│ │ │
│ ▼ │
│ 2. 写入Redo Log Buffer(内存) │
│ │ │
│ ▼ │
│ 3. 刷写到Redo Log File(磁盘) │
│ │ │
│ ▼ │
│ 4. 返回事务提交成功 │
│ │ │
│ ▼ │
│ 5. 后台异步刷写脏页到数据文件 │
│ │
└─────────────────────────────────────────────┘innodb_flush_log_at_trx_commit参数控制刷盘策略:
| 值 | 策略 | 性能 | 安全性 |
|---|---|---|---|
| 0 | 每秒刷盘 | 最高 | 最低(可能丢失1秒数据) |
| 1 | 每次提交刷盘 | 最低 | 最高(不丢数据) |
| 2 | 每次提交写OS缓存,每秒刷盘 | 中 | 中(OS崩溃可能丢数据) |
推荐配置:
-- 生产环境,数据安全优先
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
-- 性能优先,可容忍少量数据丢失
SET GLOBAL innodb_flush_log_at_trx_commit = 2;| 特性 | Redo Log | Undo Log |
|---|---|---|
| 作用 | 保证持久性 | 保证原子性、MVCC |
| 内容 | 物理日志(页修改) | 逻辑日志(反向操作) |
| 写入时机 | 事务提交时 | 数据修改时 |
| 清理时机 | Checkpoint后 | 无活跃事务需要时 |
| 存储方式 | 循环写入 | 线性增长 |
| 崩溃恢复 | 重做已提交事务 | 回滚未提交事务 |
BEGIN; -- 事务ID=1000
INSERT INTO users (id, name, age) VALUES (1, '张三', 25);
COMMIT;MVCC处理流程:
id=1, name='张三', age=25DB_TRX_ID=1000DB_ROLL_PTR=NULL(INSERT操作的Undo Log)-- 初始数据:事务ID=1000, name='张三', age=25
BEGIN; -- 事务ID=1001
UPDATE users SET age = 26 WHERE id = 1;
COMMIT;MVCC处理流程:
DB_TRX_ID=1000name='张三', age=25name='张三', age=25, DB_TRX_ID=1000name='张三', age=26DB_TRX_ID=1001DB_ROLL_PTR → Undo Log-- 数据状态:
-- 最新版本:事务ID=1001, age=26
-- Undo Log:事务ID=1000, age=25
-- 场景1:事务ID=1002查询(RC级别)
BEGIN;
SELECT * FROM users WHERE id = 1; -- 生成Read View
-- Read View: m_ids=[1002], min=1002, max=1003
-- DB_TRX_ID=1001 < min=1002,可见
-- 返回:age=26
COMMIT;
-- 场景2:事务ID=1000查询(RR级别,Read View在1001提交前生成)
BEGIN; -- 事务ID=1000
SELECT * FROM users WHERE id = 1; -- 生成Read View
-- Read View: m_ids=[1000], min=1000, max=1001
-- DB_TRX_ID=1001 >= max=1001,但在Read View生成后
-- 判断不可见,通过DB_ROLL_PTR找Undo Log
-- Undo Log: DB_TRX_ID=1000 == creator_trx_id,可见
-- 返回:age=25
COMMIT;BEGIN; -- 事务ID=1002
DELETE FROM users WHERE id = 1;
COMMIT;MVCC处理流程:
DB_TRX_ID=1002DB_ROLL_PTR → Undo Log定义:基于MVCC读取快照数据,不需要加锁。
特点:
示例:
-- 普通SELECT都是一致性读
SELECT * FROM orders WHERE id = 1001;
SELECT * FROM orders WHERE status = 'pending';定义:读取最新版本数据,需要加锁。
特点:
示例:
-- 共享锁(S锁)
SELECT * FROM orders WHERE id = 1001 LOCK IN SHARE MODE;
SELECT * FROM orders WHERE id = 1001 FOR SHARE; -- MySQL 8.0+
-- 排他锁(X锁)
SELECT * FROM orders WHERE id = 1001 FOR UPDATE;
-- UPDATE和DELETE也是当前读
UPDATE orders SET status = 'completed' WHERE id = 1001;
DELETE FROM orders WHERE id = 1001;很多排障误判都来自把两类读混为一谈:
案例1:误判并发问题
-- 事务A
SELECT * FROM orders WHERE id = 1001; -- 一致性读,快速返回
-- 事务B
SELECT * FROM orders WHERE id = 1001 FOR UPDATE; -- 当前读,等待锁如果只看事务A能快速返回,误以为没有并发问题,实际上事务B可能在等待锁。
案例2:更新丢失问题
-- 事务A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 一致性读,读到1000
-- 业务处理...
UPDATE accounts SET balance = 900 WHERE id = 1; -- 当前读,更新
COMMIT;
-- 事务B(同时进行)
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 读到1000
UPDATE accounts SET balance = 800 WHERE id = 1; -- 覆盖事务A的修改
COMMIT;解决方案:使用SELECT ... FOR UPDATE进行当前读加锁。
-- 正确做法
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 当前读+锁
-- 业务处理...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;| 特性 | 一致性读 | 当前读 |
|---|---|---|
| 加锁 | 不加锁 | 加共享锁或排他锁 |
| 读取版本 | 快照版本 | 最新已提交版本 |
| 并发性能 | 高(不阻塞写) | 低(可能阻塞) |
| 适用场景 | 查询操作 | 修改操作、需要最新数据 |
| 实现机制 | MVCC | 锁机制 |
特点:
示例:
-- 事务A (RC级别)
BEGIN; -- 事务ID=1000
SELECT * FROM users WHERE id = 1;
-- Read View-1: m_ids=[1000], min=1000, max=1001
-- 假设读到: age=25
-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 事务ID=1001
COMMIT;
-- 事务A再次查询
SELECT * FROM users WHERE id = 1;
-- Read View-2: m_ids=[1000], min=1000, max=1002
-- DB_TRX_ID=1001 不在m_ids,且已提交,可见
-- 读到: age=30(不可重复读)
COMMIT;特点:
示例:
-- 事务A (RR级别)
BEGIN; -- 事务ID=1000
SELECT * FROM users WHERE id = 1;
-- Read View: m_ids=[1000], min=1000, max=1001
-- 假设读到: age=25
-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 事务ID=1001
COMMIT;
-- 事务A再次查询
SELECT * FROM users WHERE id = 1;
-- 使用Read View: m_ids=[1000], min=1000, max=1001
-- DB_TRX_ID=1001 >= max=1001,不可见
-- 通过Undo Log找旧版本: age=25
-- 保证可重复读
COMMIT;特点:
示例:
-- 事务A (SERIALIZABLE级别)
BEGIN;
SELECT * FROM users WHERE id = 1;
-- 实际执行: SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE
-- 加共享锁,阻塞其他事务的UPDATE
-- 事务B
BEGIN;
UPDATE users SET age = 30 WHERE id = 1; -- 等待事务A释放锁
COMMIT;| 隔离级别 | Read View生成 | 是否解决脏读 | 是否解决不可重复读 | 是否解决幻读 |
|---|---|---|---|---|
| READ COMMITTED | 每次SELECT | √ | × | × |
| REPEATABLE READ | 第一次SELECT | √ | √ | √(MySQL) |
| SERIALIZABLE | 不使用MVCC | √ | √ | √ |
场景:多个线程同时处理同一订单的状态更新。
问题现象:
-- 初始状态:订单1001, status='pending', version=1
-- 线程A
BEGIN;
SELECT status FROM orders WHERE id = 1001; -- 一致性读,pending
UPDATE orders SET status = 'processing' WHERE id = 1001; -- 当前读+更新
COMMIT;
-- 线程B(并发)
BEGIN;
SELECT status FROM orders WHERE id = 1001; -- 一致性读,pending
UPDATE orders SET status = 'cancelled' WHERE id = 1001; -- 覆盖线程A的修改
COMMIT;问题分析:
pending解决方案:
-- 方案1:使用当前读+排他锁
BEGIN;
SELECT status FROM orders WHERE id = 1001 FOR UPDATE; -- 当前读+锁
UPDATE orders SET status = 'processing' WHERE id = 1001;
COMMIT;
-- 方案2:乐观锁(版本号)
BEGIN;
SELECT status, version FROM orders WHERE id = 1001;
UPDATE orders SET status = 'processing', version = version + 1
WHERE id = 1001 AND version = 1; -- 乐观锁检查
-- affected_rows = 0 则表示版本冲突,重试
COMMIT;
-- 方案3:状态机检查
UPDATE orders SET status = 'processing'
WHERE id = 1001 AND status = 'pending'; -- 利用WHERE条件检查
-- affected_rows = 0 则表示状态已变更,不符合预期场景:秒杀场景,多个用户同时购买同一商品。
问题现象:
-- 商品1001, stock=10
-- 用户A
BEGIN;
SELECT stock FROM products WHERE id = 1001; -- 一致性读,stock=10
UPDATE products SET stock = stock - 1 WHERE id = 1001; -- 当前读
COMMIT;
-- 用户B(并发)
BEGIN;
SELECT stock FROM products WHERE id = 1001; -- 一致性读,stock=10
UPDATE products SET stock = stock - 1 WHERE id = 1001;
COMMIT;
-- 实际卖出2件,但库存扣减前都看到stock=10,可能超卖解决方案:
-- 方案1:使用SELECT FOR UPDATE
BEGIN;
SELECT stock FROM products WHERE id = 1001 FOR UPDATE; -- 当前读+锁
-- stock=10,判断>0
UPDATE products SET stock = stock - 1 WHERE id = 1001;
COMMIT;
-- 方案2:乐观锁+版本号
BEGIN;
SELECT stock, version FROM products WHERE id = 1001; -- 一致性读
-- stock=10, version=5
UPDATE products SET stock = stock - 1, version = 6
WHERE id = 1001 AND version = 5;
-- affected_rows = 1则成功,=0则重试
COMMIT;
-- 方案3:利用UPDATE原子性
UPDATE products SET stock = stock - 1
WHERE id = 1001 AND stock > 0;
-- affected_rows = 1则成功,=0则库存不足场景:数据导出任务,长时间运行的事务。
问题现象:
-- 事务A(长事务)
BEGIN; -- 事务ID=1000
SELECT * FROM orders WHERE create_time > '2024-01-01';
-- 导出数据,运行2小时...
-- 事务B,C,D...持续更新orders表
-- UPDATE orders SET status = 'completed' WHERE ...
-- 产生大量Undo Log
-- 问题:
-- 1. Undo Log无法清理,空间持续增长
-- 2. 其他查询需要遍历长版本链,性能下降
-- 3. 系统整体变慢解决方案:
-- 方案1:将长事务拆分
-- 每10000条记录提交一次
BEGIN;
SELECT * FROM orders WHERE create_time > '2024-01-01' LIMIT 10000 OFFSET 0;
-- 处理数据...
COMMIT;
BEGIN;
SELECT * FROM orders WHERE create_time > '2024-01-01' LIMIT 10000 OFFSET 10000;
-- 处理数据...
COMMIT;
-- 方案2:使用只读事务+短事务
SET SESSION TRANSACTION READ ONLY;
BEGIN;
SELECT * FROM orders WHERE create_time > '2024-01-01';
COMMIT;
-- 方案3:使用历史表或数据仓库
-- 将需要导出的数据复制到历史表
CREATE TABLE orders_export AS
SELECT * FROM orders WHERE create_time > '2024-01-01';
-- 然后从orders_export导出场景:统计订单数量,发现数量不一致。
问题现象:
-- 事务A (RR级别)
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 10条
-- 事务B
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
COMMIT;
-- 事务A再次统计
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 10条(MySQL的RR防止了幻读)
-- 但如果使用当前读:
SELECT COUNT(*) FROM orders WHERE user_id = 1 FOR UPDATE; -- 11条
COMMIT;分析:
解决方案:
-- 如果需要看到最新数据,使用当前读
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1 FOR UPDATE; -- 当前读+锁
-- 业务处理...
COMMIT;
-- 或者使用RC隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 能看到已提交的新记录
COMMIT;原因:
解决方案:
SELECT ... FOR UPDATE-- 场景:读取余额后扣款
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 加锁
-- balance=1000
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;原因:
排查方法:
-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- 查看锁信息
SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME = 'accounts';
-- 查看活跃事务
SELECT * FROM information_schema.INNODB_TRX;解决方案:
SET innodb_lock_wait_timeout = 10;原因:
示例:
-- 事务A (RR级别)
BEGIN;
SELECT * FROM users WHERE id = 1; -- 一致性读,age=25
-- 事务B
UPDATE users SET age = 30 WHERE id = 1;
COMMIT;
-- 事务A使用当前读
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 当前读,age=30
COMMIT;解决方案:
监控指标:
-- 查看Undo Log信息
SHOW ENGINE INNODB STATUS\G
-- 查看事务信息
SELECT * FROM information_schema.INNODB_TRX;
-- 查看Undo表空间(MySQL 8.0;5.7 中对应 information_schema.INNODB_SYS_TABLESPACES)
SELECT * FROM information_schema.INNODB_TABLESPACES
WHERE NAME LIKE '%undo%';
-- 查看长事务
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration
FROM information_schema.INNODB_TRX
HAVING duration > 60; -- 运行超过60秒的事务优化建议:
innodb_max_undo_log_size 控制undo log大小最佳实践:
-- 1. 设置锁等待与语句执行超时,避免事务长期悬挂
SET SESSION innodb_lock_wait_timeout = 10;
SET SESSION MAX_EXECUTION_TIME = 60000; -- 60秒超时(仅对SELECT生效)
-- 2. 应用层控制事务范围
-- 避免在事务中进行耗时操作
BEGIN;
-- 只做数据库操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 不要在事务中调用外部API、发送邮件等
-- 3. 使用只读事务
SET SESSION TRANSACTION READ ONLY;
BEGIN;
SELECT * FROM orders WHERE create_time > '2024-01-01';
COMMIT;
-- 4. 监控和告警
-- 定期查询长事务并告警1. 控制事务长度
-- 不推荐:事务过长
BEGIN;
SELECT * FROM orders WHERE id = 1;
-- 业务处理(耗时操作)
UPDATE orders SET status = 'completed' WHERE id = 1;
COMMIT;
-- 推荐:事务尽可能短
-- 先在事务外处理业务逻辑
-- 仅在事务中做数据库操作
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
UPDATE orders SET status = 'completed' WHERE id = 1;
COMMIT;2. 合理使用索引
-- 无索引:需要扫描全表,遍历所有版本链
UPDATE orders SET status = 'completed' WHERE order_no = 'ORD001';
-- 有索引:快速定位,减少版本链遍历
CREATE INDEX idx_order_no ON orders(order_no);
UPDATE orders SET status = 'completed' WHERE order_no = 'ORD001';3. 避免热点数据争用
-- 热点数据:所有用户更新同一行
UPDATE config SET value = value + 1 WHERE key = 'counter';
-- 优化:分片计数器
UPDATE counter_shard_1 SET value = value + 1 WHERE id = 1;
UPDATE counter_shard_2 SET value = value + 1 WHERE id = 2;
-- 最后聚合统计1. 配置独立的Undo表空间
-- MySQL 8.0+
-- 查看Undo表空间
SHOW VARIABLES LIKE 'innodb_undo_tablespaces';
-- 创建独立的Undo表空间
CREATE UNDO TABLESPACE undo_01 ADD DATAFILE 'undo_01.ibu';2. 控制Undo Log大小
-- 设置最大Undo Log大小
SET GLOBAL innodb_max_undo_log_size = 1073741824; -- 1GB
-- 启用Undo Log自动截断
SET GLOBAL innodb_undo_log_truncate = ON;1. 选择合适的隔离级别
-- 读多写少:使用RR级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 写多读少:考虑RC级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;2. 避免跨表事务
-- 不推荐:跨多个表的长事务
BEGIN;
UPDATE orders SET status = 'completed' WHERE id = 1;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
UPDATE payments SET status = 'success' WHERE order_id = 1;
COMMIT;
-- 推荐:拆分为多个短事务
BEGIN;
UPDATE orders SET status = 'completed' WHERE id = 1;
COMMIT;
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
COMMIT;
-- 使用分布式事务保证一致性(如需要)Q1: MVCC解决的核心问题是什么?
A: MVCC解决的核心问题是在保证一致性读的同时减少读写冲突。通过多版本机制,读操作可以读取历史版本而不阻塞写操作,大大提高了并发性能。
Q2: 事务隔离级别有哪些?分别解决什么问题?
A:
Q3: 一致性读和当前读有什么区别?
A:
Q4: Read View的工作原理是什么?
A: Read View包含四个关键字段:
m_ids:活跃事务ID列表min_trx_id:最小活跃事务IDmax_trx_id:下一个应分配的事务IDcreator_trx_id:创建Read View的事务ID通过这些字段判断某版本对当前事务是否可见:
Q5: Undo Log和Redo Log有什么区别?
A:
Undo Log:
Redo Log:
Q6: 为什么RR隔离级别能防止幻读?
A: MySQL的RR隔离级别通过以下机制防止幻读:
但需要注意:一致性读和当前读的幻读行为不同。
Q7: 长事务为什么会影响MVCC性能?
A: 长事务会导致:
Q8: 如何解决更新丢失问题?
A:
SELECT ... FOR UPDATE 加锁-- 方案1:悲观锁
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;
-- 方案2:乐观锁
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;
-- 方案3:原子更新
UPDATE products SET stock = stock - 1
WHERE id = 1 AND stock > 0;Q9: 如何排查数据库并发问题?
A:
information_schema.INNODB_LOCK_WAITSinformation_schema.INNODB_TRXperformance_schema.data_locksSHOW ENGINE INNODB STATUS 查看详细信息Q10: MVCC在RR和RC隔离级别下的区别是什么?
A:
Read View生成时机:
可见性:
一致性:
遇到事务问题时,优先查看:
| 特性 | 旧版（本文编写时，MySQL 5.7） | 当前（MySQL 8.0/8.4 LTS） |
|---|---|---|
| 默认字符集 | utf8（需显式配置 utf8mb4） | utf8mb4（MySQL 8.0 起默认） |
| 索引 | 普通 B+Tree | 降序索引、隐藏索引、函数索引（8.0+） |
| SQL 能力 | 常规查询 | 递归 CTE、窗口函数（8.0+） |
| 版本策略 | 5.7 | 8.0（主流）/ 8.4 LTS / 9.x（创新版） |
| Java 驱动 | mysql-connector-java 5.x/8.0 | mysql-connector-j 8.x/9.x |
本文基于 MySQL 5.7 编写，核心概念（索引、事务、锁、MVCC、InnoDB）在 8.0/8.4 中依然适用；8.0 的默认字符集、隐藏索引与 SQL 增强（CTE/窗口函数）是升级后的主要差异。