故障解析丨一次死鎖問題的解決

来源:https://www.cnblogs.com/greatsql/p/18079727
-Advertisement-
Play Games

背景 業務端遇到報錯為"Deadlock found when trying to get lock; try restarting transaction"則表明有死鎖發生 名稱 配置 資料庫版本 GreatSQL 8.0.26 隔離級別 Read-Commited innodb status 日 ...


背景

業務端遇到報錯為"Deadlock found when trying to get lock; try restarting transaction"則表明有死鎖發生

名稱 配置
資料庫版本 GreatSQL 8.0.26
隔離級別 Read-Commited

innodb status 日誌

greatsql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2024-01-28 16:55:38 140737023727360 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 41 srv_active, 0 srv_shutdown, 17830 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
-------------
RW-LATCH INFO
-------------
Total number of rw-locks 132361
OS WAIT ARRAY INFO: reservation count 11180
OS WAIT ARRAY INFO: signal count 11177
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-28 16:53:40 140735053358848
*** (1) TRANSACTION:
TRANSACTION 37616, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1192, 1 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140737023432448, query id 652 127.0.0.1 root update
insert into info values (50,11)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 5 n bits 80 index uk_name of table `apple`.`info` trx id 37616 lock mode S waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000028; asc    (;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 5 n bits 80 index uk_name of table `apple`.`info` trx id 37616 lock mode S waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000028; asc    (;;


*** (2) TRANSACTION:
TRANSACTION 37615, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1192, 2 row lock(s), undo log entries 2
MySQL thread id 15, OS thread handle 140737024022272, query id 653 127.0.0.1 root update
insert into info values (60,8)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 5 n bits 80 index uk_name of table `apple`.`info` trx id 37615 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000028; asc    (;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 5 n bits 80 index uk_name of table `apple`.`info` trx id 37615 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000028; asc    (;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------

查看表結構

greatsql> show create table info \G
*************************** 1. row ***************************
       Table: info
Create Table: CREATE TABLE `info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

梳理 innodb status 日誌

  • 整理如下：
事務 T1 T2
操作 insert into info values (50,11) insert into info values (60,8)
關聯的對象 表apple.info的唯一索引 uk_name 表apple.info的唯一索引 uk_name
持有的鎖 lock mode S waitingheap no 7 11，40（十六進位為8，28） lock_mode X locks rec but not gapheap no 7 11，40（十六進位為8，28）
等待的鎖 lock mode S waitingheap no 7 11，40（十六進位為8，28） lock_mode X locks gap before rec insert intention waitingheap no 7 11，40（十六進位為8，28）

  • 首先事務T2獲取到了uk_name中記錄11的 lock x,rec not not gap 鎖

  • 事務T1嘗試獲取uk_name中記錄11的lock s, next key lock，由於T2持有了記錄的獨占鎖，因此被T1堵塞

  • 事務T2嘗試獲取uk_name中記錄11的lock x, gap before rec,insert intention，但被堵塞

獲取業務歷史SQL語句

通過系統表方式

通過performance_schema.threads、performance_schema.events_statements_history、performance_schema.events_statements_history_long等系統表獲取歷史SQL

  • 根據GreatSQL thread id獲得線程id
greatsql> select PROCESSLIST_ID,THREAD_ID,THREAD_OS_ID from  performance_schema.threads where processlist_id in (15,16);
+----------------+-----------+--------------+
| PROCESSLIST_ID | THREAD_ID | THREAD_OS_ID |
+----------------+-----------+--------------+
|             15 |        61 |         5714 |
|             16 |        62 |         5719 |
+----------------+-----------+--------------+
2 rows in set (0.00 sec)
  • 根據線程id獲得線程歷史SQL
greatsql> select THREAD_ID,EVENT_ID,CURRENT_SCHEMA,SQL_TEXT,MESSAGE_TEXT,EVENT_NAME,SOURCE from performance_schema.events_statements_history where thread_id in (61,62) order by THREAD_ID,EVENT_ID;
+-----------+----------+----------------+---------------------------------+--------------------------------------------------------------------+--------------------------+---------------------------------+
| THREAD_ID | EVENT_ID | CURRENT_SCHEMA | SQL_TEXT                        | MESSAGE_TEXT                                                       | EVENT_NAME               | SOURCE                          |
+-----------+----------+----------------+---------------------------------+--------------------------------------------------------------------+--------------------------+---------------------------------+
|        61 |     3762 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3807 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3852 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3897 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3942 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3987 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     4032 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     4077 | apple          | begin                           | NULL                                                               | statement/sql/begin      | init_net_server_extension.cc:94 |
|        61 |     4100 | apple          | insert into info values (40,11) | NULL                                                               | statement/sql/insert     | init_net_server_extension.cc:94 |
|        61 |     4569 | apple          | insert into info values (60,8)  | NULL                                                               | statement/sql/insert     | init_net_server_extension.cc:94 |
|        62 |     3215 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3260 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3305 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3350 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3395 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3440 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3485 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3530 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3575 | apple          | begin                           | NULL                                                               | statement/sql/begin      | init_net_server_extension.cc:94 |
|        62 |     3598 | apple          | insert into info values (50,11) | Deadlock found when trying to get lock; try restarting transaction | statement/sql/insert     | init_net_server_extension.cc:94 |
+-----------+----------+----------------+---------------------------------+--------------------------------------------------------------------+--------------------------+---------------------------------+
20 rows in set (0.00 sec)

  • 觀察show engine innodb status中的GreatSQL thread id 16和GreatSQL thread id 15

  • 通過performance_schema.threads獲取THREAD_ID

  • 通過performance_schema.events_statements_history獲取THREAD_ID執行的歷史SQL以及執行時間

最終可復現出如下業務SQL：

事務 T1 T2
語句 begin; begin;
語句 insert into info values (40,11);
語句 insert into info values (50,11);
語句 insert into info values (60,8);

通過解析binlog

$ mysqlbinlog -vv --base64-output=decode-rows bin.000030

SET @@SESSION.GTID_NEXT= 'e319a624-b2ce-11ee-9aac-00163e62ca8a:8696'/*!*/;
# at 10314
#240128 16:52:35 server id 1024  end_log_pos 10390 CRC32 0x59edb313         Query        thread_id=18        exec_time=0        error_code=0
SET TIMESTAMP=1706431955/*!*/;
BEGIN
/*!*/;
# at 10390
#240128 16:52:35 server id 1024  end_log_pos 10442 CRC32 0xc03dea61         Table_map: `apple`.`info` mapped to number 370
# at 10442
#240128 16:52:35 server id 1024  end_log_pos 10486 CRC32 0x670e0c66         Write_rows: table id 370 flags: STMT_END_F
### INSERT INTO `apple`.`info`
### SET
###   @1=30 /* INT meta=0 nullable=0 is_null=0 */
###   @2=30 /* INT meta=0 nullable=0 is_null=0 */
# at 10486
#240128 16:52:35 server id 1024  end_log_pos 10517 CRC32 0xab4e0d89         Xid = 598
COMMIT/*!*/;
# at 10517
#240128 19:22:12 server id 1024  end_log_pos 10596 CRC32 0x4f4cf08e         GTID        last_committed=30        sequence_number=36        rbr_only=yes        original_committed_timestamp=1706440932450590        immediate_commit_timestamp=1706440932450590 transaction_length=378
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1706440932450590 (2024-01-28 19:22:12.450590 CST)
# immediate_commit_timestamp=1706440932450590 (2024-01-28 19:22:12.450590 CST)
/*!80001 SET @@session.original_commit_timestamp=1706440932450590*//*!*/;
/*!80014 SET @@session.original_server_version=80026*//*!*/;
/*!80014 SET @@session.immediate_server_version=80026*//*!*/;
SET @@SESSION.GTID_NEXT= 'e319a624-b2ce-11ee-9aac-00163e62ca8a:8697'/*!*/;
# at 10596
#240128 16:53:16 server id 1024  end_log_pos 10672 CRC32 0xf222c003         Query        thread_id=15        exec_time=0        error_code=0
SET TIMESTAMP=1706431996/*!*/;
BEGIN
/*!*/;
# at 10672
#240128 16:53:16 server id 1024  end_log_pos 10724 CRC32 0x20cb8c86         Table_map: `apple`.`info` mapped to number 370
# at 10724
#240128 16:53:16 server id 1024  end_log_pos 10768 CRC32 0xd8f53958         Write_rows: table id 370 flags: STMT_END_F
### INSERT INTO `apple`.`info`
### SET
###   @1=40 /* INT meta=0 nullable=0 is_null=0 */
###   @2=11 /* INT meta=0 nullable=0 is_null=0 */
# at 10768
#240128 16:53:40 server id 1024  end_log_pos 10820 CRC32 0x23f22580         Table_map: `apple`.`info` mapped to number 370
# at 10820
#240128 16:53:40 server id 1024  end_log_pos 10864 CRC32 0x182ecdef         Write_rows: table id 370 flags: STMT_END_F
### INSERT INTO `apple`.`info`
### SET
###   @1=60 /* INT meta=0 nullable=0 is_null=0 */
###   @2=8 /* INT meta=0 nullable=0 is_null=0 */
# at 10864
#240128 19:22:12 server id 1024  end_log_pos 10895 CRC32 0x57fd1d3c         Xid = 650
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

根據binlog中部分SET @@SESSION.GTID_NEXT= 'e319a624-b2ce-11ee-9aac-00163e62ca8a:8697'該GTID的事務信息，可恢復T2，但T1執行的語句由於被回滾了，則不會記錄到binlog，可開啟general log日誌獲取排查

事務 T1 T2
語句 begin; begin;
語句 insert into info values (40,11);
語句 insert into info values (50,11);
語句 insert into info values (60,8);

分析死鎖

  • T1、T2開啟了一個事務

  • 隨後T2執行了插入(40,11)的insert語句：insert into info values (40,11)

  • T1執行了插入(50,11)的insert語句：insert into info values (50,11) 進行唯一性衝突檢查，嘗試獲取LOCK_S

  • 然後T1所在的連接會將T2中的隱式鎖轉換為顯示鎖，此時T2將獲取Lock X, Rec_not_gap。由於T2的Lock X, Rec_not_gap與T1的LOCK S不相容，因此T1被堵塞

  • 隨後，T2又執行了(60,8)的insert語句：insert into info values (60,8) 由於其插入的唯一索引值是8，因此不存在主鍵衝突，直接執行樂觀插入操作。執行樂觀插入時，需要檢查其它事務是否堵塞insert操作。其核心是獲取待插入記錄的下一個值（這裡剛好是10），並獲取該記錄上的所有鎖，與需要添加的鎖判斷是否存在衝突。

  • T1持有了記錄11的LOCK_S鎖與T2的LOCK_X、LOCK_INSERT_INTENTION不相容，因此T2被T1堵塞

  • 死鎖形成。

解決

• 適當的減少Unique索引

• 避免插入重覆的值（唯一索引所在列）

Enjoy GreatSQL

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

-Advertisement-
Play Games
更多相關文章
  • FTP的安裝和使用
    實驗環境： windows server虛擬機一臺 網路適配器：vmnet1 ip：192.168.1.220 一.安裝FTP服務 打開伺服器管理器 點擊添加角色，勾IIS，點擊下一步直到對話框消失 勾選FTP 從開始菜單找到管理工具 打開IIS 可以看見ftp服務的相關信息，說明安裝成功 二：新建 ...
  • 使用zabbix實現郵箱/釘釘告警
    目錄一、zabbix部署1、安裝zabbix服務端2、準備資料庫（mariadb簡單點）3、修改服務的配置二、登錄zabbix三、客戶端配置1、安裝軟體包2、修改配置文件3、啟動客戶端服務四、報錯解決五、添加監控主機1、安裝軟體包2、修改配置文件3、啟動客戶端服務4、回到zabbix添加主機5、添加 ...
  • 正點原子AtomPi-CA1 RK3568的Linux CH340串口驅動安裝
    想使用串口，找了半天也沒找到它所給的pin的使用示例，淘寶客服說只是硬體相容樹莓派。 想來想去，還是直接接個USB串口最方便。 但是插上不識別，說明沒有驅動。 直接在板子上編譯沒有header依賴，後續下載華碩對應的arm依賴能編譯但是驅動不能正常使用。 就在所給虛擬機上交叉編譯。 先按照手冊配置好 ...
  • innodb_log_file_size 和 innodb_buffer_pool_size
    innodb_log_file_size 和 innodb_buffer_pool_size 是 MySQL 資料庫中 InnoDB 存儲引擎的兩個重要配置參數。它們對資料庫的性能和可靠性有著顯著的影響。 1. innodb_log_file_size innodb_log_file_size 參數 ...
  • 記錄惡意SQL註入引發的RDS只讀資料庫CPU飈100%
    前言: 在廣州這座城市下著小雨的晚上，我正在廚房洗著碗，突然手機有來電，脫下手套，一看是來自阿裡雲的告警電話。打開飛書查看告警內容，發現某個業務的RDS只讀實例CPU飈到100%，下意識覺得是不是有慢查詢導致，想著不會有啥問題，上去kill慢查就好了，結果發現是大問題....一、發現問題 2024年 ...
  • [20240313]使用tpt ashtop.sql腳本的困惑.txt
    我司使用Apache DolphinScheduler作為調度框架很久了，感興趣的小伙伴可以看看這些乾貨文章： 因為之前監控到會出現重覆的調度的問題，所以此文記錄排查重覆調度問題的全過程，希望對社區其他的小伙伴能夠起到拋磚引玉的作用！ 註：本文使用的DolphinScheduler 3.1.1的版本 ...
  • 詳解GaussDB(DWS)中3個防過載檢查項
    本文分享自華為雲社區《【防過載檢查項】》，作者： 譡里個檔。 1. GUC參數檢查 目的：針對不同版本建議設定不同的參數值，當前先檢查出來，後續diagnosis會給出建議值 SELECT split_part((substring(version() from '\((.*)\)')), ' ', ...
  • 特新介紹 | MySQL生態現有計算下推方案彙總
    作者：盧文雙 資深資料庫內核研發 本文首發於 2024-03-06 20:52:24 https://dbkernel.com 前言 計算下推是資料庫優化器優化查詢性能的一種常見手段，早期的資料庫系統提及的計算下推一般是指謂詞下推，其理論源自關係代數理論。2000 年以後，隨著 Oracle RAC ...
一周排行
    -Advertisement-
    Play Games
  • UOS 開啟 VisualStudio 遠程調試 .NET 應用之旅
    前言 在我們開發過程中基本上不可或缺的用到一些敏感機密數據，比如SQL伺服器的連接串或者是OAuth2的Secret等，這些敏感數據在代碼中是不太安全的，我們不應該在源代碼中存儲密碼和其他的敏感數據，一種推薦的方式是通過Asp.Net Core的機密管理器。 機密管理器 在 ASP.NET Core ...
  • C# - 能否讓 SortedSet.RemoveWhere 內傳入的委托非同步執行
    新改進提供的Taurus Rpc 功能，可以簡化微服務間的調用，同時可以不用再手動輸出模塊名稱，或調用路徑，包括負載均衡，這一切，由框架實現並提供了。新的Taurus Rpc 功能，將使得服務間的調用，更加輕鬆、簡約、高效。 ...
  • 順序棧的介面程式
    順序棧的介面程式 目錄順序棧的介面程式頭文件創建順序棧入棧出棧利用棧將10進位轉16進位數驗證 頭文件 #include <stdio.h> #include <stdbool.h> #include <stdlib.h> 創建順序棧 // 指的是順序棧中的元素的數據類型，用戶可以根據需要進行修改 ...
  • mORMot2 獲取數據集1
    前言 整理這個官方翻譯的系列，原因是網上大部分的 tomcat 版本比較舊，此版本為 v11 最新的版本。 開源項目 從零手寫實現 tomcat minicat 別稱【嗅虎】心有猛虎，輕嗅薔薇。 系列文章 web server apache tomcat11-01-官方文檔入門介紹 web serv ...
  • 萬字長文總結與剖析C語言關鍵字 -- <<C語言深度解剖>>
    C總結與剖析:關鍵字篇 -- <<C語言深度解剖>> 目錄C總結與剖析:關鍵字篇 -- <<C語言深度解剖>>程式的本質:二進位文件變數1.變數:記憶體上的某個位置開闢的空間2.變數的初始化3.為什麼要有變數4.局部變數與全局變數5.變數的大小由類型決定6.任何一個變數,記憶體賦值都是從低地址開始往高地 ...
  • 【Flink入門修煉】2-3 Flink Checkpoint 原理機制
    如果讓你來做一個有狀態流式應用的故障恢復，你會如何來做呢？ 單機和多機會遇到什麼不同的問題？ Flink Checkpoint 是做什麼用的？原理是什麼？ ...
  • C++ 多級繼承與多重繼承：代碼組織與靈活性的平衡
    C++ 多級繼承 多級繼承是一種面向對象編程（OOP）特性，允許一個類從多個基類繼承屬性和方法。它使代碼更易於組織和維護，並促進代碼重用。 多級繼承的語法 在 C++ 中，使用 : 符號來指定繼承關係。多級繼承的語法如下： class DerivedClass : public BaseClass1 ...
  • 圖文結合手把手教你創建SpringCloud項目
    前言 什麼是SpringCloud？ Spring Cloud 是一系列框架的有序集合，它利用 Spring Boot 的開發便利性簡化了分散式系統的開發，比如服務註冊、服務發現、網關、路由、鏈路追蹤等。Spring Cloud 並不是重覆造輪子，而是將市面上開發得比較好的模塊集成進去，進行封裝，從 ...
  • 類模板的常見用法
    class_template 類模板和函數模板的定義和使用類似，我們已經進行了介紹。有時，有兩個或多個類，其功能是相同的，僅僅是數據類型不同。類模板用於實現類所需數據的類型參數化 template<class NameType, class AgeType> class Person { publi ...
  • system-v IPC共用記憶體通信
    目錄system v IPC簡介共用記憶體需要用到的函數介面shmget函數--獲取對象IDshmat函數--獲得映射空間shmctl函數--釋放資源共用記憶體實現思路註意 system v IPC簡介 消息隊列、共用記憶體和信號量統稱為system v IPC(進程間通信機制)，V是羅馬數字5，是UNI ...
所有分類