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

 

 

 

 

 

 

 

网上很多说范围啊,count等等都是不对的,不用于幻读

 

posted @ 2017-09-29 18:12  crazyYong  阅读(9583)  评论(6编辑  收藏  举报