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