MySQL事务隔离级别为"REPEATABLE-READ"下的"幻读"现象
关于mysql命令行中事务控制的语句见该文章：http://my.oschina.net/xinxingegeya/blog/296459
本片参考文章：http://blog.csdn.net/jiao_fuyou/article/details/16368827
http://www.cnblogs.com/hancf/archive/2012/08/28/2660422.html
mysql中的可重复读
这里打开两个mysql的命令行窗口，窗口A，即session1，窗口B，即session2。
session1
mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | +----+------+ 4 rows in set (0.00 sec)
session2
mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values (55, 3000); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.03 sec) mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | | 55 | 3000 | +----+------+ 5 rows in set (0.00 sec)
session2事务已经显示提交
session1
此时返回session1进行以下操作
mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | +----+------+ 4 rows in set (0.00 sec)
此时，虽然在session2中插入了一条数据，并且提交了事务，但在session1中的查询和session1的上次查询还是同一个结果，这就是重复读。如果是在"READ-COMMITTED"级别下是可以读到a=55这条记录的。
测试mysql可重复读（二）
session1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 where a = 56; +----+------+ | a | b | +----+------+ | 56 | 7000 | +----+------+ 1 row in set (0.00 sec)
session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update t1 set b = 8000 where a = 56; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1 where a = 56; +----+------+ | a | b | +----+------+ | 56 | 8000 | +----+------+ 1 row in set (0.00 sec) mysql> commit -> ; Query OK, 0 rows affected (0.04 sec)
session1
mysql> select * from t1 where a = 56; +----+------+ | a | b | +----+------+ | 56 | 7000 | +----+------+ 1 row in set (0.00 sec)
和上次查询结果一致，验证了重复读。。。
此时，如果session1提交该事务，重新开启事务，查询能查到session2中修改的结果
mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 where a = 56; +----+------+ | a | b | +----+------+ | 56 | 8000 | +----+------+ 1 row in set (0.00 sec)
REPEATABLE-READ下的幻读
session1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | | 55 | 4000 | | 56 | 8000 | +----+------+ 6 rows in set (0.00 sec)
session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | | 55 | 4000 | | 56 | 8000 | +----+------+ 6 rows in set (0.00 sec) mysql> insert into t1 values (57, 1000); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | | 55 | 4000 | | 56 | 8000 | | 57 | 1000 | +----+------+ 7 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.11 sec)
session1
mysql> update t1 set b = b+1000; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 4000 | | 52 | 4000 | | 53 | 4000 | | 54 | 4000 | | 55 | 5000 | | 56 | 9000 | | 57 | 2000 | +----+------+ 7 rows in set (0.00 sec)
session1整个会话的sql
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | | 55 | 4000 | | 56 | 8000 | +----+------+ 6 rows in set (0.00 sec) mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | | 55 | 4000 | | 56 | 8000 | +----+------+ 6 rows in set (0.00 sec) mysql> update t1 set b = b+1000; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> select * from t1; +----+------+ | a | b | +----+------+ | 51 | 4000 | | 52 | 4000 | | 53 | 4000 | | 54 | 4000 | | 55 | 5000 | | 56 | 9000 | | 57 | 2000 | +----+------+ 7 rows in set (0.00 sec) mysql>
可以看到多出了一行，这就是幻读。。。
但有一个问题，还是不明白，幻读和不可重复读到底什么区别？网上查了很多资料还是说的不明白，希望有了解的同学可以交流一下啊。。。
=====END====