Mysql加锁过程详解（2）-关于mysql 幻读理解

首先，mysql 幻读并非是”一个事务内进行两次相同操作居然得到了不一样的结果”，因为它根本不可能发生在使用了 read view / MVCC 的 RR 隔离级别下，这种幻读的定义更适合给 Oracle，Oracle 的事务隔离只有两级，RC 和 Serializable。然后还有很多人辩解说不可重复读是针对某条记录的，幻读是针对记录集合的，这是在自我安慰么？

这里给出 mysql 幻读的比较形象的场景：

 

users： id 主键

1、T1：select * from users where id = 1;

2、T2：insert into `users`(`id`, `name`) values (1, 'big cat');

3、T1：insert into `users`(`id`, `name`) values (1, 'big cat');

 

T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。

T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。

 

在 RR 隔离级别下，1、2 是会正常执行的，3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，因为T1读取的数据状态并不能支持他的下一步的业务，见鬼了一样。

在 Serializable 隔离级别下，1 执行时是会隐式的添加 gap 共享锁的，从而 2 会被阻塞，3 会正常执行，对于 T1 来说业务是正确的，成功的扼杀了扰乱业务的T2，对于T1来说他读取的状态是可以拿来支持业务的。

 

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达 读-读，幻读则是说 读-写，用写来证实读的是鬼影。

 

下面例子版本

SELECT VERSION();

 

 

 

例子1，读提交

a	b
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
1.不可重复读
begin	begin
INSERT test VALUES(1,1);
SELECT * FROM test;    这说明在读已提交的隔离级别中，对于各个事务之间在未提交事务之前的插入查询是隔离的，所以不会出现脏读	SELECT * FROM test;
commit
	SELECT * FROM test;
	COMMIT
B在一个事务的查询的结果变了，不可重复读（这个就是出现了幻读）
2.锁
begin	begin
INSERT test VALUES(2,2);
	SELECT * FROM test;
	INSERT test VALUES(2,2);
	Lock wait timeout exceeded; try restarting transaction
COMMIT	COMMIT
加锁的情况下会出现死锁的情况
	begin
	INSERT test VALUES(3,3);
	INSERT test VALUES(4,4);
	COMMIT
BEGIN	BEGIN
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;
INSERT test VALUES(5,5);
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;
COMMIT
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

 

 

例子2：重复读

 

a	b
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET AUTOCOMMIT=0;
1．可重复读
begin	begin
INSERT test VALUES(1,1);
SELECT * FROM test;	SELECT * FROM test;
commit
	SELECT * FROM test;
	COMMIT（这是由于可重复读的隔离级别保证了每次读到的结果都是一致的）
	BEGIN
	SELECT * FROM test;
	COMMIT
B在一个事务中的查询结果没有改变
2锁
begin	begin
INSERT test VALUES(2,2);
	SELECT * FROM test;
	INSERT test VALUES(2,2);
	Lock wait timeout exceeded; try restarting transaction
COMMIT	COMMIT（这块造成死锁的原因一定要弄明白？）
3(幻读)
BEGIN	BEGIN
INSERT test VALUES(3,3);
SELECT * FROM test;	SELECT * FROM test;
COMMIT
	SELECT * FROM test;
	INSERT test VALUES(3,3);
	Duplicate entry '3' for key 'PRIMARY'
	COMMIT
	BEGIN
	SELECT * FROM test;
	COMMIT
幻读，b明明查到没有，插入时候提示主键冲突，刚刚查询没有，出现幻觉？
	begin
	INSERT test VALUES(4,4);
	COMMIT
4．可重复读
BEGIN	BEGIN
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;
INSERT test VALUES(5,5);
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;
COMMIT
BEGIN
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;
COMMIT	COMMIT

原文链接： http://www.cnblogs.com/crazylqy/p/7612230.html