外观
17 - 隔离级别解决了什么,又没有解决什么
基线:MySQL 8.0.16+ / 8.4、InnoDB。前置:16 ACID、A 实验方法。 本文解释并发事务可能出现的现象,18解释历史版本,19解释锁。下文实验均给出预期结果,适用上述数据库与存储引擎。
摘要
两个请求都先查询、再修改,即使各自的事务成功提交,也可能把余额算错或让值班人数降为零。隔离级别决定事务能看到哪些并发修改,但业务是否安全,还取决于如何根据查询结果做决定。本文用两个独立连接上的实验区分五类并发异常,再说明何时需要条件更新、锁定读或共同守卫行。
阅读入口:先比较一次读取,再看业务决策
初读按“第 3 节准备 → 第 4.2 节同一行前后两次读取 → 第 5 节符合条件的行集合 → 第 6、7 节根据读取结果做修改”推进;第 4.1 节脏读和第 7.2 节 SERIALIZABLE 是对照实验,可第二遍再做。先开 A、B 两个连接即可,C 只用于独立验收。
事务是在同一连接上,以提交或回滚结束的一组数据库操作。两个事务的存续时间可以重叠,语句按先后交错执行;并发问题并不要求两条 SQL 恰好同时运行。隔离讨论的对象是同时存在的事务如何观察和影响彼此。
先分清三个问题:重读一行余额时,值是否变化;重复筛选账户时,符合条件的行是否变化;应用根据这些结果做出的扣款或请假决定是否仍合法。前两个问题关注读取,第三个问题关注“读取 → 判断 → 修改”的完整过程。读取结果稳定,并不能自动保证这个过程安全。
1. 现象:两个事务都看到有人值班,最终却没人值班
假设科室必须至少有一名医生值班。两名医生各自申请请假,两个事务都先读到值班人数为 2,于是都判断自己可以离开,再分别把自己的值班状态改为 false。
两个事务修改不同行,不会因争用同一行而直接相互阻止;都提交后,值班人数却变成 0。这里每条 UPDATE 都可以正确执行,错误出在两次判断共同依赖“至少留一人”这条规则。这种必须始终成立的规则称为业务不变量;它涉及多行时,也称跨行约束。
下面五种现象分别描述读取或决策在哪一步出了问题。表中的“谓词”就是查询筛选条件,例如 balance>=150。
| 现象 | 核心区别 |
|---|---|
| 脏读 | 读取尚未提交的修改 |
| 不可重复读 | 重读同一行,已提交值发生变化 |
| 幻读 | 重复谓词查询,符合条件的行集合变化 |
| 丢失更新 | 根据旧读值覆盖其他写入 |
| 写偏差 | 读取共同条件、更新不同行,破坏跨行约束 |
2. 原理:标准现象与产品实现分开
四个隔离级别常译为读未提交、读已提交、可重复读和可串行化。下文用 RC 表示 READ COMMITTED,用 RR 表示 REPEATABLE READ。“可串行化”是指并发执行的效果符合某种逐个执行事务的顺序,并不要求所有请求都一次成功。
先用标准现象表理解各级别的基本边界。“可能”表示该级别允许这种现象,不表示每次执行都会出现。
| 标准隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 禁止 | 可能 | 可能 |
| REPEATABLE READ | 禁止 | 禁止 | 标准现象表中可能 |
| SERIALIZABLE | 禁止 | 禁止 | 禁止 |
这张表没有列出全部业务异常,也不是所有数据库实现的完整行为清单。理解 InnoDB 的实验前,还要区分两种读取方式:
- 普通一致性读:RC、RR 下不带锁定子句的普通
SELECT按可见性视图读取数据,必要时读取历史版本。这个视图常称为快照,它规定哪些修改可见,不是复制整张表。RC 每次普通一致性读使用新的语句快照;RR 复用事务第一次普通一致性读建立的视图。 - 锁定读:例如
SELECT ... FOR UPDATE,为后续修改读取记录并加锁。它与UPDATE常被归入“当前读”,但仍须遵守语句、锁和隔离规则;存在冲突事务时可能先等待,不能理解为忽略锁直接取得最新值。
因此,InnoDB RR 的普通读可以复用稳定快照,锁定范围读又可能通过 Next-Key Lock 阻止部分并发插入。Next-Key Lock 是结合索引记录锁与相邻间隙锁的范围加锁方式,具体范围取决于访问索引等条件,见19。这两种机制不能合并成“RR 所有 SELECT 永远一样”。其他产品所称的 Snapshot Isolation(快照隔离)也不能直接等同于 InnoDB RR。
3. 准备:独立表与连接
以下初始化会删除三张实验表中的现有数据,应使用专用实验库,并确保没有其他实验正在使用这些表。autocommit=1 表示在没有显式事务时,每条语句自动提交;START TRANSACTION 开始的事务仍需明确提交或回滚。
在没有未完成事务的初始化连接中执行;若要保留该连接,先记录原自动提交设置,否则初始化完成后结束事务并关闭连接:
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
CREATE TABLE IF NOT EXISTS isolation_balance (
id BIGINT PRIMARY KEY, balance INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS isolation_doctor (
id BIGINT PRIMARY KEY, on_call BOOLEAN NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS isolation_guard (
department_id BIGINT PRIMARY KEY
) ENGINE=InnoDB;
DELETE FROM isolation_balance;
DELETE FROM isolation_doctor;
DELETE FROM isolation_guard;
INSERT INTO isolation_balance VALUES (1,100),(2,200);
INSERT INTO isolation_doctor VALUES (1,true),(2,true);
INSERT INTO isolation_guard VALUES (1);A、B、C 使用独立物理连接。在每个连接先结束旧事务,再记录并设置本连接的环境;初始化连接若也要保留,设置自动提交之前同样记录原值。
sql
USE kb_database_lab;
SET @isolation_saved_autocommit=@@session.autocommit;
SET @isolation_saved_level=@@session.transaction_isolation;
SET autocommit=1;
SELECT CONNECTION_ID(),@@session.transaction_isolation;隔离配置在事务外设置。A、B 是执行者,C 是可选观察者;表格按行交错执行,不把 A、B 指令粘贴到同一连接。每个实验后结束事务,下一实验前重置数据,不依赖前篇同名表。
4. 验证一:脏读与不可重复读
4.1 脏读
| 步骤 | 会话 A | 会话 B |
|---|---|---|
| 1 | SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; | |
| 2 | START TRANSACTION; UPDATE isolation_balance SET balance=150 WHERE id=1; | |
| 3 | SELECT balance FROM isolation_balance WHERE id=1;,预期 150 | |
| 4 | ROLLBACK; | |
| 5 | 再 SELECT,预期 100;COMMIT; |
B 最终回滚,说明 150 并没有成为已提交的余额,但 A 已经读到了它,这就是脏读。把 A 改为 RC 后重复,普通 SELECT 不应读到 B 未提交的 150;不要改为 FOR UPDATE,否则观察的是锁等待而非脏读。
4.2 RC 与 RR 对同一次提交的差异
| 步骤 | 会话 A | 会话 B |
|---|---|---|
| 1 | SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; | |
| 2 | SELECT balance FROM isolation_balance WHERE id=1;,100 | |
| 3 | UPDATE isolation_balance SET balance=200 WHERE id=1;,自动提交 | |
| 4 | 重复步骤 2,预期 200;COMMIT; |
结束事务,把 id=1 重置为 100,A 改 RR 后重复,第二次普通 SELECT 预期仍为 100。A 若在 COMMIT 前执行下面锁定读,预期为 200:
sql
SELECT balance FROM isolation_balance WHERE id=1 FOR UPDATE;RC 第二次普通读建立新快照时,B 已提交,所以预期看到 200。RR 第一次普通读建立视图时余额为 100,第二次仍使用这个视图,所以预期仍为 100。这里假定 A 没有修改该行,其他并发修改只有表中的 B。
锁定读采用另一套读取与加锁规则。这里 B 已提交且没有新的冲突写入,所以预期看到 200;若仍有冲突事务,它可能先等待。同一事务混用普通读与锁定读时,两次查询并不承诺读取同一版本。历史版本如何支持普通读,见18 MVCC。
5. 验证二:集合变化与防幻读边界
结束事务,删除 id=3 并把 id=1、2 重置为 100、200。A 使用 RC:
sql
START TRANSACTION;
SELECT id FROM isolation_balance WHERE balance>=150 ORDER BY id;B 自动提交:
sql
INSERT INTO isolation_balance VALUES (3,180);A 重复谓词 SELECT,集合预期从 {2} 变为 {2,3};结束事务。改 RR、重置并重复时,两次普通读预期均为 {2},但 A 随后的锁定读可以看到新提交的 id=3。
这次变化发生在“符合条件的行集合”上:id=3 是新出现的匹配行,因此对应幻读,而不是已有某一行的余额变化。RR 普通读仍得到 {2},是因为旧视图不包含 B 后来提交的新行;B 的插入本身并没有被这个普通读阻止。
需要阻止范围内插入时,应检查实际使用的索引、索引记录之间的间隙,以及哪个事务正在等哪个事务的锁,见19 范围锁。不能只凭两次查询结果相同,就认定范围已被锁住。
6. 验证三:RR 仍不能修复应用旧值覆盖
结束事务并把 id=1 重置为 100。A、B 均使用 RR:
| 步骤 | 会话 A | 会话 B |
|---|---|---|
| 1 | START TRANSACTION; SELECT balance FROM isolation_balance WHERE id=1;,100 | |
| 2 | START TRANSACTION; SELECT balance FROM isolation_balance WHERE id=1;,100 | |
| 3 | UPDATE isolation_balance SET balance=90 WHERE id=1; COMMIT; | |
| 4 | UPDATE isolation_balance SET balance=80 WHERE id=1; COMMIT; |
预期最终余额为 80。A 按读到的 100 扣减 10,写入 90;B 也按先前读到的 100 扣减 20,写入 80。B 的 UPDATE 写的是已经算好的固定值,不会自动把扣减应用到 A 提交后的 90 上,因此 A 的扣减被覆盖。两条 UPDATE 即使先后执行,旧值仍可能导致丢失更新。
重置后,对照使用 SET balance=balance-10 和 SET balance=balance-20:扣减在数据库更新记录时计算,预期最终为 70。这只解决本例的扣减叠加;防透支还需在更新条件中检查余额,并检查实际影响行数。复杂计算可先锁行再读取,或写入时附带版本条件:只有记录版本仍与读取时一致才更新,否则重新读取并判断。不能仅凭 RR 这个名称判断业务安全。
7. 验证四:完整复现写偏差
重置两名医生均为 true。A、B 使用 RR:
| 步骤 | 会话 A | 会话 B |
|---|---|---|
| 1 | START TRANSACTION; SELECT COUNT(*) FROM isolation_doctor WHERE on_call=true;,2 | |
| 2 | START TRANSACTION; SELECT COUNT(*) FROM isolation_doctor WHERE on_call=true;,2 | |
| 3 | UPDATE isolation_doctor SET on_call=false WHERE id=1; COMMIT; | |
| 4 | UPDATE isolation_doctor SET on_call=false WHERE id=2; COMMIT; |
C 在自动提交模式统计,预期人数为 0。A、B 各自读到的人数都足以批准一次请假,但两次批准合起来就违反了“至少一人值班”的不变量。这与第 6 节不同:没有谁覆盖另一人的修改,而是两人修改不同行,共同破坏跨行规则,这就是写偏差。
7.1 修复:先锁共同守卫,再按当前记录计算
要让两次请假判断相互协调,可以要求它们先争用同一行。isolation_guard 中代表该科室的记录称为守卫行:拿到它的锁,才允许继续判断和修改;其他请求必须等待。
每次请假遵循同一协议,以下 SQL 是包含应用条件判断的流程片段:
sql
START TRANSACTION;
SELECT department_id FROM isolation_guard WHERE department_id=1 FOR UPDATE;
SELECT id,on_call FROM isolation_doctor ORDER BY id FOR UPDATE;应用从第二条结果统计:仅当至少两人值班且申请人仍值班,才执行对应 UPDATE 并 COMMIT;否则 ROLLBACK 并拒绝。守卫让相关决策依次进行,第二条锁定读则让等待结束后的请求根据当前记录判断。不要改用 RR 旧快照上的普通 COUNT:即使已经等到守卫,旧快照也可能仍显示之前的两人。
本例第一次请求 id=1 时,读取结果满足条件后执行:
sql
UPDATE isolation_doctor SET on_call=false WHERE id=1 AND on_call=true;
SELECT ROW_COUNT() AS affected_rows;影响恰好 1 行才 COMMIT,否则 ROLLBACK。第二个请求获取守卫后读取到仅一人值班,应直接 ROLLBACK,不执行修改。人数判断和影响行数判断都由应用完成,不能把这些片段连成无条件执行的 SQL 脚本。
对照交错:A 持守卫,B 等守卫;A 修改提交,B 获取守卫后看到只剩一人,因此拒绝。所有修改约束的入口都须使用同一个守卫,包括新增、删除与批量变更。
本例只有一个科室、两名医生,因此锁定读遍历全部医生记录。生产场景应按科室过滤并建立访问索引。一个科室的相关决策都争用同一守卫,会限制该科室的并发吞吐;需要更高并发时,应重新设计约束与数据模型,不能直接删除这个协调点。
7.2 SERIALIZABLE 对照
重置数据,在 A、B 的事务外设置 SERIALIZABLE,再按第 7 节交错。共享锁允许多个事务共同读取,但会与修改所需的排他锁冲突。显式事务中的普通 SELECT 可能取得共享锁,两边随后尝试取得写锁时,就可能相互等待或形成死锁。
这个对照不能机械地等第 7 节步骤 3 返回后才发送步骤 4:若 A 的更新被 B 持有的共享锁阻塞,应在 B 连接继续发送其更新,观察冲突结果。应接受一个事务被回滚;获胜者提交后,失败者用新事务重读人数,再按“至少留一人”的规则拒绝请假。
不能断言固定受害者或所有请求一次成功。测试以最终人数至少 1、失败者没有半笔提交为条件;实际锁证据和错误由目标实例确认。
8. 排障与工程取舍
| 需求/现象 | 先检查 | 倾向方案 |
|---|---|---|
| 多次读取稳定视图 | 隔离、快照建立时机 | RR 一致性读并限制时长 |
| 单行余额/库存 | 是否旧读值覆盖 | 条件更新或版本仲裁 |
| 跨行人数/额度 | 决策有无共同冲突点 | 守卫、可序列化或模型重构 |
| 并发插入影响决策 | 索引、隔离、读类型 | 验证范围锁或约束协议 |
| 注解与行为不符 | 实际连接、已有事务 | 核验框架传播与资源绑定 |
RC 的语句快照通常更短,但大查询和长事务仍有成本;RR 不替代业务冲突检测。选型同时看正确性、锁范围、清理压力、失败率与吞吐。
Spring 传播行为描述事务边界组合,不等于隔离;副本一致性描述节点间读写承诺,也不是本地隔离。Oracle 等产品不能按本文实验直接推断。
9. 总结与导航
隔离级别回答允许什么交错,业务协议回答哪些交错仍然合法。
- 实现:18 MVCC 与 Read View。
- 锁证据:19 SELECT 与锁。
- 失败恢复:20 死锁与重试。
- 参考资料:MySQL Reference Manual 的 Transaction Isolation Levels、Consistent Nonlocking Reads、Locking Reads。标准现象表不等于具体产品的完整能力矩阵。
10. 实验退出与名词解释
每个参与连接主动结束事务,确认没有等待中的语句;错误后也要检查事务是否仍存在。然后在各自原连接恢复设置:
sql
SET SESSION transaction_isolation=@isolation_saved_level;
SET autocommit=@isolation_saved_autocommit;用户变量只属于保存它的连接。若已经重连,不执行上述恢复,重新记录新连接环境即可。初始化连接若未保存变量,按原先记录恢复或在确认事务结束后关闭。所有参与者退出后,按需只删除 isolation_balance、isolation_doctor、isolation_guard 三张实验表;删表不代替结束其他连接事务。
| 名词 | 本文含义 |
|---|---|
| 隔离级别 | 数据库对并发事务读取与交错行为的约定 |
| 快照/一致性读 | 按特定可见性视图读取,可能读到历史版本 |
| 锁定读/当前读 | 为后续修改协调记录的读取,可能等待冲突锁 |
| 谓词 | 查询的筛选条件,如 balance>=150 |
| 丢失更新 | 用旧值计算后覆盖写入,抹掉其他事务的修改 |
| 写偏差 | 根据共同条件修改不同行,最终破坏跨行规则 |
| 守卫行 | 让相关决策先争用同一行,把原本分散的冲突集中起来 |
| 可串行化 | 并发结果符合某种依次执行的效果,不保证没有失败重试 |