select…for update再insert造成deadlock的陷阱

最近side project在寫購票系統，因而踩中了這個坑。這個坑其實對InnoDB的鎖的觀念不熟悉的人會很難發現造成deadlock的原因，因此想特別寫一篇記錄一下，幫助也在開發類似功能的大家少走一點冤望路。

本篇的講解順序會先帶大家重現我之前引發Deadlock的購票功能邏輯，再來說明資料表使用InnoDB引擎下產生如此Deadlock的原因。

 

環境

• 本篇範例使用的DB是 MariaDB 10.3.7 (開源版的MySQL)
• 資料表採用預設的 InnoDB 引擎，隔離層級也是預設的 Repeatable Read
• 範例資料表ticket的schema如下：

CREATE TABLE `ticket` (
  `id`      int(11) NOT NULL AUTO_INCREMENT,
  `show_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ticket_show_id_IDX` (`show_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

id是主鍵(PK)、show_id為了之後查詢上的需要，有建非唯一索引(non-unique index)、user_id理論上是外鍵(FK)，但這個欄位不是本篇的重點所以忽略他。

 

訂票邏輯

環境都準備好後，我們來看看簡化的訂票功能程式邏輯：
 

• 程式取得前端打API傳入的欲訂票場次sid參數與使用者的uid

• 開始SQL的交易

  START TRANSACTION;
  

• 在ticket資料表查詢已購買該場次的票數

  SELECT COUNT(*) FROM `ticket` WHERE `show_id` = sid FOR UPDATE;
  

• 判斷 已購買該場次的票數 < 總購票名額(假設從其他處已知)

• 如果條件成立(場次座位還沒額滿)，在ticket資料表為使用者新增一筆紀錄，完成交易

  INSERT INTO `ticket` SET `show_id` = sid, `user_id` = uid;
  COMMIT;
  

• 如果條件沒成立(場次座位額滿)，交易回滾

  ROLLBACK;
  

 
這邏輯看起來都很合理吧，多筆操作有用交易包起來、查詢完已購買該場次的票數後也有用FOR UPDATE語法將show_id = sid的記錄用排他鎖(X Lock)鎖起來不讓其他交易異動，最後再新增一筆記錄完成這次的交易。

接下來我們實際打開兩個session模擬高併發的情況下資料庫會發生什麼事。

 

情境1：多位使用者成功買到票

假設我們的ticket資料表目前有這些資料：

INSERT INTO test.ticket (id, show_id, user_id)
VALUES                  ( 3,       1,      20),
                        ( 5,       4,      37),
                        ( 6,       4,       1),
                        ( 9,       6,      66),
                        (11,       6,     104),
                        (12,       6,      89);

這時身隔兩處的兩個使用者在幾乎同個瞬間按下了購買場次4的票…

交易1	交易2
使用者1開始了交易 START TRANSACTION;
	使用者2開始了交易 START TRANSACTION;
查詢場次4的已購買票數 SELECT COUNT(*) FROM ticket WHERE show_id = 4 FOR UPDATE;
	查詢場次4已購買的票數 SELECT COUNT(*) FROM ticket WHERE show_id = 4 FOR UPDATE; 阻塞
場次4的已購買票數未超過場次上限 使用者1可購買 INSERT INTO ticket SET show_id = 4, user_id = 1;	阻塞
完成交易 COMMIT;	阻塞
	場次4的已購買票數未超過場次上限 使用者2可購買 INSERT INTO ticket SET show_id = 4, user_id = 2;
	完成交易 COMMIT;

交易2在第四步時執行SELECT...FOR UPDATE出現阻塞，這個很好理解。因為交易1的SELECT...FOR UPDATE已經搶先在第三步取得了排他鎖(X Lock)，交易2必需先等待交易1釋放排他鎖才能存取相同的資料。

有了排他鎖的幫助，交易2才不會同時與交易1讀到同樣的數值。想像一下如果沒有排他鎖存在，雙方都讀到剩下一個座位，結果各自都寫入了自己的購票紀錄，超賣的情況就發生啦。

到目前為止都還沒出現什麼大問題，接下來我們來看看情境2。

 

情境2：出現Deadlock導致部份使用者被DB踢掉操作

我們使用與情境1相同的資料：

INSERT INTO test.ticket (id, show_id, user_id)
VALUES                  ( 3,       1,      20),
                        ( 5,       4,      37),
                        ( 6,       4,       1),
                        ( 9,       6,      66),
                        (11,       6,     104),
                        (12,       6,      89);

一樣是個高併發的場景，同樣身隔兩處的兩位使用者在幾乎同個瞬間按下了購票，只不過這次是場次5…

跟情境1不太一樣的地方在ticket資料表內並沒有任何場次5的購票紀錄。

交易3	交易4
使用者3開始了交易 START TRANSACTION;
	使用者4開始了交易 START TRANSACTION;
查詢場次5的已購買票數 SELECT COUNT(*) FROM ticket WHERE show_id = 5 FOR UPDATE;
	查詢場次5已購買的票數 SELECT COUNT(*) FROM ticket WHERE show_id = 5 FOR UPDATE;
場次5的已購買票數未超過場次上限 使用者3可購買 INSERT INTO ticket SET show_id = 5, user_id = 3; 阻塞
阻塞	場次5的已購買票數未超過場次上限 使用者4可購買 INSERT INTO ticket SET show_id = 5, user_id = 4; DB偵測到Deadlock，踢掉操作
阻塞	交易失敗，回滾 ROLLBACK;
完成交易 COMMIT;

在跑高併發測試時第一次發生這個case當下我的腦袋中出現了10億個為什麼？？？

相同的資料表、相同的SQL指令執行順序，竟然也會Deadlock？

而且怎麼這次SELECT...FOR UPDATE沒有阻塞勒？

折騰了我好一段時間後把MySQL的官方文件看了又看，弄懂InnoDB的鎖與隔離層級後，慢慢能體會Deadlock是怎麼發生的了。

 

Deadlock發生背後的真相

真相只有一個，就是

在存在索引的情況下
SELECT…FOR UPDATE會對查詢條件命中的索引上排他鎖(X Lock)
當查詢條件沒有命中，也就是索引的BTree沒有節點可以上鎖
則會改成使用間距鎖(Gap Lock)，鎖查詢條件兩端的這段範圍

看到這邊，腦筋動比較快的人可能會有疑惑為什麼都已經查無記錄了，還要多此一舉去上鎖呢？這就扯到InnoDB在隔離層級Repeatable Read下，必需保證同一個交易內，執行兩次讀取所得到的資料必需是一致的，因而將這個範圍鎖起來防止插入新的記錄。(有興趣的人自己再去查InnoDB的隔離層級與MVCC機制)

既然間距鎖只為了防止在鎖定的間距內插入新記錄，就可以合理解釋為什麼情境2的第三步與第四部的SELECT...FOR UPDATE並沒有阻塞了：因為他只防寫入才不管你怎麼讀取呢。

所以回到情境2的第三步與第四部，分別在各自的交易中設置了間距鎖，雙方都鎖定了show_id=4 ~ 6的這個區間。

接著繼續執行第五步，發現場次5的已購買票數未超過場次上限，因此交易3想在show_id=4 ~ 6的這個區間插入一筆新的show_id=5的購票紀錄。交易3此時被交易4的間距鎖阻塞了，進行等待。

往下走來到第六步，發現場次5的已購買票數未超過場次上限，因此交易4也想在show_id=4 ~ 6的這個區間插入一筆新的show_id=5的購票紀錄。此時交易4又必需等待交易3在show_id=4 ~ 6的間距鎖釋放後才能插入，Do Re Me So~ Deadlock就是這麼地發生了。

 

Deadlock解方

 

降級隔離層級至 Read Committed

既然使用間距鎖(Gap Lock)會導致Deadlock，那將他禁用好啦。反正會使用到間距鎖的時候該場次的紀錄為零，直接禁用，新增記錄就不會Deadlock了。

聽起來是滿有道理的，但是假設今天這場的座位餘額本來就只有20個，而DB最大連線數允許有50條時呢？

50條連線同時查詢發現COUNT(*)出來是0，於是50條連線又同時對ticket插入購票記錄，這時就出現超賣了。如果此部份的功能沒有在QA測試時被測出來，又不幸被部署到正式環境去了…，公司肯定會被客戶求償…

因此此方案行不通。

 

升級隔離層級至 Serializable

這個方案可以，但必需是非不得已才使用。因為升級隔離層級至Serializable會大大降低資料庫併發的能力，換句話說就是照順序一次只放行一個交易去執行。如果今天又是搶票系統這種高併發的場景，其他request可能都已經timeout了都還輪不到他被執行。

 

創建新的資料表記錄該場次的票數餘額

我自己是採用這個方案，創立場次資訊時就會把該場次座位餘額寫在另一張表裡面(我是存在Redis)。這樣先去SELECT...FOR UPDATE那張表查詢餘額時一定是對該場次座位餘額的記錄加上排他鎖，再UPDATE該筆記錄的值從原本的值-1，最後去ticket新增購票記錄。Redis的話直接DECR扣值跟讀值的動作一氣呵成，簡單又美麗。

 

心得

寫高併發程式真的需要非常細心，對於寫下的每個語句之間會不會產生什麼影響都要三思而行。這也是我自己覺得很有趣、很吸引我持續挑戰的地方，顧好每個小細節才能完成偉大的系統。

 

參考資料

MySQL Document: InnoDB Locking

Rikito Taniguchi: Avoid deadlock caused by a conflict of transactions that (accidentally) acquire gap lock in InnoDB