RR模式下的事务隔离

<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)
结论 查询的时候看到的是老数据,更新的时候是以其他会话提交为准。