专栏首页数据库干货铺MySQL默认隔离级别为什么是RR

MySQL默认隔离级别为什么是RR

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

01

准备工作

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: 0

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);

与主库执行的顺序是一致的,因此,主从的结果是一致的。

03

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应用后,最终的结果将导致主库的数据不一样(具体案例后续安装低版本后演示)。

因而,此种场景下很容易导致数据不一样。

04

结语

通过上述的实践,可以发现在RR级别下,binlog为任何格式均不会造成主从数据不一致的情况出现,但是当低版本MySQL使用RC+STATEMENT组合时(MySQL5.1.5前只有statement格式)将会导致主从数据不一致。当前这个历史遗漏问题以及解决,大家可以将其设置为RC+ROW组合的方式(例如ORACLE等数据库隔离级别就是RC),而不是必须使用RR(会带来更多的锁等待),具体可以视情况选择。

本文分享自微信公众号 - 数据库干货铺(database_gjc),作者:懂点IT的耿小厨

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL基础(1)

    DDL: 数据定义语言,用于定义不同的数据段、数据库、表、列、索引等数据库对象。常用的关键字包含create、alter、drop、truncate等

    July
  • SQL基础(2)

    之前的SQL基础1中已经介绍了部分Select的内容,但是,实际使用中select 还有很多其他的用法,本文会再介绍部分select的其他用法。

    July
  • MySQL5.7安装步骤

    本次进行MySQL5.7版本的安装,关于MySQL版本选择、官网下载地址、相关系统配置等操作可以参照之前的博文,本文就不在赘述咯。可以参考历史文章处理,下面直奔...

    July
  • requests项目实战--抓取百度热搜

    注意:123是搜索关键字。这不是重点,因为必须要搜索,才能在网页右侧出现百度热搜。

    py3study
  • 为你的项目配置Maven私服Nexus

    Nexus是一个强大的Maven仓库管理器,它极大地简化了自己内部仓库的维护和外部仓库的访问。

    Java后端技术
  • Bootstrap+jQuery实现卡片标签样式的分页

    很多人问我为什么要写这么多的博客,其实回想起从前,刚刚工作的那会,我也是什么都不会,每天遇到难题的时候只能打开百度,搜索关键词,看看网上的前辈有没有遇到和我一样...

    王小婷
  • picker-extend 移动端级联选择插件

    <h1 align="center" >picker-extend.js</h1>

    念念不忘
  • leetcode哈希表之第一个只出现一次的字符

    这里借助LinkedHashMap来计数,最后按顺序遍历,找出count为1的得到第一个只出现一次的字符。

    codecraft
  • leetcode哈希表之第一个只出现一次的字符

    这里借助LinkedHashMap来计数,最后按顺序遍历,找出count为1的得到第一个只出现一次的字符。

    codecraft
  • MySQL explain 中的 rows 究竟是如何计算的?

    疑问1:上述SQL理应按id主键(聚簇索引)范围查找,为啥explain里的rows会多余两者之差呢?

    架构精进之路

扫码关注云+社区

领取腾讯云代金券