MySQL InnoDB 鎖的二三事_ZenDei技術網路在線

近日, 在一個小型項目中, 遇到了一個觸及我知識盲區的bug. 項目用的是MySQL 5.7.25, 其中有一張表 config_data, 包含四個欄位, id, name, value, expireAt. 其中id為主鍵, name建有唯一索引, 表的用途大概就是存放一些有時效性的配置. 以上 ...

近日, 在一個小型項目中, 遇到了一個觸及我知識盲區的bug.

項目用的是MySQL 5.7.25, 其中有一張表 config_data, 包含四個欄位, id, name, value, expireAt. 其中id為主鍵, name建有唯一索引, 表的用途大概就是存放一些有時效性的配置. 以上就是這次故事的背景.

(不要問我為什麼要用這麼奇怪的方式處理需要過時的配置, 項目太簡陋以至只有一臺虛擬主機和一個資料庫, 別的什麼都沒了, 包括Redis)

這張表的使用場景大致為, 假設需要使用某配置a, 先嘗試從表中查找a, 若找到, 判斷是否過期, 過期或者值不存在則從外部獲取配置的值並存入表中, 以便下次使用. 偽代碼流程如下:

config = query('select value, expireAt from config_data where name = "a" lock in share mode;');

if (!config || config.expireAt < now) {    // 不存在或已過期
    beginTransaction();
    config = query('select value, expireAt from config_data where name = "a" for update;');
    if (config && config.expireAt > now) {
        rollback();
        return config.value;
    }
    value = getConfigValueFrom3rdPartyServer();    // 從外部伺服器獲取配置值
    execute('insert config_data (name, value, expireAt) value ("a", value, newExpireTime) on duplicate key update `value`=value, `expireAt`=newExpireTime;');    // 插入或更新配置值以及過期時間
    commit();
    return value;
}

return config.value;

由於配置的值需要從外部伺服器通過介面調用獲取, 執行代價較大, 更重要的是, 第三方伺服器的介面有每日調用次數限制, 因此必須控制出現併發更新配置值時 (即同一時間多個請求到來時配置項過期了) 只有一個進程發起請求獲取配置值並更新資料庫, 其餘進程需等待更新完成並使用更新後的數據.

Again, 只有虛擬主機+DB, 故只好借用資料庫方式加鎖. 基本思路就是, 開始時使用共用鎖 (S Lock) 查找配置值 (資料庫使用了預設的autocommit, 語句執行完後共用鎖自動釋放), 如果需要更新, 開啟事務, 使用排他鎖 (X Lock) 鎖住待更新行, 從外部伺服器獲取配置值 (不考慮獲取失敗情況, 配置值都獲取不了只能直接往外拋異常了) , 使用 insert ... on duplicate key update 方式插入或更新資料庫, 提交事務, done~

假設有兩個進程A, B同時獲取配置值, A, B均能同時獲得共用鎖並查詢到已過期的配置, 然後嘗試獲取排他鎖, 但只會有一個進程能成功獲取排他鎖, 這裡假設是A, 則B在第5行時會被block住, 在A更新完成並提交事務後, B才能從第5行繼續並獲取到最新的配置值. 假如在A更新完成前, 第三個進程C又需要獲取這個配置值, 則會在第1行嘗試獲取共用鎖時由於排他鎖已被A獲得而被block住. 同樣, 待A提交事務後C就能獲得共用鎖並拿到最新的值.

粗看邏輯沒有問題, 併發的問題貌似完全可以由MySQL的行鎖 (Record Lock) 解決. Perfect~ 於是就簡單試了下功能, 扔代碼上主機, 項目就上線運行了.

就這樣過了兩三天, 項目體量實在太迷你了, 每天最多也就1~2k的訪問量, 因此伺服器配置也是低得令人髮指. 期間偶爾收到反饋說介面會報500錯誤, 我一概以“伺服器配置太低”或者“網路問題”為由搪塞過去 (甩鍋小能手~) , 倒也無驚無險地過來了. 直到那一次, 收到某個需求要小改一下前端界面, 調試的時候偶遇了這個神秘的500, 好奇看了一眼報錯內容……

