MySQL使用可重复读作为默认隔离级别的原因之一

一般的DBMS系统，默认都会使用读提交（Read-Comitted，RC）作为默认隔离级别，如Oracle、SQL Server等，而MySQL却使用可重复读（Read-Repeatable，RR）。要知道，越高的隔离级别，能解决的数据一致性问题越多，理论上性能损耗更大，可并发性越低。隔离级别依次为

SERIALIZABLE > RR > RC > Read-Uncommited

在SQL标准中，前三种隔离级别分别解决了幻象读、不可重复读和脏读的问题。那么，为什么MySQL使用可重复读作为默认隔离级别呢？

1. 从Binlog说起

Binlog是MySQL的逻辑操作日志，广泛应用于复制和恢复。MySQL 5.1以前，Statement是Binlog的默认格式，即依次记录系统接受的SQL请求；5.1及以后，MySQL提供了Row和Mixed两个Binlog格式。

从MySQL 5.1开始，如果打开语句级Binlog，就不支持RC和Read-Uncommited隔离级别。要想使用RC隔离级别，必须使用Mixed或Row格式。


mysql> set tx_isolation='read-committed';

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into t1 values(1,1);

ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

 

那么，为什么RC隔离级别不支持语句级Binlog呢？我们关闭binlog，做以下测试。


会话1
会话2

use test;

#初始化数据

create table t1(c1 int, c2 int) engine=innodb;

create table t2(c1 int, c2 int) engine=innodb;

 

insert into t1 values(1,1), (2,2);

insert into t2 values(1,1), (2,2);

 

#设置隔离级别

set tx_isolation='read-committed';

Query OK, 0 rows affected (0.00 sec)

 

#连续更新两次

mysql> Begin;

Query OK, 0 rows affected (0.03 sec)

 

mysql> update t2 set c2 = 3 where c1 in (select c1 from t1);

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

 

 

 

 

 

mysql> update t2 set c2 = 4 where c1 in (select c1 from t1);

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from t2;

+------+------+

| c1   | c2   |

+------+------+

|    1 |    4 |

|    2 |    3 |

+------+------+

2 rows in set (0.00 sec)

 

mysql> commit;
 

 

 

 

 

 

 

 

 

#设置隔离级别

set tx_isolation='read-committed';

Query OK, 0 rows affected (0.00 sec)

 

 

 

 

 

 

 

 

#两次更新之间执行删除

mysql> delete from t1 where c1 = 2;

Query OK, 1 row affected (0.03 sec)

 

 

 

 

由以上测试知，RC隔离级别下，会话2执行时序在会话1事务的语句之间，并且会话2的操作影响了会话1的结果，这会对Binlog结果造成影响。

由于Binlog中语句的顺序以commit为序，如果语句级Binlog允许，两会话的执行时序是


#会话2

set tx_isolation='read-committed';

delete from t1 where c1 = 2;

commit;

 

#会话1

set tx_isolation='read-committed';

 

Begin;

 

update t2 set c2 = 3 where c1 in (select c1 from t1);

 

update t2 set c2 = 4 where c1 in (select c1 from t1);

 

select * from t2;

+------+------+

| c1   | c2   |

+------+------+

|    1 |    4 |

|    2 |    2 |

+------+------+

2 rows in set (0.00 sec)

 

commit;

由上可知，在MySQL 5.1及以上的RC隔离级别下，语句级Binlog在DR上执行的结果是不正确的！

 

那么，MySQL 5.0呢？5.0允许RC下语句级Binlog，是不是说很容易产生DB/DR不一致呢？

事实上，在5.0重复上述一个测试，并不存在这个问题，原因是5.0的RC与5.1的RR使用类似的并发和上锁机制，也就是说，MySQL 5.0的RC与5.1及以上的RC可能存在兼容性问题。

下面看看RR是怎么解决这个问题的。

2. 默认隔离级别-可重复读

导致RC隔离级别DB/DR不一致的原因是：RC不可重复读，而Binlog要求SQL串行化！

在RR下，重复以上测试


会话1
会话2

use test;

#初始化数据

create table t1(c1 int, c2 int) engine=innodb;

create table t2(c1 int, c2 int) engine=innodb;

 

insert into t1 values(1,1), (2,2);

