MySQL InnoDB 鎖的二三事

近日, 在一個小型專案中, 遇到了一個觸及我知識盲區的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 = https://www.cnblogs.com/reginald-lee/p/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.

然后我就 (黑人問號.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 = https://www.cnblogs.com/reginald-lee/p/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, 開始時我以為流程和表中已有資料的情景是大同小異的, 但實際結果卻是

問題更大條了. 首先, 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 = https://www.cnblogs.com/reginald-lee/p/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/

標籤：MySQL

上一篇：我眼中的大資料（二）——HDFS

下一篇：SQLite基礎語法速用大法（Flutter）