Deadlock found when trying to get lock; try restarting transaction

WTF? Deadlock???

一頓操作排查之後, 基本可以確定問題就是出在上面這段查找配置值的代碼上. 當配置值過期後需要更新時, 如果同時有多個進程嘗試執行上面的代碼更新配置值時, 就會被檢測出死鎖. 具體表現為, 其中某個進程成功更新了資料庫, 其餘進程全部會拋出死鎖異常, 幾乎100%必現 (必現的bug就是好bug~).

按一般對死鎖的理解, 常見的場景是兩個進程按相反順序加鎖訪問兩個資源, 然後卡在互相等對方釋放第一個資源造成的. 然而, 上面的代碼明顯和這個場景完全不沾邊啊?…… 百思不得其解, 只能用盡各種模擬方法嘗試找到原因. 還好最後終於確認了重現的步驟:

首先慣例假設有兩個進程A和B.

A操作步驟	B操作步驟
開始事務	開始事務
select ... for update 查找name=a的行並獲得結果
	select ... for update 查找name=a的行 (被阻塞)
insert ... on duplicate key update ... 更新數據成功
	(deadlock found, gg) 事務被強行中斷並回滾
提交事務, 完成更新

然後我就 (黑人問號.jpg) . Why???? 我不就是更新了行數據, 你都被阻塞了, 等我更新完再去拿結果不就好了?

而且, 即使我將 insert ... on duplicate key update ... 替換成 insert ... , 也照樣能造成B死鎖, 只是A也因唯一索引衝突插入失敗而已, 也就是說, 死鎖和更新無關 (也許吧).

這真的超出我理解範圍了. 調出死鎖分析看看 (執行 show engine InnoDB status; 然後查看Status欄位的 LATEST DETECTED DEADLOCK 部分)

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-09-16 09:57:38 0x7f17c41d5700
*** (1) TRANSACTION:
TRANSACTION 2311, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5, OS thread handle 139740050691840, query id 134 172.17.0.1 root statistics
select * from config_data where name = 'a'
LIMIT 0, 1000
for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 72 index name_UNIQUE of table `test`.`config_data` trx id 2311 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 61; asc a;;
1: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:
TRANSACTION 2310, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 139740051232512, query id 136 172.17.0.1 root update
insert config_data (name, value) value ('a', 2) on duplicate key update value = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 4 n bits 72 index name_UNIQUE of table `test`.`config_data` trx id 2310 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 61; asc a;;
1: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 72 index name_UNIQUE of table `test`.`config_data` trx id 2310 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 61; asc a;;
1: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (1)

大意就是, A在第一步select時拿到了name = a那行的行排他鎖, B同時在select時等待name = a的行鎖, 然後A嘗試插入name = a的行數據時, 插入操作也需要獲取對應行的排他鎖, 這時相當於A和B都在等待同一行的排他鎖, 而鎖目前被A在第一步select ... for update占據著, InnoDB 認為A要完成插入操作需要等待B獲取並釋放鎖, 而B要獲取鎖需要等待A釋放第一步取得的鎖, 而A要釋放已取得的鎖必須要完成插入操作, 於是, boom, deadlock found. InnoDB檢測到死鎖之後會判斷以最小代價 (即選擇已進行較少修改操作的事務) 中斷並回滾涉及事務, 因此B被中斷, A繼續執行並提交事務.

為了驗證以上的推論, 修改了幾種操作順序及方式並測試結果: (上述的死鎖原因分析我並沒有找到嚴謹的官方資料說明, 只是通過多次試驗及閱讀官方關於鎖的介紹文檔推理所得)

如果將A的 insert ... on duplicate key update ... 替換成普通的 insert ... , A的插入操作會因為唯一索引衝突而失敗, 但B仍然會因檢測到死鎖而被強制回滾. 區別僅是 on duplicate key update 方式嘗試獲取的行鎖是排他鎖 (X Lock) (因為涉及更新操作) 而普通insert衝突後嘗試獲取的行鎖是共用鎖 (S Lock). 此處並不對上面的結論造成影響, 故不詳細分析.
如果將A的 insert ... on duplicate key update ... 替換成 update ... 操作, B會等待update操作完成並提交或回滾後順利獲得行鎖, 死鎖並不會出現. 推測是因為 update 和 insert 需要的鎖類型不同, update 需要的鎖已在前面的 select ... for update 處取得, 故能直接執行更新操作.
如果將A的 insert ... on duplicate key update ... 與B的 select ... for update 調換順序 (即先完成插入後再在另外一個事務嘗試獲取鎖) , 則B會在A事務提交或回滾後獲得行鎖, 死鎖同樣不會出現. 因此可知獲取鎖的順序對結果有影響.

