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
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類