《A Critique of ANSI SQL Isolation Levels》論文實驗
來自專欄分散式與存儲技術
48 人贊了文章
1序言
《A Critique of ANSI SQL Isolation Levels》是理解資料庫隔離性的最關鍵論文之一。
論文對異常現象給出了明確的定義。依據異常現象，可以確定的劃分與比較資料庫產品的隔離級別。
主要異常現象包括：
- 讀未提交（A1）
- 不可重複讀（A2）
- 幻象（A3）
- 更新丟失（P4）
- 幻象（A3B）
- Write Skew（A5B）
- Read Skew（A5A）
本文對Oracle、PostgreSQL、MySQL（InnoDB）的各個隔離級別下的異常操作進行了實驗，結果可用判斷資料庫隔離級別的差異。對於新的資料庫與中間件產品，可以採用此方法判斷其隔離級別的實際水平。
2 結果匯總與分析
Oracle Read Committed、PG Read Committed與 InnoDB Read Committed能力一致。
Oracle Serializable、PG Repeatable Read能力一致。InnoDB Repeatable Read比前兩者低。
PG Serializable與InnoDB Serializable能力一致。
3 Oracle
3.1 Oracle read committed
臟讀A1（不出現）
操作序列：
P1: w1[x]...r2[x]...(c1 or a1) and (c2 or a2) in any order
A1: w1[x]...r2[x]...(a1 and c2 in any order)
異常是否出現：否
操作步驟：
不可重複讀A2（出現）
操作序列：
P2: r1[x]..w2[x]..(c1 or a1) and (c2 or a2) in any order
A2: r1[x]..w2[x]..c2..r1[x]..c1
異常是否出現：是
操作步驟：
幻象A3（出現）
操作序列：
P3:r1[P]..wi[2][y in P]..(c1 or a1) and (c2 or a2) in any order
A3:r1[P]..wi[2][y in P]..c2..r1[P]..c1
異常是否出現：是
操作步驟：
更新丟失P4（出現）
操作序列：
P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)
是否出現異常：是
操作序列：
Read skewA5A（出現）
操作序列：
A5A: r1[x]..w2[x]..w2[ y]..c2..r1[y]...(c1 or a1)
是否出現異常：是
操作序列：
write skewA5B（出現）
操作序列：
A5B: r1[x]...r2[y]..w1[y]..w2[x]..(c1 and c2 occur)
是否出現異常：是
操作序列：
幻象A3B（出現）
操作序列：
b1..b2..r1[P]..w1[y in P]..r2[P]..w2[y in P]..c1..c2
是否出現異常：是
操作序列：
3.2 Oracle serializable
臟讀A1（不出現）
操作序列：
P1: w1[x]...r2[x]...(c1 or a1) and (c2 or a2) in any order
A1: w1[x]...r2[x]...(a1 and c2 in any order)
異常是否出現：否
操作步驟：
不可重複讀A2（不出現）
操作序列：
P2: r1[x]..w2[x]..(c1 or a1) and (c2 or a2) in any order
A2: r1[x]..w2[x]..c2..r1[x]..c1
異常是否出現：否
操作步驟：
幻象A3（不出現）
操作序列：
P3:r1[P]..wi[2][y in P]..(c1 or a1) and (c2 or a2) in any order
A3:r1[P]..wi[2][y in P]..c2..r1[P]..c1
異常是否出現：否
操作步驟：
更新丟失P4（不出現）
操作序列：
P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)
是否出現異常：不出現
操作序列：
Read skewA5A（不出現）
操作序列：
A5A: r1[x]..w2[x]..w2[ y]..c2..r1[y]...(c1 or a1)
是否出現異常：否
操作序列：
write skewA5B（出現）
操作序列：
A5B: r1[x]...r2[y]..w1[y]..w2[x]..(c1 and c2 occur)
是否出現異常：是
操作序列：
幻象A3B（出現）
操作序列：
b1..b2..r1[P]..w1[y in P]..r2[P]..w2[y in P]..c1..c2
是否出現異常：是
操作序列：
4 PostgreSQL
4.1 pg read committed
以下會話設置隔離級別
SET SESSION CHARACTERISTICS AS TRANSACTION isolation level read committed;
臟讀A1（不出現）
操作序列：
P1: w1[x]...r2[x]...(c1 or a1) and (c2 or a2) in any order
A1: w1[x]...r2[x]...(a1 and c2 in any order)
異常是否出現：否
操作步驟：
不可重複讀A2（出現）
操作序列：
P2: r1[x]..w2[x]..(c1 or a1) and (c2 or a2) in any order
A2: r1[x]..w2[x]..c2..r1[x]..c1
異常是否出現：是
操作步驟：
幻象A3（出現）
操作序列：
P3:r1[P]..wi[2][y in P]..(c1 or a1) and (c2 or a2) in any order
A3:r1[P]..wi[2][y in P]..c2..r1[P]..c1
異常是否出現：是
操作步驟：
更新丟失P4（出現）
操作序列：
P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)
是否出現異常：是
操作序列：
Read skewA5A（出現）
操作序列：
A5A: r1[x]..w2[x]..w2[ y]..c2..r1[y]...(c1 or a1)
是否出現異常：是
操作序列：
write skewA5B（出現）
操作序列：
A5B: r1[x]...r2[y]..w1[y]..w2[x]..(c1 and c2 occur)
是否出現異常：是
操作序列：
幻象A3B（出現）
操作序列：
b1..b2..r1[P]..w1[y in P]..r2[P]..w2[y in P]..c1..c2
是否出現異常：是
操作序列：
4.2 pg repeatable read
會話設置如下隔離級別
SET SESSION CHARACTERISTICS AS TRANSACTION isolation level repeatable read;
臟讀A1（不出現）
操作序列：
P1: w1[x]...r2[x]...(c1 or a1) and (c2 or a2) in any order
A1: w1[x]...r2[x]...(a1 and c2 in any order)
異常是否出現：否
操作步驟：
不可重複讀A2（不出現）
操作序列：
P2: r1[x]..w2[x]..(c1 or a1) and (c2 or a2) in any order
A2: r1[x]..w2[x]..c2..r1[x]..c1
異常是否出現：否
操作步驟：
幻象A3（不出現）
操作序列：
P3:r1[P]..wi[2][y in P]..(c1 or a1) and (c2 or a2) in any order
A3:r1[P]..wi[2][y in P]..c2..r1[P]..c1
異常是否出現：否
操作步驟：
更新丟失P4（不出現）
操作序列：
P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)
是否出現異常：否
操作序列：
Read skewA5A（不出現）
操作序列：
A5A: r1[x]..w2[x]..w2[ y]..c2..r1[y]...(c1 or a1)
是否出現異常：否
操作序列：
write skewA5B（出現）
操作序列：
A5B: r1[x]...r2[y]..w1[y]..w2[x]..(c1 and c2 occur)
是否出現異常：是
操作序列：
幻象A3B（出現）
操作序列：
b1..b2..r1[P]..w1[y in P]..r2[P]..w2[y in P]..c1..c2
是否出現異常：是
操作序列：
4.3 pg serializable
設置會話級別
SET SESSION CHARACTERISTICS AS TRANSACTION isolation level serializable;
臟讀A1（不出現）
操作序列：
P1: w1[x]...r2[x]...(c1 or a1) and (c2 or a2) in any order
A1: w1[x]...r2[x]...(a1 and c2 in any order)
異常是否出現：否
操作步驟：
不可重複讀A2（不出現）
操作序列：
P2: r1[x]..w2[x]..(c1 or a1) and (c2 or a2) in any order
A2: r1[x]..w2[x]..c2..r1[x]..c1
異常是否出現：否
操作步驟：
幻象A3（不出現）
操作序列：
P3:r1[P]..wi[2][y in P]..(c1 or a1) and (c2 or a2) in any order
A3:r1[P]..wi[2][y in P]..c2..r1[P]..c1
異常是否出現：否
操作步驟：
更新丟失P4（不出現）
操作序列：
P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)
是否出現異常：否
操作序列：
Read skewA5A（不出現）
操作序列：
A5A: r1[x]..w2[x]..w2[ y]..c2..r1[y]...(c1 or a1)
是否出現異常：否
操作序列：
write skewA5B（不出現）
操作序列：
A5B: r1[x]...r2[y]..w1[y]..w2[x]..(c1 and c2 occur)
是否出現異常：否
操作序列：
幻象A3B（不出現）
操作序列：
b1..b2..r1[P]..w1[y in P]..r2[P]..w2[y in P]..c1..c2
是否出現異常：是
操作序列：
5 InnoDB
5.1 InnoDB read committed
設置隔離級別
set session transaction isolation level Read committed;
臟讀A1（不出現）
操作序列：
P1: w1[x]...r2[x]...(c1 or a1) and (c2 or a2) in any order
A1: w1[x]...r2[x]...(a1 and c2 in any order)
異常是否出現：否
操作步驟：
不可重複讀A2（出現）
操作序列：
P2: r1[x]..w2[x]..(c1 or a1) and (c2 or a2) in any order
A2: r1[x]..w2[x]..c2..r1[x]..c1
異常是否出現：是
操作步驟：
幻象A3（出現）
操作序列：
P3:r1[P]..wi[2][y in P]..(c1 or a1) and (c2 or a2) in any order
A3:r1[P]..wi[2][y in P]..c2..r1[P]..c1
異常是否出現：是
操作步驟：
更新丟失P4（出現）
操作序列：
P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)
是否出現異常：是
操作序列：
Read skewA5A（出現）
操作序列：
A5A: r1[x]..w2[x]..w2[ y]..c2..r1[y]...(c1 or a1)
是否出現異常：是
操作序列：
write skewA5B（出現）
操作序列：
A5B: r1[x]...r2[y]..w1[y]..w2[x]..(c1 and c2 occur)
是否出現異常：是
操作序列：
幻象A3B（出現）
操作序列：
b1..b2..r1[P]..w1[y in P]..r2[P]..w2[y in P]..c1..c2
是否出現異常：是
操作序列：
5.2 InnoDB repeatable read
設置會話隔離級別語句
set session transaction isolation level Repeatable read;
臟讀A1（不出現）
操作序列：
P1: w1[x]...r2[x]...(c1 or a1) and (c2 or a2) in any order
A1: w1[x]...r2[x]...(a1 and c2 in any order)
異常是否出現：否
操作步驟：
不可重複讀A2（不出現）
操作序列：
P2: r1[x]..w2[x]..(c1 or a1) and (c2 or a2) in any order
A2: r1[x]..w2[x]..c2..r1[x]..c1
異常是否出現：否
操作步驟：
幻象A3（不出現）
操作序列：
P3:r1[P]..wi[2][y in P]..(c1 or a1) and (c2 or a2) in any order
A3:r1[P]..wi[2][y in P]..c2..r1[P]..c1
異常是否出現：否
操作步驟：
更新丟失P4（出現）
操作序列：
P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)
是否出現異常：是
操作序列：
Read skewA5A（不出現）
操作序列：
A5A: r1[x]..w2[x]..w2[ y]..c2..r1[y]...(c1 or a1)
是否出現異常：否
操作序列：
write skewA5B（出現）
操作序列：
A5B: r1[x]...r2[y]..w1[y]..w2[x]..(c1 and c2 occur)
是否出現異常：是
操作序列：
幻象A3B（出現）
操作序列：
b1..b2..r1[P]..w1[y in P]..r2[P]..w2[y in P]..c1..c2
是否出現異常：是
操作序列：
InnoDB serializable
設置隔離級別
set session transaction isolation level serializable;
臟讀A1（不出現）
操作序列：
P1: w1[x]...r2[x]...(c1 or a1) and (c2 or a2) in any order
A1: w1[x]...r2[x]...(a1 and c2 in any order)
異常是否出現：否
操作步驟：
不可重複讀A2（不出現）
操作序列：
P2: r1[x]..w2[x]..(c1 or a1) and (c2 or a2) in any order
A2: r1[x]..w2[x]..c2..r1[x]..c1
異常是否出現：否
操作步驟：
幻象A3（不出現）
操作序列：
P3:r1[P]..wi[2][y in P]..(c1 or a1) and (c2 or a2) in any order
A3:r1[P]..wi[2][y in P]..c2..r1[P]..c1
異常是否出現：否
操作步驟：
更新丟失P4（不出現）
操作序列：
P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)
是否出現異常：否
操作序列：
Read skewA5A（不出現）
操作序列：
A5A: r1[x]..w2[x]..w2[ y]..c2..r1[y]...(c1 or a1)
是否出現異常：否
操作序列：
write skewA5B（不出現）
操作序列：
A5B: r1[x]...r2[y]..w1[y]..w2[x]..(c1 and c2 occur)
是否出現異常：否
操作序列：
幻象A3B（不出現）
操作序列：
b1..b2..r1[P]..w1[y in P]..r2[P]..w2[y in P]..c1..c2
是否出現異常：是
操作序列：
版權聲明：自由轉載-非商用-非衍生-保持署名（創意共享3.0許可證）
推薦閱讀：
※SQL每日一練【180609】
※全球首個活人腦細胞資料庫公布
※Nosql資料庫服務之redis
※高並發下的sql優化
※企名片-5.24日國內外融資事件清單(42筆）