mysql innodb事务中出现幻读的_mysql innodb默认事务隔离级别-repeatable read在事务中是否能导致幻读的实验...

测试准备：

1.show variables like '%unsafe%';确保 innodb_locks_unsafe_for_binlog值为 OFF(或者0)；

2.show variables like '%tx%'; 确保 tx_isolation 的值为 REPEATABLE-READ。

3.新建表t_test :

CREATE TABLEt_test

(

idINT(32) NOT NULL,

valCHAR(1) DEFAULT '0' NOT NULL,PRIMARY KEY(id)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8;

test1:

start transaction;select * from t_test where id >100; //无结果select sleep(10); // >>此时执行tx2: insert into t_test values (102,0); //插入成功select * from t_test where id >100; //无结果commit;

结论：第二次查询与第一次一致，没有出现幻读。根据mysql repeatable read的mvcc机制，两次select读均为快照读(即读取的数据来自undo log)。这也是避免了不可重复读的原因。

test2:

start transaction;select * from t_test where id >100; //无结果select sleep(10); // >>此时执行tx2: insert into t_test values (103,0); //插入成功update t_test set val=1 where id >100;select * from t_test where id >100; //查询到了103这一条commit;

结论：第二次查询出现幻读。执行update时创建了undo log中涉及到的行的新版本。

test3:

start transaction;select * from t_test where id >100;//有id为103这一行原始数据select sleep(10); //>>此时执行tx2:insert into t_test values (104,0); //插入成功update t_test set val=2 where id <104;select * from t_test where id >100;//只查询到id为103的行commit;

结论：未出现幻读。和test2相比，update的where条件没有覆盖到tx2的insert行。

test4:

start transaction;select * from t_test where id >104 FOR UPDATE; //无结果select sleep(10); // >>此时执行tx2:insert into t_test values (105,0); //阻塞 直到tx1 commit后插入成功update t_test set val=1 where id >100;select * from t_test where id >104; //无结果commit;

结论：未出现幻读。第一个查询的 FOR UPDATE相当于给id>100的数据加了next-key lock。当然，我们的实验改用共享锁LOCK IN SHARE MODE结果是一样的。

test5:

start transaction;select * from t_test where id >105; //无结果select sleep(10); // >>此时执行tx2:insert into t_test values (106,0); //插入成功select * from t_test where id >105 FOR UPDATE; //出现幻读commit;

结论：第二次查询出现幻读。第二次加锁的查询为当前读(即跳过undo log,直接查库)。

test6:

start transaction;select * from t_test where id =106; //id:106 val:0

select sleep(10); // >>此时执行tx2:update t_test set val ='8' where id=106;//执行成功select * from t_test where id =106 FOR UPDATE; //id:106 val:8出现不可重复读commit;

结论：第二次查询出现不可重复读。原理同test5

test7:

start transaction;select * from t_test where id =107;//无结果select sleep(10); //>>此时执行insert into t_test values (107,0); //插入成功select * from t_test where id =107 FOR UPDATE; //查询到id为107的数据select * from t_test where id =107;//无结果commit;

结论：第三次查询没有受到第二次加锁查询的影响，说明加锁查询并不会对undo log进行任何操作。

总结：我们在这里可以将undo log抽象地理解为一种缓存机制 ，我们只要保证这个“缓存”在事务开始时是最新数据的缓存，并记录该缓存版本号，即使在事务执行过程中有其他事务提交数据的增删改操作，也不会影响到该版本号“缓存”的数据。在事务中执行不加锁的select操作时，会直接从“缓存”中拿数据；在事务中执行增删改时，会更新undo log中影响的数据行的信息，而若在第一次查询和更新之间的时间范围内，有其他事务提交了插入数据的操作，且更新时的条件覆盖了插入的数据，则会导致幻读；而对查询语句加锁，则会直接从库中查数据(即当前读)，但加锁查询并不会更新快照。