insert into t2 values(1,1), (2,2);

 

#设置隔离级别

set tx_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

 

#连续更新两次

mysql> Begin;

Query OK, 0 rows affected (0.03 sec)

 

mysql> update t2 set c2 = 3 where c1 in (select c1 from t1);

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

 

 

 

 

 

mysql> update t2 set c2 = 4 where c1 in (select c1 from t1);

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

 

mysql> select * from t2;

+------+------+

| c1   | c2   |

+------+------+

|    1 |    4 |

|    2 |    4 |

+------+------+

2 rows in set (0.00 sec)

 

 

mysql> commit;
 

 

 

 

 

 

 

 

#设置隔离级别

set tx_isolation=' repeatable-read';

Query OK, 0 rows affected (0.00 sec)

 

 

 

 

 

 

 

 

 

 

#两次更新之间执行删除

mysql> delete from t1 where c1 = 2;

--阻塞，直到会话1提交

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Query OK, 1 row affected (18.94 sec)

 

与RC隔离级别不同的是，在RR中，由于保证可重复读，会话2的delete语句会被会话1阻塞，直到会话1提交。

在RR中，会话1语句update t2 set c2 = 3 where c1 in (select c1 from t1)会先在t1的记录上S锁（5.1的RC中不会上这个锁，但5.0的RC会），接着在t2的满足条件的记录上X锁。由于会话1没提交，会话2的delete语句需要等待会话1的S锁释放，于是阻塞。

因此，在RR中，以上测试会话1、会话2的依次执行，与Binlog的顺序一致，从而保证DB/DR一致。

幻象读

除了保证可重复读，MySQL的RR还一定程度上避免了幻象读（幻象读是由于插入导致的新记录）。（为什么说一定程度呢？参考第3节可重复读和串行化的区别。）


会话1
会话2

use test;

#初始化数据

create table t1(c1 int primary key, c2 int) engine=innodb;

create table t2(c1 int primary key, c2 int) engine=innodb;

 

insert into t1 values(1,1), (10,10);

insert into t2 values(1,1), (5,5), (10,10);

 

#设置隔离级别

set tx_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

 

#连续更新两次

mysql> Begin;

Query OK, 0 rows affected (0.03 sec)

 

mysql> update t2 set c2 = 20 where c1 in (select c1 from t1);

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

 

 

 

 

 

mysql> delete from where c1 in (select c1 from t1);

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

 

mysql> select * from t2;

+------+------+

| c1   | c2   |

+------+------+

|    5 |    5 |

+------+------+

2 rows in set (0.00 sec)

 

 

mysql> commit;
 

 

 

 

 

 

 

 

#设置隔离级别

set tx_isolation=' repeatable-read';

Query OK, 0 rows affected (0.00 sec)

 

 

 

 

 

 

 

 

 

#两次更新之间执行插入

mysql> insert into t1 values(5,5);

--阻塞，直到会话1提交

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Query OK, 1 row affected (18.94 sec)

 

由上述例子知，会话2的插入操作被阻塞了，原因是RR隔离级别中，除了记录锁外，还会上间隙锁(gap锁)。例如，对于表t1，update t2 set c2 = 20 where c1 in (select c1 from t1)以上的锁包括：

(-∞, 1), 1, (1, 10), 10, (10, +∞)

由于对t1做全表扫描，因此，所有记录和间隙都要上锁，其中(x,y)表示间隙锁，数字表示记录锁，全部都是S锁。会话2的insert操作插入5，位于间隙(1,10)，需要获得这个间隙的X锁，因此两操作互斥，会话2阻塞。

 

SQL标准的RR并不要求避免幻象读，而InnoDB通过gap锁来避免幻象，从而实现SQL的可串行化，保证Binlog的一致性。

 

要想取消gap lock，可使用参数innodb_lock_unsafe_for_binlog=1，默认为0。

 

3. 可重复读与串行化的区别

InnoDB的RR可以避免不可重复读和幻象读，那么与串行化有什么区别呢？


会话1
会话2

use test;

#初始化数据

create table t3(c1 int primary key, c2 int) engine=innodb;

 

#设置隔离级别

set tx_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

 

mysql> Begin;

Query OK, 0 rows affected (0.03 sec)

 

mysql> select * from t3 where c1 = 1;

