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 TABLE
    t_test 
    (
        id INT(32) NOT NULL,
        val CHAR(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中影响的数据行的信息，而若在第一次查询和更新之间的时间范围内，有其他事务提交了插入数据的操作，且更新时的条件覆盖了插入的数据，则会导致幻读；而对查询语句加锁，则会直接从库中查数据（即当前读），但加锁查询并不会更新快照。