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

最近在寫購票系統,因而踩中了這個坑。這個坑其實對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(*) 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(*)
WHERE show_id = 4
FOR UPDATE;
查詢場次4已購買的票數
SELECT COUNT(*)
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(*)
WHERE show_id = 5
FOR UPDATE;
查詢場次5已購買的票數
SELECT COUNT(*)
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


2 則迴響

  • alan

    2021-01-19

    好複雜...淡江的學弟表示崇拜...

    回復
    • Andy Wu

      2021-01-20

      有遇到踩坑了就自然會想把他弄懂😂

      回復

發佈留言