<pre name="code" class="html">mysql> select * from t100; Session 2:
+----+------+-------+------+
| sn | id | quota | free | mysql> select * from t100; 读到的记录为 10 | 300 | 1 | 290
+----+------+-------+------+
| 1 | 7 | NULL | NULL |
| 2 | 9 | NULL | NULL |
| 3 | 990 | NULL | NULL |
| 4 | 12 | NULL | NULL |
| 5 | 13 | NULL | NULL |
| 6 | 14 | NULL | NULL |
| 7 | 15 | NULL | NULL |
| 8 | 22 | NULL | NULL |
| 9 | 23 | NULL | NULL |
| 10 | 300 | 1 | 290 |
+----+------+-------+------+
10 rows in set (0.00 sec)
Session 2:
mysql> update t100 set free = free -quota where sn=10; mysql> select * from t100; 读到的还是290 因为RR模式下 以第一读为准
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t100;
+----+------+-------+------+
| sn | id | quota | free |
+----+------+-------+------+
| 1 | 7 | NULL | NULL |
| 2 | 9 | NULL | NULL |
| 3 | 990 | NULL | NULL |
| 4 | 12 | NULL | NULL |
| 5 | 13 | NULL | NULL |
| 6 | 14 | NULL | NULL |
| 7 | 15 | NULL | NULL |
| 8 | 22 | NULL | NULL |
| 9 | 23 | NULL | NULL |
| 10 | 300 | 1 | 289 |
+----+------+-------+------+ Session 2:
10 rows in set (0.00 sec)
执行
mysql> select * from t100;
+----+------+-------+------+
| sn | id | quota | free |
+----+------+-------+------+
| 1 | 7 | NULL | NULL |
| 2 | 9 | NULL | NULL |
| 3 | 990 | NULL | NULL |
| 4 | 12 | NULL | NULL |
| 5 | 13 | NULL | NULL |
| 6 | 14 | NULL | NULL |
| 7 | 15 | NULL | NULL |
| 8 | 22 | NULL | NULL |
| 9 | 23 | NULL | NULL |
| 10 | 300 | 1 | 290 |
+----+------+-------+------+
10 rows in set (0.00 sec)
mysql> update t100 set free = free -quota where sn=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t100;
+----+------+-------+------+
| sn | id | quota | free |
+----+------+-------+------+
| 1 | 7 | NULL | NULL |
| 2 | 9 | NULL | NULL |
| 3 | 990 | NULL | NULL |
| 4 | 12 | NULL | NULL |
| 5 | 13 | NULL | NULL |
| 6 | 14 | NULL | NULL |
| 7 | 15 | NULL | NULL |
| 8 | 22 | NULL | NULL |
| 9 | 23 | NULL | NULL |
| 10 | 300 | 1 | 288 |
+----+------+-------+------+
10 rows in set (0.00 sec)
结论 查询的时候看到的是老数据,更新的时候是以其他会话提交为准。
RR模式下的事务隔离
最新推荐文章于 2023-10-31 10:51:33 发布