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 [[email protected] ~]# mkdir lamp [[email protected] ~]# cd lamp/ [[email protected] lamp]# mkdir files [[email protected] lamp]# ls files [[email protected] ...
  • 2022-09-18-21:28:59 老師作業說明: TOP500中國超算占比,LINUX系統占比 說明:當時使用的是bing搜索,中國超算占比其實澎湃新聞什麼的都有介紹,但是我對它的數據來源持懷疑態度,索性自己去官網上看資料,後面解決Linux系統占比問題時,也是直接想著在官網解決,其實後面和同 ...
一周排行
    -Advertisement-
    Play Games
  • Github / Gitee QQ群(1群) : 813100564 / QQ群(2群) : 579033769 視頻教學 介紹 MiniWord .NET Word模板引擎,藉由Word模板和數據簡單、快速生成文件。 Getting Started 安裝 nuget link : https:// ...
  • Array.Sort Array類中相當實用的我認為是Sort方法,相比起冗長的冒泡排序,它的出現讓排序更加的簡化 結果如下: 還可以聲明一個靜態方法用來專門調用指定數組排序,從名為 array 的一維數組中 a 索引處開始,到 b 元素 從小到大排序。 註意: a + b 不能大於 array 的 ...
  • 前言 在上一篇文章CLR類型系統概述里提到,當運行時掛起時, 垃圾回收會執行堆棧遍歷器(stack walker)去拿到堆棧上值類型的大小和堆棧根。這裡我們來翻譯BotR里一篇專門介紹Stackwalking的文章,希望能加深理解。 順便說一句,StackWalker在中文里似乎還沒有統一的翻譯,J ...
  • 使用過 nginx 的小伙伴應該都知道,這個中間件是可以設置跨域的,作為今天的主角,同樣的 反向代理中間件的 YARP 毫無意外也支持了跨域請求設置。 有些小伙伴可能會問了,怎樣才算是跨域呢? 在 HTML 中,一些標簽,例如 img、a 等,還有我們非常熟悉的 Ajax,都是可以指向非本站的資源的 ...
  • 什麼是Git Git 是一個開源的分散式版本控制系統,用於敏捷高效地處理任何或小或大的項目。 Git 是 Linus Torvalds 為了幫助管理 Linux 內核開發而開發的一個開放源碼的版本控制軟體。 Git 與常用的版本控制工具 CVS, Subversion 等不同,它採用了分散式版本庫的 ...
  • 首先CR3是什麼,CR3是一個寄存器,該寄存器內保存有頁目錄表物理地址(PDBR地址),其實CR3內部存放的就是頁目錄表的記憶體基地址,運用CR3切換可實現對特定進程記憶體地址的強制讀寫操作,此類讀寫屬於有痕讀寫,多數驅動保護都會將這個地址改為無效,此時CR3讀寫就失效了,當然如果能找到CR3的正確地址... ...
  • 說明 onlyoffice為一款開源的office線上編輯組件,提供word/excel/ppt編輯保存操作 以下操作均基於centos8系統,officeonly鏡像版本7.1.2.23 鏡像下載地址:https://yunpan.360.cn/surl_y87CKKcPdY4 (提取碼:1f92 ...
  • 二叉樹查找指定的節點 前序查找的思路 1.先判斷當前節點的no是否等於要查找的 2.如果是相等,則返回當前節點 3.如果不等,則判斷當前節點的左子節點是否為空,如果不為空,則遞歸前序查找 4.如果左遞歸前序查找,找到節點,則返回,否繼續判斷,當前的節點的右子節點是否為空,如果不為空,則繼續向右遞歸前 ...
  • ##Invalid bound statement (not found)出現原因和解決方法 ###前言: 想必各位小伙伴在碼路上經常會碰到奇奇怪怪的事情,比如出現Invalid bound statement (not found),那今天我就來分析以下出現此問題的原因。 其實出現這個問題實質就是 ...
  • ###一、背景知識 爬蟲的本質就是一個socket客戶端與服務端的通信過程,如果我們有多個url待爬取,只用一個線程且採用串列的方式執行,那隻能等待爬取一個結束後才能繼續下一個,效率會非常低。 需要強調的是:對於單線程下串列N個任務,並不完全等同於低效,如果這N個任務都是純計算的任務,那麼該線程對c ...