【巨人的肩膀】关于 MYSQL 的事务隔离级别

最新推荐文章于 2022-06-07 20:34:56 发布

我永远吹着泡泡

最新推荐文章于 2022-06-07 20:34:56 发布

阅读量128

点赞数

分类专栏：巨人的肩膀文章标签： mysql 数据库服务器

原文链接：https://mp.weixin.qq.com/s/Sw6GME28ecHYVkwVpvrnJw

版权

巨人的肩膀专栏收录该内容

32 篇文章 3 订阅

订阅专栏

为什么MySQL选择Repeatable Read作为默认隔离级别

ANSI/ISO SQL定义的标准隔离级别有四种，从高到底依次为：可序列化(Serializable)、可重复读(Repeatable Reads)、提交读(Read Committed)、未提交读(Read Uncommitted)

RU 隔离级别下，可能发生脏读、幻读、不可重复读等问题
RC 隔离级别下，解决了脏读的问题，存在幻读、不可重复读的问题
RR 隔离级别下，解决了脏读、不可重复读的问题，存在幻读的问题
Serializable 隔离级别下，解决了脏读、幻读、不可重复读的问题

这四种隔离级别是 ANSI/ISO SQL 定义的标准定义的，我们比较常用的 MySQL 对这四种隔离级别是都支持的。但是 Oracle 数据库只支持Serializable 和 Read Committed。Oracle 默认的隔离级别是 RC，而 MySQL 默认的隔离级别是 RR

Oracle支持三种隔离级别：即 Read Committed、Serializable 和 Read-Only
Read-Only 只读隔离级别类似于可序列化隔离级别，但是只读事务不允许在事务中修改数据，除非用户是 SYS
在 Oracle 这三种隔离级别中，Serializable 和 Read-Only 显然都是不适合作为默认隔离级别的，那么就只剩 Read Committed 这个唯一的选择了

MySQL 剩下的 RR 和 RC 两种，怎么选？

在 MySQL 设计之初，他的定位就是提供一个稳定的关系型数据库。而为了要解决 MySQL 单点故障带来的问题，MySQL 采用主从复制的机制，所谓主从复制，其实就是通过搭建 MySQL 集群，整体对外提供服务，集群中的机器分为主服务器（Master）和从服务器（Slave），主服务器提供写服务，从服务器提供读服务。为了保证主从服务器之间的数据的一致性，就需要进行数据同步

MySQL在主从复制的过程中，数据的同步是通过 bin log 进行的，简单理解就是主服务器把数据变更记录到 bin log 中，然后再把 bin log 同步传输给从服务器，从服务器接收到 bin log 之后，再把其中的数据恢复到自己的数据库存储中

那么，binlog 里面记录的是什么内容呢？格式是怎样的呢？
MySQL 的 bin log 主要支持三种格式，分别是 statement、row 以及 mixed。MySQL 是在 5.1.5 版本开始支持 row 的、在 5.1.8 版本中开始支持 mixed。
statement 和 row 最大的区别，当 binlog 的格式为 statement 时，binlog 里面记录的就是 SQL 语句的原文

MySQL 早期只有 statement 这种 bin log 格式，这时候，如果使用提交读(Read Committed)、未提交读(Read Uncommitted)这两种隔离级别会出现问题
例如：
在这里插入图片描述
以上两个事务执行之后，数据库里面的记录会变成（11，2）和（20，2），这个发上在主库的数据变更大家都能理解
因为事务的隔离级别是 read committed，所以，事务1在更新时，只会对b=2这行加上行级锁，不会影响到事务2对b=1这行的写操作
以上两个事务执行之后，会在 bin log 中记录两条记录，因为事务 2 先提交，所以 UPDATE t1 SET b=2 where b=1; 会被优先记录，然后再记录 UPDATE t1 SET a=11 where b=2;（再次提醒：statement 格式的 bin log 记录的是 SQL 语句的原文）
这样 bin log 同步到备库之后，SQL 语句回放时，会先执行 UPDATE t1 SET b=2 where b=1;，再执行 UPDATE t1 SET a=11 where b=2;。
这时候，数据库中的数据就会变成（11，2）和（11，2）。这就导致主库和备库的数据不一致了

为了避免这样的问题发生。MySQL 就把数据库的默认隔离级别设置成了 Repetable Read，那么，Repetable Read 的隔离级别下是如何解决这样问题的呢？

那是因为 Repetable Read 这种隔离级别，会在更新数据的时候不仅对更新的行加行级锁，还会增加 GAP lock。上面的例子，在事务2执行的时候，因为事务1增加了 GAP lock，就会导致事务执行被卡住，需要等事务1提交或者回滚后才能继续执行

除了设置默认的隔离级别外，MySQL 还禁止在使用 statement 格式的 bin log 的情况下，使用 READ COMMITTED 作为事务隔离级别，一旦用户主动修改隔离级别，尝试更新时，会报错

为什么阿里等大厂会改成 Read Committed

RR 和 RC 的区别，分析下各自的优缺点

