技术分享 | Oracle 和 MySQL 各自的默认隔离级别及原因分析

作者：杨磊
墨天伦社区 MVP、DBASK 技术专家、ACDU 核心伙伴；某航司数据架构师，专注于数据专业领域工作近10年，一直致力于数据领域开源化、国产化、新技术研究和应用，对数据生态链中的国产数据库、开源库、数据缓存、大数据、分布式存储和计算、云计算有密切关注。 微信公众号：一森咖记。
本文来源：转载自公众号-戏说数据那点事
* 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

引言

大家都知道数据库事务四特性：原子性、一致性、隔离性和持久性，也就是人们熟知的 ACID 特性。在实际生产应用中，数据库中的数据是要被多用户共享/访问，而在多个用户同时操作相同数据时，可能会出现一些事务的并发问题，这就就有了事务隔离性的四种不同级别。今天，本文将围绕事务的特性、并发问题、隔离级别进行集中学习。

之前的一篇相关推文介绍过 MySQL 的三种锁：

《浅谈MySQL三种锁：全局锁、表锁和行锁》

首先回顾下事物的概念：

一个事务中的一系列的处理操作操作要么全部成功，要么一个都不做。在数据库操作中，一项事务（Transaction）是由一条或多条操作数据库的 SQL 语句组成的一个不可分割的工作单元。

那么，事务的处理结束就会有两种：

1. 当事务中的所有步骤全部成功执行时，事务提交，成功；

2. 如果其中任何一个步骤失败，该事务都将发生回滚操作，撤消已执行的所有操作。

再来看下事务的四个特性：

1）原子性（Atomic）

表示将事务中所进行的操作捆绑成一个不可分割的单元，即对事务所进行的数据修改等操作，要么全部执行，要么全不执行；如果失败，就回滚到事务开始前的状态。

2）一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

3）隔离性（Isolation）

指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4）持久性（Durability）

持久性也称永久性（permanence），持久性就是指如果事务一旦被提交，数据库中数据的改变就是永久性的，即使断电或者宕机的情况下，也不会丢失提交的事务操作。

本文主讲隔离性，这里再把事务的隔离性（Isolation）单拉出来说明下：隔离性是指，多个用户的并发事务访问同一个数据库时，一个用户的事务不应该被其他用户的事务干扰，多个并发事务之间要相互隔离。

事务隔离性的重要性：

事务的隔离性主要是从提升数据库的数据处理速度，即并发角度考虑的；换句话说，事务隔离性和整体数据库的性能/并发执行，有直接决定性作用。

一个思维模式：想要理解一个知识点，就假设如果没有该点会有什么发生；事务隔离性也是，如果事务中没有隔离性这个概念，会发生点啥事？

在实际应用中，数据库中性能的好坏标准之一就是数据能被尽可能多的用户共同访问，当多个用户同时处理同一数据时，可能就会出现一些事务的并发问题，导致如下四种情况出现：

1）脏读

指一个事务读取到另一个事务未提交的数据。

2）不可重复读

指一个事务对同一行数据重复读取两次，但得到的结果不同。

举例：事务 1 读取表的一条数据期间，事务 2 更新了该条记录并提交，事务 1 再次读取该表该条记录时，发现和第一次内容不一致。

3）幻读

指一个事务执行两次查询，但第二次查询的结果包含了第一次查询中未出现的数据。

举例：事务 1 读取一个表期间，事务 2 对表做了 delete/update/insert 操作并提交，事务 1 再次读取表时，此时读取到事务 2 操作的记录，两次操作结果不一致。

这里，有朋友会问，不可重复读和幻读这不一样吗？

答案为：不一样。有啥区别？

幻读和不可重复读都是读取了另一条已经提交的事务；但不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

不可重复读和幻读是初学者不易分清的概念；简单来说，解决不可重复读的方法是大家常说的加行锁，解决幻读方式是加表锁。

4）丢失更新

指两个事务同时更新一行数据，后提交（或撤销）的事务将之前事务提交的数据覆盖了。

注意：丢失更新可分为两类，分别为第一类丢失更新和第二类丢失更新。

第一类丢失更新：两个事务同时操作同一个数据时，当第一个事务撤销时，把已经提交的第二个事务的更新数据覆盖了，第二个事务就造成了数据丢失。

第二类丢失更新：当两个事务同时操作同一个数据时，第一个事务将修改结果成功提交后，对第二个事务已经提交的修改结果进行了覆盖，对第二个事务造成了数据丢失。

可以看出，上述问题，均是在并发情况下发生的，并发度越高，上述出现的情况也普遍。

怎么办？

为了避免上述事务并发问题的出现，标准 SQL 规范定义了四种事务隔离级别，不同的隔离级别对事务的处理有所不同。这四种事务的隔离级别如下：

