Mysql死鎖如何排查：insert on duplicate死鎖一次排查分析過程

来源:https://www.cnblogs.com/jay-huaxiao/archive/2019/09/04/11456921.html
-Advertisement-
Play Games

前言 遇到Mysql死鎖問題，我們應該怎麼排查分析呢？之前線上出現一個insert on duplicate死鎖問題，本文將基於這個死鎖問題，分享排查分析過程，希望對大家有幫助。 死鎖案發還原 表結構： CREATE TABLE ( int(11) NOT NULL AUTO_INCREMENT, ...


前言

遇到Mysql死鎖問題，我們應該怎麼排查分析呢？之前線上出現一個insert on duplicate死鎖問題，本文將基於這個死鎖問題，分享排查分析過程，希望對大家有幫助。

死鎖案發還原

表結構：
CREATE TABLE `song_rank` ( `id` int(11) NOT NULL AUTO_INCREMENT, `songId` int(11) NOT NULL, `weight` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `songId_idx` (`songId`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
隔離級別：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

資料庫版本：

+------------+
| @@version  |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)

關閉自動提交：

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

表中的數據：

mysql> select * from song_rank;
+----+--------+--------+
| id | songId | weight |
+----+--------+--------+
|  1 |     10 |     30 |
|  2 |     20 |     30 |
+----+--------+--------+
2 rows in set (0.01 sec)

死鎖案發原因：

併發環境下，執行insert into … on duplicate key update…導致死鎖

死鎖模擬復現：

事務一執行：

mysql> begin;    //第一步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(15,100) on duplicate key update  weight=weight+1;  //第二步
Query OK, 1 row affected (0.00 sec)

mysql> rollback;   //第七步
Query OK, 0 rows affected (0.00 sec)

事務二執行：

mysql> begin;   //第三步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(16,100) on duplicate key update  weight=weight+1;  //   第四步
Query OK, 1 row affected (40.83 sec)

事務三執行：

mysql> begin;  //第五步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(18,100) on duplicate key update  weight=weight+1; //第六步

事務一，事務二，事務三執行：

步驟 事務一 事務二 事務三
第一步 begin;
第二步 insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; (Query OK, 1 row affected (0.00 sec) )
第三步 begin;
第四步 insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; //被阻塞
第五步 begin;
第六步 insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1; //被阻塞
第七步 rollback;
結果 Query OK, 1 row affected (40.83 sec) ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死鎖浮出水面：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死鎖破案排查分析

遇到死鎖問題時，我們應該怎麼處理呢？分一下幾個步驟

1.查看死鎖日誌

當資料庫發生死鎖時，可以通過以下命令獲取死鎖日誌：

show engine innodb status;

上面例子insert on duplicate死鎖問題的日誌如下：

*** (1) TRANSACTION:
TRANSACTION 27540, ACTIVE 19 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 14896, query id 582 localhost ::1 root update
insert into  song_rank(songId,weight) values(18,100) on duplicate key update  weight=weight+1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27540 lock_mode X 
locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 27539, ACTIVE 41 sec inserting, thread declared inside InnoDB 1
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 22, OS thread handle 6976, query id 580 localhost ::1 root update
insert into  song_rank(songId,weight) values(16,100) on duplicate key update  weight=weight+1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X 
locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X 
locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

2.分析死鎖日誌

如何分析死鎖日誌呢? 分享一下我的思路

  • 死鎖日誌分事務1，事務2拆分
  • 找出發生死鎖的SQL
  • 找出事務持有什麼鎖，都在等待什麼鎖
  • SQL加鎖分析

事務1日誌分析

從日誌我們可以看到事務1正在執行的SQL為：

insert into  song_rank(songId,weight) values(18,100) on duplicate key update  weight=weight+1

該條語句正在等待索引songId_idx的插入意向排他鎖：

lock_mode X locks gap before rec insert intention waiting

事務2日誌分析

從日誌我們可以看到事務2正在執行的SQL為：

insert into  song_rank(songId,weight) values(16,100) on duplicate key update  weight=weight+1

該語句持有一個索引songId_idx的間隙鎖：

lock_mode X locks gap before rec

該條語句正在等待索引songId_idx的插入意向排他鎖：

lock_mode X locks gap before rec insert intention waiting

鎖相關概念補充（附）：

考慮到有些讀者可能對上面insert intention鎖等不太熟悉，所以這裡這裡補一小節鎖相關概念。
官方文檔

InnoDB 鎖類型思維導圖：

我們主要介紹一下相容性以及鎖模式類型的鎖

1.共用鎖與排他鎖：

InnoDB 實現了標準的行級鎖，包括兩種：共用鎖（簡稱 s 鎖）、排它鎖（簡稱 x 鎖）。

  • 共用鎖（S鎖）：允許持鎖事務讀取一行。
  • 排他鎖（X鎖）：允許持鎖事務更新或者刪除一行。

如果事務 T1 持有行 r 的 s 鎖，那麼另一個事務 T2 請求 r 的鎖時，會做如下處理：

  • T2 請求 s 鎖立即被允許，結果 T1 T2 都持有 r 行的 s 鎖
  • T2 請求 x 鎖不能被立即允許

如果 T1 持有 r 的 x 鎖，那麼 T2 請求 r 的 x、s 鎖都不能被立即允許，T2 必須等待T1釋放 x 鎖才可以，因為X鎖與任何的鎖都不相容。

2.意向鎖

  • 意向共用鎖( IS 鎖)：事務想要獲得一張表中某幾行的共用鎖
  • 意向排他鎖( IX 鎖)： 事務想要獲得一張表中某幾行的排他鎖

比如：事務1在表1上加了S鎖後，事務2想要更改某行記錄，需要添加IX鎖，由於不相容，所以需要等待S鎖釋放；如果事務1在表1上加了IS鎖，事務2添加的IX鎖與IS鎖相容，就可以操作，這就實現了更細粒度的加鎖。

InnoDB存儲引擎中鎖的相容性如下表：

3.記錄鎖（Record Locks）

  • 記錄鎖是最簡單的行鎖，僅僅鎖住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
  • 記錄鎖永遠都是加在索引上的，即使一個表沒有索引，InnoDB也會隱式的創建一個索引，並使用這個索引實施記錄鎖。
  • 會阻塞其他事務對其插入、更新、刪除

記錄鎖的事務數據（關鍵詞：lock_mode X locks rec but not gap），記錄如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

4.間隙鎖（Gap Locks）

  • 間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最後一個索引之後的間隙。
  • 使用間隙鎖鎖住的是一個區間，而不僅僅是這個區間中的每一條數據。
  • 間隙鎖只阻止其他事務插入到間隙中，他們不阻止其他事務在同一個間隙上獲得間隙鎖，所以 gap x lock 和 gap s lock 有相同的作用。

5.Next-Key Locks

  • Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。

6.插入意向鎖（Insert Intention）

  • 插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖，這個鎖釋放了一種插入方式的信號，亦即多個事務在相同的索引間隙插入時如果不是插入間隙中相同的位置就不需要互相等待。
  • 假設有索引值4、7，幾個不同的事務準備插入5、6，每個鎖都在獲得插入行的獨占鎖之前用插入意向鎖各自鎖住了4、7之間的間隙，但是不阻塞對方因為插入行不衝突。

事務數據類似於下麵：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

SQL加鎖分析：

通過分析死鎖日誌，我們可以找到發生死鎖的SQL，以及相關等待的鎖，我們再對對應的SQL進行加鎖分析，其實問題就迎刃而解了。

OK，我們回到對應的SQL，insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1 執行過程到底加了什麼鎖呢？加鎖機制官方文檔

insert加鎖策略：

insert語句會對插入的這條記錄加排他記錄鎖，在加記錄鎖之前還會加一種 GAP 鎖，叫做插入意向(insert intention)鎖，如果出現唯一鍵衝突，還會加一個共用記錄(S)鎖。

（SQL加鎖分析非常重要，在這裡給大家推薦一篇文章，講的非常好，解決死鎖之路 - 常見 SQL 語句的加鎖分析)

insert on duplicate key加鎖驗證

為了驗證一下insert on duplicate key加鎖情況，我們拿上面demo的事務1和2在走一下流程。
事務1：

mysql> begin;    //第一步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(15,100) on duplicate key
update  weight=weight+1;  //第二步
Query OK, 1 row affected (0.00 sec)

事務2（另開視窗）：

mysql> begin;   //第三步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  song_rank(songId,weight) values(16,100) on duplicate key 
update  weight=weight+1;  //   第四步

使用show engine innodb status查看當前鎖請求信息，如圖：

有圖可得：

事務2持有：IX鎖（表鎖），gap x鎖，insert intention lock（在等待事務1的gap鎖）

所以，insert on duplicate 執行過程會上這三把鎖。

死鎖原因分析

回歸到本文開頭介紹的死鎖案發模擬現場(事務1,2,3)以及死鎖日誌現場,

案發後事務1的鎖：

案發後事務2的鎖：

案發複原路線：

1.首先，執行事務1執行:
begin;
insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1;
會獲得 gap鎖（10,20），insert intention lock（插入意向鎖）

2.接著，事務2執行:
begin;
insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1;
會獲得 gap鎖（10,20），同時等待事務1的insert intention lock（插入意向鎖）。

3.再然後，事務3執行：
begin;
insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1;
會獲得 gap鎖（10,20），同時等待事務1的insert intention lock（插入意向鎖）。

4.最後，事務1回滾（rollback），釋放插入意向鎖，導致事務2,3同時持有gap鎖，等待insert intention鎖，死鎖形成！

鎖模式相容矩陣（橫向是已持有鎖，縱向是正在請求的鎖）：

這是MySql5.7的一個bug

如何避免該insert on duplicate死鎖問題

1.把insert on duplicate改為insert

   try{
       insert();
   }catch(DuplicateKeyException e){
       update();
   }

因為insert不會加gap鎖，所以可以避免該問題。

2.更改MySql版本

既然這是MySql5.7的一個bug，那麼可以考慮更改Mysql版本。

3.儘量減少使用unique index。

gap鎖跟索引有關，並且unique key 和foreign key會引起額外的index檢查，需要更大的開銷，所以我們儘量減少使用不必要的索引。

本文總結（重要）

本文介紹了MySql5.7死鎖的一個bug。我們應該怎樣去排查死鎖問題呢？

  • 1.show engine innodb status;查看死鎖日誌
  • 2.找出死鎖SQL
  • 3.SQL加鎖分析
  • 4.分析死鎖日誌（持有什麼鎖，等待什麼鎖）
  • 5.熟悉鎖模式相容矩陣，InnoDB存儲引擎中鎖的相容性矩陣。

參考與感謝

個人公眾號

  • 如果你是個愛學習的好孩子，可以關註我公眾號，一起學習討論。
  • 如果你覺得本文有哪些不正確的地方，可以評論，也可以關註我公眾號，私聊我，大家一起學習進步哈。

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

-Advertisement-
Play Games
更多相關文章
  • ORA-12514錯誤分析
    ORA-12514 TNS 監聽程式當前無法識別連接描述符中請求服務 的解決方法 ORA-12514 TNS 監聽程式當前無法識別連接描述符中請求服務 有同事遇到這個問題，現總結一下，原因如下： 你oracle安裝成功後，一直未停止資料庫（即資料庫是啟動的），客戶端配置成功後，應該一直不會有什麼問題 ...
  • MySQL中SQL語句常見優化策略
    1.避免全表掃描 對查詢進行優化，應儘量避免全表掃描，首先應考慮在where 及order by 涉及的列上建立索引。 2.避免判斷null 值 應儘量避免在where 子句中對欄位進行null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如： select id from t where n ...
  • SQL左右連接中的on and和on where的區別
    在使用left join時，on and和on where條件的區別如下： 1、on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。 2、where條件是在臨時表生成好後，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了， ...
  • MYSQL的操作命令
    一、御前 1 win+R DOS 輸入 net start mtsql 和 net stop mysql 啟動和停止Mysql 服務，也可通過電腦——管理——服務和應用程式——服務——MYSQL——右擊 啟動mysql服務出現服務名無效的原因及解決方法【失敗】 問題原因：mysql服務沒有安裝。 ...
  • 最簡單流處理引擎——Kafka Streams簡介
    Kafka在0.10.0.0版本以前的定位是分散式，分區化的，帶備份機制的日誌提交服務。而kafka在這之前也沒有提供數據處理的顧服務。大家的流處理計算主要是還是依賴於Storm，Spark Streaming，Flink等流式處理框架。 Storm，Spark Streaming，Flink流處理 ...
  • Sql Server中變的定義以及賦值的應用
    --申明變數declare @ad_begin datetimedeclare @fydl varchar(50)declare @userid varchar(50)declare @jdrbm varchar(50) --變數賦值 set @ad_begin='2019-09-04'set @f ...
  • 將mysql從5.5.25升級到8.0.12
    將mysql從5.5.25升級到8.0.12過程中遇到幾個問題，記錄如下： 將資料庫安裝好後，導入原來的數據，啟動tomcat，報錯unable to load authentication caching_sha2_plugin，解決辦法如下： ALTER USER 'root'@'localho ...
  • SQLServer常用快捷鍵彙總
    菜單激活鍵盤快捷鍵、文檔視窗和瀏覽器鍵盤快捷鍵、代碼編輯器鍵盤快捷鍵、T-SQL 調試器鍵盤快捷捷 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類