Session A

Session B

Session A>drop table t;
Session A>create table t(x int primary key);
Session A>insert into t values (1),(5);
Session A>begin;
Session A>select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
Session A>select * from t where x=5 for update;

上面的for update進(jìn)行的鎖定讀，此時(shí)并沒(méi)有創(chuàng)建read view注意：for update和lock in share mode都是鎖定讀，此時(shí)并不會(huì)創(chuàng)建快照

Session B>begin;
Session B>select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
Session B>insert into t select 3;
Session B>commit;

Session A>select * from t;  #此時(shí)才創(chuàng)建read view生成快照
+---+
| x |
+---+
| 1 |
| 3 |
| 5 |
+---+

你可能會(huì)發(fā)現(xiàn)RR隔離級(jí)別，不是應(yīng)該看不到其他事務(wù)修改的數(shù)據(jù)嗎？這正是因?yàn)閎egin開啟一個(gè)事務(wù)，不是begin的時(shí)候創(chuàng)建read view，而是第一次進(jìn)行快照讀的時(shí)候才創(chuàng)建

Session A

Session B

ession A>create table a (x int primary key,y int);
Query OK, 0 rows affected (0.17 sec)
#開啟一個(gè)新事務(wù)
Session A>begin;
Query OK, 0 rows affected (0.00 sec)

Session A>select * from a;
Empty set (0.00 sec)
#插入數(shù)據(jù)
Session A>insert into a select 1,2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
#當(dāng)前事務(wù)能看到insert的數(shù)據(jù)
Session A>select * from a;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)#暫時(shí)不commit

#在A提交之前，B開啟事務(wù)                                     Session B>begin;
Query OK, 0 rows affected (0.00 sec)
#此時(shí)查詢表a的數(shù)據(jù)，一致性非鎖定讀，讀到的是事務(wù)開啟前已經(jīng)提交的數(shù)據(jù)，因?yàn)锽的事務(wù)開始的時(shí)候A事務(wù)還沒(méi)有提交，故A中insert的數(shù)據(jù)不會(huì)顯示
Session B>select * from a;
Empty set (0.00 sec)

#session A提交上面的事務(wù)                          Session A>commit;
Query OK, 0 rows affected (0.00 sec)

#在B中select查詢，依舊進(jìn)行的是快照讀，故看不到數(shù)據(jù)                          Seesion B>select * from a;
Empty set (0.00 sec)

Seesion B>select * from aa;
Empty set (0.00 sec)
#INSERT INTO ... SELECT * FROM A;操作
Seesion B>insert into aa select * from a;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Seesion B>select * from a;
Empty set (0.00 sec)
#發(fā)現(xiàn)直接select表a還是查不到數(shù)據(jù)，而aa表能看到數(shù)據(jù)
Seesion B>select * from aa;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

#使用lock in share mode或for update進(jìn)行當(dāng)前讀能看到表a的數(shù)據(jù)
Seesion B>select * from a lock in share mode;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

Seesion B>select * from a;
Empty set (0.00 sec)
Seesion B>select * from a for update;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)