1）Read Uncommitted（读未提交）

一个事务在执行过程中，既读取其他事务未提交的数据，又可以读取本事务未提交的修改数据。一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据。此隔离级别可防止丢失更新。

但因为能读取到其他事务未修改的数据，即不能防止“脏读”。这种事务隔离级别下，select 语句不加锁。此时，可能读取到不一致的数据。

读未提交是并发最高，但一致性也最差的隔离级别。

2）Read Committed（读已提交）

此隔离级别可有效防止脏读。

在该隔离级别下，不允许 2 个未提交的事务之间并行执行，但它允许在一个事务执行的过程中，另外一个事务得到执行并提交。读取数据的一个事务不会禁止其他写事务，读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。此隔离级别不能解决不可重复读问题。

该方式是 oracle 数据库默认的隔离级别，事务提交需手动进行。

注意，在互联网大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

原因如下：

“读未提交”虽说有最高的并行执行度，但大量的“脏读”是不被用户认可的；互联网场景下，经常会有大量的读写操作，当有大量写操作未提交时，会限制其他事务对数据的任何访问，这对互联网需要访问热点数据的需求下显得极为不够友好。

3）Repeatable Read（可重复读取）

该隔离级别可解决不可重复读的问题。在该隔离级别下，在一个事务使用某行的数据的过程中，不允许别的事务再对该行数据进行操作。可重复读是给数据库的行加上了锁。

读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。这里的事务读数据时禁止其他进程写，就保障了一个事务的可重复读性。

但是不能解决幻读，为啥？看个场景

在可重复读取隔离级别下，因为只是对一个事务写操作的行加了行锁，但依旧允许别的事务在该表其他行插入和删除数据，于是就会出现，在事务 1 执行的过程中，如果先后两次 select 出符合某个条件的行，如果在这两次 select 之间另一个事务得到了执行，insert 或 delete 了某些行，就会出现先后两次 select 出来的符合同一个条件的结果不一样，第一次 select 好像出现了幻觉一样，因此，这个问题也被成为幻读。要想解决幻读问题，需要将数据库的隔离级别设置为串行化。

4）Serializable（可串行化）

提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。此隔离级别可有效防止脏读、不可重复读和幻读。

但这个级别可能导致大量的超时现象和锁竞争，在实际应用中很少使用。

Serializable（可串行化）隔离级别可避免 脏读、不可重复读、幻读 的发生，但失去了并发效率。

介绍完四种隔离级别，这里小结下：

隔离级别

脏读

不可重复读

幻读

读未提交（Read uncommitted）

√

√

√

读已提交（Read committed）

×

√

√

可重复读（Repeated read）

×

×

√

可串行化（Serializable）

×

×

×

Oracle 数据库只支持 Serializable（串行化）级别和 Read committed（读已提交）两种级别，默认隔离级别为 Read committed（读已提交）级别；

MySQL数据库中支持上面四种隔离级别，默认隔离级别为 Repeatable read（可重复读）。

这里可以反问一个问题：

为啥 Oracle 默认隔离级别是 Read committed（读已提交）？

为啥 MySQL 默认隔离级别是 Repeatable read（可重复读）？

个人推断原因如下：

1. 时代背景分析：

Oracle 为商业数据库，服务对象面临的是传统行业。传统场景下，事务的增删改并不是很频繁，通常读和写比较均衡，且写操作是比较常见的一种 DML 方式，故在读写选择时做了折中处理，在一个事务在读操作时，允许其他事务做写操作。

Oracle 数据库只支持 Serializable（串行化）和 Read committed（读已提交）两种隔离级别，串行化不支持并发，为了保障较好的服务体验，必须保障一定的并发性，于是便默认选择 Read committed（读已提交）隔离模式；但这种模式确实会发生不可重复读和幻读的现风险。这是为什么在 Oracle 中更新一张大表时，常规操作时会依据一定条件做批量写提交，减少其他读事务的不可重复读现象。

2. 场景原因分析：

随着硬件工艺和制作成本的降低，互联网大并发读访问需求下，越来越多的开源库架构开始使用 share-nothing 的 MPP 架构，MySQL 隔离级别之所以选定为 Repeatable read（可重复读）；原因为要能最大限度的满足互联网场景下的高并发访问多次读的需求；且往往在大并发读场景下，分布式架构能有效把读操作进行分库分表式的方法访问，无形中增加了读操作的并行处理能力；

MySQL 在面临大量的写操作时，Repeatable read（可重复读）隔离级别就显得很不友好；一是一个读事务在读取一些行数据时会禁止对这些行的写事务（但允许读事务）；二是一个写事务会禁止其他任何其他事务操作。