Empty set (0.00 sec)

 

 

 

 

 

mysql> select * from t3 where c1 = 1;

Empty set (0.00 sec)

 

 

mysql> update t3 set c2 =2 where c1 = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from t3 where c1 = 1;

+----+------+

| c1 | c2   |

+----+------+

|  1 |    2 |

+----+------+

1 row in set (0.00 sec)

 

mysql> commit;
 

 

 

 

#设置隔离级别

set tx_isolation=' repeatable-read';

Query OK, 0 rows affected (0.00 sec)

 

 

 

 

 

 

 

mysql> insert into t3 values(1,1);

Query OK, 1 row affected (0.05 sec)

 

 

 

 

 

 

 

 

 

 

 

 

由上述会话1中，连续两次读不到数据，但更新却成功，并且更新后的相同读操作就能读到数据了，这算不算幻读呢？

 

其实，RR隔离级别的防止幻象主要是针对写操作的，即只保证写操作的可串行化，因为只有写操作影响Binlog；而读操作是通过MVCC来保证一致性读（无幻象）。

 

然而，可串行化隔离级别要求读写可串行化。使用可串行化重做以上测试。


会话1
会话2

use test;

#初始化数据

create table t3(c1 int primary key, c2 int) engine=innodb;

 

#设置隔离级别

set tx_isolation='SERIALIZABLE';

Query OK, 0 rows affected (0.00 sec)

 

mysql> Begin;

Query OK, 0 rows affected (0.03 sec)

 

mysql> select * from t3 where c1 = 1;

Empty set (0.00 sec)

 

 

 

 

 

mysql> select * from t3 where c1 = 1;

Empty set (0.00 sec)

 

 

mysql> update t3 set c2 =2 where c1 = 1;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0  Changed: 0  Warnings: 0

 

mysql> select * from t3 where c1 = 1;

Empty set (0.00 sec)

 

mysql> commit;
 

 

 

 

#设置隔离级别

set tx_isolation='SERIALIZABLE';

Query OK, 0 rows affected (0.00 sec)

 

 

 

 

 

 

 

mysql> insert into t3 values(1,1);

#阻塞，直到会话1提交

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Query OK, 1 row affected (48.90 sec)

设置为串行化后，会话2的插入操作被阻塞。由于在串行化下，查询操作不在使用MVCC来保证一致读，而是使用S锁来阻塞其他写操作。因此做到读写可串行化，然而换来就是并发性能的大大降低。

4. 小结

MySQL使用可重复读来作为默认隔离级别的主要原因是语句级的Binlog。RR能提供SQL语句的写可串行化，保证了绝大部分情况（不安全语句除外）的DB/DR一致。

另外，通过这个测试发现MySQL 5.0与5.1在RC下表现是不一样的，可能存在兼容性问题。

 

尊重版权，原链接：http://www.cnblogs.com/vinchen/archive/2012/11/19/2777919.html

原文链接:[http://wely.iteye.com/blog/2360219]

时间： 2025-01-03 07:34:27

MySQL使用可重复读作为默认隔离级别的原因之一的相关文章

【MySQL】可重复读模式下 unique key失效案例

一 [背景]    今天上午文能提笔安天下,武能上马定乾坤的登博给团队出了一道题目,谁先复现问题,奖励星巴克一杯.激起了一群忙碌的屌丝DBA的极大热情.问题是这样滴,如下图登博提示了几个细节:   1. code上的uk并未失效.   2. rr隔离级别.   3. 有并发线程的操作.二 [原理分析]1 事务隔离级别的基础知识:  未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据.  提交读(Read Committed):只能读取到已经提交

【MySQL】可重复读下的幻读

[背景] 在研究gap lock的时候,参考了一篇文章http://www.mysqlperformanceblog.com/2012/03/27/innodbs-gap-locks/按照文章的实验来进行测试并不会出现幻读. [概念] 幻读(Phantom Read) 是指当用户读取某一范围的数据行时,B事务在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的"幻影"行.InnoDB和Falcon存储引擎通 过多版本并发控制机制解决了幻读问题. [验证] 做一个小的测试来验

Mysql事务，并发问题，锁机制-- 幻读、不可重复读（转）

1.什么是事务 事务是一条或多条数据库操作语句的组合,具备ACID,4个特点. 原子性:要不全部成功,要不全部撤销 隔离性:事务之间相互独立,互不干扰 一致性:数据库正确地改变状态后,数据库的一致性约束没有被破坏 持久性:事务的提交结果,将持久保存在数据库中   2.事务并发会产生什么问题 1)第一类丢失更新:在没有事务隔离的情况下,两个事务都同时更新一行数据,但是第二个事务却中途失败退出, 导致对数据的两个修改都失效了. 例如: 张三的工资为5000,事务A中获取工资为5000,事务B获取工资

