MySQL Repeatable-Read
ddatsh
SQL标准最初1986被ANSI正式接受，1987发布为ISO 9075-1987标准。1989和1992进行修订
ANSI SQL标准(1992)：基于异象
ANSI首先尝试指定统一的隔离级别标准，定义不同级别的异象(phenomenas)，依据能避免多少异象来划分隔离标准
-
脏读（Dirty Read）: 读到了其他事务还未提交的数据
-
不可重复读（Non-Repeatable/Fuzzy Read）：由于其他事务的修改或删除，对某数据的两次读取结果不同
-
幻读（Phantom Read）：由于其他事务的修改，增加或删除，导致Range的结果失效（如where 条件查询）
A Critique of ANSI(1995)：基于锁
92 ANSI标准存在两个致命的问题：
- 不完整，缺少对Dirty Write(两个未提交的事务先后对同一个对象进行了修改)的排除
假设有相关性约束x=y，T1尝试将二者都修改为1，T2尝试将二者都修改为2，顺序执行结果应该是二者都为1或2，但Dirty Write结果变为x=2，y=1，不一致
- 歧义
r1[P] w2[insert y to P] r2[z] w2[z] c2 r1[z] c1
T1根据条件P查询所有的雇员列表，之后T2增加了一个雇员并增加了雇员人数值z，之后T1读取雇员人数z，最终T1的列表中的人数比z少，不一致
但如果T1并没有在T2修改链表后再使用P中的值，是否就不属于ANSI中对Phantom的定义了呢？这也导致了对ANSI的表述可能有严格和宽松两种解读。对于Read Dirty和Non-Repeatable/Fuzzy Read也有同样的问题
Critique of ANSI 解决上述两个问题的答案是：宁可错杀三千，不可放过一个，即给ANSI标准中的异象最严格的定义。Critique of ANSI改造了异象的定义：
P0: w1[x]…w2[x]…(c1 or a1) (Dirty Write)
P1: w1[x]…r2[x]…(c1 or a1) (Dirty Read)
P2: r1[x]…w2[x]…(c1 or a1) (Fuzzy or Non-Repeatable Read)
P3: r1[P]…w2[y in P]…(c1 or a1) (Phantom)
- c1 代表第一个事务提交（Commit）
- a1 代表第一个事务中止（Abort）
c2和a2代表第二个事务的提交和中止
例如，P1（Dirty Read）中，如果一个事务（T1）写入一个数据项，另一个事务（T2）读取这个数据项，然后T1中止（a1），那么T2读取的数据就是无效的，因为它最终没有被提交到数据库中。这种未提交的数据读取被称为“脏读”
直接阻止了对应的读写组合顺序。此时得到的其实就是基于锁的定义:
- Read Uncommitted，阻止P0：整个事务阶段对x加长写锁
- Read Commited，阻止P0，P1：短读锁 + 长写锁
- Repeatable Read，阻止P0，P1，P2：长读锁 + 短谓词锁 + 长写锁
- Serializable，阻止P0，P1，P2，P3：长读锁 + 长谓词锁 + 长写锁
这种方式的隔离性定义保证了正确性，但却产生了依赖实现方式的问题：太过严格的隔离性定义，阻止了Optimize或Multi-version的实现方式中的一些正常的情况：
- 针对P0：Optimize的实现方式可能会让多个事务各自写自己的本地副本，提交的时候只要顺序合适是可以成功的，只在需要的时候才abort，但这种选择被P0阻止
- 针对P2：只要T1没有在读x，后续没有与x相关的操作，且先于T2提交。在Optimize的实现中是可以接受的，却被P2阻止
Critique of ANSI中指出的ANSI标准问题，包括Dirty Write和歧义，其实都是由于多Object之间有相互约束关系导致的
黑色部分表示的是ANSI中针对某一个异象描述的异常情况，灰色部分由于多Object约束导致的异常部分，但这部分在传统的异象定义方式中并不能描述，因此其只能退而求其次，扩大限制的范围到黄色部分，从而限制了正常的情况
出问题的本质：由于异象的描述只针对单个object，缺少描述多object之间的约束关系，导致需要用锁的方式来作出超出必须的限制。相应地，解决问题的关键：要有新的定义异象的模型，使之能精准的描述多object之间的约束关系，从而使得我们能够精准地限制上述灰色部分，而将黄色的部分解放出来
A Generalized Theory(1999)：基于序列化图
思路为先定义冲突关系；并以冲突关系为有向边形成序列化图；再以图中的环类型定义不同的异象；最后通过阻止不同的异象来定义隔离级别
每个节点表示一个事务，有向边表示存在一种依赖关系，事务需要等到所有指向其的事务先行提交，合法的提交顺序应该为：T1，T2，T3
有向边包括三种情况：
- 写写冲突ww（Directly Write-Depends）：表示两个事务先后修改同一个数据库Object(w1[x]…w2[x]…)
- 先写后读冲突wr（Directly Read-Depends）：一个事务修改某个数据库Object后，另一个对该Object进行读操作（w1[x]…r2[x]…）
- 先读后写冲突rw（Directly Anti-Depends）：一个事务读取某个Object或者某个Range后，另一个事务进行了修改（r1[x]…w2[x]… or r1[P]…w2[y in P]）
基于序列化图的异象定义
根据有向图定义，将事务对不同Object依赖关系表示到同一张图中，而所谓异象就是在图中找不到一个正确的序列化顺序，即存在某种环。而这种基于环的定义其实就是将基于Lock定义的异象最小化到图中灰色部分
- P0(Dirty Write) 最小化为 G0(Write Cycles)：**序列化图中包含两条边都为ww冲突组成的环，如H0：
H0: w1[x] w2[x] w2[y] c2 w1[y] c1
T1在x上与T2写写冲突，T2又在y上与T1写写冲突，形成了如下图所示的环
-
P1(Dirty Read) 最小化为 G1：Dirty Read异象的最小集包括三个部分G1a(Aborted Reads)，读到的uncommitted数据最终被abort；G1b(Intermediate Reads) ：读到其他事务中间版本的数据；以及G1c(Circular Information Flow)：DSG中包含ww冲突和wr冲突形成的环
-
P2(Fuzzy or Non-Repeatable Read) 最小化为 G2-item(Item Anti-dependency Cycles) ：DSG中包含环，且其中至少有一条关于某个object的rw冲突
-
P3(Phantom) 最小化为 G2(Anti-dependency Cycles): DSG中包含环，并且其中至少有一条是rw冲突，仍然以上面的H3为例：
H3：r1[P] w2[insert y to P] r2[z] w2[z] c2 r1[z] c1
T1在谓词P上与T2 rw冲突，反过来T2又在z上与T1wr冲突，如下图所示：
对应的隔离级别
通过环的方式成功最小化了异象的限制范围，那么排除这些异象就得到了更宽松的，通用的隔离级别定义：
- PL-1（Read Uncommitted）：阻止G0
- PL-2（Read Commited）：阻止G1
- PL-2.99（Repeatable Read）：阻止G1，G2-item
- PL-3（Serializable）：阻止G1，G2
其他隔离级别
除了上述的隔离级别外，在正确性的频谱中还有着大量空白，也就存在着各种其他隔离级别的空间，商业数据库的实现中有两个比较常见：
- Cursor Stability
该隔离界别介于Read Committed和Repeatable Read之间，通过对游标加锁而不是对object加读锁的方式避免了Lost Write异象
- Snapshot Ioslation
事务开始时拿一个Start-Timestamp的snapshot，所有的操作都在这个snapshot上做，commit时拿Commit-Timestamp，检查所有有冲突的值不能再[Start- Timestamp, Commit-Timestamp]被提交，否则abort
Snapshot Ioslation下还会出现Write Skew的异象
Jim Grey 也有文章说这个不合理, 然而此时MVCC, snapshot isolation 还没被发明. 等有snapshot isolation 以后发现snapshot isolation 能够规避Dirty Read, Non-Repeatable Read, 因此认为snapshot isolation 和 Repeatable-read 很像, 所以MySQL, Pg 把他们实现的snapshot isolation 就称为了Repeatable-read isolation.
另外snapshot isolation 其实也没有准确的定义, 因此MySQL 和 PG, Oracle 等等的实现也是有很大的区别的.
总结
-
ANSI isolation levels 定义了异象标准，并根据所排除的异象，定义了，Read Uncommitted、Read Committed、Repeatable Read、Serializable四个隔离级别
-
A Critique of ANSI SQL Isolation Levels认为ANSI的定义并没将有多object约束的异象排除在外，并选择用更严格的基于Lock的定义扩大了每个级别限制的范围
-
Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions认为基于Lock的定义过多的扩大了限制的范围，导致正常情况被排除在外，从而限制了Optimize类型并行控制的使用；指出解决该问题的关键是要有模型能准确地描述这种多Object约束；并给出了基于序列化图的定义方式，将每个级别限制的范围最小化
MySQL Repeatable-Read
snapshot isolation 其实也没有准确的定义, MySQL,PG, Oracle 等实现也是有很大的区别的
pg, oracle 实现：读写都是读取snapshot 版本
mysql InnoDB 只有读是snapshot 版本, DML 操作是读取当前已提交的最新版本
first-committer-wins
在si 模式下, 如果在Start-Timestamp -> Commit-Timestamp 这之间如果有其他的trx2 修改了当前trx1 修改过的内容, 并且在trx1 提交的时候, trx2 已经提交了. 那么trx1 就会abort, 这个叫first-committer-wins
但InnoDB 并不遵守这个规则, repeatable read 模式下, 如果trx1, trx2 都修改了同一行, trx2 是先提交的, 那么trx1 的提交会直接把trx2 覆盖. PG, Oracle 实现的snapshot isolation 里面, 遵守first-committer-wins 规则
所以InnoDB 的snapshot isolation
- 仅仅Read 操作读的是历史版本
- 不遵守first-committer-wins 规则
官方把这种实现叫做 Write committed Repeatable Read
CREATE TABLE IF NOT EXISTS checking (
name CHAR(20) PRIMARY KEY,
balance INT
);
truncate table checking;
insert into checking values ('Tom', 1000), ('Dick', 2000), ('John', 1500);
-- client 1
begin;
select * from checking;
update checking set balance = balance - 250
where name = 'Dick';
update checking set balance = balance + 250
where name = 'Tom';
select sleep(5);
select * from checking;
commit;
-- client 2
set innodb_lock_wait_timeout=10;
begin;
select * from checking;
update checking set balance = balance - 200
where name = 'John';
update checking set balance = balance + 200
where name = 'Tom';
-- Client 2 waits on the locked record
select * from checking;
commit;
client 2 write 操作是直接读取的已提交的最新版本的数据1250,而不是snapshot 中的数据1000