retrospect isolation levels
昨晚散步时突然想到为啥 SI 不能避免 write skew 呢，原来是忘了约束条件，所以又读了 A Critique of ANSI SQL Isolation Levels 记录一下
这篇文章主要是对 ANSI SQL 隔离级别的修正和补充，结果如下
对比 ANSI SQL 的原始定义，增加了 P0 以及 cursor stability 和 snapshot 隔离级别，扩充了 dirty read、non-repeatable read、 phantom 的定义
下面对 anomaly 和 phantom 进行归纳 (w代表修改，r代表读取，c代表提交，a代表回滚， P代表谓词，即：Predicate，数字则表示事务编号）
| 异常 | 定义 | 备注 | 例子 |
|---|---|---|---|
| P0 | w1[x]…w2[x]…((c1 or a1) and (c2 or a2) in any order) | Dirty Write | 显而易见 |
| P1 | w1[x]…r2[x]…((c1 or a1) and (c2 or a2) in any order) | Dirty Read | 显而易见 |
| P2 | r1[x]…w2[x]…((c1 or a1) and (c2 or a2) in any order) | Non-Repeatable Read | 考虑从x转移40到y，r1[x=50]r2[x=50]w2[x=10]r2[y=50]w2[y=90]c2 r1[y=90]c1，这里T1和T2读到的都是已经提交的数据，问题是T1在读y时x已经被T2修改了，如果T1再次读x，会发现x已经变了 |
| P3 | r1[P]…w2[y in P]…((c1 or a1) and (c2 or a2) in any order) | Phantom | r1[P] w2[insert y to P] r2[z] w2[z] c2 r1[z] c1，这里T1根据条件读取了一批数据，T2插入了满足条件的新数据，然后更新了数据的总量z，然后T1读取z，对比z和读取数据的数量是否相等，很显然是不相等的 |
| P4 | r1[x]…w2[x]…w1[x]…c1 | Lost Update | r1[x=100] r2[x=100] w2[x=120] c2 w1[x=130] c1 很显然即使T2提交了T2的修改仍然丢失（被T3的修改覆盖）这和P0的区别是P4是提交的丢失，P0是未提交的丢失 |
| P4C | rc1[x]…w2[x]…w1[x]..c1 | cursor版本，举例来讲就是 cursor 要一直持有读🔒直到结束 | N/A |
| A5A | r1[x]…w2[x]…w2[y]…c2…r1[y]…(c1 or a1) | Read Skew，和 A5B 一起属于 Data Item Constraint Violation | 这里 T1 先读了x的旧版本（在T2修改前），再读 y 的新版本，可能导致违反约束，比如 x = 10, y = 10，T2 从x转移5到 y，满足 x + y = 20，而T1看到的是 10 + 15 = 25 违反了约束，这个和 P1 的区别是 A5A 读到的都是一提交的数据 |
| A5B | r1[x]…r2[y]…w1[y]…w2[x]…(c1 and c2) | Write Skew | r1[x=10]r1[y=20]r2[x=10]r2[y=20]w1[x=20]w2[y=10]c1c2，这不是 serializable 的，serializable 下 x 应该等于 y，而不是交换了值。再如：r1[x=100]r2[y=200]r1[y=200]r2[x=100]w1[x=-100]w2[y=0]c1c2，T1和T2有相同的约束：在减去200后，x+y > 0，然而在提交后 x + y = -100 显然违反了约束，而这在顺序执行时是不可能发生的。简单来讲就是多个事务读取了相同的数据，但各自更新的数据不冲突，那么SI就允许提交，这就可能造成问题 |
简单来讲，以上两个表格描述的是：如果并发执行的事务的结果和串行执行的一样，那么串行能避免的异常，并发的也能避免。修正后的隔离级别强弱关系是
READ UNCOMMITED < READ COMMITED < CURSOR STABILITY < {REPEATABLE READ, SNAPSHOT} < SERIALIZABLE
REPEATABLE READ 和 SNAPSHOT 不可比较，因为都有对方能避免的，而自身不能避免的异常
至于 Phantom 可能会在 SI 下发生，这个要看具体实现，比如
事务 A
SELECT * FROM Orders WHERE order_amount > 100;
-- 假设此时返回订单 ID 为 1, 2, 3
-- （稍后） 再次执行相同的查询
SELECT * FROM Orders WHERE order_amount > 100;
事务 B
INSERT INTO Orders (customer_id, order_amount) VALUES (200, 150.00);
如果事务 B 在事务 A 的两次查询之间提交，那么事务 A 的第二次查询可能会返回订单 ID 为 1, 2, 3, 4。但如果实现上对 predicate lock，那么 A 的第二次查询仍然返回 1, 2, 3。SI 不仅仅关注已存在行的修改，还关注满足特定搜索条件的行的集合是否保持稳定