MySQL数据库隔离级别到底是RC还是RR?

前奏

MySQL 的默认事务隔离级别为 Repeatable Read。而 ORACLESQLServer 等的默认隔离级别使用的是 Read Committed 模式,为什么呢?

开始我们的内容,相信大家一定遇到过下面的一个面试场景

面试官:“讲讲 mysql 有几个事务隔离级别?”

你:“读未提交,读已提交,可重复读,串行化四个!默认是可重复读”
面试官:“为什么 mysql 选可重复读作为默认的隔离级别?”
(你面露苦色,不知如何回答!)
面试官:“你们项目中选了哪个隔离级别?为什么?”
你:“当然是默认的可重复读,至于原因…呃…”
(然后你就可以回去等通知了!)

为了避免上述尴尬的场景,请继续往下阅读!
Mysql 默认的事务隔离级别是可重复读 (Repeatable Read),那互联网项目中 Mysql 也是用默认隔离级别,不做修改么?
OK,不是的,我们在项目中一般用读已提交 (Read Commited) 这个隔离级别!
what!居然是读已提交,网上不是说这个隔离级别存在不可重复读和幻读问题么?不用管么?好,带着我们的疑问开始本文!

我们先来思考一个问题,在 OracleSqlServer 中都是选择读已提交 (Read Commited) 作为默认的隔离级别,为什么 Mysql 不选择读已提交 (Read Commited) 作为默认隔离级别,而选择可重复读 (Repeatable Read) 作为默认的隔离级别呢?

Why?Why?Why?

这个是有历史原因的,当然要从我们的主从复制开始讲起了!
主从复制,是基于什么复制的?
是基于 binlog 复制的!这里不想去搬 binlog 的概念了,就简单理解为 binlog 是一个记录数据库更改的文件吧~
binlog 有几种格式?
OK,三种,分别是:

  • statement: 记录的是修改 SQL 语句
  • row:记录的是每行实际数据的变更
  • mixed:statement 和 row 模式的混合

Mysql 在 5.0 这个版本以前,binlog 只支持 STATEMENT 这种格式!而这种格式在读已提交 (Read Commited) 这个隔离级别下主从复制是有 bug 的,因此 Mysql 将可重复读 (Repeatable Read) 作为默认的隔离级别!
接下来,就要说说当 binlogSTATEMENT 格式,且隔离级别为读已提交 (Read Commited) 时,有什么 bug 呢?如下图所示,在主 (master) 上执行如下事务

http://static.cyblogs.com/image_editor_38dbfa43-6a3f-4812-a26c-c9dc984d4ccc.jpg

此时在主 (master) 上执行下列语句

1
select * from test

输出如下

1
2
3
4
5
6
±–+
| b |
±–+
| 3 |
±–+
1 row in set

但是,你在此时在从 (slave) 上执行该语句,得出输出如下

1
Empty set

这样,你就出现了主从不一致性的问题!原因其实很简单,就是在 master 上执行的顺序为先删后插!而此时 binlog 为 STATEMENT 格式,它记录的顺序为先插后删!从 (slave) 同步的是 binglog,因此从机执行的顺序和主机不一致!就会出现主从不一致!

如何解决?

解决方案有两种!

  • 隔离级别设为可重复读 (Repeatable Read), 在该隔离级别下引入间隙锁。当 Session 1 执行 delete 语句时,会锁住间隙。那么,Ssession 2 执行插入语句就会阻塞住!
  • 将 binglog 的格式修改为 row 格式,此时是基于行的复制,自然就不会出现 sql 执行顺序不一样的问题!奈何这个格式在 mysql5.1 版本开始才引入。因此由于历史原因,mysql 将默认的隔离级别设为可重复读 (Repeatable Read),保证主从复制不出问题!

那么,当我们了解完 mysql 选可重复读 (Repeatable Read) 作为默认隔离级别的原因后,接下来我们将其和读已提交 (Read Commited) 进行对比,来说明为什么在互联网项目为什么将隔离级别设为读已提交(Read Commited)!

对比

OK,我们先明白一点!项目中是不用读未提交 (Read UnCommitted) 和串行化 (Serializable) 两个隔离级别,原因有二

采用读未提交 (Read UnCommitted), 一个事务读到另一个事务未提交读数据,这个不用多说吧,从逻辑上都说不过去!
采用串行化 (Serializable),每个次读操作都会加锁,快照读失效,一般是使用 mysql 自带分布式事务功能时才使用该隔离级别!(笔者从未用过 mysql 自带的这个功能,因为这是 XA 事务,是强一致性事务,性能不佳!互联网的分布式方案,多采用最终一致性的事务解决方案!)
也就是说,我们该纠结都只有一个问题,究竟隔离级别是用读已经提交呢还是可重复读?
接下来对这两种级别进行对比,讲讲我们为什么选读已提交 (Read Commited) 作为事务隔离级别!
假设表结构如下

