mysql read-view_read view初探

innodb为实现MVCC所使用的内部快照，RR(REPEATABLE READ)隔离级别下在第一次查询时创建read view,RC(READ COMMITTED)隔离级别下会在每次查询时创建read view

以下测试在RR隔离级别下，数据库版本为5.7.20

1.

session A

session B

start transaction;

start transaction;

select * from tab1;

Empty set

insert into tab1 values (1,"1");

select * from tab1;

Empty set

commit;

select * from tab1;

Empty set

commit;

select * from tab1;

+------+------+

| col1 | col2 |

+------+------+

| 1 | 1 |

+------+------+

结论：在已经查询后，其他事务做的修改，在本事务不可见

2.

session A

session B

truncate table tab1;

start transaction;

start transaction;

insert into tab1 values (1,"1");

commit;

select * from tab1;

+------+------+

| col1 | col2 |

+------+------+

| 1 | 1 |

+------+------+

结论：尽管事务A比事务B先开始，但是第一次查询在B事务提交后，所以可以查询到结果

3.

session A

session B

truncate table tab1;

start transaction;

start transaction;

select * from tab1;

Empty set

insert into tab1 values (1,"1");

insert into tab1 values (2,"2");

insert into tab1 values (3,"3");

commit;

select * from tab1;

Empty set

update tab1 set col2 ="22" where col1>=2;

2 rows affected

select * from tab1;

+------+------+

| col1 | col2 |

+------+------+

| 2 | 22 |

| 3 | 22 |

+------+------+

结论：虽然事务A看不到事务B做的修改，但是修改也会影响事务B已经提交的数据，且修改发生后，被修改的记录(尽管是其他事务提交的)，也会变为对该事务可见

另外：

1.select ... for update和select ... lock in share mode(8.0是select ... for share)会重新生成read view

2.select ... with consistent snapshot不会读到在本事务开始后提交的数据，即使第一次select是在其他事务提交后

参考网址：

1. https://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html

2. http://kabike.iteye.com/blog/1820553