RR 和RC 幻读问题

<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模式下,可以幻读