本文是我读过的带有代码实战的解释 Repeatable Read 隔离级别最好的文章。请一定要耐心、细心读完 (尤其是代码演示部分)！如果你英文过关，建议翻到最后阅读原文。
为了避免并发事务相互干扰，SQL引擎将 隔离(Isolation) 作为一个特性来实现。这个属性对应于众所周知的 ACID 缩写中的字母 I，其他属性分别是：原子性(Atomicity)、一致性(Consistency)和持久性(Durability)。
隔离性是可配置的，在执行并发事务时，不同级别的隔离对应不同的行为。
SQL-1992标准定义了四个隔离级别，从最弱到最强分别是：
Read Uncommitted：读未提交。一个事务可以看到其它事务提交前变更的数据。PostgreSQL没实现此模式。
Read Committed：读提交。其它事务一旦提交，另一个事务就能看到它们的变更 。
Repeatable Read：可重复读。当一个事务重新读取一个该事务之前读取过的行时，读取到的必定是相同的值，即使该行已经被同一时间提交的另一个事务所更改。
Serializable：可序列化。a transaction cannot see or produce results that could not have occurred if other transactions were not concurrently changing the data (这句话无法翻译，先搁置...)。PostgreSQL自9.1版起实现了真正的序列化。
如果你正在移植为MySQL写的代码，或者正在编写同时针对PostgreSQL和MySQL/MariaDB 的代码，那么你可能需要注意，在PostgreSQL中默认的隔离级别是 Read Committed，而在 MySQL或MariaDB(使用InnoDB引擎)中默认隔离级别是 Repeatable Read。顺便说一句，SQL标准规定默认情况下应该使用Serializable，因此两个引擎都没遵循这一建议（而像Oracle、MS SQL Server、DB2、Sybase ASE 等……它们也忽略了这一点）。
不管怎样，让我们先看一个非常简单的例子，来说明MySQL和PostgreSQL在默认隔离级别上的区别：
示例 1
假设我们有一张只有一个列的表，它有4条数据：
CREATE TABLE a(x int);INSERT INTO a VALUES (1),(2),(3),(4);
一个事务(记为 Tx1) 通过2条不同的查询进行求和和求平均值操作。而另一个事务(记为 Tx2) 往表中插入一条新数据，其执行和提交的时间点位于 Tx1 的两个查询之间。
对于 PostgreSQL 默认的 Read Committed 隔离级别：
-- Tx1 -- Tx2=# BEGIN; =# BEGIN;BEGIN BEGIN=# SELECT SUM(x) FROM a;sum-----10(1 row)=# INSERT INTO a VALUES(50);INSERT 0 1=# COMMIT;COMMIT=# SELECT AVG(x) FROM a;avg---------------------11.6666666666666667(1 row)=# COMMIT;COMMIT
50 这个值被 SUM 所忽略，但被AVG考虑在内。如果我们只看 Tx1 事务，它的两个查询结果在数学上是不一致的：平均值不可能大于总和。这是因为在 Read Committed 级别中，每个新的SQL语句所开始的状态都会包含其它已提交事务的变更。在我们上面的例子中，该变更即是 Tx2 事务所插入的 50 这个值。
对于 MySQL/MariaDB，它的默认隔离级别是 Repeatable Read，我们得到的结果又会不同：
-- Tx1 -- Tx2mysql> BEGIN; mysql> BEGIN;Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)> SELECT SUM(x) FROM a;+--------+| SUM(x) |+--------+| 10 |+--------+1 row in set (0.00 sec)> INSERT INTO a VALUES(50);Query OK, 1 row affected (0.00 sec)> COMMIT;Query OK, 0 rows affected (0.02 sec)> SELECT AVG(x) FROM a;+--------+| AVG(x) |+--------+| 2.5000 |+--------+1 row in set (0.00 sec)> COMMIT;Query OK, 0 rows affected (0.00 sec)
对于 MySQL/MariaDB，Tx2 插入的新行被 Tx1 所忽略，因为受到 Repeatable Read 隔离性的影响：之前为SUM操作所读取的值，必须被AVG操作所复用。
为了让 PostgreSQL 得到同样的行为，我们必须将事务隔离级别设置的更高(Repeatable Read 或 Serializable)。
要考虑的关键是，即使是简单的并发查询，在不同引擎的默认配置中也有可能产生不同的结果。
现在我们可能认为，为了获得与MySQL和PostgreSQL相同的结果，我们应该在相同的隔离级别上使用它们。这听起来很合理，但总的来说并非如此。事实上，在相同的隔离级别上，不同的SQL引擎的行为可能有很大的差异。让我们用另一个例子来说明这一点。
示例二
现在让我们在两个引擎中使用 Repeatable Read。要将会话切换到此隔离级别，以免受到默认值的影响，我们要执行以下语句：
对于 MySQL/MariaDB：
> SET SESSION transaction isolation level Repeatable Read;
对于 PostgreSQL：
=# SET default_transaction_isolation TO 'Repeatable Read';
我们创建2个空表：
CREATE TABLE a(xa int);CREATE TABLE b(xb int);
现在，让我们执行两个并发事务，在每个表中插入另一个表的 count(*) 结果。
PostgreSQL 脚本：
-- Tx1 -- Tx2=# BEGIN; =# BEGIN;BEGIN BEGIN=# INSERT INTO aSELECT count(*) FROM b;INSERT 0 1=# INSERT INTO bSELECT count(*) FROM a;INSERT 0 1=# COMMIT;COMMIT=# SELECT COUNT(*) FROM a;count-------0(1 row)=# COMMIT;COMMIT
Tx1 和 Tx2 都结束后，我们得到如下结果：
=# SELECT * FROM a;xa----0(1 row)=# SELECT * FROM b;xb----0(1 row)
最终出现在两个表中的都是 0，因为对于这两个事务中的每一个事务，每张表就其可见性而言都是空的。
但是对于MySQL/MariaDB，行为和最终结果都是不同的，如下面的脚本所示。Tx2 需要等待 Tx1，并在 Tx1 结束后将其结果合并给自己，而不是完全与 Tx1 隔离。
-- Tx1 -- Tx2mysql> BEGIN; mysql> BEGIN;Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)> INSERT INTO a SELECT count(*) FROM b;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0> INSERT INTO b SELECT count(*)FROM a;-- (Tx2 gets blocked here)> COMMIT;Query OK, 0 rows affected (0.01 sec)-- (Tx2 continues)Query OK, 1 row affected (5.03 sec)Records: 1 Duplicates: 0 Warnings: 0> -- this result differs from PG> SELECT COUNT(*) FROM a;+----------+| count(*) |+----------+| 1 |+----------+> COMMIT;Query OK, 0 rows affected (0.01 sec)-- Tx1 and Tx2 are done> SELECT * FROM a;+------+| xa |+------+| 0 |+------+1 row in set (0.01 sec)> -- this result differs from PG> SELECT * FROM b;+------+| xb |+------+| 1 |+------+1 row in set (0.00 sec)
尽管这一指令序列非常简单，而且两个引擎中的事务都是 Repeatable Read 级别，但结果在 Postgres 和 MySQL/MariaDB 之间却有所不同：b表在PostgreSQL中有一行 0，在MySQL中有一行 1。
由于 Tx1 和 Tx2 不是写入同一行（实际上在本例中，它们甚至不是写入同一张表），对于PostgreSQL，Tx1 的插入根本不会干扰 Tx2。Tx2 不需要等待 Tx1 完成，它可以通过快照(snapshot)计算a中的行数。
而在MySQL中，Tx2 需要等待 Tx1 结束，并在合并 Tx1 所做的操作(一次插入)后计算a中的行数。所以 Tx1 和 Tx2 的隔离度较低。从习惯了PostgreSQL的 Repteatable Read 模式的用户的角度来看，这种行为是相当令人惊讶的（事实上，这部分看起来像PostgreSQL中的 Read Committed 模式）。
还有更多的区别。让我们看看另一个例子，它是本例的变体，其中 Tx2 在事务开始时会对a表 进行查询。
示例三
这个例子是前一个例子的变体，在这个例子中，Tx2 在开头读取a表并返回 count(*)。对于PostgreSQL，它对结果没有任何影响：在所有Tx2期间，a表的 count(*) 始终为0，无论是作为INSERT语句的子查询还是在主查询中。
但是对于MySQL来说，为了避免“不可重复读取(Non Repeatable Read)”现象，行为有明显的不同，这是 Repeatable Read 隔离级别最小的期望。
让我们看看MySQL/MariaDB的以下脚本：
-- Tx1 -- Tx2mysql> BEGIN; mysql> BEGIN;Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)> INSERT INTO a SELECT count(*) FROM b;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0> -- Initiate a repeatable read> SELECT count(*) FROM a;+----------+| count(*) |+----------+| 0 |+----------+1 row in set (0.00 sec)> INSERT INTO b SELECT count(*)FROM a;-- (Tx2 gets blocked)> COMMIT;Query OK, 0 rows affected (0.01 sec)-- (Tx2 gets unblocked)Query OK, 1 row affected (3.13 sec)Records: 1 Duplicates: 0 Warnings: 0> SELECT * FROM b;+------+| xb |+------+| 1 |+------+1 row in set (0.00 sec)> -- repeat the read> SELECT COUNT(*) FROM a;+----------+| count(*) |+----------+| 0 |+----------+1 row in set (0.00 sec)> COMMIT;Query OK, 0 rows affected (0.01 sec)
a表和b表中的最终结果与上一个示例中包含 1 的表b中的结果相同，但这次在 Tx2 的末尾，从a表中查询 COUNT(*) 返回 0，而在上一个示例中返回的是 1。
不同之处在于，此刻 “不可重复读取”现象被禁止了，因为在事务开始时有一个 SELECT count(*) 返回 0。因此，来自a表的任何后续 SELECT count(*) 都必须产生 0。但只有当它直接运行时才是这样，而通过 INSERT INTO b SELECT count（*）From a 这种子查询执行时就不是这样了。
在 b.xb 中读到的是 1 而返回给客户端的却是 0，上面的解释就是这种奇怪的不一致产生的原因，尽管它是同一个 Repeatable Read 事务中同一表达式的结果。
另外，这种结果上的差异在 Tx2 的其余时间内仍然存在。例如，在提交之前，我们可以使用以下语句序列：
mysql> SELECT * FROM b;+------+| xb |+------+| 1 |+------+1 row in set (0.01 sec)mysql> INSERT INTO b SELECT COUNT(*) FROM a;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> SELECT * FROM b;+------+| xb |+------+| 1 || 1 |+------+2 rows in set (0.00 sec)mysql> SELECT COUNT(*) FROM a;+----------+| count(*) |+----------+| 0 |+----------+1 row in set (0.00 sec)
示例四
在PostgreSQL和MySQL/MariaDB中，Repeatable Read 处理写冲突(这种冲突可以造成“丢失的更新,lost updates”)的方式还有一个非常显著的区别。PostgreSQL及其快照隔离技术会通过终止其中一个事务来避免同一行上的写冲突。
相比之下，处于同一隔离级别的MySQL/MariaDB不会终止事务，而是禁止第二次写入(在这种情况下是删除)，而不会发出任何错误。
让我们看一张表，它只有一个列，并且有值为从1到4的4行数据。有两个并发事务，Tx1 从每个值中减去1，Tx2 删除表中最大值对应的行。其想法是，用 Tx2 并发删除 Tx1 更改的行。
CREATE TABLE list(x int);INSERT INTO list VALUES (1),(2),(3),(4);
PostgreSQL 脚本：
-- Tx1 -- Tx2=# BEGIN; =# BEGIN;BEGIN BEGIN=# UPDATE list SET x=x-1; =# SELECT * FROM list;x---1234(4 rows)=# DELETE FROM list WHERE x=4;-- (Tx2 gets blocked)=# COMMITCOMMIT-- Tx2 ends in errorERROR: could not serialize accessdue to concurrent update=# \echo :SQLSTATE40001=# ROLLBACK;ROLLBACK
在 Repeatable Read 隔离级别，引擎拒绝 Tx2 对 Tx1 修改过的行的写入(广义上说，删除也是一种写入)。该拒绝包括终止事务并提供特定错误代码（SQLSTATE 40001）。
MySQL/MariaDB如下所示，没有终止事务。删除没有报错，且没有删除掉 Tx1 更改的 x=4 的行，即使该行在 Tx2 结束之前一直可见。
-- Tx1 -- Tx2mysql> BEGIN; mysql> BEGIN;Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)> UPDATE list SET x=x-1; > SELECT * FROM list;Query OK, 4 rows affected (0.00 sec) +------+Rows matched: 4 Changed: 4 Warnings: 0 | x |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.00 sec)> DELETE FROM list WHERE x=4;-- (Ici Tx2 se trouve bloquée)Query OK, 0 rows affected (5.73 sec)> COMMIT;Query OK, 0 rows affected (0.01 sec)> SELECT * FROM list;+------+| x |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.01 sec)> DELETE FROM list WHERE x=4;Query OK, 0 rows affected (0.00 sec)> SELECT * FROM list;+------+| x |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.00 sec)> COMMIT;Query OK, 0 rows affected (0.00 sec)
当重复 SELECT * FROM list 和 DELETE 操作时，我们可以看到 x=4 的行仍然可见，并且 DELETE 不会删除它。唯一表明引擎不想删除它的是与删除操作一起返回的信息: 0 rows affected (0行受到影响)。
与PostgreSQL相比，Tx2 不删除该行看起来像是其 Read Committed 级别的行为，但该行仍然可见这一事实看起来像是 Repeatable Read 级别的行为。从这个意义上说，MySQL中的 Repeatable Read 级别感觉好像介于Postgres的 Read Committed 和 Repeatable Read 之间。
总结
在将应用程序从MySQL移植到PostgreSQL或从PostgreSQL移植到MySQL时，或者在设计需要同时处理这两者的服务时，我们应该预料到并发事务有不同的行为，即使是在相同的隔离级别上配置的。
SQL标准规定，某些隔离级别必须避免某些现象，但是每个SQL实现都有自己的解读，对于同一段SQL代码，从可见结果中可以明显发现不同。
默认情况下，PostgreSQL使用 Read Committed，而MySQL选择了 Repeatable Read，后者隔离性更好。但是当PostgreSQL事务使用 Repeatable Read 级别时，它比MySQL事务有着更强的隔离性。
文章译自 Daniel Vérité 所写的《Isolation Repeatable Read in PostgreSQL versus MySQL》。点击 阅读原文 可查看。
如果觉得有用请点击右下角「在看」，译者需要您的支持！
