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没有解决的幻读