1
2
3
4
5
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`color` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

数据如下

1
2
3
4
5
6
7
8
±—±------+
| id | color |
±—±------+
| 1 | red |
| 2 | white |
| 5 | red |
| 7 | white |
±—±------+

为了便于描述,下面将

  • 可重复读 (Repeatable Read),简称为 RR
  • 读已提交 (Read Commited),简称为 RC

缘由一

RR 隔离级别下,存在间隙锁,导致出现死锁的几率比 RC 大的多!
此时执行语句

1
select * from test where id = 2 for update;

RR 隔离级别下,存在间隙锁,可以锁住 (2,5) 这个间隙,防止其他事务插入数据!而在 RC 隔离级别下,不存在间隙锁,其他事务是可以插入数据!

RC 隔离级别下并不是不会出现死锁,只是出现几率比 RR 低而已!

缘由二

RR 隔离级别下,条件列未命中索引会锁表!而在 RC 隔离级别下,只锁行
此时执行语句

1
update test set color = 'blue' where color = 'red';

RC 隔离级别下,其先走聚簇索引,进行全部扫描。加锁如下:

http://static.cyblogs.com/image_editor_4485ac7d-e85b-4b76-962e-5d420e1ac0a4.png

但在实际中,MySQL 做了优化,在 MySQL Server 过滤条件,发现不满足后,会调用 unlock_row 方法,把不满足条件的记录放锁。
实际加锁如下

http://static.cyblogs.com/image_editor_d329942e-3fd7-4a88-b6ae-b294989c34b5.png

然而,在 RR 隔离级别下,走聚簇索引,进行全部扫描,最后会将整个表锁上,如下所示

http://static.cyblogs.com/image_editor_b504b652-9960-495a-a5ce-37826d0acdc7.jpg

缘由三

RC 隔离级别下,半一致性读 (semi-consistent) 特性增加了 update 操作的并发性!
5.1.15 的时候,innodb 引入了一个概念叫做 “semi-consistent”,减少了更新同一行记录时的冲突,减少锁等待。
所谓半一致性读就是,一个 update 语句,如果读到一行已经加锁的记录,此时 InnoDB 返回记录最近提交的版本,由 MySQL 上层判断此版本是否满足 updatewhere 条件。若满足 (需要更新),则 MySQL 会重新发起一次读操作,此时会读取行的最新版本 (并加锁)!
具体表现如下:
此时有两个 SessionSession1Session2
Session1 执行

1
update test set color = 'blue' where color = 'red';

先不 Commit 事务!
与此同时 Ssession2 执行

1
update test set color = 'blue' where color = 'white';

Session2 尝试加锁的时候,发现行上已经存在锁,InnoDB 会开启 semi-consistent read,返回最新的 committed 版本 (1,red),(2,white),(5,red),(7,white)。MySQL 会重新发起一次读操作,此时会读取行的最新版本 (并加锁)!
而在 RR 隔离级别下,Session2 只能等待!

两个疑问

RC 级别下,不可重复读问题需要解决么?
不用解决,这个问题是可以接受的!毕竟你数据都已经提交了,读出来本身就没有太大问题!Oracle 的默认隔离级别就是 RC,你们改过 Oracle 的默认隔离级别么?

RC 级别下,主从复制用什么 binlog 格式?
OK, 在该隔离级别下,用的 binlogrow 格式,是基于行的复制!Innodb 的创始人也是建议 binlog 使用该格式!

最后总结

  • 数据库默认隔离级别: mysql —repeatable、oracle,sql server —read commited
  • mysql binlog 的格式三种:statementrowmixed
  • 为什么 mysql 用的是 repeatable 而不是 read committed:5.0 之前只有 statement 一种格式,而主从复制存在了大量的不一致(bug),故选用 repeatable
  • 为什么其他数据库默认的隔离级别都会选用 read commited 原因有二:repeatable 存在间隙锁会使死锁的概率增大,在 RR 隔离级别下,条件列未命中索引会锁表!而在 RC 隔离级别下,只锁行
  • RC 级用别下,主从复制用什么 binlog 格式:row 格式,是基于行的复制!如果使用 statement 格式,会导致主从不一致。

参考地址

如果大家喜欢我的文章,可以关注个人订阅号。欢迎随时留言、交流。如果想加入微信群的话一起讨论的话,请加管理员微信号:chengcheng222e,他会拉你们进群。

简栈文化服务订阅号