脏读&幻读&不可重复读和事务隔离级别

脏读&幻读&不可重复读

一、脏读

 脏读就是一个事务访问到了另外一个事务未提交的数据

时间点 会话1 会话2
T1 start tx
T2 start tx
T3 update account set balance=10 where user='a'
T4 select balance from account where user='a';return 10

脏读实验测试：

T1-会话1：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

T2-会话2：

mysql> set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

T3-会话1：

mysql> select balance from account where user_name= '李明' ;
+---------+
| balance |
+---------+
|     100 |
+---------+
1 row in set (0.00 sec)
mysql> update account set balance=balance-10 where user_name= '李明';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

T4-会话2：

mysql> select balance from account where user_name= '李明' ;
+---------+
| balance |
+---------+
|      90 |
+---------+
1 row in set (0.00 sec)

从这发现T4时间会话2读取到了会话1未提交的事务数据，这就是脏读。

二、不可重复读

 不可重复读就是事务A多次读取同一范围记录，事务B在事务A多次读取的过程中，对数据进行更新并提交，从而导致事务A多次读取同一数据时，结果不一致。

不可重复读实验测试

  • read-committed隔离级别的不可重复读：

T1-会话1：

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select balance from account where user_name= '李明' ;
+---------+
| balance |
+---------+
|     100 |
+---------+
1 row in set (0.00 sec)

T2-会话2：

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=90 where user_name= '李明' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

T3-会话1：

mysql> select balance from account where user_name= '李明' ;
+---------+
| balance |
+---------+
|      90 |
+---------+
1 row in set (0.00 sec)
  • repeatable-read隔离级别的不可重复读：
  1. 事务A第一次select是在事务B提交之前

T1-会话1：

mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select balance from account where user_name= '李明' ;
+---------+
| balance |
+---------+
|      80 |
+---------+
1 row in set (0.00 sec)

T2-会话2：

mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=60 where user_name= '李明' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

T3-会话1：

mysql> select balance from account where user_name= '李明' ;
+---------+
| balance |
+---------+
|      80 |
+---------+
1 row in set (0.00 sec)
  1. 事务A第一次select是在事务B提交之后

T1-会话1：

mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

T2-会话2：

mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=40 where user_name= '李明' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

T3-会话1：

mysql> select balance from account where user_name= '李明' ;
+---------+
| balance |
+---------+
|      40 |
+---------+
1 row in set (0.00 sec)

三、幻读

幻读主要是说多次读取一个范围内的记录(包括直接查询所有记录结果或者做聚合统计)，发现结果不一致(记录增多或者减少)
其实对于幻读，MySQL的InnoDB引擎默认的RR级别已经通过MVCC自动帮我们解决了，所以在该级别下，你也模拟不出幻读的场景。理论上RR级别是无法解决幻读的问题，但是由于InnoDB引擎的RR级别还使用了MVCC，所以也就极大程度避免了幻读的出现。

但是某些情况还是无法避免幻读的出现，测试如下：
T1:会话1

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where user_id>5;
+---------+-----------+---------+
| user_id | user_name | balance |
+---------+-----------+---------+
|       6 | 李白5     |    5000 |
|       7 | 李白5     |    5000 |
|       8 | 李白      |    5000 |
|       9 | 李白3     |    5000 |
|      11 | 李白10    |    5000 |
+---------+-----------+---------+
5 rows in set (0.00 sec)

T2：会话2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account(user_id,user_name,balance) values(null,'李白12',5000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

T3：会话1

mysql> select * from account where user_id>5;
+---------+-----------+---------+
| user_id | user_name | balance |
+---------+-----------+---------+
|       6 | 李白5     |    5000 |
|       7 | 李白5     |    5000 |
|       8 | 李白      |    5000 |
|       9 | 李白3     |    5000 |
|      11 | 李白10    |    5000 |
+---------+-----------+---------+
5 rows in set (0.00 sec)

mysql> update account set balance=123 where user_id=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account where user_id>5;
+---------+-----------+---------+
| user_id | user_name | balance |
+---------+-----------+---------+
|       6 | 李白5     |    5000 |
|       7 | 李白5     |    5000 |
|       8 | 李白      |    5000 |
|       9 | 李白3     |    5000 |
|      11 | 李白10    |    5000 |
|      12 | 李白12    |     123 |
+---------+-----------+---------+
6 rows in set (0.00 sec)

可以看到在会话1中查询到了原来并不存在的第12条记录，这就是幻读。这种情况影响不大，只是新增了一条数据库，如下情况影响就比较大了：
T1:会话1

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account where user_id=11;
+---------+-----------+---------+
| user_id | user_name | balance |
+---------+-----------+---------+
|      11 | 李白10    |    5000 |
+---------+-----------+---------+
1 row in set (0.00 sec)

T2：会话2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=balance+5000 where user_id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

T1：会话1

mysql> select * from account where user_id=11;
+---------+-----------+---------+
| user_id | user_name | balance |
+---------+-----------+---------+
|      11 | 李白10    |    5000 |
+---------+-----------+---------+
1 row in set (0.00 sec)

mysql> update account set balance=balance+1000 where user_id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account where user_id=11;
+---------+-----------+---------+
| user_id | user_name | balance |
+---------+-----------+---------+
|      11 | 李白10    |   11000 |
+---------+-----------+---------+
1 row in set (0.00 sec)

在会话1看来莫名其妙就增加了5000，这如果是在银行账户系统中就是比较严重的问题。也属于幻读的一种。
这个问题如何解决呢？
通过加锁解决

select * from account where user_id=11 LOCK IN SHARE MODE; #加一把共享锁，这样另一事务不能修改这条记录

如果加共享锁，可能会产生死锁的情况发生，如果想避免死锁的话可以加互斥锁。

select * from account where user_id=11 for update; #加一把互斥锁，这样另一事务就不可以再获得这条记录上的锁，从而避免死锁

下面来看事务隔离级别与脏读、幻读和不可重复读之间的关系

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
读已提交（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
串行化（serializable） 否 否 否

总结：

  1. 脏读：一个事务读取到了另外一个事务未提交的数据。
  2. 不可重复读：在一个事务中读取到了另外事务已提交的变更数据库。在RR事务隔离级别中，读取的数据是否为另外一个事务已提交的数据，取决于该事务第一条select语句是在另外事务提交之前还是之后，如果是之前则读取提交之前的数据，如果是之后则读取提交之后的数据。
  3. 幻读：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读。
  4. 会话级别是在事务开始之前生效，即在begin开始一个事务之前是什么事务隔离级别该事务就是什么隔离级别，事务开始之后再进行更改是无法生效的。
  5. 在可重复读隔离级别下配合MVCC很大程度可以避免幻读，但是无法完全避免，在设计钱方面的业务需要注意幻读的影响，在并发性和隔离性做出平衡。

参考：
MySQL事务隔离级别为"REPEATABLE-READ"下的"幻读"现象

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容