RR 和RC隔离问题

Sesssion 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;
+-------+
| c1    |
+-------+
|    33 |
|    34 |
|    87 |
|    89 |
|   100 |
|   999 |
| 88888 |
+-------+
7 rows in set (0.00 sec)


Session 2:
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;
+-------+
| c1    |
+-------+
|    33 |
|    34 |
|    87 |
|    89 |
|   100 |
|   999 |
| 88888 |
+-------+
7 rows in set (0.00 sec)

mysql> insert into t1 values(909090);
Query OK, 1 row affected (0.00 sec)

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


Session 1再次查看:
mysql> select * from t1;
+-------+
| c1    |
+-------+
|    33 |
|    34 |
|    87 |
|    89 |
|   100 |
|   999 |
| 88888 |
+-------+
7 rows in set (0.00 sec)


Session 1看不到 909090这条记录




切换到RC模式:
transaction-isolation =READ-COMMITTED

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;
+--------+
| c1     |
+--------+
|     33 |
|     34 |
|     87 |
|     89 |
|    100 |
|    999 |
|  88888 |
| 909090 |
+--------+
8 rows in set (0.00 sec)


Session 2:
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 @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(98989800);
Query OK, 1 row affected (0.00 sec)

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


Session 1:

再次查看:

mysql> select * from t1;
+----------+
| c1       |
+----------+
|       33 |
|       34 |
|       87 |
|       89 |
|      100 |
|      999 |
|    88888 |
|   909090 |
| 98989800 |
+----------+
9 rows in set (0.00 sec)


可以看到98989800 这条记录