RR模式下的事务隔离

来源：互联网发布：rightfont for mac 编辑：程序博客网时间：2024/05/22 06:30
<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: 0mysql> 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)结论 查询的时候看到的是老数据,更新的时候是以其他会话提交为准。
0 0