MySQL与PostgreSQL在事务隔离上的差异

前言：主流的开源数据源产品如MySQL、PostgreSQL虽然都各自实现了Repeatable Read、Serializable 事务隔离级别，不过他们在概念上或者实现方式上还是存在一定差异，本文旨在通过实际测验来对比两块数据库产品在实现上的差异。

在《A Critique of ANSI SQL Isolation Levels》论文中描述了事务不同隔离级别下异常现象，总结出的结论如图：

这里主要对比P3、P4、A5B现象表现上存在差异

论文中对A5B、P3、P4的定义如下:

A5B Write Skew Suppose T1 reads x and y, which are consistent with C(), and then a T2 reads x and y, writes x, and commits. Then T1 writes y. If there were a constraint between x and y, it might be violated. In terms of histories: A5B: r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur) (Write Skew)

P3: SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search condition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.
r1[P]...w2[y in P]...((c1 or a1) and (c2 or a2) any order)

P4 (Lost Update): The lost update anomaly occurs when transaction T1 reads a data item and then T2 updates the data item (possibly based on a previous read), then T1 (based on its earlier read value) updates the data item and commits. In terms of histories, this is:

P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)

下面分别测试MySQL、PostgreSQL 在Repeatable Read 、Serializable 隔离级别下A5B、P3现象的差异

MySQL Repeatable Read

MySQL版本5.7

MySQL Repeatable Read 下能够避免A5B

MySQL Repeatable Read 下不能避免P3

MySQL Repeatable Read 下不能避免P4

PostgreSQL Repeatable Read

PostgreSQL版本10

PostgreSQL Repeatable Read 下不能够避免A5B

PostgreSQL Repeatable Read 下能避免P3

PostgreSQL Repeatable Read 下能避免P4

MySQL Serializable

MySQL版本5.7

MySQL Serializable 下能避免P3

PostgreSQL Serializable

PostgreSQL版本10

PostgreSQL Serializable下能够避免A5B

结论：

• 通过上面测验可以看出MySQL innodb中实现的Repeatable Read与论文中定义的Repeatable Read不一致：能够避免A5B（写倾斜）;不能避免P3（幻读）和P4（丢失更新，RR隔离级别下innodb读的时候是快照读，但是写的时候采用的是当前读，也就是更新的时候，不再考虑快照，而是基于新的版本来更新）；
• 而PostgreSQL中的Repeatable Read也与论文中定义的不一致：能够避免P3（幻读）而不能避免A5B（写倾斜），实际上PostgreSQL中实现的Repeatable Read与论文中定义的Snapshot一致；
• MySQL利用SS2PL读阻塞写进而消除P3、P4实现了Serializable ，但事务并发度较差；
• 而PostgreSQL在Snapshot基础上通过检测读写冲突，发现冲突后回滚一个事务进而消除A5B实现了Serializable Snapshot，事务并发度相较SS2PL实现的Serializable要好很多。
• PostgreSQL和MySQL innodb在事物的并发控制上都采用了SS2PL+MVCC，PostgreSQL更倾向于使用后者，而MySQL innodb 更倾向使用前者。

参考：