MySQL虚读和幻读

发表于   |   分类于

1. 数据库事务ACID特性

数据库事务的4个特性：

原子性(Atomic)： 事务中的多个操作，不可分割，要么都成功，要么都失败； All or Nothing.

一致性(Consistency)： 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后，总金额不变；

隔离性(Isolation)： 多个事务之间就像是串行执行一样，不相互影响;

持久性(Durability)： 事务提交后被持久化到永久存储.

2. 隔离性

其中 隔离性 分为了四种：

READ UNCOMMITTED：可以读取未提交的数据，未提交的数据称为脏数据，所以又称脏读。此时：幻读，不可重复读和脏读均允许；

READ COMMITTED：只能读取已经提交的数据；此时：允许幻读和不可重复读，但不允许脏读，所以RC隔离级别要求解决脏读；

REPEATABLE READ：同一个事务中多次执行同一个select,读取到的数据没有发生改变；此时：允许幻读，但不允许不可重复读和脏读，所以RR隔离级别要求解决不可重复读；

SERIALIZABLE：幻读，不可重复读和脏读都不允许，所以serializable要求解决幻读；

3. 几个概念

脏读：可以读取未提交的数据。RC 要求解决脏读；

不可重复读：同一个事务中多次执行同一个select, 读取到的数据发生了改变(被其它事务update并且提交)；

可重复读：同一个事务中多次执行同一个select, 读取到的数据没有发生改变(一般使用MVCC实现)；RR各级级别要求达到可重复读的标准；

幻读：同一个事务中多次执行同一个select, 读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务delete/insert并且提交)。SERIALIZABLE要求解决幻读问题；

这里一定要区分 不可重复读 和 幻读：

不可重复读的重点是修改： 同样的条件的select, 你读取过的数据, 再次读取出来发现值不一样了

幻读的重点在于新增或者删除： 同样的条件的select, 第1次和第2次读出来的记录数不一样

从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大： 对于前者, 在RC下只需要锁住满足条件的记录，就可以避免被其它事务修改，也就是 select for update, select in share mode; RR隔离下使用MVCC实现可重复读； 对于后者, 要锁住满足条件的记录及所有这些记录之间的gap，也就是需要 gap lock。

隔离级别：

隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读 （Phantom Read）

未提交读（Read uncommitted）

可能

可能

可能

已提交读（Read committed）

不可能

可能

可能

可重复读（Repeatable read）

不可能

不可能

可能

可串行化（Serializable ）

不可能

不可能

不可能

4. 数据库的默认隔离级别

除了MySQL默认采用RR隔离级别之外，其它几大数据库都是采用RC隔离级别。

但是他们的实现也是极其不一样的。Oracle仅仅实现了RC 和 SERIALIZABLE隔离级别。默认采用RC隔离级别，解决了脏读。但是允许不可重复读和幻读。其SERIALIZABLE则解决了脏读、不可重复读、幻读。

MySQL的实现：MySQL默认采用RR隔离级别，SQL标准是要求RR解决不可重复读的问题，但是因为MySQL采用了gap lock，所以实际上MySQL的RR隔离级别也解决了幻读的问题。那么MySQL的SERIALIZABLE是怎么回事呢？其实MySQL的SERIALIZABLE采用了经典的实现方式，对读和写都加锁。

create table `test_trx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT  CHARSET=utf8;

insert into test_trx(value) value(1),(2),(3),(4),(5);

下面以例子来说明上面上面的情况

1. 脏读

事务1 事务2

1

 
set session transaction isolation level read uncommitted;

select @@tx_isolation;
+------------------+
|\

@@tx_isolation | ------------------ |\

READ-UNCOMMITTED | ------------------

begin; ----

 
set session transaction isolation level read uncommitted;

select @@tx_isolation;
+------------------+
|\

@@tx_isolation | ------------------ |\

READ-UNCOMMITTED | ------------------

begin;

2

 
update test_trx set value = 6 where id=3;

3

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

6 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | -----------+ ----

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

6 | |\

4 | 4 \

4 | |\

5 | 5 \
Note

未提交隔离级别, 事务1中更新的value,未提交,事务2中可以看到. 造成脏读.

2. 虚读(不可重复读)

事务1 事务2

1

 
set session transaction isolation level read committed;

select @@tx_isolation;
+------------------+
|\

@@tx_isolation | ------------------ |\

READ-COMMITTED | ------------------

begin; ----

 
set session transaction isolation level read committed;

select @@tx_isolation;
+------------------+
|\

@@tx_isolation | ------------------ |\

READ-COMMITTED | ------------------

begin;

2

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

3 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | -----------+ ----

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

3 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | -----------+ ----

3

 
update test_trx set value = 6 where id=3;

4

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

6 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | -----------+ ----

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

3 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | -----------+ ----

5

 
commit;

6

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

6 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | -----------+ ----

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

6 | |\

4 | 4 \

4 | |\

5 | 5 \
Note

读提交隔离级别,事务2在同一个事务中,读取事务1提交后的update更改

3. 幻读

看下大部分mysql所说的幻读现象

事务1 事务2

1

 
set session transaction isolation level repeatable read ;

select @@tx_isolation;
+-----------------+
|\

@@tx_isolation | ----------------- |\

REPEATABLE-READ | -----------------

begin; ----

 
set session transaction isolation level repeatable read ;

select @@tx_isolation;
+-----------------+
|\

@@tx_isolation | ----------------- |\

REPEATABLE-READ | -----------------

begin;

2

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

3 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | |\

6 | 6 \

6 | -----------+ 6 rows in set (0.00 sec) ----

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

3 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | |\

6 | 6 \

6 | -----------+ 6 rows in set (0.00 sec) ----

3

 
insert into test_trx(id,value) values(7,7);
Query OK, 1 row affected (0.00 sec)

select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

3 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | |\

6 | 6 \

6 | |\

7 | 7 \

7 | -----------+ 7 rows in set (0.00 sec)

commit; ----

4

 
select * from test_trx;
+----+-------+
|\

id | id \

value | -----------+ |\

1 | 1 \

1 | |\

2 | 2 \

2 | |\

3 | 3 \

3 | |\

4 | 4 \

4 | |\

5 | 5 \

5 | |\
Note

事务2,查询记录里面没有记录7,插入7的时候提示主键重复,幻读.

RR事务隔离级别号称可以解决幻读的问题（通过当前读加锁来实现）

5.gap锁（当前读）解决幻读问题

mysql说对数据加锁不管共享锁还是互斥锁就能解决幻读的问题

select * from test_trx where id between 1 and 7 lock in share mode;

select * from test_trx where id between 1 and 7 for update;

通过gap锁+当前读解决幻读问题,但是lock in share mode 和for update不是标准的sql语法.

6.RR没有解决的幻读

场景：我们知道grap锁能够将右边的记录进行加锁，因此我要统计表记录的数量，我只需要对最大记录加锁就行了

文章目录
  1. 1. 数据库事务ACID特性
  2. 2. 隔离性
  3. 3. 几个概念
  4. 4. 数据库的默认隔离级别
    1. 1. 脏读
    2. 2. 虚读(不可重复读)
    3. 3. 幻读
  5. 5.gap锁（当前读）解决幻读问题
  6. 6.RR没有解决的幻读
Charles ©2021   浙ICP备14035515号-1