脏读、不可重复读、幻读

锁就是防止其他事务访问指定的资源的手段.锁是实现并发控制的主要方法,是多个用户能够同时操纵同一个数据库中的数据而不发生数据不一致现象的重要保障.一般来说,锁可以防止脏读.不可重复读和幻觉读.   事务并发产生的问题:         脏读:一个事务读取到了另外一个事务没有提交的数据             事务1:更新一条数据                              ------------->事务2:读取事务1更新的记录             事务1:调用commit进行提

SQL Server 中的事务与事务隔离级别以及如何理解脏读, 未提交读,不可重复读和幻读产生的过程和原因

原文:SQL Server 中的事务与事务隔离级别以及如何理解脏读, 未提交读,不可重复读和幻读产生的过程和原因 原本打算写有关 SSIS Package 中的事务控制过程的,但是发现很多基本的概念还是需要有 SQL Server 事务和事务的隔离级别做基础铺垫.所以花了点时间,把 SQL Server 数据库中的事务概念,ACID 原则,事务中常见的问题,问题造成的原因和事务隔离级别等这些方面的知识好好的整理了一下. 其实有关 SQL Server 中的事务,说实话因为内容太多, 话题太广,稍

事务 脏读、不可重复读、幻影读的分析

1 丢失修改 2 脏读:当事务1修改了一条记录,没有提交时,事务2读取了该记录:当事务1回滚了,那么事务2的记录就是一条不存在的记录: 3 不可重复读:当事务1读取了一条记录,未提交事务,事务2修改了该条记录并且提交事务:事务1又读取了该条记录,发现两条记录不一样: 4 幻影读:当事务1根据某种检索条件读取了若干条记录,未提交事务:而事务2又插入了一条记录,该记录也符合事务1的检索条件:那么当事务1在根据相同查询条件检索数据时候,出现了不一致的现象. 根据锁机制来避免上诉问题: 排他锁:数据加锁

.NET中 关于脏读 不可重复读与幻读的代码示例_实用技巧

并发可能产生的三种问题 脏读 定义:A事务执行过程中B事务读取了A事务的修改,但是A事务并没有结束(提交),A事务后来可能成功也可能失败. 比喻:A修改了源代码并且并没有提交到源代码系统,A直接通过QQ将代码发给了B,A后来取消了修改. 代码示例 复制代码 代码如下: [TestMethod]         public void 脏读_测试()         {             //前置条件             using (var context = new TestEnti

mysql 不指定 ip 的连接默认都是 localhost

问题场景 在本地使用 docker 启动了一个 mysql 容器,并把本地的 33067 端口映射到容器内 mysql 的 3306 ,我们假设容器的 ip 地址是 172.17.0.2 ,使用本地的 mysql 客户端尝试连接容器内部的 mysql 服务. 方法 1 由于是绑定本地的 33067 端口,所以在连接时指定端口号即可. mysql -uroot -P33067 此时 mysql-client 实际上进入了本地的 mysql 服务,使用 status 命令可以查看 Connectio

数据库 不可重复读是为什么处理什么样的应用场景？

问题描述 数据库 不可重复读是为什么处理什么样的应用场景? 数据库 不可重复读是为什么处理什么样的应用场景? 在实际项目中会导致什么样的问题出现? 解决方案 在一个事务内,多次读同一个数据.在这个事务还没有结束时,另一个事务也访问该同一数据.那么,在第一个事务的两次读数据之间.由于第二个事务的修改,那么第一个事务读到的数据可能不一样,这样就发生了在一个事务内两次读到的数据是不一样的,因此称为不可重复读,即原始读取不可重复. 解决方案二: 第一个事务进行读取数据未结束,同时第二个事务对数据进行修改