MySQL的repeatable read

####在 REPEATABLE-READ隔離級別下, session 1
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb2;
+------+------+
| id   | c1   |
+------+------+
|   20 |    0 |
|   30 |    0 |
|   10 |    0 |
+------+------+
3 rows in set (0.00 sec)

####在 REPEATABLE-READ隔離級別下, session 2執行下列操作
mysql> START TRANSACTION;
mysql> insert into tb2 values(40,0);
mysql> commit;

或者
mysql> START TRANSACTION;
mysql> delete from tb2 where id=20;
mysql> commit;

或者
mysql> START TRANSACTION;
mysql> update tb2 set c1=111 where id=10;
mysql> commit;

####在 REPEATABLE-READ隔離級別下, session 1
mysql> select * from tb2;
+------+------+
| id   | c1   |
+------+------+
|   20 |    0 |
|   30 |    0 |
|   10 |    0 |
+------+------+
3 rows in set (0.00 sec)

在session 1的同一個transaction中，兩次相同查詢得到的結果一樣，稱之為: repeatable read
==========================================================================
但是在RR隔離級別下，locking read(SELECT with FOR UPDATE or LOCK IN SHARE MODE) 或更新，刪除時是會看到已提交的修改的，包括新插入的行。

####在 REPEATABLE-READ隔離級別下, session 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)

mysql> select id,c1 from tb2 where id=10;
+------+------+
| id   | c1   |
+------+------+
|   10 |    0 |
+------+------+
1 row in set (0.00 sec)

mysql>

####session 2:
mysql> update tb2 set c1=101 where id =10;


####session 1:
mysql> select id,c1 from tb2 where id=10;
+------+------+
| id   | c1   |
+------+------+
|   10 |    0 |
+------+------+
1 row in set (0.00 sec)

mysql> select id,c1 from tb2 where id=10 LOCK IN SHARE MODE;
+------+------+
| id   | c1   |
+------+------+
|   10 |  101 |
+------+------+
1 row in set (0.00 sec)

mysql> update tb2 set c1=c1+1000 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,c1 from tb2 where id=10;
+------+------+
| id   | c1   |
+------+------+
|   10 | 1101 |
+------+------+
1 row in set (0.00 sec)

mysql> select id,c1 from tb2 where id=10 LOCK IN SHARE MODE;
+------+------+
| id   | c1   |
+------+------+
|   10 | 1101 |
+------+------+
1 row in set (0.00 sec)

mysql>
===========================================================================
在RR的隔離級別下，預設採用Next-Key Locks(Record lock和gap lock的結合),它既鎖住記錄本身，也鎖住索引之間的間隙，所以這個gap lock機制預設開啟，並不會產生幻行
在MySQL 5.6.3之前,可以使用innodb_locks_unsafe_for_binlog引數可以禁用gap lock。
innodb_locks_unsafe_for_binlog was deprecated in MySQL 5.6.3. The READ COMMITTED isolation level provides similar functionality.

以下設定成RC隔離級別，模擬幻讀
==========================================================================
####Session 1: 設定隔離級別
mysql> set global transaction isolation level read committed;
mysql> set session transaction isolation level read committed;
mysql> select @@global.transaction_isolation,@@transaction_isolation;

####Session 1: 發起一個事務,檢視資料
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id>2 for update;
+------+------+
| id   | name |
+------+------+
|    3 | VV   |
+------+------+
1 row in set (0.00 sec)

####Session 2: 插入一行資料
mysql> insert into t values(4,'YY');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

####Session 1: 再次查詢，出現幻讀
mysql> select * from t where id>2 for update;
+------+------+
| id   | name |
+------+------+
|    3 | VV   |
|    4 | YY   |
+------+------+
2 rows in set (0.00 sec)
========================================================================

gap鎖的出現主要是為了避免幻讀，gap鎖只會阻塞insert操作, 資料庫施加gap lock的條件：
1 事務隔離級別為REPEATABLE-READ，且sql走的索引為非唯一索引
或者
2 事務隔離級別為REPEATABLE-READ，且sql是一個範圍的當前讀操作，這時即使不是非唯一索引也會加gap lock