一致性读
一致性读，又称为快照读。快照即当前行数据之前的历史版本。快照读就是使用快照信息显示基于某个时间点的查询结果，而不考虑与此同时运行的其他事务所执行的更改

在 MySQL 中，只有 READ COMMITTED 和 REPEATABLE READ 这两种事务隔离级别才会使用一致性读

在 RC 中，每次读取都会重新生成一个快照，总是读取行的最新版本，既事务中的每一条 select 语句会读取到他自己执行时已经提交了的记录，也就是每一条 select 都有自己的一致性读 ReadView
在 RR 中，快照会在事务中第一次 select 语句执行时生成，只有在本事务中对数据进行更改才会更新快照，既事务中的一致性读的 ReadView 是以第一条 select 语句的运行时，作为本事务的一致性读 snapshot 的建立时间点的。只能读取该时间点之前已经提交的数据

在数据库的 RC 这种隔离级别中，还支持"半一致读" ，一个 update 语句，如果读到一行已经加锁的记录，此时 InnoDB 返回记录最近提交的版本，由 MySQL 上层判断此版本是否满足 update 的 where 条件。若满足(需要更新)，则 MySQL 会重新发起一次读操作，此时会读取行的最新版本(并加锁)

锁机制
数据库的锁，在不同的事务隔离级别下，是采用了不同的机制的

在 MySQL 中，有三种类型的锁，分别是 Record Lock、Gap Lock 和 Next-Key Lock

Record Lock 表示记录锁，锁的是索引记录
Gap Lock 是间隙锁，锁的是索引记录之间的间隙
Next-Key Lock 是 Record Lock 和 Gap Lock 的组合，同时锁索引记录和间隙。他的范围是左开右闭的

在 RC 中，只会对索引增加 Record Lock，不会添加 Gap Lock 和 Next-Key Lock
在 RR 中，为了解决幻读的问题，在支持 Record Lock 的同时，还支持 Gap Lock 和 Next-Key Lock

主从同步
在数据主从同步时，不同格式的 binlog 也对事务隔离级别有要求

RC 隔离级别只支持 row 格式的 binlog。如果指定了 mixed 作为 binlog 格式，那么如果使用 RC，服务器会自动使用基于 row 格式的日志记录
RR 的隔离级别同时支持 statement、row 以及 mixed 三种

为什么互联网公司选择使用 RC

提升并发
RC 在加锁的过程中，是不需要添加 Gap Lock 和 Next-Key Lock 的，只对要修改的记录添加行级锁就行了。这就使得并发度要比 RR 高很多
另外，因为 RC 还支持"半一致读"，可以大大的减少了更新语句时行锁的冲突；对于不满足更新条件的记录，可以提前释放锁，提升并发度
减少死锁
因为 RR 这种事务隔离级别会增加 Gap Lock 和 Next-Key Lock，这就使得锁的粒度变大，那么就会使得死锁的概率增大
死锁：一个事务锁住了表A，然后又访问表B；另一个事务锁住了表B，然后企图访问表A；这时就会互相等待对方释放锁，就导致了死锁
例子1
insert into t select … from s where 在RC 和 RR 隔离级别下的加锁过程

下面是官方文档中的说明：http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.

insert inot t select … from s where … 语句和 create table … select … from s where 加锁过程是相似的(RC 和 RR 加锁不一样)：

RC 隔离级别时但是设置 innodb_locks_unsafe_for_binlog=1 时，select … from s where 对 s 表进行的是一致性读，所以是无需加锁的
如果是 RR 隔离级别(默认innodb_locks_unsafe_for_binlog=0)，或者是 serializable 隔离级别，那么对 s 表上的每一行都要加上 shared next-key lock

这个区别是一个很大的不同，下面是生成中的一个 insert into t select … from s where 导致的系统宕机的案例：

一程序猿执行一个分表操作：

// tb_async_src_acct 有 4000W 数据
insert into tb_async_src_acct_201508 select * from tb_async_src_acct where src_status=3 and create_time>='2015-08-01 00:00:00' and create_time <= '2015-08-31 23:59:59';

分表的目的是想提升下性能。结果一执行该语句，该条 SQL 被卡住，然后所有向 tb_async_src_acct 的写操作，要么是 get lock fail, 要么是 lost connection，全部卡住，然后主库就宕机了

显然这里的原因，就是不知道默认 RR 隔离级别中 insert into t select … from s where 语句的在 s 表上的加锁过程，该语句一执行，所有符合 where 条件的 s 表中的行记录都会加上 shared next-key lock(如果没有使用到索引，还会锁住表中所有行)，在整个事务过程中一直持有，因为表 tb_async_src_acct 数据很多，所以运行过程是很长的，所以加锁过程也是很长，所以其它所有的对 tb_async_src_acct 的 insert, delete, update, DDL 都会被阻塞掉，这样被阻塞的事务就越来越多，而事务也会申请其它的表中的行锁，结果就是系统中被卡住的事务越来越多，系统自然就宕机了

例子2
下面是来自 itpub 的一个例子：http://www.itpub.net/thread-1941624-1-1.html
MySQL5.6, 隔离级别RR，autocommit=off;

