聊聊MySQL 为啥会使用可重复读（Repeatable-Read）作为默认隔离级别

今天主要来聊下mysql 选择可重复读作为默认的隔离级别的原因

1. 什么是可重复读？

简单来说就是在同一个事务中多次读取同样的记录的结果是一样的，Innodb使用MVCC来实现。这里就不展开说了。

我们知道大多数数据库系统比如：Oracle、SQLServer等数据库默认的事务隔离级别是提交读，但是MySQL不是。MySQL 把可重复读作为默认的隔离级别，这和数据库二进制文件的格式有关，我们知道对于数据库的修改操作会记录到一个二进制文件里面（当然，需要开启），这个二进制文件的作用主要在于服务器故障后可以进行基于时间点的数据恢复，另外就是用于主从复制。下面来看下bin_log 主要有哪几种类型的格式。

2. 二进制文件的几种格式

statement 记录的是可能改变数据sql语句

row 记录的是每行数据的变更

mix 混合型，默认会使用statement格式记录二进制文件，在某些特殊情况（比如使用了一些不确定性的函数（rand()，UUID()，user() 等，这个时候可能会导致主从数据不一致的情况。）使用row。

另一方面，如果使用提交读（Read Committed）作为默认的隔离级别且二进制文件的格式是statement的，主从同步的时候会导致部分丢失更新的现象。 下面我们来具体解释下为啥出现这种情况。

读提交（Read Committed）：大部分数据库采用的默认隔离级别。一个事务的更新操作结果只有在该事务提交之后，另一个事务才可以读取到同一笔数据更新后的结果。
丢失更新：两个不同事物同时获得相同数据，然后在各自事务中同时修改了该数据，那么先提交的事务更新会被后提交事务的更新给覆盖掉，这种情况事务A的更新就被覆盖掉了、丢失了。

3. 测试

我们先来看下在Read Committed （可提交读）的情况下会产生的问题。为了测试暂时关闭binlog（二进制文件），不然写binlog文件会报错。
下面来看个例子：
先来创建两张测试表

create table t1 (c1 int,c2 int) engine = innodb;
create table t1 (c1 int,c2 int) engine = innodb;
insert into t1  values (1,1),(2,2);
insert into t2  values (1,1),(2,2);

并发事务.png

在上面的测试中我们发现在可提交读这个隔离级别下，session2的操作穿插在session1的执行过程中，并最终影响了session1的执行结果。
由于binlog中的语句以commit为序，如果bin log 的格式是Statement (sql语句)，两个会话的执行顺序是：

// session2;
set tx_isolation='read-committed';
delete from t1 where c1 = 2;
commit;

//session1
set tx_isolation='read-committed';
Begin;
update t2 set c2 = 3 where c1 in (select c1 from t1);
update t2 set c2 = 4 where c1 in (select c1 from t1);
select * from t2;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    4 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)
commit;

由此可知，在提交读这个隔离级别下，语句级bin log的执行结果是不正确的。
下面我们在REPEATABLE-READ（可重复读）下重新上面的测试。

并发事务.png

由于session2在执行delete操作时被阻塞了，直到session1提交完才能执行。在可重复读隔离级别下，session1中的update t2 set c2 = 3 where c1 in (select c1 from t1); 这条语句时会给c1中的记录加行锁（实验表明：t1和t2表共同的那些记录），使得session2中的删除操作被阻塞直到session1提交完成，这样的话就会使得binlog中记录的sql语句顺序和执行的顺序是一致的，从而避免主从同步时丢失更新的现象。

共享锁【S锁】：又称为读锁，若事务T对数据对象A加上S锁，则事务T可以读A但是不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。
排他锁【X锁】：又称写锁，若事务T对数据对象A加上X锁，事务T可以读A也可以修改A,其他事务不能再对A加任何锁，直到T释>放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
间隙锁【gap lock】: 锁加在不存在的空闲空间，可以是两个索引记录之间，也可以是第一个索引记录之前或者最后一个索引记录之后的空间。唯一作用是防止其他事务的插入操作，以此来达到防止幻读的发生，所以间隙锁部分共享锁和排他锁。
Next-Key Lock:行锁与间隙锁组合起来就叫做Next-Key Lock。

4. 总结：

MySQL使用可重复读（Repeatable-Read）作为默认的存储引擎主要是因为老版本的MySQL二进制文件的各种只支持statement这一种格式，这种格式的二进制文件如果使用提交读（Read-Committed）作为默认的隔离级别会导致丢失更新的问题，所以MySQL使用了可重复读（Repeatable-Read）作为默认的存储引擎。如果把二进制文件的格式改成row,那也可以使用提交读（Read-Committed）作为隔离级别，可以提高数据库的并发性能。

注：这里的实验没有开启间隙锁。查看间隙锁是否开启：show variables like 'innodb_locks_unsafe_for_binlog'; 如果值为off则表示没有开启。

先写到这里，如果有小伙伴需要补充的欢迎在下面给我留言哦，看到会及时回复的。