SQL 事务隔离级别说明

SQL 标准定义了 4 类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果。

Repeatable Read（可重读）

这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read），简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的 “幻影” 行。

InnoDB 存储引擎通过 MVCC 机制（快照读）和 next-key lock（当前读）解决了该问题。

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

事务隔离带来的问题

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读 (Drity Read)：一个事务读取到另一事务未提交的更新数据。当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中（这个数据在有可能会回滚），这时，另外一个事务也访问这个数据，然后使用了这个数据。

不可重复读 (Non-repeatable read)：在一个事务内，前后两次读到的数据是不一样。在 T1 事务两次读取同一数据之间，T2 事务对该数据进行了修改，就会发生 T1 事务中的两次数据读取不一样的结果。相反， 可重复读：在同一事务中多次读取数据时，能够保证所读数据一样，也就是后续读取不能读到另一事务已提交的更新数据。

幻读 (Phantom Read)：指当事务不是独立执行时发生的一种现象，例如：T1 事务对表中的 "全部数据行" 进行了修改，同时 T2 事务向表中插入了一行 "新数据"，操作 T1 事务的用户发现表中还存在没有修改的数据行，就好象发生了幻觉一 样。一般解决幻读的方法是增加范围锁 RangeS，锁定检锁范围为只读，这样就避免了幻读。

不可重复读和幻读的异同

  • 两者都表现为两次读取的结果不一致
  • 不可重复读是由于另一个事务对数据的更改所造成的
  • 幻读是由于另一个事务插入或删除引起的
  • 对于不可重复读，只需要锁住满足条件的记录
  • 对于幻读，要锁住满足条件及其相近的记录
  • 不可重复读表达的是：记录（一行或多行）的值在同一次事务中出现两个不同的结果
  • 幻读表达的是：同一事务中查询两次得到两个不同的结果集
  • 不可重复读侧重表达 读 - 读
  • 幻读则是说 读 - 写，用写来证实读的是鬼影

关于幻读

这里给出 MySQL 在 Repeatable Read 隔离界别下幻读的比较形象的场景：

时间事务 1事务 2
T1start transaction;
select * from users where id = 1;
结果为：0
T2 start transaction;
insert into users(id, name) values (1, 'big cat');
commit;
T3insert into users(id, name) values (1, 'big cat');
主键冲突，插入失败
T4select * from users where id = 1;
结果为：0
T5rollback;

假设 users 表中 id 为主键

  • T1 的时间点事务 1 检测表中没有 id 为 1 的记录
  • T2 时间点事务 2 插入 id 为 1 的记录并提交事务
  • T3 时间点事务1 尝试插入 id 为 1 的数据时提示主键冲突
  • T4 时间点再去检查表中还是没有 id 为 1 的记录（由于 Repeatable Read 隔离级别，事务 2 的插入提交事务 1 读取不到）

MVCC

MVCC 多版本并发控制（Multiversion Concurrency Control）。

每一条记录都有一些隐藏字段，其中 trx_id（事务 id）和 roll_pointer（回滚指针）字段就和 MVCC 密切相关

版本链
每次对数据修改都会使 roll_pointer 指向生成 undo 日志，即形成了数据修改的 版本链，版本链的头节点就是当前数据的最新值。

Read View
对于 RC、RR 隔离级别来说，事务启动后在不同的时机会生成 Read View，用于判断当前记录是否在事务中可见。Read View 包含创建时刻所有活跃的 trx_id 的集合 m_ids。当判断数据是否在当前事务中显示时，需要从头到尾遍历版本链，依次取得数据的历史记录判断，直到取到符合条件的历史版本，如果所有的历史记录都不满足则忽略此条数据。

  • 当前版本数据记录的 trx_id 如果小于 m_ids 中最小的 trx_id，说明当前版本数据是在当前事务 Read View 生成之前生成的，所以对当前事务可见。
  • 当前版本数据记录的 trx_id 如果大于 m_ids 中最大的 trx_id，说明当前版本数据是在当前事务 Read View 生成之后生成的，所以对当前事务不可见。
  • 当前版本数据记录的 trx_id 如果在 m_ids 中，说明当前版本数据在当前事务 Read View 生成之前还未提交，所以对当前事务不可见。
  • 当前版本数据记录的 trx_id 如果不在 m_ids 中，说明当前版本数据是在当前事务 Read View 生成之前已提交，所以对当前事务可见。
RC 隔离级别在每次读取数据前都会生成 Read View，RR 隔离级别在第一次读取数据时生成 Read View，运用以上可见规则就很容易推断出这两种隔离级别的隔离能力了。

另外，undo 日志会在系统判断没有比它更早的 Read View 存在时就会被删除。所以当系统中存在大量长事务的时候，会导致 undo 日志不能被及时清理而占用大量的存储空间。

MySQL 隔离级别

隔离级别脏读（Dirty Read）不可重复读（NonRepeatable Read）幻读（Phantom Read）
未提交读（Read uncommitted）可能可能可能
已提交读（Read committed）不可能可能可能
可重复读（Repeatable read）不可能不可能可能
可串行化（SERIALIZABLE）不可能不可能不可能

MySQL 事务隔离级别设置

InnoDB 默认是可重复读的（REPEATABLE READ）

修改全局默认的事务级别，在 my.inf 文件的 [mysqld] 节里类似如下设置该选项（不推荐）

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

改变单个会话或者所有新进连接的隔离级别（推荐使用）

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

查询全局和会话事务隔离级别方法

#查询全局的事务隔离级别
SELECT @@global.tx_isolation;
#查询当前会话的事务级别
SELECT @@session.tx_isolation;
— The End —
Last modification：December 15, 2021
© Allow specification reprint
If you think my article is useful to you, please feel free to appreciate