MySQL RR 模式下 事务隔离问题:
Session 1:
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| NULL | a |
| 2 | b |
| 3 | c |
| 1 | a01 |
| 4 | a |
| 4 | a |
| 5 | c |
+------+------+
7 rows in set (0.00 sec)
mysql> update test set name='a999' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| NULL | a |
| 2 | b |
| 3 | c |
| 1 | a999 |
| 4 | a |
| 4 | a |
| 5 | c |
+------+------+
7 rows in set (0.00 sec)
Session 2:
mysql> select * from test ;
+------+------+
| id | name |
+------+------+
| NULL | a |
| 2 | b |
| 3 | c |
| 1 | a01 |
| 4 | a |
| 4 | a |
| 5 | c |
+------+------+
7 rows in set (0.00 sec)
mysql> select * from test ;
+------+------+
| id | name |
+------+------+
| NULL | a |
| 2 | b |
| 3 | c |
| 1 | a999 |
| 4 | a |
| 4 | a |
| 5 | c |
+------+------+
7 rows in set (0.00 sec)
此时没有开启事务,RR模式下 更新立即被看到
/****************************************************
Session 2开启事务:
SESSION 1:
mysql> update test set name='a0101' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
+------+-------+
| id | name |
+------+-------+
| NULL | a |
| 2 | b |
| 3 | c |
| 1 | a0101 |
| 4 | a |
| 4 | a |
| 5 | c |
+------+-------+
7 rows in set (0.00 sec)
此时在查看Session 2:
mysql> select * from test ;
+------+------+
| id | name |
+------+------+
| NULL | a |
| 2 | b |
| 3 | c |
| 1 | a999 |
| 4 | a |
| 4 | a |
| 5 | c |
+------+------+
7 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec) ---需要手动提交事务
mysql> select * from test ;
+------+-------+
| id | name |
+------+-------+
| NULL | a |
| 2 | b |
| 3 | c |
| 1 | a0101 |
| 4 | a |
| 4 | a |
| 5 | c |
+------+-------+
7 rows in set (0.00 sec)