RDBMS - SX LOCK 與 MVCC 不同的 isolation level 運作方式介紹

之前有介紹到 RDBMS 的 Isolation Level 總共有四種以及每個隔離層級可以預防的 Race Condition 的程度在哪。而根據不同的資料庫的底層設計，分成 SX LOCK 跟 MVCC，它們各自實作這四種 Isolation Level 又是如何設計的？今天就來介紹這些～

這次內容依舊參考，[TritonHo 大神的簡報](https://github.com/TritonHo/slides/tree/master/Taipei 2019-04 course)，好教材！

SX LOCK 機制下的 Read Committed Level 介紹

記住以下原則：

• 對該 Record 改動前會將 Record 加入 X LOCK，直到 TX 結束

• 讀取該 Record 前，會把 Record 加入 S LOCK，在 statement 結束後立即歸還

• 其他試圖讀取 / 改動這些 record 的 TX 會被 block 住，直到本 TX 完成

• 改動會阻擋讀寫

SX LOCK 如何解決 Dirty Read

奸商	客人
Start transaction read committed;	Start transaction read committed;
Update stock set last_price = '0USD' where stock_name = 'MSFT'; /* 資料被加上 X LOCK */
	Select last_price from stock where stock_name = 'MSFT'; /* 拿取 S lock 失敗，所以本 TX 被 blocking */
Rollback; /* 歸還 X lock */
	Commit; /* 成功拿取 S lock，顯示正確股價 */

就算奸商不斷地發起 Rollback，在 Read Committed Isolation 下，客人只是需要額外的等待時間，不會收到奸商沒有 commit 的 dirty data

MVCC 機制下的 Read Committed Level 介紹

記住以下原則：

• 進行 insert/update/delete 時，會先為 Record 加上 X LOCK，直到 TX 完成才歸還
• 讀取 Record 時，只會考慮已經 committed 的最新版本，就算該 Record 已經被加上 X LOCK，Read 也不會被阻擋
• 當兩個 TX 想改動同一個 Record 時，其中一個才會被阻擋

MVCC 如何解決 Dirty Read

奸商	客人
Start transaction read committed;	Start transaction read committed;
Update stock set last_price = '0USD' where stock_name = 'MSFT'; /* 資料被加上 X lock，並且建立還未 Commit 的 ver1 副本 */
	Select last_price from stock where stock_name = 'MSFT'; /* 資料庫無視還未 Committed 的 Ver1 副本，直接讀取最新的 ver0 副本 */
Rollback; /* 歸還 X lock */	Commit;

同樣地，就算奸商不斷地發起 Rollback，在 Read Committed Isolation 下，客人不需要有額外的等待時間，會直接讀取到 Committed 的最新版本的資料，因此不會有額外的等待時間也不會收到奸商沒有 Commit 的 Dirty Data

SX LOCK 機制下的 Repeatable Read Level 介紹

記住以下原則：

跟 Read Committed 相似，只是讀取時取得的 S LOCK，必須在 TX 結束時才能歸還，所以讀過的 Record 就不能改動，必須等待 TX 結束才可以！

會容易引起 deadlock，因為：改動會阻擋讀寫 + 讀取會阻擋改動

SX LOCK 如何解決 Non-repeatable read

顧客	航空公司
Start transaction repeatable read;
Select sum(cost) from flight_misc_cost where flight_name = 'HKG-->BKK' ; /* 資料被加上 S lock */
	航空公司同一時間卻決定加價： Start transaction repeatable read; Update flight set price = '300USD' where flight_name = 'HKG-->BKK'; /* 因為無法拿到 X lock，所以被 blocking */
Update user set balance = balance - (Select price from flight where flight_name = 'HKG-->BKK';) /* 資料的 S lock 提升為 X lock，這時用戶會 被扣除 200USD */
Commit; /* 返回所有鎖 */
	/* 終於拿到了 X lock，執行資料改動 */ Commit; /* 返回所有鎖 */

MVCC 機制下的 Repeatable Read Level 介紹

記住以下原則：

• 讀取資料時，只考慮在 TX 開始前已經 committed 的版本，又稱 Snapshot Isolation
• 改動資料時，除了拿取 X LOCK，還檢查 Record 是否存在 TX 開始後的 Committed 版本，如果存在就會 raise exception 並強制 Rollback 目前 TX
  1. PostgreSQL 錯誤訊息： could not serialize access due to concurrent update
  2. Oracle：將檢查延到 TX Commit 才進行
• 不會像 SX LOCK 容易產生 deadlock，但是 Repeatable Read TX 很容易被 Rollback 重來
• Oracle 聲稱的 Serializable Isolation，實際上只是 Repeatable Read

MVCC 如何解決 Non-repeatable read

例子一：

顧客	航空公司
Start transaction repeatable read;
Select sum(cost) from flight_misc_cost where flight_name = 'HKG-->BKK'; /* ver0 的 price 是 200USD */
	航空公司同一時間卻決定加價： Start transaction repeatable read; <br />Update flight set price = ‘300USD’ where flight_name = ‘HKG–>BKK’; Commit; /* 返回所有鎖，並且把 ver1 副本狀態改成 Committed */
Update user set balance = balance - (Select price from flight where flight_name = 'HKG-->BKK';) /* 忽略 TX 開始後才建立的 ver1，用戶被扣 掉 200USD */
Commit;

即使航空公司的 TX 從中間開始並且先行完成，顧客的 TX 還是只使用 TX 開始前的數據。實際上現實效果的排序就是：顧客的 TX、航空公司的 TX

例子二：

顧客	航空公司
	Start transaction repeatable read; Update flight set price = '300USD' where flight_name = 'HKG-->BKK';
Start transaction repeatable read;
Select sum(cost) from flight_misc_cost where flight_name = 'HKG-->BKK'; /* ver0 的 price 是 200USD */
	Commit; /* 返回所有鎖，並且把 ver1 副本狀態改成 Committed */
Update user set balance = balance - (Select price from flight where flight_name = 'HKG-->BKK';) /* 忽略 TX 開始後才建立的 ver1，用戶被扣 掉 200USD */
Commit;

必須注意的：即使航空公司 TX 開始時間在顧客 TX 之前，邏輯排序卻是：顧客的 TX，航空公司的 TX。

SX LOCK 機制下的 Serializable Level 介紹

記住以下原則：

• RDBMS 的 Serializable 不等於數學上的 Serializable

  Auto Increment、Sequence 不被包括到 TX

• 有 predicate lock，像是 Where age between 20 and 35

  在 Repeatable Read 的基礎下，執行 query 時，除了會將被讀取的 rows 加入 S LOCK，還加上 Predicate LOCK，而 TX 的 insert/update，只要影響到的 rows 滿足 predicate lock 的範圍，那個 TX 會被阻擋

• MySQL 的 Serializable 不遵守 ANSI SQL 定義

• MySQL 用了 gap lock 去防止 Phantom Read

• 極度吃 CPU 也極容易引起 deadlock，沒特別原因不會使用該模式

SX LOCK 如何解決 Phantom read

顧客	航空公司
Start transaction SERIALIZABLE;
Select sum(cost) from flight_misc_cost where flight_name = 'HKG-->BKK'; /* 建立 where flight_name = 'HKG–>BKK’的 predicate lock */
	政府決定讓航空公司徵收燃油附加費，航空公司當然立即跟隨 Start transaction SERIALIZABLE; Insert into flight_misc_cost(flight_name, item_name, cost) values ( 'HKG-->BKK', '燃油附加費', '10USD') /* 因為資料符合 predicate lock 所以 insert 被阻擋 */
Update user set balance = balance - (Select sum(cost) from flight_misc_cost where flight_name = 'HKG-->BKK') /* 這時用戶會被扣除 60USD */
Commit; /* 返回所有鎖 */
	/* 沒有了 predicate lock，TX 可以繼續執行 */ Commit;

MVCC 機制下的 Serializable Level 介紹

記住以下原則：

• 如果沒有使用 sequence /auto increment，RDBMS 的 Serializable (MVCC) 等於數學上的 Serializable
• 在 Repeatable Read 的基礎上，為每個 Query 的 Predicate 加上 Predicate monitoring，當有新版本的 committed records 滿足 predicate，而其版本是在本 TX 開始後的時間點，則本 TX raise exception and rollback
• 高 CPU 要求，沒特別原因不建議使用
• Oracle 沒有這個級別

MVCC 如何解決 Phantom read

顧客	航空公司
Start transaction SERIALIZABLE;
Select sum(cost) from flight_misc_cost where flight_name = 'HKG-->BKK'; /* 建立 where flight_name = 'HKG–>BKK’的 predicate monitoring */
	政府決定讓航空公司徵收燃油附加費，航空公 司當然立即跟隨 Start transaction SERIALIZABLE; Insert into flight_misc_cost(flight_name, item_name, cost) values ( 'HKG-->BKK', '燃油附加費', '10USD')
	Commit; /* 因為資料符合 predicate，提醒 predicate 相關的 TX */
/* 接收到 predicate monitoring 的 exception， 引起強制性 Rollback */
rollback; /* 返回所有鎖 和 monitoring*/

MVCC 機制下的 Write Skew 問題

• Phantom Read 的一種
• 兩個同時執行的 TX，都在 insert record，而這些 record 是符合對方的 select 條件
• 在 Repeatable read isolation 下，T1 和 T2 都看不見對方的 insert
• Consistency 是指能為 T1 和 T2 分出先後次序，而在 Write Skew 時不能
• 在 MVCC 世界中 Serialization Isolation 就是為了防範 Write Skew

MVCC 如何解決 Write Skew

背景介紹：

• 某銀行，要求你的總負債不超過 100NT

  Select sum(amount) from debts where user_id = ? and status = ‘unpaid’

• 在 Repeatable-Read 時，TX 看不到開始時候的 Insert

• 所以只要同一秒按下借錢，可以突破 100NT 限制

使用者分身一	使用者分身二
Start transaction Repeatable read;
	Start transaction REPEATABLE READ;
Select sum(amount) from debts where user_id = X and status = 'unpaid'; /* 返回 70NT，通過檢查 */
/* 執行借錢 20NT */ Insert into debts(user_id, amount, status) values (X, 20NT, 'unpaid');
	Select sum(amount) from debts where user_id = X and status = 'unpaid'; /* 返回 70NT，通過檢查 */
	/* 執行借錢 20NT */ Insert into debts(user_id, amount, status) values (X, 20NT, 'unpaid');
	commit;
commit;

結果是：借款人在二個 TX 都同時 commit 後，欠債到達 110NT，是 race condition。

而如果採用 SERIALIZABLE 的話，在使用者分身二 commit 之後，因為資料符合 predicate，提醒 predicate 相關的 TX，因為使用者分身一的 TX 就會被強制 Rollback。因此使用者分身一的借錢根本就不會成立，而使用者分身二最後成立，成功借了 20 塊錢，總欠債 90 元，並沒有超過 100 元，因此並沒有造成 Race Condition

總結

透過這些介紹，可以知道 SX LOCK 與 MVCC 面對不同的 Isolation level 的實作方式，為什麼需要這些事情，因為這會影響你開發系統你要選擇怎樣類型的 RDBMS，而當選定後要如何去根據該 RDBMS 的特性去 Debug，這才是最重要的！

下篇文章應該會帶來實際上去操作資料庫，試著模擬以上例子造成的 Race Condition 來看看。