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