为什么MySQL基础事务隔离级别是RR

曾多次听到“MySQL为什么选择RR为默认隔离级别”的问题，其实这是个历史遗留问题，当前以及解决，但是MySQL的各个版本沿用了原有习惯。历史版本中的问题是什么，本次就通过简单的测试来说明一下。

1、准备工作

1.1 部署主从

ASP站长网部署一套主从架构的集群，创建过程较简单，可以参考历史文章部署 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: 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

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)

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

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

mysql>commit;

Query OK, 0 rows affected (0.00 sec)

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)

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)

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)

在从库查看数据

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

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)

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

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)

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)

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)

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)

查看从库数据

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里的顺序语句内容

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

步骤说明如下：

步骤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

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)

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

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)

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)

root@testdb:3306 01:28:41>commit;

Query OK, 0 rows affected (0.00 sec)

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;

查看从库数据

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)

也就是此时主从结果也是一致的。

热点

为什么MySQL基础事务隔离级别是RR

由 dawei

您错过了

如何做中小型公司的网站

怎么做公司网站步骤

做公司网站企业网站建设流程

摩托罗拉全球首发智变柔性屏

友情链接

为什么MySQL基础事务隔离级别是RR

由 dawei

相关文章

MySQL中的用户创建怎么实现

MySQL索引结构分析

mysql存储引擎之间的区别

您错过了

如何做中小型公司的网站

怎么做公司网站步骤

做公司网站企业网站建设流程

摩托罗拉全球首发智变柔性屏