共计 14490 个字符，预计需要花费 37 分钟才能阅读完成。
曾多次听到“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: 0
mysql> 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: 02、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 table users;
Query OK, 0 rows affected (0.08 sec)
root@testdb:3306 12:14:29>truncate table class;
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: 0
root@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 table users;
Query OK, 0 rows affected (0.08 sec)
root@testdb:3306 12:14:29>truncate table class;
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: 0
root@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（会带来更多的锁等待），具体可以视情况选择。