// 表结构：
mysql> show create table t1
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  `e` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_t1_bcd` (`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

// 表数据：
mysql> select * from t1;
+---+---+---+---+------+
| a | b | c | d | e    |
+---+---+---+---+------+
| 1 | 1 | 1 | 1 | a    |
| 2 | 2 | 2 | 2 | b    |
| 3 | 3 | 2 | 2 | c    |
| 4 | 3 | 1 | 1 | d    |
| 5 | 2 | 3 | 5 | e    |
| 6 | 6 | 4 | 4 | f    |
| 7 | 4 | 5 | 5 | g    |
| 8 | 8 | 8 | 8 | h    |
+---+---+---+---+------+
8 rows in set (0.00 sec)

// 操作过程：

// session 1:
delete from t1 where b>2 and b<5 and c=2;
mysql> explain select * from t1 where b>2 and b<5 and c=2
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: idx_t1_bcd
          key: idx_t1_bcd
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using index condition
1 row in set (0.00 sec)

// session 2:
delete from t1 where a=4
// 结果 session 2 被锁住

// session 3:
mysql> select * from information_schema.innodb_locks;
+---------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 38777:390:3:5 | 38777       | X         | RECORD    | `test`.`t1` | PRIMARY    |        390 |         3 |        5 | 4         |
| 38771:390:3:5 | 38771       | X         | RECORD    | `test`.`t1` | PRIMARY    |        390 |         3 |        5 | 4         |
+---------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+

根据锁及 ICP 的知识，此时加锁的情况应该是在索引 idx_t1_bcd 上的 b>2 and b<5 之间加 gap lock, idx_t1_bcd 上的 c=2 加 X锁主键 a=3 加 x 锁。应该 a=4上是没有加X锁的，可以进行删除与更改。但是从session3上的结果来，此时 a=4 上被加上了X锁。求大牛解惑，谢谢

要理解这里为什么 a=4 被锁住了，需要理解 gap lock，锁处理 RR 隔离级别和RC隔离级别的区别等等

这里的原因如下：
很简单，我们注意到：key_len: 4 和 Extra: Using index condition，这说明了，仅仅使用了索引 idx_t1_bcd 中的 b 一列，没有使用到 c 这一列。c 这一列是在 ICP 时进行过滤的
所以：delete from t1 where b>2 and b<5 and c=2 其实锁定的行有：

mysql> select * from t1 where b>2 and b<=6;
+---+---+---+---+------+
| a | b | c | d | e    |
+---+---+---+---+------+
| 3 | 3 | 2 | 2 | c    |
| 4 | 3 | 1 | 1 | d    |
| 6 | 6 | 4 | 4 | f    |
| 7 | 4 | 5 | 5 | g    |
+---+---+---+---+------+
4 rows in set (0.00 sec)

所以显然 delete from t1 where a=4 就被阻塞了。那么为什么 delete from t1 where a=6 也会被阻塞呢？？？

这里 b<=6 的原因是，b 列中没有等于 5 的记录，所以 and b<5 实现为锁定 b<=6 的所有索引记录，这里有等于号的原因是，如果我们不锁定 =6 的索引记录，那么怎么实现锁定 <5 的gap 呢？也就是说锁定 b=6 的索引记录，是为了实现锁定 b< 5 的 gap。也就是不能删除 b=6 记录的原因

而这里 b >2 没有加等于号(b>=2) 的原因，是因为 b>2 的这个 gap 是由 b=3 这个索引记录(的gap)来实现的，不是由 b=2 索引记录(的gap) 来实现的，b=2 的索引记录的 gap lock 只能实现锁定 <2 的 gap，b>2 的 gap 锁定功能，需要由 b=3 的索引记录对应的 gap 来实现(b>2，b<3的gap)
所以我们在 session2 中可以删除：a=1,2,5,8的记录，但是不能删除 a=6(因为该行的b=6)的记录

如果我们使用 RC 隔离级别时，则不会发生阻塞，其原因就是：
RC 和 RR 隔离级别中的锁处理不一样，RC 隔离级别时，在使用 c 列进行 ICP where 条件过滤时，对于不符合条件的记录，锁会释放掉，而 RR 隔离级别时，即使不符合条件的记录，锁也不会释放(虽然违反了“2阶段锁”原则)。所以 RC 隔离级别时 session 2 不会被阻塞

Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record

参照

MySQL 中隔离级别 RC 与 RR 的区别

我永远吹着泡泡

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【巨人的肩膀】关于 MYSQL 的事务隔离级别

为什么MySQL选择Repeatable Read作为默认隔离级别ANSI/ISO SQL定义的标准隔离级别有四种，从高到底依次为：可序列化(Serializable)、可重复读(Repeatable Reads)、提交读(Read Committed)、未提交读(Read Uncommitted)RU 隔离级别下，可能发生脏读、幻读、不可重复读等问题RC 隔离级别下，解决了脏读的问题，存在幻读、不可重复读的问题RR 隔离级别下，解决了脏读、不可重复读的问题，存在幻读的问题Serializable
复制链接

扫一扫