事务隔离级别: REPEATABLE_READ 的瑕疵

[codefollower]

REPEATABLE_READ 这个事务隔离级别的含义是顾名思义的，简单说就是在同一个事务中对同一条记录的两次读取应该都能读到相同的记录，哪怕在这两次读取记录的过程中另一个事务更新了这条记录也不会受影响。

以 MySQL 5.7 举例说明，MySQL 5.7 的默认事务隔离级别就是 REPEATABLE_READ

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

1. 通过打开两个 MySQL Monitor 来演示正常情况下的 REPEATABLE_READ

先在 MySQL Monitor A 中创建一张测试表并增加一条测试记录：

mysql> create table tx_isolation_test (pk int primary key, count int);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into tx_isolation_test(pk, count) values(10, 100);
Query OK, 1 row affected (0.00 sec)

然后紧接着在 MySQL Monitor A 中启动事务 T1，先执行下面的 SQL 读取记录的 count 字段的当前值：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select count from tx_isolation_test where pk=10;
+-------+
| count |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

从查询结果可以看出 count 字段的当前值是 100。

接着在 MySQL Monitor B 窗口中启动另一个事务 T2，执行下面的 SQL 更新 count 字段的值:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tx_isolation_test set count=count-10 where pk=10;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select count from tx_isolation_test where pk=10;
+-------+
| count |
+-------+
|    90 |
+-------+
1 row in set (0.00 sec)

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

从上面的结果可以看出，当事务 T2 提交后，count 字段的值已经是 90 了。

最后切换到 MySQL Monitor A 窗口，再查一下 count 字段的值是多少:

mysql> select count from tx_isolation_test where pk=10;
+-------+
| count |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

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

从结果看出，count 字段的值还是 100，说明在事务 T1 中执行的两次读取操作不受事务 T2 的影响，这满足了 REPEATABLE_READ 事务隔离级别的语义。

2. 再来看看特殊情况下 REPEATABLE_READ 有什么瑕疵

先在 MySQL Monitor A 窗口中启动事务 T3，执行下面的 SQL 看看 count 字段的当前值：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select count from tx_isolation_test where pk=10;
+-------+
| count |
+-------+
|    90 |
+-------+
1 row in set (0.00 sec)

结果表明前面的事务 T2 确实成功提交了，count 字段的值已经是 90 了，

然后再切换到 MySQL Monitor B 窗口启动事务 T4，执行跟事务 T2 一样的操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tx_isolation_test set count=count-10 where pk=10;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select count from tx_isolation_test where pk=10;
+-------+
| count |
+-------+
|    80 |
+-------+
1 row in set (0.00 sec)

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

当事务 T4 提交后，count 字段的值变成 80 了。

然后再切回 MySQL Monitor A 窗口，在事务 T3 中看看 count 字段的值是多少：

mysql> select count from tx_isolation_test where pk=10;
+-------+
| count |
+-------+
|    90 |
+-------+
1 row in set (0.00 sec)

因为事务 T3 要遵循 REPEATABLE_READ 的语义，所以此时 count 字段的值显然是 90，
接下来，试着在事务 T3 中对 count 字段减 20，第一直觉是 count 字段会变成 70 吧(90-20=70)……

mysql> update tx_isolation_test set count=count-20 where pk=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

直觉也许不对呢，查查看:

mysql> select count from tx_isolation_test where pk=10;
+-------+
| count |
+-------+
|    60 |
+-------+
1 row in set (0.00 sec)

怎么会是 60？为什么不是 70 ？
如果是 70 会出什么问题？

如果执行完事务 T3 再执行 T4，在事务 T4 中把 count 字段减去 10，而 T3 把 count 字段减去 20，最后结果确实应该是 60。

但是这里事务 T3 和 T4 并不是串行执行的，而是在事务 T3 执行的过程中执行了 T4，并且是在 REPEATABLE_READ 这样的事务隔离级别下执行的，如果在 T3 中完全遵循 REPEATABLE_READ 的语义，那么 count 字段的值应该是 70 才对，但是这样会导致更新丢失的问题，事务 T4 的更新被丢弃了。

所以，宁愿不产生更新丢失的问题也要违反 REPEATABLE_READ 的语义。

在事务的 ACID 语义中，I 就是代表事务的隔离级别，而 REPEATABLE_READ 只是其中的一种隔离级别，
而更新丢失违反了 ACID 中的 D，在 I 的语义无法满足并且又不会产生致命影响时优先满足 ACD。

如果想严格遵循 REPEATABLE_READ 的语义，又不会产生更新丢失的问题，那么可以在事务 T3 的查询语句中加上 for update，这样会锁住记录，直到事务 T3 提交后，事务 T4 才能继续更新。

数据库领域总会碰到这样的不完美，遇到特殊场景时就会发明一些新的 SQL 语法或配置参数去弥补。

[codefollower]

MySQL的事务实现倒也合理，默认事务隔离级别是REPEATABLE_READ，在这种隔离级别中运行的事务，如果执行的是select语句，就遵循REPEATABLE_READ，如果还包含update语句，从我的几个试验例子来看，对于update语句它用的是READ_COMMITTED并且为where子句中读到的记录加读锁。 ​​​​

类似下面的例子可以猜出MySQL的事务实现方式，如果在另一个事务中更新pk=20的记录将被阻塞。

 update tx_isolation_test set count = 100 where pk=10 and 10=(select count
 from (select count from tx_isolation_test where pk=20) as t);

[lzb6666]

在InnoDB中，可重复隔离级别下出现不可重复读、幻读情况的唯一条件就是，先进行快照读，再进行当前读；对快照进行修改是没有意义的，除非在提交后进行版本检查，出现更新丢失的情况即版本冲突，事务回退重新执行；