MySQL 事务提交方式为默认提交，即 MySQL 执行每一条增删改 DML 语句后会默认自动提交，对行锁的获取和释放均很快操作结束，最大程度降低了读事务和写事物的冲突；因 MySQL 是 share-nothing 设计架构，自动提交事务的方式也能最大限度的保障从节点能和主库保持数据一致性。

MySQL 分布式架构也把读和写的操作分散在不同的节点上，也从另一方面降低了读事务和写事务的冲突，从而保障了前段业务的可用性。

但如下场景会是一个头疼的事：当读事务访问的某些数据行和写事务访问的某些行均落在分布式架构的一个节点上，会引起资源争用，这个时候只能等待事务完成，释放资源；或者杀掉某个事务会话，迫使其释放资源，让另一个事务完成后再行执行。

3. 底层设计思维的不同：

先说 oracle 架构设计思维：

大家都知道 oracle 是基于 share-disk 的设计思维，存储节点只有一份，控制文件、redo 日志、归档日志、数据文件均在共享盘阵上。RAC 架构的数据一致性在计算节点间的内存层 buffer cache 进行保证，然后落盘 redo 日志。因为是在内存级别保证数据一致性，且 redo 是顺序化的写入，故处理速度会非常快，Oracle 在一个事务提交后，会依据如下条生成 redo 日志，redo 日志记录的是发生变化的数据块，包含已经提交和未提交的（The redo log records all changes made to data, including both uncommitted and committed changes.）。Redo 的功能主要通过 3 个组件来实现：Redo Log Buffer、LGWR 后台进程和 Redo Log File。

因为 redo 日志的刷新机制速度快且较为频繁（见下文），故 Oracle 虽然采用 Read committed（读已提交），也能最大限度的减少其他读事务的不可重复读现象。

Redo Log Buffer：如果数据需要写到在线重做日志中，则在写至磁盘之前要在 Redo Buffer 中临时缓存这些数据。由于内存到内存的传输比内存到磁盘的传输快得多，因此使用重做日志 Buffer 可以加快数据库的操作。数据在重做缓冲区的停留时间不会太长。实际上 LGWR 会在以下某个情况发生时启动对这个区的刷新输出(flush):

* 每 3 秒一次

* 无论何时有人提交请求

* 要求 LGWR 切换日志文件

* 重做缓冲区 1/3 满，或者包含了 1MB 的缓存重做日志数据

再谈 MySQL 设计思维：

MySQL 是基于 share-nothing 的设计思维，所有的计算节点和存储节点为自身独享，通过网络来保持主从间的同步。不像 oracle 的 share-disk 共享存储的设计架构（所有数据共享一份数据存储）；MySQL 主从节点为保持数据的一致性，须尽快将主库事务落盘，通过网络把主库的 bin-log 日志传送至从库，从库根据中继日志 relay-log 中抽取的 sql 重新执行，达到主从库数据一致性，然后才能满足业务对从库的数据读取，实现读写分离。为此，MySQL 主库在执行完一个增删改的 DML 操作时，默认进行提交，有助于主库尽快将该事务通过网络同步至从库；也有助于降低读事务和写事务的冲突。

通常，MySQL 主从架构在通过半同步方式强化主从库的数据一致性；Innodb cluster 使用 paxos 协议（二阶段提交）来保证集群环境的数据一致性。

总结：

1. 四种隔离级别最高的是 Serializable 级别，最低的是 Read uncommitted 级别，当然级别越高，执行效率就越低。像 Serializable 这样的级别，就是以锁表的方式（类似于 Java 多线程中的锁）使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在 MySQL 数据库中默认的隔离级别为 Repeatable read（可重复读）；

2. 在 MySQL 数据库中，支持上面四种隔离级别，默认的为 Repeatable read（可重复读）；而在 Oracle 数据库 中，只支持 Serializable（串行化）级别和 Read committed（读已提交）这两种级别，其中默认的为 Read committed（读已提交） 级别；

3. 通常来说，事务的隔离级别越高，越能保证数据库的完整性和一致性，但相对来说，隔离级别越高，对并发性能的影响也越大。因此，Oracle 通常将数据库的隔离级别设置为 Read Committed，即读已提交数据，它既能防止脏读，又能有较好的并发性能。虽然这种隔离级别会导致不可重复读、幻读和第二类丢失更新这些并发问题，但可通过在应用程序中采用悲观锁和乐观锁加以控制；

4. 最后，对 Oracle 默认隔离级别为什么是 Read committed（读已提交）和 MySQL 默认隔离级别为什么是 Repeatable read （可重复读）的原因分析在时代背景、场景演变、底层数据库设计逻辑的三个角度进行了个人解读；大家如有疑问和新的看法，欢迎来交流。

参考：
http://c.biancheng.net/view/4220.html
https://blog.csdn.net/li_canhui/article/details/83058068
https://blog.csdn.net/u010960184/article/details/82557978