一文看懂mysql数据库事务隔离级别

概述

我们都知道除了MySQL默认采用RR隔离级别之外，其它几大数据库都是采用RC隔离级别。那为啥mysql要这样设置呢？其实是MySQL为了规避一个数据复制场景中的缺陷，而选择 Repeatable Read 作为默认隔离级别。不过不同数据库实现方式还是不太一样。

• Oracle仅仅实现了RC 和
  SERIALIZABLE隔离级别。默认采用RC隔离级别，解决了脏读。但是允许不可重复读和幻读。其SERIALIZABLE则解决了脏读、不可重复读、幻读。
• MySQL的实现：MySQL默认采用RR隔离级别，SQL标准是要求RR解决不可重复读的问题，但是因为MySQL采用了gap lock，所以实际上MySQL的RR隔离级别也解决了幻读的问题。

那么MySQL的SERIALIZABLE是怎么回事呢？其实MySQL的SERIALIZABLE采用了经典的实现方式，对读和写都加锁。

一、数据库事务管理

事务处理之父Jim Gray对事务隔离性的定义[1]：

Isolation: Concurrently executing transactions see the stored information as if they were running serially (one after another).

并发执行的事务能够看到存储的信息，好似事务之间是串行执行的。这里看似还是太理论，但是只要解决三个并发问题，事务间就不会有环（cycle），因此就能达到真正隔离性的要求。而这三个问题Jim Gray将其总结为Lost Update、Read Uncommitted、Unrepeatable Read。
越高的隔离级别，能解决的数据一致性问题越多，理论上性能损耗更大，可并发性越低。隔离级别依次为

SERIALIZABLE > RR > RC > Read-Uncommited

1、数据库事务ACID特性

数据库事务的4个特性：
原子性(Atomic): 事务中的多个操作，不可分割，要么都成功，要么都失败； All or Nothing.
一致性(Consistency): 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后，总金额不变；
隔离性(Isolation): 多个事务之间就像是串行执行一样，不相互影响;
持久性(Durability): 事务提交后被持久化到永久存储.

2、事务隔离性

其中 隔离性 分为了四种（注意是层层递进）：

READ UNCOMMITTED：可以读取未提交的数据，未提交的数据称为脏数据，所以又称脏读。此时：幻读，不可重复读和脏读均允许；
READ COMMITTED：只能读取已经提交的数据；此时：允许幻读和不可重复读，但不允许脏读，所以RC隔离级别要求解决脏读；
REPEATABLE READ（真正符合隔离性的要求）：同一个事务中多次执行同一个select,读取到的数据没有发生改变；此时：允许幻读，但不允许不可重复读和脏读，所以RR隔离级别要求解决不可重复读；
SERIALIZABLE: 幻读，不可重复读和脏读都不允许，所以serializable要求解决幻读；

3、脏读、不可重复读以及可重复读概念

脏读：可以读取未提交的数据。RC 要求解决脏读；

不可重复读：同一个事务中多次执行同一个select, 读取到的数据发生了改变(被其它事务update并且提交)；

可重复读：同一个事务中多次执行同一个select, 读取到的数据没有发生改变(一般使用MVCC实现)；RR各级级别要求达到可重复读的标准；

幻读：同一个事务中多次执行同一个select, 读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务delete/insert并且提交)。SERIALIZABLE要求解决幻读问题；

这里一定要区分 不可重复读和幻读：

不可重复读的重点是修改:
同样的条件的select, 你读取过的数据, 再次读取出来发现值不一样了
幻读的重点在于新增或者删除:
同样的条件的select, 第1次和第2次读出来的记录数不一样

从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大：
对于前者, 在RC下只需要锁住满足条件的记录，就可以避免被其它事务修改，也就是 select for update, select in share mode; RR隔离下使用MVCC实现可重复读；
对于后者, 要锁住满足条件的记录及所有这些记录之间的gap，也就是需要 gap lock。

而ANSI SQL标准没有从隔离程度进行定义，而是定义了事务的隔离级别，同时定义了不同事务隔离级别解决的三大并发问题：

二、MySQL数据库中RC和RR隔离级别的区别

MySQL数据库中默认隔离级别为RR，但是实际情况是使用RC 和 RR隔离级别的都不少。好像淘宝、网易都是使用的 RC 隔离级别。那么在MySQL中 RC 和 RR有什么区别呢？

