MySQL事務死鎖問題排查

来源:https://www.cnblogs.com/Jcloud/archive/2023/09/26/17730491.html
-Advertisement-
Play Games

一、背景 在預發環境中，由消息驅動最終觸發執行事務來寫庫存，但是導致MySQL發生死鎖，寫庫存失敗。 com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: rpc error: code = Aborted desc = ...


一、背景

在預發環境中，由消息驅動最終觸發執行事務來寫庫存，但是導致MySQL發生死鎖，寫庫存失敗。

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: rpc error: code = Aborted desc = Deadlock found when trying to get lock; try restarting transaction (errno 1213) (sqlstate 40001) (CallerID: ): Sql: "/* uag::omni_stock_rw;xx.xx.xx.xx:xxxxx;xx.xx.xx.xx:xxxxx;xx.xx.xx.xx:xxxxx;enable */  insert into stock_info(tenant_id, sku_id, store_id, available_num, actual_good_num, order_num, created, modified, SAVE_VERSION, stock_id) values (:vtg1, :vtg2, :_store_id0, :vtg4, :vtg5, :vtg6, now(), now(), :vtg7, :__seq0) /* vtgate:: keyspace_id:e267ed155be60efe */", BindVars: {__seq0: "type:INT64 value:"29332459" "_store_id0: "type:INT64 value:"50650235" "vtg1: "type:INT64 value:"71" "vtg2: "type:INT64 value:"113817631" "vtg3: "type:INT64 value:"50650235" "vtg4: "type:FLOAT64 value:"1000.000" "vtg5: "type:FLOAT64 value:"1000.000" "vtg6: "type:INT64 value:"0" "vtg7: "type:INT64 value:"20937611645" "}

初步排查，在同一時刻有兩條請求進行寫庫存的操作。

時間前後相差1s，但最終執行結果是，這兩個事務相互死鎖，均失敗。

事務定義非常簡單，偽代碼描述如下：

start transaction
// 1、查詢數據
data = select for update(tenantId, storeId, skuId);
if (data == null) {
    // 插入數據
    insert(tenantId, storeId, skuId);
} else {
    // 更新數據
    update(tenantId, storeId, skuId);
}
end transaction

該資料庫表的索引結構如下：

索引類型 索引組成列
PRIMARY KEY (stock_id)
UNIQUE KEY (sku_id,store_id)

所使用的資料庫引擎為Innodb，隔離級別為RR[Repeatable Read]可重覆讀。

二、分析思路

首先瞭解下Innodb引擎中有關於鎖的內容

2.1 Innodb中的鎖

2.1.1 行級鎖

在Innodb引擎中，行級鎖的實現方式有以下三種：

名稱 描述
Record Lock 鎖定單行記錄，在隔離級別RC和RR下均支持。
Gap Lock 間隙鎖，鎖定索引記錄間隙（不包含查詢的記錄），鎖定區間為左開右開，僅在RR隔離級別下支持。
Next-Key Lock 臨鍵鎖，鎖定查詢記錄所在行，同時鎖定前面的區間，故區間為左開右閉，僅在RR隔離級別下支持。

同時，在Innodb中實現了標準的行鎖，按照鎖定類型又可分為兩類：

名稱 符號 描述
共用鎖 S 允許事務讀一行數據，阻止其他事務獲得相同的數據集的排他鎖。
排他鎖 X 允許事務刪除或更新一行數據，阻止其他事務獲得相同數據集的共用鎖和排他鎖。

簡言之，當某個事物獲取了共用鎖後，其他事物只能獲取共用鎖，若想獲取排他鎖，必須要等待共用鎖釋放；若某個事物獲取了排他鎖，則其餘事物無論獲取共用鎖還是排他鎖，都需要等待排他鎖釋放。如下表所示：

將獲取的鎖（下）\已獲取的鎖（右） 共用鎖S 排他鎖X
共用鎖S 相容 不相容
排他鎖X 不相容 不相容

2.1.2 RR隔離級別下加鎖示例