可是, 明顯上述的推演在邏輯上並不那麼令人信服, 儘管B的select先於A的insert嘗試獲取鎖, 但A已經事實上持有對應行的行鎖, 按理是可以完成插入操作的 (由調換B的select與A的insert則避免死鎖可以證明). 因此, 這種行為與其說是feature, 更像是bug. (實際上, 這種死鎖場景只會在5.7版本出現, 8.0以後是重現不了的, 但我找不到具體是哪個版本的哪項更新內容, 如有瞭解的大神煩請指教) (btw, 像docker這種容器技術在這種多版本測試場景是真香)

到目前為止, 看上去問題找到了, 之後只要找到解決問題的方法就好, so far so good, right?

然而, 坑總是不會單獨出現的, 避開一個總有一串坑等著你.

因為config_data這張配置表的設計是如果查詢的配置項不存在, 需要由一個進程獲取配置項的值並插入到表中. 為了測試這種場景, 我將config_data的數據清空, 驗證初次使用配置項的邏輯.

照舊假設有兩個進程A和B, 開始時我以為流程和表中已有數據的情景是大同小異的, 但實際結果卻是

A操作步驟	B操作步驟
開始事務
select ... for update 查找name=b的行並獲得空結果
	select ... for update 查找name=b的行並獲得空結果 (非阻塞)
請求第三方服務介面獲取配置項b的值	請求第三方服務介面獲取配置項b的值
(假設較B更早獲得請求結果) insert ... on duplicate key update ... 插入數據並被阻塞
	insert ... on duplicate key update ... 插入數據並觸發InnoDB死鎖檢測, 事務被強制中斷並回滾 (gg again)
插入數據成功
提交事務

問題更大條了. 首先, select ... for update 並沒能block住進程, 導致重覆請求了第三方服務. 考慮到前文提到的第三方介面有日請求次數限制, 萬一同時有一千個進程被執行, 瞬間一千次訪問配額就被消耗了. 其次, 每個進程等於都會嘗試執行插入操作一次, 但有且僅有一個進程能成功, 無疑是對資源的極大浪費.

可為什麼加鎖讀 (Locking read) 沒能阻塞住進程呢? 為什麼最先的插入操作會被阻塞, 而後面的插入操作又會觸發死鎖呢?

一番查閱官方文檔和各種第三方資料, 才明白這種看似違反直覺的結果是我又一個知識盲區.

首先是前置知識, InnoDB的索引可以分為兩種: Clustered Index 和 Secondary Index (抱歉不太確定准確的中文翻譯是什麼). Clustered Index 本質就是主鍵+數據, 可以視作表本身; Secondary Index 就是創建的各種索引, 由組成索引的欄位+主鍵構成. 使用主鍵查找數據時, 直接使用Clustered Index查找並返回結果; 使用其它索引查找數據時, 先使用Secondary Index找到主鍵, 再根據主鍵查找數據 (如果要查找的數據就是索引的一部分或者主鍵本身, 則省略第二步). InnoDB的鎖實際上必須加在上述兩種索引任意之一的記錄上 (或者說, 必須以索引中的記錄作為鎖的錨點). 比如說, 某索引由欄位a構成, 現有兩條記錄 a = 1 , a = 10 . 如果想對 a = 5 加鎖, 只能獲取兩條記錄1和10之間的間隙鎖 (Gap Lock).

