数据库系统这门课，做到实验八、实验九的时候，才算是真正开始碰数据库里最核心的两个问题：并发控制和故障恢复。我当年做这两组实验时，最大的感触是——前面写SQL、调索引，更多是在“用数据库”，而到了实验八和实验九，是在“理解数据库”。如果你也是软件工程相关专业的学生，或者正在自学的后端开发者，这两组实验相当于把事务、锁、日志、备份恢复这些东西全部揉在一起考一遍，绕不过去。
实验八和实验九在不同学校的编号安排会略有差异，常见的是实验八对应事务与并发控制，实验九对应备份与故障恢复。这篇博文就按这个组合来复盘，把原理、步骤、踩坑一次性说透。
1. 实验八、实验九到底在做什么
1.1 实验八的定位：用锁和事务回答“并发会不会乱”
并发控制解决的是一个老问题：多个事务同时操作同一批数据，怎么保证结果和串行执行一致。实验八的基本套路，是通过两个或多个并发会话，模拟转账、抢购、库存扣减这类场景，观察隔离级别对结果的影响，再通过锁机制防止数据被写坏。
实验的核心工具是MySQL的InnoDB引擎。你会发现实验指导书里会反复提到隔离级别、共享锁、排他锁、死锁这些词。做实验时你的目标很明确：先让问题发生，再想办法阻止问题发生。脏读、不可重复读、幻读这三类异常，不是背定义背出来的，而是在两个终端同时执行SQL时，眼睁睁看着数据变了才真正理解。
1.2 实验九的定位：用日志和备份回答“数据没了怎么找回来”
故障恢复解决的是另一个问题：数据文件损坏、误删、突然断电，怎么把数据库恢复到某个时间点。实验九的典型流程是定期做备份，开启日志记录，再人为制造故障，用备份和日志把数据恢复回来。
这里要区分两个概念：备份是“静态的副本”，日志是“动态的补丁”。只有全量备份还不够，因为备份之后还有新数据写入；只有日志也不行，因为日志重放需要一个起点。实验九的精髓，就是理解全量备份加日志增量这套组合拳。
1.3 为什么选MySQL InnoDB做这两组实验
很多学校做数据库实验会用SQL Server或者Oracle，但我个人觉得用MySQL InnoDB来做并发控制和故障恢复，是最适合理解的。
一方面，InnoDB的锁机制、MVCC、隔离级别实现得很典型，事务四大特性ACID都有落地的机制，锁等待超时、死锁检测这些行为都可以用命令直接观察。另一方面，MySQL有mysqldump、binlog这一套完整的备份恢复工具链，实验环境好搭，学到的知识放到生产环境同样适用。课程实验的核心是“原理验证”，MySQL能覆盖绝大部分知识点，还不折腾人。
2. 实验八核心细节：隔离级别、锁与死锁
2.1 四种隔离级别不是理论空话
实验指导书里会给一张表：读未提交、读已提交、可重复读、串行化。很多人背完就忘，其实实验里可以用SQL亲自测出来。
MySQL默认隔离级别是可重复读，查看当前隔离级别用这条命令：
SHOW VARIABLES LIKE 'transaction_isolation';临时修改当前会话隔离级别：
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;四种级别分别防什么，可以这样记：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | 可能 | 可能 | 可能 |
| 读已提交 | 防止 | 可能 | 可能 |
| 可重复读 | 防止 | 防止 | 主要防止（InnoDB间隙锁处理） |
| 串行化 | 防止 | 防止 | 防止 |
实验里复现脏读的操作流程是这样的：把两个事务的隔离级别都调到READ UNCOMMITTED，事务A执行update但不提交，事务B在另一个会话里select，就能读到事务A还没提交的数据。这个数据如果最终回滚，那B读到的就是凭空出现的“脏数据”。
不可重复读的复现思路，是在事务B里先select一次，然后事务A把那条数据update并提交，事务B再次select，两次结果不一致。同一个事务里读同一行，两次结果不一样，这在业务上是很要命的——比如统计余额时一会多一会少。
幻读比不可重复读更隐蔽。不可重复读针对的是一行数据被修改，幻读针对的是一批数据条数变化，比如事务A查某个区间的记录，事务B插入了一条新记录并提交，事务A再查时多了一条“幻影行”。InnoDB用间隙锁解决幻读，后面实操部分再展开讲。
2.2 锁的类型：从共享锁到间隙锁
InnoDB的锁可以按粒度分成行级锁和表级锁，按模式分成共享锁和排他锁。共享锁和排他锁的关系，你可以类比图书馆的座位：共享锁是“有人坐但允许别人一起看这本书”，排他锁是“这个人独占，别人碰都不能碰”。
SQL层面最常见的两种加锁语句：
-- 共享锁 SELECT ... LOCK IN SHARE MODE; -- 排他锁 SELECT ... FOR UPDATE;UPDATE、DELETE、INSERT操作默认都得先拿排他锁。两个事务同时update同一行，后者的update会阻塞，直到前者提交或回滚。
间隙锁是InnoDB在可重复读级别下的特殊机制。当你在一个索引范围上执行查询时，InnoDB不仅锁住已存在的记录，还会锁住记录之间的“间隙”，防止其他事务往这个缝隙里插入新数据。这也是为什么MySQL在高隔离级别下能挡住幻读。
做实验时建议把锁等待时间调短一点，不然卡住后会很浪费时间：
SET SESSION innodb_lock_wait_timeout = 5;2.3 死锁：数据库也有“互相等对方”的尴尬
死锁是并发实验里最经典的现象。两个事务各自持有一把锁，然后都想拿对方手里的锁，互相不放手，最后困在死循环里。MySQL的InnoDB会自动检测死锁，并且回滚其中一个事务让另一个继续执行。
模拟死锁的经典做法：
-- 事务A START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE id = 1; UPDATE account SET balance = balance + 100 WHERE id = 2; -- 此时不提交 -- 事务B START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE id = 2; UPDATE account SET balance = balance + 100 WHERE id = 1;如果事务A先锁了id=1，事务B先锁了id=2，接着A想锁id=2，B想锁id=1，就会出现死锁。InnoDB检测到后，会提示“Deadlock found when trying to get lock”，然后回滚一个事务。
避免死锁的思路也很直白：让所有事务按相同顺序操作资源。比如约定“先更新小id，再更新大id”，两个事务就永远不会交叉等待。
3. 实验八实操过程：从建表到死锁复现
3.1 环境和测试表准备
建议用MySQL 8.0，存储引擎选InnoDB。打开两个终端，分别连入同一个数据库，模拟两个并发会话。
先用最简单的一组表演示并发问题：
CREATE DATABASE concurrency_lab; USE concurrency_lab; CREATE TABLE account ( id INT PRIMARY KEY, balance DECIMAL(10, 2) NOT NULL ); INSERT INTO account VALUES (1, 1000.00), (2, 1000.00);这个表结构足够覆盖转账、扣款、余额查询这些场景。后面做的所有实验都围绕这两行数据展开。
3.2 复现脏读、不可重复读和幻读
脏读的操作步骤：
- 会话A执行
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; - 会话B也执行同样的设置。
- 会话A开启事务，把id=1的balance改成500，不提交。
- 会话B查询account表，能看到500。
- 会话A执行ROLLBACK。
- 会话B再查，数据回到1000。
这个瞬间读到的500就是脏读。
不可重复读的操作步骤：
- 两个会话都设成
READ COMMITTED。 - 会话B开启事务，查询id=1的balance，得到1000。
- 会话A开启事务，把id=1的balance改成800并提交。
- 会话B在同一个事务里再次查询，得到800。
同一个事务B，两次select得到不同结果，这就是不可重复读。
幻读的实验稍微麻烦一点，建议给account表加一个普通索引，用一个范围来测试。比如往表里插入一些新账户，然后在一个事务里反复执行范围查询，看到条数变化就是幻读。
注意，做这类实验时，开完事务一定要记得提交或回滚，不然锁会一直占着，后面的排查会找不到头绪。
3.3 锁等待和死锁的现场记录
先试一下行锁阻塞：会话A执行UPDATE account SET balance = balance - 100 WHERE id = 1;不提交；会话B执行UPDATE account SET balance = balance - 100 WHERE id = 1;此时这条语句会卡住。
把innodb_lock_wait_timeout设置成5秒后，5秒内不提交事务A，B就会报：
Lock wait timeout exceeded; try restarting transaction这个报错说明B在等待A释放行锁。
然后试死锁。按上面2.3的方式，两个事务交叉更新两行数据，InnoDB会自动检测并回滚。实验报告里要记录的信息有：死锁发生的时间、涉及的事务id、被回滚的是哪个事务、最后的处理措施是什么。
做死锁实验时，有个经验很实用：先只开两个会话，不要多个窗口同时操作，不然你根本分不清谁锁了哪一行。检查当前有哪些锁可以使用：
SELECT * FROM performance_schema.data_locks;看这张表可以快速定位某个锁属于哪个事务，在排查时非常管用。
4. 实验九核心细节：备份策略、binlog与崩溃恢复
4.1 逻辑备份和物理备份怎么选
实验九的第一步是理解备份类型。逻辑备份导出的是SQL语句，最典型的工具就是mysqldump。物理备份复制的是数据文件，常见工具是xtrabackup。
| 对比项 | 逻辑备份 | 物理备份 |
|---|---|---|
| 备份内容 | 建表语句+数据INSERT | 数据页文件 |
| 恢复速度 | 慢，逐条执行SQL | 快，直接拷贝文件 |
| 跨版本兼容 | 较好 | 较差 |
| 实验常用 | 是 | 较少 |
实验环境里用mysqldump就足够了。它的命令也很简单：
mysqldump -u root -p concurrency_lab > backup.sql只备份某个表也可以：
mysqldump -u root -p concurrency_lab account > account_backup.sql恢复时执行：
mysql -u root -p concurrency_lab < backup.sql做项目时还会分全量备份和增量备份。全量备份把整个库都导出来，增量备份只记录从上一次备份之后的变化。实验九里经常是先做一次全量备份，再开启binlog记录之后所有写操作，以便实现按时间点恢复。
4.2 binlog：让恢复精确到秒
binlog是MySQL Server层的二进制日志，记录所有会修改数据的操作。开启binlog后，每次写操作都会追加到日志文件里。
查看binlog是否开启：
SHOW VARIABLES LIKE 'log_bin';查看当前正在写的binlog：
SHOW MASTER STATUS;实验九的经典恢复流程是：
- 做一次全量备份。
- 继续正常进行写操作，binlog记录这些操作。
- 模拟误删数据。
- 用全量备份恢复到备份时间点。
- 用binlog从备份时间点重放到误删前的一瞬间。
binlog有三种格式：STATEMENT、ROW、MIXED。实验里我推荐用ROW格式，因为它记录的是每行数据实际变化，应用binlog时不容易出现函数计算差异。设置方式：
[mysqld] server-id=1 log-bin=mysql-bin binlog_format=ROW用binlog做恢复的核心命令是mysqlbinlog，后面实操部分会给完整示例。
4.3 redo log和undo log在崩溃恢复中的角色
binlog是逻辑日志，而InnoDB内部还有redo log和undo log。这两个日志是数据库崩溃恢复的关键。
redo log像是“记账本重放”。事务对数据页做的修改，先写redo log，再把脏页刷到磁盘。如果数据库突然断电，内存里的修改丢了，但redo log还在，重启后把redo log里的记录重放一遍，就能恢复到崩溃前状态。
undo log则是“撤销操作记录”。事务回滚时，需要靠undo log把数据还原成旧版本。在可重复读隔离级别下，MVCC也需要借助undo log构造历史版本数据。
简单理解就是：redo log保证“已经提交的事务不丢失”，undo log保证“没提交的事务能撤销”。两者配合，ACID里的持久性和原子性就落地了。
5. 实验九实操过程：从全量备份到按时间点恢复
5.1 开启binlog并完成一次全量备份
先修改MySQL配置文件，在[mysqld]区域添加：
server-id=1 log-bin=mysql-bin binlog_format=ROW expire_logs_days=7重启MySQL后确认binlog生效：
SHOW VARIABLES LIKE 'log_bin';接着建一个测试库和表，插入一些数据：
CREATE DATABASE recovery_lab; USE recovery_lab; CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50) NOT NULL, qty INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO orders (product_name, qty) VALUES ('苹果', 10), ('香蕉', 20);做一次全量备份：
mysqldump --single-transaction -u root -p recovery_lab > /tmp/recovery_lab_full.sql--single-transaction参数表示用InnoDB的一致性快照做备份，不会长时间锁表。这个参数在实验里非常常用，避免了备份过程中阻塞正常写入。
5.2 模拟误删，用备份加binlog恢复
全量备份完成后，继续写入一批数据：
INSERT INTO orders (product_name, qty) VALUES ('橘子', 15); INSERT INTO orders (product_name, qty) VALUES ('西瓜', 5);这时候我们假设是上午10点，记录一下当前时间。继续执行几条写操作后，故意执行误删：
DROP TABLE orders;此时数据完全丢失。先创建同名的空库：
CREATE DATABASE recovery_lab;然后恢复全量备份：
mysql -u root -p recovery_lab < /tmp/recovery_lab_full.sql此刻orders表恢复到了备份时点的状态，但备份之后插入的“橘子”和“西瓜”还不存在。接下来用binlog补上：
先找到误删操作发生的binlog位置。查看binlog列表：
SHOW BINARY LOGS;然后用mysqlbinlog定位：
mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000001 | grep -A 20 'DROP TABLE'实际操作中，最好把binlog导出成SQL文件，手动查看删除语句前后的时间点：
mysqlbinlog --start-datetime="2025-01-01 09:30:00" --stop-datetime="2025-01-01 10:00:00" \ /var/lib/mysql/mysql-bin.000001 > /tmp/recover_orders.sql这里面 10:00:00 是误删发生前的时间点。执行恢复：
mysql -u root -p < /tmp/recover_orders.sql注意恢复前先确认这个SQL文件里没有包含DROP TABLE语句，否则会把刚恢复的数据又删掉。
5.3 恢复后的一致性校验
数据恢复完成后，一定要做校验，不能看一眼表里有数据就完事。最基础的校验是：
USE recovery_lab; SELECT COUNT(*) FROM orders; SELECT * FROM orders ORDER BY id;对比误删前最后一次查询的记录条数和内容，确保同时包含两个阶段的数据：全量备份时已有的“苹果”“香蕉”，以及binlog补回来的“橘子”“西瓜”。
另外，实验里还要注意自增主键的变化。恢复后的表自增计数器不会自动回到误删前的值，mysqlbinlog重放INSERT时通常能正确推进，但如果只有部分记录，后续新插入的记录可能与历史id冲突。这个问题在常见问题部分专门讲。
6. 常见问题与排查技巧实录
6.1 并发实验时卡住不动，多半是锁等待
做实验八时经常遇到“SQL执行了半天没反应”，十有八九是行了锁。先查performance_schema.data_locks，找到阻塞的锁，再看看自己的事务有没有忘记COMMIT。
排查步骤：
- 查看当前正在执行但未完成的事务，通过
information_schema.innodb_trx查trx_state、trx_started。 - 查看锁等待关系，确定谁在等谁。
- 把占用锁的事务提交或回滚后，卡住的SQL会自动继续。
如果确定是自己开的实验事务，直接ROLLBACK最省事。调试并发实验时，我习惯把innodb_lock_wait_timeout改成3秒，避免长时间卡住影响整个实验节奏。
6.2 mysqldump备份时把业务写堵住了
有的同学做备份恢复实验时，在备份命令里加锁参数不对，导致备份过程中新的写入全部被阻塞。mysqldump默认会用锁，但配合--single-transaction可以基于一致性快照导数据，不会长时间阻塞业务。实验环境没有压力可能感觉不明显，但养成习惯很重要：
mysqldump --single-transaction --routines --triggers -u root -p 库名 > 备份文件.sql其中--routines导出存储过程，--triggers导出触发器。这两个选项在实验九涉及存储过程时特别重要，漏掉的话恢复后函数和触发器会全部丢失。
6.3 恢复后自增主键冲突
恢复完数据后，如果继续往表里插入数据，有时会遇到Duplicate entry 'xxx' for key 'PRIMARY'。原因是binlog重放时，AUTO_INCREMENT计数器没有恢复到最新值。
解决办法是在恢复后手动调整自增起点：
ALTER TABLE orders AUTO_INCREMENT = 100;生产环境恢复时会更麻烦，但实验里记住这一步就能避免很多莫名其妙的问题。
6.4 两个容易被忽略的小细节
第一个细节是时区。binlog记录时间戳时使用MySQL的系统时区，恢复时如果mysqlbinlog解析时间范围写错，很容易漏掉或多加数据。做实验时统一用本地时间，并且把--start-datetime和--stop-datetime写宽一点点，宁可多恢复几条再手动剔除，也别漏恢复。
第二个细节是事务是否提交。实验九里，如果模拟故障前有未提交事务，binlog里这部分数据未必能恢复。恢复验证时发现数据对不上，可以先查binlog里是否有COMMIT语句，很多丢失的数据都是因为事务压根没提交。
7. 最后聊几句个人体会
数据库系统的实验八和实验九，是我整个数据库课程里花时间最多的两组实验。实验八让我彻底明白了为什么业务代码里要控制事务粒度，为什么不能在一个长事务里做太多事，为什么并发高的系统要对操作顺序做约束。实验九让我建立了对备份和日志的敬畏，生产环境里的误删恢复，本质上就是实验九的放大版。
如果你刚开始做这两组实验，我的建议是：不要急着刷完报告，每个问题都亲手复现一遍。脏读、幻读、死锁、恢复丢失，这些坑在数据库里真实存在于每一套应用系统里。把实验八、九吃透，后面遇到线上事务超时、数据回滚、误删恢复，你脑子里会自然浮现出当时实验窗口里那些变化的数据行。这种体感，比背一百个概念都管用。
