innodb為實現MVCC所使用的內部快照,RR(REPEATABLE READ)隔離級別下在第一次查詢時創建read view,RC(READ COMMITTED)隔離級別下會在每次查詢時創建read view以下測試在RR隔離級別下,資料庫版本為5.7.201. select * from ta ...
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; |
|
insert into tab1 values (1,"1"); | |
select * from tab1; |
|
commit; | |
select * from tab1; |
|
commit; |
|
select * from tab1; |
|
結論:在已經查詢後,其他事務做的修改,在本事務不可見
2.
session A | session B |
truncate table tab1; | |
start transaction; |
|
start transaction; | |
insert into tab1 values (1,"1"); | |
commit; | |
select * from tab1; |
結論:儘管事務A比事務B先開始,但是第一次查詢在B事務提交後,所以可以查詢到結果
3.
session A | session B |
truncate table tab1; | |
start transaction; |
|
start transaction; | |
select * from tab1; |
|
insert into tab1 values (1,"1"); | |
insert into tab1 values (2,"2"); | |
insert into tab1 values (3,"3"); | |
commit; | |
select * from tab1; |
|
update tab1 set col2 ="22" where col1>=2; 2 rows affected |
|
select * from tab1; |
結論:雖然事務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