共计 7270 个字符，预计需要花费 19 分钟才能阅读完成。
一般的 DBMS 系统，默认都会使用读提交（Read-Comitted，RC）作为默认隔离级别，如 Oracle、SQL Server 等，而 MySQL 却使用可重复读（Read-Repeatable，RR）。要知道，越高的隔离级别，能解决的数据一致性问题越多，理论上性能损耗更大，可并发性越低。隔离级别依次为
SERIALIZABLE > RR > RC > Read-Uncommited
在 SQL 标准中，前三种隔离级别分别解决了幻象读、不可重复读和脏读的问题。那么，为什么 MySQL 使用可重复读作为默认隔离级别呢？
1. 从 Binlog 说起
Binlog是 MySQL 的逻辑操作日志，广泛应用于复制和恢复。MySQL 5.1以前，Statement是 Binlog 的默认格式，即依次记录系统接受的 SQL 请求；5.1及以后，MySQL提供了 Row 和Mixed两个 Binlog 格式。
从 MySQL 5.1 开始，如果打开语句级 Binlog，就不支持RC 和Read-Uncommited隔离级别。要想使用 RC 隔离级别，必须使用 Mixed 或Row格式。
mysql> set tx_isolation=’read-committed’; Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1,1); ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level ‘READ-COMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’ |
那么，为什么 RC 隔离级别不支持语句级 Binlog 呢？我们关闭binlog，做以下测试。
会话1 | 会话2 |
use test; #初始化数据 create table t1(c1 int, c2 int) engine=innodb; create table t2(c1 int, c2 int) engine=innodb;
insert into t1 values(1,1), (2,2); insert into t2 values(1,1), (2,2);
#设置隔离级别 set tx_isolation=’read-committed’; Query OK, 0 rows affected (0.00 sec)
#连续更新两次 mysql> Begin; Query OK, 0 rows affected (0.03 sec)
mysql> update t2 set c2 = 3 where c1 in (select c1 from t1); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> update t2 set c2 = 4 where c1 in (select c1 from t1); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t2; +——+——+ | c1 | c2 | +——+——+ | 1 | 4 | | 2 | 3 | +——+——+ 2 rows in set (0.00 sec)
mysql> commit; |
#设置隔离级别 set tx_isolation=’read-committed’; Query OK, 0 rows affected (0.00 sec)
#两次更新之间执行删除 mysql> delete from t1 where c1 = 2; Query OK, 1 row affected (0.03 sec)
|
由以上测试知，RC隔离级别下，会话 2 执行时序在会话 1 事务的语句之间，并且会话 2 的操作影响了会话 1 的结果，这会对 Binlog 结果造成影响。
由于 Binlog 中语句的顺序以 commit 为序，如果语句级 Binlog 允许，两会话的执行时序是
#会话2 set tx_isolation=’read-committed’; delete from t1 where c1 = 2; commit;
#会话1 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; |
由上可知，在 MySQL 5.1 及以上的 RC 隔离级别下，语句级 Binlog 在DR上执行的结果是不正确的！
那么，MySQL 5.0呢？5.0允许 RC 下语句级 Binlog，是不是说很容易产生DB/DR 不一致呢？
事实上，在 5.0 重复上述一个测试，并不存在这个问题，原因是 5.0 的RC与 5.1 的RR使用类似的并发和上锁机制 ，也就是说，MySQL 5.0 的RC与 5.1 及以上的 RC 可能存在兼容性问题。
下面看看 RR 是怎么解决这个问题的。
2. 默认隔离级别 – 可重复读
导致 RC 隔离级别 DB/DR 不一致的原因是：RC不可重复读，而 Binlog 要求 SQL 串行化！
在 RR 下，重复以上测试
会话1 | 会话2 |
use test; #初始化数据 create table t1(c1 int, c2 int) engine=innodb; create table t2(c1 int, c2 int) engine=innodb;
insert into t1 values(1,1), (2,2); insert into t2 values(1,1), (2,2);
#设置隔离级别 set tx_isolation=’repeatable-read’; Query OK, 0 rows affected (0.00 sec)
#连续更新两次 mysql> Begin; Query OK, 0 rows affected (0.03 sec)
mysql> update t2 set c2 = 3 where c1 in (select c1 from t1); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> update t2 set c2 = 4 where c1 in (select c1 from t1); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t2; +——+——+ | c1 | c2 | +——+——+ | 1 | 4 | | 2 | 4 | +——+——+ 2 rows in set (0.00 sec)
mysql> commit; |
#设置隔离级别 set tx_isolation=’ repeatable-read’; Query OK, 0 rows affected (0.00 sec)
#两次更新之间执行删除 mysql> delete from t1 where c1 = 2; – 阻塞，直到会话 1 提交
Query OK, 1 row affected (18.94 sec)
|
与 RC 隔离级别不同的是，在 RR 中，由于保证可重复读，会话 2 的delete语句会被会话 1 阻塞，直到会话 1 提交。
在 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 一致。
幻象读
除了保证可重复读，MySQL的 RR 还一定程度上避免了幻象读（幻象读是由于插入导致的新记录）。（为什么说一定程度呢？参考第 3 节可重复读和串行化的区别。）
会话1 | 会话2 |
use test; #初始化数据 create table t1(c1 int primary key, c2 int) engine=innodb; create table t2(c1 int primary key, c2 int) engine=innodb;
insert into t1 values(1,1), (10,10); insert into t2 values(1,1), (5,5), (10,10);
#设置隔离级别 set tx_isolation=’repeatable-read’; Query OK, 0 rows affected (0.00 sec)
#连续更新两次 mysql> Begin; Query OK, 0 rows affected (0.03 sec)
mysql> update t2 set c2 = 20 where c1 in (select c1 from t1); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> delete from where c1 in (select c1 from t1); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t2; +——+——+ | c1 | c2 | +——+——+ | 5 | 5 | +——+——+ 2 rows in set (0.00 sec)
mysql> commit; |
#设置隔离级别 set tx_isolation=’ repeatable-read’; Query OK, 0 rows affected (0.00 sec)
#两次更新之间执行插入 mysql> insert into t1 values(5,5); – 阻塞，直到会话 1 提交
Query OK, 1 row affected (18.94 sec)
|
由上述例子知，会话 2 的插入操作被阻塞了，原因是 RR 隔离级别中，除了记录锁外，还会上间隙锁 (gap 锁)。例如，对于表 t1，update t2 set c2 = 20 where c1 in (select c1 from t1) 以上的锁包括：
(-∞, 1), 1, (1, 10), 10, (10, +∞)
由于对 t1 做全表扫描，因此，所有记录和间隙都要上锁，其中 (x,y) 表示间隙锁，数字表示记录锁，全部都是 S 锁。会话 2 的insert操作插入 5，位于间隙(1,10)，需要获得这个间隙的X 锁，因此两操作互斥，会话 2 阻塞。
SQL标准的 RR 并不要求避免幻象读，而 InnoDB 通过 gap 锁来避免幻象，���而实现 SQL 的可串行化，保证 Binlog 的一致性。
要想取消gap lock，可使用参数innodb_lock_unsafe_for_binlog=1，默认为0。
3. 可重复读与串行化的区别
InnoDB的 RR 可以避免不可重复读和幻象读，那么与串行化有什么区别呢？
会话1 | 会话2 |
use test; #初始化数据 create table t3(c1 int primary key, c2 int) engine=innodb;
#设置隔离级别 set tx_isolation=’repeatable-read’; Query OK, 0 rows affected (0.00 sec)
mysql> Begin; Query OK, 0 rows affected (0.03 sec)
mysql> select * from t3 where c1 = 1; Empty set (0.00 sec)
mysql> select * from t3 where c1 = 1; Empty set (0.00 sec)
mysql> update t3 set c2 =2 where c1 = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t3 where c1 = 1; +—-+——+ | c1 | c2 | +—-+——+ | 1 | 2 | +—-+——+ 1 row in set (0.00 sec)
mysql> commit; |
#设置隔离级别 set tx_isolation=’ repeatable-read’; Query OK, 0 rows affected (0.00 sec)
mysql> insert into t3 values(1,1); Query OK, 1 row affected (0.05 sec)
|
由上述会话 1 中，连续两次读不到数据，但更新却成功，并且更新后的相同读操作就能读到数据了，这算不算幻读呢？
其实，RR隔离级别的防止幻象主要是针对写操作的，即 只保证写操作的可串行化，因为只有写操作影响 Binlog；而读操作是通过MVCC 来保证一致性读（无幻象）。
然而，可串行化隔离级别要求读写可串行化。使用可串行化重做以上测试。
会话1 | 会话2 |
use test; #初始化数据 create table t3(c1 int primary key, c2 int) engine=innodb;
#设置隔离级别 set tx_isolation=’SERIALIZABLE’; Query OK, 0 rows affected (0.00 sec)
mysql> Begin; Query OK, 0 rows affected (0.03 sec)
mysql> select * from t3 where c1 = 1; Empty set (0.00 sec)
mysql> select * from t3 where c1 = 1; Empty set (0.00 sec)
mysql> update t3 set c2 =2 where c1 = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from t3 where c1 = 1; Empty set (0.00 sec)
mysql> commit; |
#设置隔离级别 set tx_isolation=’SERIALIZABLE’; Query OK, 0 rows affected (0.00 sec)
mysql> insert into t3 values(1,1); #阻塞，直到会话 1 提交
Query OK, 1 row affected (48.90 sec) |
设置为串行化后，会话 2 的插入操作被阻塞。由于在串行化下，查询操作不在使用 MVCC 来保证一致读，而是使用 S 锁来阻塞其他写操作。因此做到读写可串行化，然而换来就是并发性能的 大大降低。
4. 小结
MySQL使用可重复读来作为默认隔离级别的主要原因是语句级的 Binlog。RR 能提供 SQL 语句的写可串行化，保证了绝大部分情况（不安全语句除外）的 DB/DR 一致。
另外，通过这个测试发现 MySQL 5.0 与5.1在 RC 下表现是不一样的，可能存在兼容性问题。
参考
http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-02/140847.htm
