幻读与不可重复读区别

不可重复读与幻读比较相似，都是在一个事务中多次读取到不同的数据。网络上的总结如下

不可重复读：所谓的虚读，也就是大家经常说的不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另 一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内 两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

所谓幻读，是指事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。
幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也 修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一 样.一般解决幻读的方法是增加范围锁RangeS，锁定检锁范围为只读，这样就避免了幻读。简单来说，幻读是由插入或者删除引起的。

大致的区别在于不可重复读是由于另一个事务对数据的更改所造成的，而幻读是由于另一个事务插入或删除引起的。验证如下：

设置全局与线程隔离级别为read commit，并设置事务手动提交：

mysql> set global transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;

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

| @@tx_isolation |

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

| READ-COMMITTED |

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

1 row in set (0.00 sec)

mysql> set global autocommit=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show global variables like '%auto%';

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

| Variable_name               | Value |

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

| auto_increment_increment    | 1     |

| auto_increment_offset       | 1     |

| autocommit                  | OFF   |

| automatic_sp_privileges     | ON    |

| innodb_autoextend_increment | 64    |

| innodb_autoinc_lock_mode    | 1     |

| innodb_stats_auto_recalc    | ON    |

| sql_auto_is_null            | OFF   |

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

8 rows in set (0.00 sec)

 

开始事务：t1                             事务t2

mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test1; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | |  4 | d    | +----+------+ 4 rows in set (0.01 sec)
	mysql> use tpcc Database changed mysql> update test1 set name='e' where id=4; Query OK, 1 row affected (0.03 sec) Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from test1; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | |  4 | d    | +----+------+ 4 rows in set (0.00 sec) 未发生脏读
	mysql> commit; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | |  4 | e    | +----+------+ 4 rows in set (0.00 sec) 不可重复读发生
	mysql> insert into test1(name) values ('f'); Query OK, 1 row affected (0.00 sec)   mysql> commit; Query OK, 0 rows affected (0.01 sec)
mysql> select * from test1; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | |  4 | e    | |  5 | f    | +----+------+ 5 rows in set (0.00 sec) 幻读发生
mysql> commit; Query OK, 0 rows affected (0.00 sec) 结束事务，释放锁。

 

可发现在read committed模式下存在不可重复读与幻读。t1、t2事务对test1加共享锁，均可以修改数据但是在commit后生效。

修改事务隔离级别为repeatable read：

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> set global transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

T1 T2

mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select name from test1 where id =4; +------+ | name | +------+ | d    | +------+ 1 row in set (0.00 sec)
	mysql> update test1 set name='f' where id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
mysql> select name from test1 where id =4; +------+ | name | +------+ | d    | +------+ 1 row in set (0.00 sec) 未发生脏读
	mysql> commit; Query OK, 0 rows affected (0.00 sec) 成功提交
mysql> select name from test1 where id =4; +------+ | name | +------+ | d    | +------+ 1 row in set (0.00 sec) 未发生不可重复读
mysql> commit; Query OK, 0 rows affected (0.00 sec) 事务提交，释放表锁
mysql> select name from test1 where id =4; +------+ | name | +------+ | f    | +------+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) 数据变化
mysql> begin; Query OK, 0 rows affected (0.00 sec)   mysql> select * from test1; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | |  4 | f    | |  5 | f    | +----+------+ 5 rows in set (0.00 sec)
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test1(name)values('g'); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)   mysql> select * from test1 where id=6; +----+------+ | id | name | +----+------+ |  6 | g    | +----+------+ 1 row in set (0.00 sec)   mysql> commit; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | |  4 | f    | |  5 | f    | +----+------+ 5 rows in set (0.00 sec)   mysql> select count(*) from test1; +----------+ | count(*) | +----------+ |        5 | +----------+ 1 row in set (0.00 sec)   mysql> select * from test1 where id=6; Empty set (0.00 sec) 因垂斯汀！！！幻读不见了

后来了解到是因为GAP锁的存在解决了幻读。