1、RC 与 RR 在锁方面的区别

1)RR 支持 gap lock(next-key lock)，而RC则没有gap lock。

因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；

2)RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，即使不符合where条件的记录，也不会是否行锁和gap lock；所以从锁方面来看，RC的并发应该要好于RR；另外 insert into t select … from s where 语句在s表上的锁也是不一样的

2、RC 与 RR 在复制方面的区别

RC 隔离级别不支持 statement 格式的bin log，因为该格式的复制，会导致主从数据的不一致；只能使用 mixed 或者 row 格式的bin log; 这也是为什么MySQL默认使用RR隔离级别的原因。复制时最好使用：binlog_format=row。

3、 RC 与 RR 在一致性读方面的区别

RC隔离级别时，事务中的每一条select语句会读取到他自己执行时已经提交了的记录，也就是每一条select都有自己的一致性读ReadView; 而RR隔离级别时，事务中的一致性读的ReadView是以第一条select语句的运行时，作为本事务的一致性读snapshot的建立时间点的。只能读取该时间点之前已经提交的数据。

4、RC 支持半一致性读，RR不支持

RC隔离级别下的update语句，使用的是半一致性读(semi consistent)；而RR隔离级别的update语句使用的是当前读；当前读会发生锁的阻塞。

》》半一致性读：

A type of read operation used for UPDATE statements, that is a combination of read committed and consistent read. When an UPDATE statement examines a row that is already locked, InnoDB returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again, and this time InnoDB either locks it or waits for a lock on it. This type of read operation can only happen when the transaction has the read committed isolation level, or when the innodb_locks_unsafe_for_binlog option is enabled.

简单来说，semi-consistent read是read committed与consistent read两者的结合。一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足 update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。semi-consistent read只会发生在read committed隔离级别下，或者是参数innodb_locks_unsafe_for_binlog被设置为true(该参数即将被废弃)。

对比RR隔离级别，update语句会使用当前读，如果一行被锁定了，那么此时会被阻塞，发生锁等待。而不会读取最新的提交版本，然后来判断是否符合where条件。

半一致性读的优点：

• 减少了update语句时行锁的冲突；对于不满足update更新条件的记录，可以提前放锁，减少并发冲突的概率。

三、为什么MySQL将RR作为默认的隔离级别？选哪种级别比较好？

关于MySQL数据库为什么要将RR作为默认的隔离级别？我们项目中应该选哪种隔离级别这个问题，我们这里就要从binlog开始说起了。

1、binary log 的主要用途

MySQL的 binary log 记录了数据库的“事件”。这些“事件”描述了数据库的改动，包括 表的创建、数据修改等。

binary log 主要用于：

• 主从复制。主库 master 会将其 binary log 中的数据改动记录（“事件”）发送给 从库slave。从库执行这些“事件”使数据与主库保持一致。
• 数据恢复。
  

2、binary log 的三种模式

binary log 有三种模式：

1）、STATEMENT
每一条会修改数据的sql都会记录在binlog中。

e.g：delete from t1 where id<=1000;

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。只需要记录在 master 上所执行的语句的细节，以及执行语句时候的上下文的信息。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数，rand()函数等会出现问题warning)。

2）ROW

不记录sql语句上下文相关信息，仅保存哪条记录被修改，也就是说日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。

eg:
delete from t1 where id=1;
delete from t1 where id=2;
……
delete from t1 where id=1000;

优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

缺点：在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

3）MIXED
是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种；
新版本的MySQL中对row模式也被做了优化，并不是所有的修改都会以rowl来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

3、基于Statement的数据复制问题

上述两个事务的隔离级别都是 Read-Committed。
真实的执行顺序是 先删后插。而 binlog 中记录的 Statement 顺序为 先插后删。
当基于这份 Statement 格式的 binlog 进行主从复制或恢复数据时，得到的最终数据与实际情况不符。

4、针对数据复制问题的两种解决方法：

选用 Row 模式 的 binlog
针对实际数据行的记录当然可以准确反映改动历史
选用 Repeatable-Read 事务隔离级别
“可重复读”隔离级别下，delete、insert 等语句会对数据加 间隙锁。上述Session2中的insert语句将被阻塞，直到session1 commit;
因为早期MySQL只支持 Statement 格式的 binlog，所以只能采用 Repeatable-Read 隔离级别来规避数据不一致的风险。从 5.7.7 开始，MySQL的默认 binlog 格式已经是 ROW