假如現在有這樣一張表user，下麵將針對不同的查詢請求逐一分析加鎖情況。user表定義如下：

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用戶id',
  `mobile_num` bigint(20) NOT NULL COMMENT '手機號',
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_USER_ID` (`user_id`),
  KEY `IDX_MOBILE_NUM` (`mobile_num`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶信息表'

其中主鍵id與user_id為唯一索引，user_name為普通索引。

假設該表中現有數據如下所示：

id user_id mobile_num
1 1 3
5 5 6
8 8 7
9 9 9

下麵將使用select ... for update 語句進行查詢，分別針對唯一索引、普通索引來進行舉例。

1、唯一索引等值查詢

select * from user
where id = 5 for update

select * from user
where user_id = 5 for update

在這兩條SQL中，Innodb執行查詢過程時，會如何加鎖呢？

我們都知道Innodb預設的索引數據結構為B+樹，B+樹的葉子結點包含指向下一個葉子結點的指針。在查詢過程中，會按照B+樹的搜索方式來進行查找，其底層原理類似二分查找。故在加鎖過程中會按照以下兩條原則進行加鎖：

1.只會對滿足查詢目標附近的區間加鎖，並不是對搜索路徑中的所有區間都加鎖。本例中對搜索id=5或者user_id=5時，最終可以定位到滿足該搜索條件的區域(1,5]。

2.加鎖時，會以Next key Lock為加鎖單位。那按照1滿足的區域進行加Next key Lock鎖（左開右閉），同時因為id=5或者user_id=5存在，所以該Next key Lock會退化為Record Lock，故只對id=5或user_id=5這個索引行加鎖。

如果查詢的id不存在，例如：

select * from user
where id = 6 for update

按照上面兩條原則，首先按照滿足查詢目標條件附近區域加鎖，所以最終會找到的區間為(5,8]。因為id=6這條記錄並不存在，所以Next key Lock(5, 8]最終會退化為Gap Lock，即對索引(5,8)加間隙鎖。

2、唯一索引範圍查詢

select * from user
where id >= 4 and id <8 for update

同理，在範圍查詢中，會首先匹配左值id=4，此時會對區間(1,5]加Next key Lock，因為id=4不存在，所以鎖退化為 Gap Lock(1,5)；接著會往後繼續查找id=8的記錄，直到找到第一個不滿足的區間，即Next key Lock(8, 9]，因為8不在範圍內，所以鎖退化為Gap Lock(8, 9)。故該範圍查詢最終會鎖的區域為(1, 9)

3、非唯一索引等值查詢

對非唯一索引查詢時，與上述的加鎖方式稍有區別。除了要對包含查詢值區間內加Next key Lock之外，還要對不滿足查詢條件的下一個區間加Gap Lock，也就是需要加兩把鎖。

select * from user
where mobile_num = 6 for update

需要對索引(3, 6]加Next key Lock，因為此時是非唯一索引，那麼也就有可能有多個6存在，所以此時不會退化為Record Lock；此外還要對不滿足該查詢條件的下一個區間加Gap Lock，也就是對索引(6,7)加鎖。故總體來看，對索引加了(3,6]Next key Lock和(6, 7) Gap Lock。

若非唯一索引不命中時，如下：

select * from user 
where mobile_num = 8 for update

那麼需要對索引(7, 9]加Next key Lock，又因為8不存在，所以鎖退化為Gap Lock (7, 9)

4、非唯一索引範圍查詢

select * from user
where mobile_num >= 6 and mobile_num < 8
for update

首先先匹配mobile_num=6，此時會對索引(3, 6]加Next Key Lock，雖然此時非唯一索引存在，但是不會退化為Record Lock；其次再看後半部分的查詢mobile_num=8，需要對索引(7, 9]加Next key Lock，又因為8不存在，所以退化為Gap Lock (7, 9)。最終，需要對索引行加Next key Lock(3, 6] 和 Gap Lock(7, 9)。

2.1.3 意向鎖（Intention Locks）

Innodb為了支持多粒度鎖定，引入了意向鎖。意向鎖是一種表級鎖，用於表明事務將要對某張表某行數據操作而進行的鎖定。同樣，意向鎖也分為類：共用意向鎖（IS）和排他意向鎖（IX）。

名稱 符號 描述
共用意向鎖 IS 表明事務將要對錶的個別行設置共用鎖
排他意向鎖 IX 表明事務將要對錶的個別行設置排他鎖

例如select ... lock in shared mode會設置共用意向鎖IS；select ... for update會設置排他意向鎖IX

設置意向鎖時需要按照以下兩條原則進行設置：

1.當事務需要申請行的共用鎖S時，必須先對表申請共用意向IS鎖或更強的鎖

2.當事務需要申請行的排他鎖X時，必須先對表申請排他意向IX鎖

表級鎖相容性矩陣如下表：

將獲取的鎖（下）/已獲取的鎖（右） X IX S IS
X 衝突 衝突 衝突 衝突
IX 衝突 相容 衝突 相容
S 衝突 衝突 相容 相容
IS 衝突 相容 相容 相容

如果請求鎖的事務與現有鎖相容，則會將鎖授予該事務，但如果與現有鎖衝突，則不會授予該事務。事務等待，直到衝突的現有鎖被釋放。

意向鎖的目的就是為了說明事務正在對錶的一行進行鎖定，或將要對錶的一行進行鎖定。在意向鎖概念中，除了對全表加鎖會導致意向鎖阻塞外，其餘情況意向鎖均不會阻塞任何請求！

2.1.4 插入意向鎖

插入意向鎖是一種特殊的意向鎖，同時也是一種特殊的“Gap Lock”，是在Insert操作之前設置的Gap Lock。

如果此時有多個事務執行insert操作，恰好需要插入的位置都在同一個Gap Lock中，但是並不是在Gap Lock的同一個位置時，此時的插入意向鎖彼此之間不會阻塞。

2.2 過程分析

回到本文的問題上來，本文中有兩個事務執行同樣的動作，分別為先執行select ... for update獲取排他鎖，其次判斷若為空，則執行insert動作，否則執行update動作。偽代碼描述如下：

start transaction
// 1、查詢數據
data = select for update(tenantId, storeId, skuId);
if (data == null) {
    // 插入數據
    insert(tenantId, storeId, skuId);
} else {
    // 更新數據
    update(tenantId, storeId, skuId);
}
end transaction

現在對這兩個事務所執行的動作進行逐一分析，如下表所示：

時間點 事務A 事務B 潛在動作
1 開始事務 開始事務
2 執行select ... for update操作 事務A申請到IX 事務A申請到X，Gap Lock
3 執行select ... for update操作 事務B申請到IX，與事務A的IX不衝突。 事務B申請到Gap Lock，Gap Lock可共存。
4 執行insert操作 事務A先申請插入意向鎖IX，與事務B的Gap Lock衝突，等待事務B的Gap Lock釋放。
5 執行insert操作 事務B先申請插入意向鎖IX，與事務A的Gap Lock衝突，等待事務A的Gap Lock釋放。
6 死鎖檢測器檢測到死鎖

詳細分析：

•時間點1，事務A與事務B開始執行事務

•時間點2，事務A執行select ... for update操作，執行該操作時首先需要申請意向排他鎖IX作用於表上，接著申請到了排他鎖X作用於區間，因為查詢的值不存在，故Next key Lock退化為Gap Lock。

•時間點3，事務B執行select ... for update操作，首先申請意向排他鎖IX，根據2.1.3節表級鎖相容矩陣可以看到，意向鎖之間是相互相容的，故申請IX成功。由於查詢值不存在，故可以申請X的Gap Lock，而Gap Lock之間是可以共存的，不論是共用還是排他。這一點可以參考Innodb關於Gap Lock的描述，關鍵描述本文粘貼至此：

Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

•時間點4，事務A執行insert操作前，首先會申請插入意向鎖，但此時事務B已經擁有了插入區間的排他鎖，根據2.1.3節表級鎖相容矩陣可知，在已有X鎖情況下，再次申請IX鎖是衝突的，需要等待事務B對X Gap Lock釋放。

•時間點5，事務B執行insert操作前，也會首先申請插入意向鎖，此時事務A也對插入區間擁有X Gap Lock，因此需要等待事務A對X鎖進行釋放。

•時間點6，事務A與事務B均在等待對方釋放X鎖，後被MySQL的死鎖檢測器檢測到後，報Dead Lock錯誤。

思考：假如select ... for update 查詢的數據存在時，會是什麼樣的過程呢？過程如下表：

時間點 事務A 事務B 潛在動作
1 開始事務 開始事務
2 執行select ... for update操作 事務A申請到IX 事務A申請到X行鎖，因數據存在故鎖退化為Record Lock。
3 執行select ... for update操作 事務B申請到IX，與事務A的IX不衝突。 事務B想申請目標行的Record Lock，此時需要等待事務A釋放該鎖資源。
4 執行update操作 事務A先申請插入意向鎖IX，此時事務B僅僅擁有IX鎖資源，相容，不衝突。然後事務A擁有X的Record Lock，故執行更新。
5 commit 事務A提交，釋放IX與X鎖資源。
6 執行select ... for update操作 事務B事務B此時獲取到X Record Lock。
7 執行update操作 事務B擁有X Record Lock執行更新
8 commit 事務B釋放IX與X鎖資源

也就是當查詢數據存在時，不會出現死鎖問題。

三、解決方法

1、在事務開始之前，採用CAS+分散式鎖來控制併發寫請求。分散式鎖key可以設置為store_skuId_version

2、事務過程可以改寫為：

start transaction
// RR級別下，讀視圖
data = select from table(tenantId, storeId, skuId)
if (data == null) {
    // 可能出現寫併發
    insert
} else {
    data = select for update(tenantId, storeId, skuId)
    update
}
end transaction

雖然解決了插入數據不存在時會出現的死鎖問題，但是可能存在併發寫的問題，第一個事務獲得鎖會首先插入成功，第二個事務等待第一個事務提交後，插入數據，因為數據存在了所以報錯回滾。

3、調整事務隔離級別為RC，在RC下沒有next key lock（註意，此處並不准確，RC會有少部分情況加Next key lock），故此時僅僅會有record lock，所以事務2進行select for update時需要等待事務1提交。

參考文獻

[1] Innodb鎖官方文檔：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

[2] https://blog.csdn.net/qq_43684538/article/details/131450395

[3] https://www.jianshu.com/p/027afd6345d5

[4] https://www.cnblogs.com/micrari/p/8029710.html

若有錯誤，還望批評指正

作者：京東零售  劉哲

來源：京東雲開發者社區 轉載請註明來源


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

-Advertisement-
Play Games
更多相關文章
  • Abp vNext 模塊載入機制
    文章目錄 生命周期 PreConfigureServices 添加依賴註入或者其它配置之前 ConfigureServices 添加依賴註入或者其它配置 PostConfigureServices 添加依賴註入或者其它配置之後 OnPreApplicationInitialization 初始化所有 ...
  • 記一次 .NET某新能源MES系統 非托管泄露
    一：背景 1. 講故事 前些天有位朋友找到我，說他們的程式有記憶體泄露，跟著我的錯題集也沒找出是什麼原因，剛好手頭上有一個 7G+ 的 dump，讓我幫忙看下是怎麼回事，既然找到我了那就給他看看吧，不過他的微信頭像有點像 二道販子，不管到我這裡是不是 三道，該分析的還得要分析呀。😄😄😄 二：Wi ...
  • 理解並掌握C#的Channel：從使用案例到源碼解讀（一）
    引言 在C#的併發編程中，Channel是一種非常強大的數據結構，用於在生產者和消費者之間進行通信。本文將首先通過一個實際的使用案例，介紹如何在C#中使用Channel，然後深入到Channel的源碼中，解析其內部的實現機制。 使用案例一：文件遍歷和過濾 在我們的使用案例中，我們需要遍歷一個文件夾及 ...
  • 掌握嵌入式Linux編程3引導程式
    一、vi編譯器介紹 Vi編輯器是所有Unix及Linux系統下標準的編輯器，類似於windows系統下的notepad（記事本）編輯器，由於在Unix及Linux系統的任何版本，Vi編輯器是完全相同的，因 此可以在其他任何介紹vi的地方都能進一步瞭解它，Vi也是Linux中最基本的文本編輯器，學會它 ...
  • Linux CentOS 7.x離線安裝PostgreSQL操作手冊
    一、準備環節 rpm -qa | grep postgres 檢查PostgreSQL 是否已經安裝 rpm -qal | grep postgres 檢查PostgreSQL 安裝位置 postgresql-12.2.tar.gz 二、Pgsql資料庫安裝下載 下載地址: http://www.p ...
  • Linux-Stream記憶體帶寬及MLC記憶體延遲性能測試方法
    1、Stream記憶體帶寬測試 Stream是業界主流的記憶體帶寬測試程式，測試行為相對簡單可控。該程式對CPU的計算能力要求很小，對CPU記憶體帶寬壓力很大。隨著處理器核心數量的增大，而記憶體帶寬並沒有隨之成線性增長，因此記憶體帶寬對提升多核心的處理能力就越發重要。Stream具有良好的空間局部性，是對TL ...
  • Win11自動撥號的實現（教程）
    前言 不想看可以跳過前言部分，教程在下幾章。 ​ 最新搬到新校園，寢室的校園網可使用網線連接。雖然撥號的寬頻賬號和密碼已經自動記錄，但啟動電腦並登入電腦時仍需要手動進入設置並點擊自動登錄，就像鞋子里的小石子，雖然腳不會出血，但就是難受。於是開始網上搜索教程win11自動撥號。結合了兩篇文章實現了開機 ...
  • MySQL 高級(進階) SQL 語句
    MySQL 高級(進階) SQL 語句 use gy; create table location (Region char(20),Store_Name char(20)); insert into location values('East','Boston'); insert into loc ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類