曾多次听到“MySQL为什么选择RR为默认隔离级别”的问题，其实这是个历史遗留问题，当前以及解决，但是MySQL的各个版本沿用了原有习惯。历史版本中的问题是什么，本次就通过简单的测试来说明一下。
1、 准备工作
1.1 部署主从
部署一套主从架构的集群，创建过程较简单，可以参考历史文章部署 MySQL主从复制搭建 部署一主一从即可。
1.2 创建测试表及数据
在主库中创建表及测试数据
mysql> create table users(id int primary key auto_increment，user_name varchar(20)，c_id tinyint(4)，c_note varchar(50)，key c_id(c_id)) engine=innodb; Query OK， 0 rows affected (0.01 sec) mysql> insert into users values(1，'刘备'，2，null)，(2，'曹操'，1，null)，(3，'孙权'，3，null)，(4，'关羽'，2，null)，(5，'司马懿'，1，null); Query OK， 5 rows affected (0.00 sec) Records： 5 Duplicates： 0 Warnings： 0mysql> create table class(c_id int primary key ，c_name varchar(1)，c_note varchar(50)) engine=innodb; Query OK， 0 rows affected (0.00 sec) mysql> insert into class values(1，'魏'，null)，(2，'蜀'，null)，(3，'吴'，null)，(4，'晋'，''); Query OK， 4 rows affected (0.00 sec) Records： 4 Duplicates： 0 Warnings： 0
2、 RR隔离级别
MySQL默认的隔离级别为 RR(Repeatable Read)，在此隔离级别下，对比binlog格式为ROW、STATEMENT是否会造成主从数据不一致
2.1 ROW格式
其实不用测试大家也应该对RR级别下ROW格式的binlog有信心，但是，万事皆需实践检验。
步骤说明如下：
- 步骤1 - 分别查看两个会话中的事务隔离级别及binlog格式(隔离级别均为RR，binlog为ROW格式)
- 步骤2 - SESSION A 开启事务，更新users 表中c_id字段存在于class表中的记录，结果为 5 条记录均更新，并将c_note内容更新为 t1
- 步骤3- SESSION B 开启事务，准备删除class表中 c_id等于 2 的记录，此时无法更新，处于阻塞状态，因为在RR级别下需要保证重复读。达到所等待超时时间后将会报错。
- 步骤4- SESSION A 提交事务(此步骤也可以在步骤 3 时操作，结果不一样，后续步骤中将采用此方式)
- 步骤5- SESSION B 重启事务，再次删除class表中 c_id等于 2 的记录，此时提交可以成功了，成功删除了一条记录
- 步骤6- SESSION A 开启事务，更新users 表中c_id字段存在于class表中的记录，结果为 3 条记录更新成功，并将c_note内容更新为 t2，有 2 条记录因为c_id不存在与class表中，因此不会更新
- 步骤7- 分别在SESSON A和SESSION B查看users表中的内容，结果一致
- 步骤8- 在从库查看users表中的内容，数据与主库一致
具体步骤如下：
| 步骤 | SESSION A | SESSION B |
| 1 | mysql>show variables like '%iso%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 2 rows in set (0.00 sec) mysql>show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec) | mysql>show variables like '%iso%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 2 rows in set (0.00 sec) mysql>show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.01 sec) |
| 2 | mysql>set autocommit=0; mysql>update users set c_note='t1' where c_id in (select c_id from class); Query OK， 5 rows affected (0.00 sec) Rows matched： 5 Changed： 5 Warnings： 0 | |
| 3 | mysql>set autocommit=0; Query OK， 0 rows affected (0.00 sec) mysql>delete from class where c_id=2; ERROR 1205 (HY000)： Lock wait timeout exceeded; try restarting transaction | |
| 4 | mysql>commit; Query OK， 0 rows affected (0.00 sec) | |
| 5 | mysql>set autocommit=0; Query OK， 0 rows affected (0.00 sec) mysql>delete from class where c_id=2; Query OK， 1 row affected (0.00 sec) mysql>commit; Query OK， 0 rows affected (0.00 sec) | |
| 6 | mysql>update users set c_note='t2' where c_id in (select c_id from class); Query OK， 3 rows affected (0.00 sec) Rows matched： 3 Changed： 3 Warnings： 0 mysql>commit; Query OK， 0 rows affected (0.00 sec) | |
| 7 | mysql>select * from users; +----+-----------+------+--------+ | id | user_name | c_id | c_note | +----+-----------+------+--------+ | 1 | 刘备 | 2 | t1 | | 2 | 曹操 | 1 | t2 | | 3 | 孙权 | 3 | t2 | | 4 | 关羽 | 2 | t1 | | 5 | 司马懿 | 1 | t2 | +----+-----------+------+--------+ 5 rows in set (0.00 sec) | mysql>select * from users; +----+-----------+------+--------+ | id | user_name | c_id | c_note | +----+-----------+------+--------+ | 1 | 刘备 | 2 | t1 | | 2 | 曹操 | 1 | t2 | | 3 | 孙权 | 3 | t2 | | 4 | 关羽 | 2 | t1 | | 5 | 司马懿 | 1 | t2 | +----+-----------+------+--------+ 5 rows in set (0.00 sec) |
| 8 | 在从库查看数据 root@testdb：3307 12：02：20>select * from users; +----+-----------+------+--------+ | id | user_name | c_id | c_note | +----+-----------+------+--------+ | 1 | 刘备 | 2 | t1 | | 2 | 曹操 | 1 | t2 | | 3 | 孙权 | 3 | t2 | | 4 | 关羽 | 2 | t1 | | 5 | 司马懿 | 1 | t2 | +----+-----------+------+--------+ 5 rows in set (0.00 sec) |
2.2 STATEMENT格式
为了和之前的步骤一致，先初始化数据
root@testdb：3306 12：14：27>truncate tableusers; Query OK， 0 rows affected (0.08 sec) root@testdb：3306 12：14：29>truncate tableclass; Query OK， 0 rows affected (0.04 sec) root@testdb：3306 12：14：50>insert into users values(1，'刘备'，2，null)，(2，'曹操'，1，null)，(3，'孙 权'，3，null)，(4，'关羽'，2，null)，(5，'司马懿'，1，null); Query OK， 5 rows affected (0.00 sec) Records： 5 Duplicates： 0 Warnings： 0root@testdb：3306 12：15：10>insert into class values(1，'魏'，null)，(2，'蜀'，null)，(3，'吴'，null)，(4，'晋'，''); Query OK， 4 rows affected (0.00 sec) Records： 4 Duplicates： 0 Warnings： 0
再将binlog日志格式改为STATAMENT格式(全局及会话级都改一下，或者修改全局变量后重新登录也行，当然 只改会话级别的也可以测试)，然后 再次进行测试。
步骤说明如下：
- 步骤1 - 分别查看两个会话中的事务隔离级别及binlog格式(隔离级别均为RR，binlog为STATENENT格式)
- 步骤2 - SESSION A 开启事务，更新users 表中c_id字段存在于class表中的记录，结果为 5 条记录均更新，并将c_note内容更新为 t1
- 步骤3- SESSION B 开启事务，准备删除class表中 c_id等于 2 的记录，此时无法更新，处于阻塞状态，立即进行步骤4
- 步骤4- SESSION A 在SESSION B执行commit的动作，则SESSION B的删除操作可以执行通过，但注意class表的数据两个SESSION中查看到的是不一样的
- 步骤5- 此时SESSION B执行commit，否则后面session A 更新数据时也会阻塞。此时如果SESSION A不执行commit，查看class表的结果也是不一样的，如步骤中的情况
- 步骤6- SESSION A 开启事务，更新users 表中c_id字段存在于class表中的记录，结果为 3 条记录更新成功，并将c_note内容更新为 t2，另外 2 条记录虽然本此时查看class表中存在对应的c_id，但是不会更新，此时提交事务，然后再次查看class的内容，结果和SESSION B 查看的结果一致了(幻读)
- 步骤7- 在从库查看users、class表中的内容，数据与主库一致
| 步 骤 | SESSION A | SESSION B |
| 1 | mysql>show variables like '%iso%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 2 rows in set (0.01 sec) mysql>show variables like '%binlog_format%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.01 sec) | mysql>show variables like '%iso%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 2 rows in set (0.01 sec) mysql>show variables like '%binlog_format%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.01 sec) |
| 2 | root@testdb：3306 12：37：04>set autocommit=0; Query OK， 0 rows affected (0.00 sec) root@testdb：3306 12：37：17>update users set c_note='t1' where c_id in (select c_id from class); Query OK， 5 rows affected， 1 warning (0.00 sec) Rows matched： 5 Changed： 5 Warnings： 1 | |
| 3 | root@testdb：3306 12：28：25>set autocommit=0; Query OK， 0 rows affected (0.00 sec) root@testdb：3306 12：38：06>delete from class where c_id=2; Query OK， 1 row affected (4.74 sec) | |
| 4 | root@testdb：3306 12：38：09>commit; Query OK， 0 rows affected (0.00 sec) root@testdb：3306 12：38：13>select * from users; +----+-----------+------+--------+ | id | user_name | c_id | c_note | +----+-----------+------+--------+ | 1 | 刘备 | 2 | t1 | | 2 | 曹操 | 1 | t1 | | 3 | 孙 权 | 3 | t1 | | 4 | 关羽 | 2 | t1 | | 5 | 司马懿 | 1 | t1 | +----+-----------+------+--------+ 5 rows in set (0.00 sec) root@testdb：3306 12：39：07>select * from class; +------+--------+--------+ | c_id | c_name | c_note | +------+--------+--------+ | 1 | 魏 | NULL | | 2 | 蜀 | NULL | | 3 | 吴 | NULL | | 4 | 晋 | | +------+--------+--------+ 4 rows in set (0.00 sec) | |
| 5 | root@testdb：3306 12：38：13>commit; Query OK， 0 rows affected (0.00 sec) root@testdb：3306 12：39：56>select * from class ; +------+--------+--------+ | c_id | c_name | c_note | +------+--------+--------+ | 1 | 魏 | NULL | | 3 | 吴 | NULL | | 4 | 晋 | | +------+--------+--------+ 3 rows in set (0.00 sec) | |
| 6 | root@testdb：3306 12：52：23>update users set c_note='t2' where c_id in (select c_id from class); Query OK， 3 rows affected， 1 warning (0.00 sec) Rows matched： 3 Changed： 3 Warnings： 1 root@testdb：3306 12：52：45>select * from class; +------+--------+--------+ | c_id | c_name | c_note | +------+--------+--------+ | 1 | 魏 | NULL | | 2 | 蜀 | NULL | | 3 | 吴 | NULL | | 4 | 晋 | | +------+--------+--------+ 4 rows in set (0.00 sec) root@testdb：3306 12：52：49>select * from users; +----+-----------+------+--------+ | id | user_name | c_id | c_note | +----+-----------+------+--------+ | 1 | 刘备 | 2 | t1 | | 2 | 曹操 | 1 | t2 | | 3 | 孙 权 | 3 | t2 | | 4 | 关羽 | 2 | t1 | | 5 | 司马懿 | 1 | t2 | +----+-----------+------+--------+ 5 rows in set (0.01 sec) root@testdb：3306 12：53：03>commit; Query OK， 0 rows affected (0.00 sec) root@testdb：3306 12：53：06>select * from users; +----+-----------+------+--------+ | id | user_name | c_id | c_note | +----+-----------+------+--------+ | 1 | 刘备 | 2 | t1 | | 2 | 曹操 | 1 | t2 | | 3 | 孙 权 | 3 | t2 | | 4 | 关羽 | 2 | t1 | | 5 | 司马懿 | 1 | t2 | +----+-----------+------+--------+ 5 rows in set (0.00 sec) root@testdb：3306 12：53：11>select * from class; +------+--------+--------+ | c_id | c_name | c_note | +------+--------+--------+ | 1 | 魏 | NULL | | 3 | 吴 | NULL | | 4 | 晋 | | +------+--------+--------+ 3 rows in set (0.00 sec) | |
| 7 | 查看从库数据 root@testdb：3307 12：44：22>select * from class; +------+--------+--------+ | c_id | c_name | c_note | +------+--------+--------+ | 1 | 魏 | NULL | | 3 | 吴 | NULL | | 4 | 晋 | | +------+--------+--------+ 3 rows in set (0.01 sec) root@testdb：3307 12：57：07>select * from users; +----+-----------+------+--------+ | id | user_name | c_id | c_note | +----+-----------+------+--------+ | 1 | 刘备 | 2 | t1 | | 2 | 曹操 | 1 | t2 | | 3 | 孙 权 | 3 | t2 | | 4 | 关羽 | 2 | t1 | | 5 | 司马懿 | 1 | t2 | +----+-----------+------+--------+ 5 rows in set (0.00 sec) |
也就是此时主从结果也是一致的，原因在于，binlog里存储的语句顺序如下：
| binlog里的顺序 | 语句内容 |
| 1 | update users set c_note='t1' where c_id in (select c_id from class); |
| 2 | delete from class where c_id=2; |
| 3 | update users set c_note='t2' where c_id in (select c_id from class); |
与主库执行的顺序是一致的，因此，主从的结果是一致的。
3、 RC隔离级别
3.1 ROW格式
为了和之前的步骤一致，先初始化数据
root@testdb：3306 12：14：27>truncate tableusers; Query OK， 0 rows affected (0.08 sec) root@testdb：3306 12：14：29>truncate tableclass; Query OK， 0 rows affected (0.04 sec) root@testdb：3306 12：14：50>insert into users values(1，'刘备'，2，null)，(2，'曹操'，1，null)，(3，'孙 权'，3，null)，(4，'关羽'，2，null)，(5，'司马懿'，1，null); Query OK， 5 rows affected (0.00 sec) Records： 5 Duplicates： 0 Warnings： 0root@testdb：3306 12：15：10>insert into class values(1，'魏'，null)，(2，'蜀'，null)，(3，'吴'，null)，(4，'晋'，''); Query OK， 4 rows affected (0.00 sec) Records： 4 Duplicates： 0 Warnings： 0
再将binlog日志格式改为STATAMENT格式(全局及会话级都改一下，或者修改全局变量后重新登录也行，当然 只改会话级别的也可以测试)，然后 再次进行测试。
步骤说明如下：
- 步骤1 - 分别查看两个会话中的事务隔离级别及binlog格式(隔离级别均为RC，binlog为ROW格式)
- 步骤2 - SESSION A 开启事务，更新users 表中c_id字段存在于class表中的记录，结果为 5 条记录均更新，并将c_note内容更新为 t1
- 步骤3- SESSION B 开启事务，准备删除class表中 c_id等于 2 的记录，此时不会像RR事务隔离级别那样处于阻塞状态，而是可以直接执行通过
- 步骤4- 此时SESSION A查看class数据还是删除前的，因为session B 暂未提交
- 步骤5- SESSION B 提交事务，
- 步骤6- 更新users 表中c_id字段存在于class表中的记录，结果为 3 条记录更新成功，并将c_note内容更新为 t2
- 步骤7- 在从库查看users、class表中的内容，数据与主库一致
| 步 骤 | SESSION A | SESSION B |
| 1 | root@testdb：3306 01：25：24>show variables like '%iso%'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | | tx_isolation | READ-COMMITTED | +-----------------------+----------------+ 2 rows in set (0.01 sec) root@testdb：3306 01：25：36>show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.01 sec) | root@testdb：3306 01：24：57>show variables like '%iso%'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | | tx_isolation | READ-COMMITTED | +-----------------------+----------------+ 2 rows in set (0.01 sec) root@testdb：3306 01：25：39>show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec) |
| 2 | root@testdb：3306 01：27：55>set autocommit=0; Query OK， 0 rows affected (0.00 sec) root@testdb：3306 01：28：27>update users set c_note='t1' where c_id in (select c_id from class); Query OK， 5 rows affected (0.00 sec) Rows matched： 5 Changed： 5 Warnings： 0 | |
| 3 | root@testdb：3306 01：26：07>set autocommit=0; Query OK， 0 rows affected (0.00 sec) root@testdb：3306 01：28：37>delete from class where c_id=2; Query OK， 1 row affected (0.00 sec) | |
| 4 | root@testdb：3306 01：28：27>select * from class; +------+--------+--------+ | c_id | c_name | c_note | +------+--------+--------+ | 1 | 魏 | NULL | | 2 | 蜀 | NULL | | 3 | 吴 | NULL | | 4 | 晋 | | +------+--------+--------+ 4 rows in set (0.00 sec) | |
| 5 | root@testdb：3306 01：28：41>commit; Query OK， 0 rows affected (0.00 sec) | |
| 6 | root@testdb：3306 01：28：59>select * from class; +------+--------+--------+ | c_id | c_name | c_note | +------+--------+--------+ | 1 | 魏 | NULL | | 3 | 吴 | NULL | | 4 | 晋 | | +------+--------+--------+ 3 rows in set (0.01 sec) root@testdb：3306 01：29：13>update users set c_note='t2' where c_id in (select c_id from class); Query OK， 3 rows affected (0.00 sec) Rows matched： 3 Changed： 3 Warnings： 0 root@testdb：3306 01：29：26>select * from class; +------+--------+--------+ | c_id | c_name | c_note | +------+--------+--------+ | 1 | 魏 | NULL | | 3 | 吴 | NULL | | 4 | 晋 | | +------+--------+--------+ 3 rows in set (0.00 sec) root@testdb：3306 01：29：31>select * from users; +----+-----------+------+--------+ | id | user_name | c_id | c_note | +----+-----------+------+--------+ | 1 | 刘备 | 2 | t1 | | 2 | 曹操 | 1 | t2 | | 3 | 孙 权 | 3 | t2 | | 4 | 关羽 | 2 | t1 | | 5 | 司马懿 | 1 | t2 | +----+-----------+------+--------+ 5 rows in set (0.00 sec) root@testdb：3306 01：29：38>commit; | |
| 7 | 查看从库数据 root@testdb：3307 01：40：32>select * from users; +----+-----------+------+--------+ | id | user_name | c_id | c_note | +----+-----------+------+--------+ | 1 | 刘备 | 2 | t1 | | 2 | 曹操 | 1 | t2 | | 3 | 孙 权 | 3 | t2 | | 4 | 关羽 | 2 | t1 | | 5 | 司马懿 | 1 | t2 | +----+-----------+------+--------+ 5 rows in set (0.00 sec) root@testdb：3307 01：40：35>select * from class; +------+--------+--------+ | c_id | c_name | c_note | +------+--------+--------+ | 1 | 魏 | NULL | | 3 | 吴 | NULL | | 4 | 晋 | | +------+--------+--------+ 3 rows in set (0.00 sec) |
也就是此时主从结果也是一致的。
3.2 STATEMENT格式
因为当前版本已经不支持RC+STATEMENT组合下数据的操作，否则将报如下错误：
Cannot execute statement： impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
因此单纯根据步骤讲解
| 步骤 | SESSION A | SESSION B |
| 1 | mysql>set autocommit=0; mysql>update users set c_note='t1' where c_id in (select c_id from class); | |
| 2 | mysql>set autocommit=0; mysql>delete from class where c_id=2; mysql>commit; | |
| 3 | mysql>update users set c_note='t2' where c_id in (select c_id from class); | |
| 4 | commit; |
因为binlog是按照commit时间的顺序保存，因此上述步骤在binlog里会以如下顺序存储：
| binlog里的顺序 | 语句内容 |
| 1 | delete from class where c_id=2; |
| 2 | update users set c_note='t1' where c_id in (select c_id from class); |
| 3 | update users set c_note='t2' where c_id in (select c_id from class); |
从库通过binlog应用后，最终的结果将导致主库的数据不一样(具体案例后续安装低版本后演示)。
因而，此种场景下很容易导致数据不一样。
4、总结
通过上述的实践，可以发现在RR级别下，binlog为任何格式均不会造成主从数据不一致的情况出现，但是当低版本MySQL使用RC+STATEMENT组合时(MySQL5.1. 5 前只有statement格式)将会导致主从数据不一致。当前这个历史遗漏问题以及解决，大家可以将其设置为RC+ROW组合的方式(例如ORACLE等数据库隔离级别就是RC)，而不是必须使用RR(会带来更多的锁等待)，具体可以视情况选择。
本文转载自微信公众号【数据库干货铺】。