5、项目应该选哪种事务隔离级别？
大多数项目中可以选 Read-Committed（读已提交），既降低死锁几率，又能提升并发性能。同时选用 Row 模式的binlog。
当然，这类 “最终一致性”的事务解决方案可能无法满足某些特殊项目的需求，需要采用 Serializable （串行）的隔离级别。

四、基于实验测试RR和RC不同事务隔离级别

MySQL使用可重复读来作为默认隔离级别的主要原因是语句级的Binlog。RR能提供SQL语句的写可串行化，保证了绝大部分情况（不安全语句除外）的DB/DR一致。MySQL 5.1以前，Statement是Binlog的默认格式，即依次记录系统接受的SQL请求；5.1及以后，MySQL提供了Row和Mixed两个Binlog格式。

从MySQL 5.1开始，如果打开语句级Binlog，就不支持RC和Read-Uncommited隔离级别。要想使用RC隔离级别，必须使用Mixed或Row格式。
下面以mysql5.7版本（默认binlog格式为row，事务隔离级别为RR）开始测试

1、binlog_format=row,事务隔离级别=RC

测试如下：

由以上测试知，RC隔离级别下，会话2执行时序在会话1事务的语句之间，并且会话2的操作影响了会话1的结果，这会对Binlog结果造成影响。

由于Binlog中语句的顺序以commit为序，两会话的执行时序是：

--实时查看指定binlog文件的内容
python  binlog2sql.py -h172.26.151.78 -uroot -p fswl\@1234 -d test --start-file='mysql-bin.000031' |grep test
================================================================================================================
DELETE FROM `test`.`t1` WHERE `c2`=2 AND `c1`=2 LIMIT 1; #start 283018 end 283245 time 2020-05-21 10:50:57
UPDATE `test`.`t2` SET `c2`=3, `c1`=1 WHERE `c2`=1 AND `c1`=1 LIMIT 1; #start 283689 end 283944 time 2020-05-21 10:50:03
UPDATE `test`.`t2` SET `c2`=3, `c1`=2 WHERE `c2`=2 AND `c1`=2 LIMIT 1; #start 283689 end 283944 time 2020-05-21 10:50:03
UPDATE `test`.`t2` SET `c2`=4, `c1`=1 WHERE `c2`=3 AND `c1`=1 LIMIT 1; #start 283689 end 284044 time 2020-05-21 10:51:06

2、binlog_format=row,事务隔离级别=RR

测试如下：

在RR中，会话1语句update t2 set c2 = 3 where c1 in (select c1 from t1)会先在t1的记录上S锁（5.1的RC中不会上这个锁，但5.0的RC会），接着在t2的满足条件的记录上X锁。由于会话1没提交，会话2的delete语句需要等待会话1的S锁释放，于是阻塞。

因此，在RR中，以上测试会话1、会话2的依次执行，与Binlog的顺序一致，从而保证DB/DR一致。

由于Binlog中语句的顺序以commit为序，两会话的执行时序是：

--实时查看指定binlog文件的内容
python  binlog2sql.py -h172.26.151.78 -uroot -p fswl\@1234 -d test --start-file='mysql-bin.000031' |grep test
================================================================================================================
UPDATE `test`.`t2` SET `c2`=3, `c1`=1 WHERE `c2`=1 AND `c1`=1 LIMIT 1; #start 3986 end 4241 time 2020-05-21 11:09:30
UPDATE `test`.`t2` SET `c2`=3, `c1`=2 WHERE `c2`=2 AND `c1`=2 LIMIT 1; #start 3986 end 4241 time 2020-05-21 11:09:30
UPDATE `test`.`t2` SET `c2`=4, `c1`=1 WHERE `c2`=3 AND `c1`=1 LIMIT 1; #start 3986 end 4359 time 2020-05-21 11:10:22
UPDATE `test`.`t2` SET `c2`=4, `c1`=2 WHERE `c2`=3 AND `c1`=2 LIMIT 1; #start 3986 end 4359 time 2020-05-21 11:10:22
DELETE FROM `test`.`t1` WHERE `c2`=2 AND `c1`=2 LIMIT 1; #start 4390 end 4617 time 2020-05-21 11:10:12