使用select ... for update時, 如果查找的條件完全命中一個唯一索引 (即, 假如唯一索引由a和b兩個欄位組成, 則查找條件必須完全包含這兩個欄位), 且返回了一條結果, 則會對這條結果加上行鎖. 但如果指定的查詢條件找不到任何結果, 因為索引的記錄實際不存在, 無法加行鎖, 所以只能對查找條件對應的區間加上間隙鎖. 例如上面的流程中, 假設name分別有a和c兩條記錄, 使用name = b作查找條件進行locking read時將會加上a到c之間的間隙鎖 (如果沒有c這條記錄, 則是a到正無窮之間).

間隙鎖與行鎖的不同之處在於, 即使獲取的間隙鎖是排他鎖, 同一個間隙鎖 (更確切地說, 是同一個區間上的間隙鎖) 可以被多個事務同時持有. 這就是上面的流程中進程A和B執行select ... for update時均能立即返回而沒有阻塞的原因, 它們都獲取到了name = b所在區間的間隙排他鎖 (這裡叫排他鎖多少顯得不太準確). 由於間隙鎖可以被多個事務同時持有, 可以推論出間隙鎖只能是一種“純限制”(purely inhibitive) 鎖, 即, 獲得鎖之後, 只能禁止其它事務往這個區間上修改數據, 並不能使當前事務能夠修改此區間上的數據.

因為間隙鎖的獲取並不能保證可以插入數據, 因此A進程到insert那一步時, 仍然需要等待獲得這行的行排他鎖. 由於B進程也持有這個區間的間隙鎖, 禁止了A插入數據, 因此必須等B進程釋放持有的間隙鎖才能完成插入操作. 然而, B進程在釋放間隙鎖之前, 同樣需要向同一個位置插入數據, 也需要等待A釋放間隙鎖後獲取這行的行鎖. 因此, A和B都在等對方釋放間隙鎖, deadlock found, again.

值得一提的是, 上述情景只會在預設的隔離級別 (Isolation Levels) 可重覆讀 (REPEATABLE READ) 或更高的級別中出現, 讀提交 (READ COMMITTED) 或更低級別中, 由於不會使用間隙鎖, 因此不會造成死鎖. (但仍然存在多次訪問第三方服務介面問題)

問題原因基本上確定了, 然後就是尋找解決方案. 可行的方案例如有:

方案一. 更新配置值的事務開始前, 先查找一次配置項, 若為空 (即未初始化), 先嘗試插入一個超時時間戳為0的數據, 然後再執行更新操作. 具體流程偽代碼如下:

config = query('select value, expireAt from config_data where name = "a" lock in share mode;');

if (!config) {
    execute('insert ignore config_data (name, value, expireAt) values ("a", 0, 0);');    // expireAt 必須設置為小於當前時間戳, 確保插入的數據無效
    config = query('select value, expireAt from config_data where name = "a" lock in share mode;');
}

if (config.expireAt < now) {
    beginTransaction();
    config = query('select value, expireAt from config_data where name = "a" for update;');
    if (config.expireAt > now) {
        rollback();
        return config.value;
    }
    value = getConfigValueFrom3rdPartyServer();    // 從外部伺服器獲取配置值
    execute('update config_data set `value`=value, `expireAt`=newExpireTime;');    // 更新配置值以及過期時間
    commit();
    return value;
}

return config.value;

相當於配置項不存在時, 強制初始化一個過期的配置項再走常規的查找或更新流程. 由於單純的更新操作可以直接使用select ... for update時獲得的鎖, 因此不會造成死鎖問題.

方案二. 使用MySQL提供的GET_LOCK/RELEASE_LOCK方法, 在查找配置項前加鎖, 查找或更新後釋放鎖. 相當於將所有查找配置項操作變為線性順序, 避免任何併發. 缺點是性能代價較高.

方案三. 依賴外部鎖機制. 這在本項目中較難實現.

綜合考慮, 對於本項目來說, 方案一是較為合適的解決方案. 因為只有第一次初始化時需要執行插入操作, 其餘時間除了配置項過期需要更新時會出現阻塞等待, 絕大部分場景都可以進行併發讀取數據, 性能相對較好.

refs.

https://fastmail.blog/advanced/mysql-lock-nonexistent-row/

https://mysqlquicksand.wordpress.com/2019/12/20/select-for-update-on-non-existent-rows/

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

https://jahfer.com/posts/innodb-locks/