<pre name="code" class="html">顯然 RR 支援 gap lock(next-key lock)，而RC則沒有gap lock。因為MySQL的RR需要gap lock來解決幻讀問題。而RC隔離級别則是允許存在不可重複讀和幻讀的。是以RC的并發一般要好于RR；
RR 和RC 幻讀問題:
幻讀：同一個事務中多次執行同一個select, 讀取到的資料行發生改變。
也就是行數減少或者增加了(被其它事務delete/insert并且送出)。SERIALIZABLE要求解決幻讀問題；
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
Sessio 1:
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> use scan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where id>100;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> select * from t1 where c1>100;
+------+
| c1 |
+------+
| 119 |
| 200 |
| 300 |
| 400 |
| 8999 |
+------+
5 rows in set (0.00 sec)
Sessio 2:
mysql> insert into t1 values(9999);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Session 1 再次查詢:
mysql> select * from t1 where c1>100;
+------+
| c1 |
+------+
| 119 |
| 200 |
| 300 |
| 400 |
| 8999 |
+------+
5 rows in set (0.00 sec)
此時在RR模式下沒有幻讀
設定隔離級别為RC：
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
Session 1:
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
mysql> use scan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where c1>100;
+------+
| c1 |
+------+
| 119 |
| 200 |
| 300 |
| 400 |
| 8999 |
| 9999 |
+------+
6 rows in set (0.00 sec)
Session 2:
mysql> select * from t1;
+------+
| c1 |
+------+
| 33 |
| 34 |
| 87 |
| 89 |
| 119 |
| 200 |
| 300 |
| 400 |
| 8999 |
| 9999 |
+------+
10 rows in set (0.00 sec)
mysql> insert into t1 values(7777777);
Query OK, 1 row affected (0.01 sec)
Session 1再次查詢:
mysql> select * from t1 where c1>100;
+---------+
| c1 |
+---------+
| 119 |
| 200 |
| 300 |
| 400 |
| 8999 |
| 9999 |
| 7777777 |
+---------+
7 rows in set (0.00 sec)
說明RC模式下,可以幻讀
