MySQL InnoDB 鎖的二三事

来源:https://www.cnblogs.com/reginald-lee/archive/2022/09/22/16697879.html
-Advertisement-
Play Games

近日, 在一個小型項目中, 遇到了一個觸及我知識盲區的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/


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 前言 本次案例最終實現效果 開發環境 python 3.8: 解釋器 pycharm: 代碼編輯器 界面代碼實現 先導入所需模塊 import tkinter as tk from tkinter import ttk import tkinter.messagebox 創建視窗 root = tk ...
  • 一:背景 1. 講故事 其實這個問題是前段時間有位朋友咨詢我的,由於問題說的比較泛,不便作答,但想想梳理一下還是能回答一些的,這篇就來聊一聊下麵這幾個鎖。 Interlocked AutoResetEvent / ManualResetEvent Semaphore 用戶態層面我就不想說了,網上一搜 ...
  • .NET運行時之書(Book of the Runtime,簡稱BotR)是一系列描述.NET運行時的文檔,2007年左右在微軟內部創建,最初的目的為了幫助其新員工快速上手.NET運行時;隨著.NET開源,BotR也被公開了出來,如果你想深入理解CLR,這系列文章你不可錯過。 BotR系列目錄: [ ...
  • 從頭一二去閱讀語法和命令說明,對於腳本小白來說比較枯燥,難以堅持,所以這裡選擇對一份完整的shell腳本代碼來逐行逐段解讀,希望可以一渡小白,幫助我們快速進入腳本的大門^_^ ...
  • 版本控制gitlab 什麼是版本控制gitlab GitLab 是一個用於倉庫管理系統的開源項目,使用Git作為代碼管理工具,併在此基礎上搭建起來的Web服務。安裝方法是參考GitLab在GitHub上的Wiki頁面。Gitlab是目前被廣泛使用的基於git的開源代碼管理平臺, 基於Ruby on ...
  • 提到直流無刷電機,那不得不提的就是有刷電機了。有刷電機有一個比較令人討厭的缺點:那就是“吵”。 因為電刷和換向環需要時刻不停地摩擦,才能給電樞供電。 所以,如果你想要一個“靜音風扇”的話,肯定不能選使用了有刷電機的產品。 並且電刷使用時間久了,比較容易損壞。電流較大的時候,你甚至可以看到電刷在換向的 ...
  • 腳本安裝lamp [root@localhost ~]# mkdir lamp [root@localhost ~]# cd lamp/ [root@localhost lamp]# mkdir files [root@localhost lamp]# ls files [root@localhos ...
  • 2022-09-18-21:28:59 老師作業說明: TOP500中國超算占比,LINUX系統占比 說明:當時使用的是bing搜索,中國超算占比其實澎湃新聞什麼的都有介紹,但是我對它的數據來源持懷疑態度,索性自己去官網上看資料,後面解決Linux系統占比問題時,也是直接想著在官網解決,其實後面和同 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...