關(guān)于MySQL死鎖問題的深入分析
前言
如果我們的業(yè)務處在一個非常初級的階段，并發(fā)程度比較低，那么我們可以幾年都遇不到一次死鎖問題的發(fā)生，反之，我們業(yè)務的并發(fā)程度非常高，那么時不時爆出的死鎖問題肯定讓我們非常撓頭。不過在死鎖問題發(fā)生時，很多沒有經(jīng)驗的同學的第一反應就是成為一只鴕鳥：這玩意兒很高深，我也看不懂，聽天由命吧，又不是一直發(fā)生。其實如果大家認真研讀了我們之前寫的3篇關(guān)于MySQL中語句加鎖分析的文章，加上本篇關(guān)于死鎖日志的分析，那么解決死鎖問題應該也不是那么摸不著頭腦的事情了。
準備工作
為了故事的順利發(fā)展，我們需要建一個表：
CREATE TABLE hero ( id INT, name VARCHAR(100), country varchar(100), PRIMARY KEY (id), KEY idx_name (name) ) Engine=InnoDB CHARSET=utf8;
我們?yōu)閔ero表的id列創(chuàng)建了聚簇索引，為name列創(chuàng)建了一個二級索引。這個hero表主要是為了存儲三國時的一些英雄，我們向表中插入一些記錄：
INSERT INTO hero VALUES (1, 'l劉備', '蜀'), (3, 'z諸葛亮', '蜀'), (8, 'c曹操', '魏'), (15, 'x荀彧', '魏'), (20, 's孫權(quán)', '吳');
現(xiàn)在表中的數(shù)據(jù)就是這樣的：
mysql> SELECT * FROM hero; +----+------------+---------+ | id | name | country | +----+------------+---------+ | 1 | l劉備 | 蜀 | | 3 | z諸葛亮 | 蜀 | | 8 | c曹操 | 魏 | | 15 | x荀彧 | 魏 | | 20 | s孫權(quán) | 吳 | +----+------------+---------+ 5 rows in set (0.00 sec)
準備工作就做完了。
創(chuàng)建死鎖情景
我們先創(chuàng)建一個發(fā)生死鎖的情景，在Session A和Session B中分別執(zhí)行兩個事務，具體情況如下：
我們分析一下：
- 從第③步中可以看出，Session A中的事務先對hero表聚簇索引的id值為1的記錄加了一個X型正經(jīng)記錄鎖。
- 從第④步中可以看出，Session B中的事務對hero表聚簇索引的id值為3的記錄加了一個X型正經(jīng)記錄鎖。
- 從第⑤步中可以看出，Session A中的事務接著想對hero表聚簇索引的id值為3的記錄也加了一個X型正經(jīng)記錄鎖，但是與第④步中Session B中的事務加的鎖沖突，所以Session A進入阻塞狀態(tài)，等待獲取鎖。
- 從第⑥步中可以看出，Session B中的事務想對hero表聚簇索引的id值為1的記錄加了一個X型正經(jīng)記錄鎖，但是與第③步中Session A中的事務加的鎖沖突，而此時Session A和Session B中的事務循環(huán)等待對方持有的鎖，死鎖發(fā)生，被MySQL服務器的死鎖檢測機制檢測到了，所以選擇了一個事務進行回滾，并向客戶端發(fā)送一條消息：
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
以上是我們從語句加了什么鎖的角度出發(fā)來進行死鎖情況分析的，但是實際應用中我們可能壓根兒不知道到底是哪幾條語句產(chǎn)生了死鎖，我們需要根據(jù)MySQL在死鎖發(fā)生時產(chǎn)生的死鎖日志來逆向定位一下到底是什么語句產(chǎn)生了死鎖，從而再優(yōu)化我們的業(yè)務。
查看死鎖日志
設(shè)計InnoDB的大叔給我們提供了SHOW ENGINE INNODB STATUS命令來查看關(guān)于InnoDB存儲引擎的一些狀態(tài)信息，其中就包括了系統(tǒng)最近一次發(fā)生死鎖時的加鎖情況。在上邊例子中的死鎖發(fā)生時，我們運行一下這個命令：
mysql> SHOW ENGINE INNODB STATUS\G ...省略了好多其他信息 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-06-20 13:39:19 0x70000697e000 *** (1) TRANSACTION: TRANSACTION 30477, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s) MySQL thread id 2, OS thread handle 123145412648960, query id 46 localhost 127.0.0.1 root statistics select * from hero where id = 3 for update *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30477 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 000000007517; asc u ;; 2: len 7; hex 80000001d0011d; asc ;; 3: len 10; hex 7ae8afb8e8919be4baae; asc z ;; 4: len 3; hex e89c80; asc ;; *** (2) TRANSACTION: TRANSACTION 30478, ACTIVE 8 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1160, 2 row lock(s) MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics select * from hero where id = 1 for update *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 000000007517; asc u ;; 2: len 7; hex 80000001d0011d; asc ;; 3: len 10; hex 7ae8afb8e8919be4baae; asc z ;; 4: len 3; hex e89c80; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000007517; asc u ;; 2: len 7; hex 80000001d00110; asc ;; 3: len 7; hex 6ce58898e5a487; asc l ;; 4: len 3; hex e89c80; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ ...省略了好多其他信息
我們只關(guān)心最近發(fā)生的死鎖信息，所以就把以LATEST DETECTED DEADLOCK這一部分給單獨提出來分析一下。下邊我們就逐行看一下這個輸出的死鎖日志都是什么意思：
首先看第一句：
2019-06-20 13:39:19 0x70000697e000
這句話的意思就是死鎖發(fā)生的時間是：2019-06-20 13:39:19，后邊的一串十六進制0x70000697e000表示的操作系統(tǒng)為當前session分配的線程的線程id。
然后是關(guān)于死鎖發(fā)生時第一個事務的有關(guān)信息：
*** (1) TRANSACTION: # 為事務分配的id為30477，事務處于ACTIVE狀態(tài)已經(jīng)10秒了，事務現(xiàn)在正在做的操作就是：“starting index read” TRANSACTION 30477, ACTIVE 10 sec starting index read # 此事務使用了1個表，為1個表上了鎖（此處不是說為該表加了表鎖，只要不是進行一致性讀的表，都需要加鎖，具體怎么加鎖請看加鎖語句分析或者小冊章節(jié)） mysql tables in use 1, locked 1 # 此事務處于LOCK WAIT狀態(tài)，擁有3個鎖結(jié)構(gòu)（2個行鎖結(jié)構(gòu)，1個表級別X型意向鎖結(jié)構(gòu)，鎖結(jié)構(gòu)在小冊中重點介紹過），heap size是為了存儲鎖結(jié)構(gòu)而申請的內(nèi)存大?。ㄎ覀兛梢院雎裕?，其中有2個行鎖的結(jié)構(gòu) LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s) # 本事務所在線程的id是2（MySQL自己命名的線程id），該線程在操作系統(tǒng)級別的id就是那一長串數(shù)字，當前查詢的id為46（MySQL內(nèi)部使用，可以忽略），還有用戶名主機信息 MySQL thread id 2, OS thread handle 123145412648960, query id 46 localhost 127.0.0.1 root statistics # 本事務發(fā)生阻塞的語句 select * from hero where id = 3 for update # 本事務當前在等待獲取的鎖： *** (1) WAITING FOR THIS LOCK TO BE GRANTED: # 等待獲取的表空間ID為151，頁號為3，也就是表hero的PRIMAY索引中的某條記錄的鎖（n_bits是為了存儲本頁面的鎖信息而分配的一串內(nèi)存空間，小冊中有詳細介紹），該鎖的類型是X型正經(jīng)記錄鎖（rec but not gap） RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30477 lock_mode X locks rec but not gap waiting # 該記錄在頁面中的heap_no為2，具體的記錄信息如下： Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 # 這是主鍵值 0: len 4; hex 80000003; asc ;; # 這是trx_id隱藏列 1: len 6; hex 000000007517; asc u ;; # 這是roll_pointer隱藏列 2: len 7; hex 80000001d0011d; asc ;; # 這是name列 3: len 10; hex 7ae8afb8e8919be4baae; asc z ;; # 這是country列 4: len 3; hex e89c80; asc ;;
從這個信息中可以看出，Session A中的事務為2條記錄生成了鎖結(jié)構(gòu)，但是其中有一條記錄上的X型正經(jīng)記錄鎖（rec but not gap）并沒有獲取到，沒有獲取到鎖的這條記錄的位置是：表空間ID為151，頁號為3，heap_no為2。當然，設(shè)計InnoDB的大叔還貼心的給出了這條記錄的詳細情況，它的主鍵值為80000003，這其實是InnoDB內(nèi)部存儲使用的格式，其實就代表數(shù)字3，也就是該事務在等待獲取hero表聚簇索引主鍵值為3的那條記錄的X型正經(jīng)記錄鎖。
然后是關(guān)于死鎖發(fā)生時第二個事務的有關(guān)信息：
其中的大部分信息我們都已經(jīng)介紹過了，我們就挑重要的說：
*** (2) TRANSACTION: TRANSACTION 30478, ACTIVE 8 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1160, 2 row lock(s) MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics select * from hero where id = 1 for update # 表示該事務獲取到的鎖信息 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 # 主鍵值為3 0: len 4; hex 80000003; asc ;; 1: len 6; hex 000000007517; asc u ;; 2: len 7; hex 80000001d0011d; asc ;; 3: len 10; hex 7ae8afb8e8919be4baae; asc z ;; 4: len 3; hex e89c80; asc ;; # 表示該事務等待獲取的鎖信息 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 # 主鍵值為1 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000007517; asc u ;; 2: len 7; hex 80000001d00110; asc ;; 3: len 7; hex 6ce58898e5a487; asc l ;; 4: len 3; hex e89c80; asc ;;
從上邊的輸出可以看出來，Session B中的事務獲取了hero表聚簇索引主鍵值為3的記錄的X型正經(jīng)記錄鎖，等待獲取hero表聚簇索引主鍵值為1的記錄的X型正經(jīng)記錄鎖（隱含的意思就是這個hero表聚簇索引主鍵值為1的記錄的X型正經(jīng)記錄鎖已經(jīng)被SESSION A中的事務獲取到了）。
看最后一部分：
*** WE ROLL BACK TRANSACTION (2)
最終InnoDB存儲引擎決定回滾第2個事務，也就是Session B中的那個事務。
死鎖分析的思路
1、查看死鎖日志時，首先看一下發(fā)生死鎖的事務等待獲取鎖的語句都是啥。
本例中，發(fā)現(xiàn)SESSION A發(fā)生阻塞的語句是：
select * from hero where id = 3 for update
SESSION B發(fā)生阻塞的語句是：
select * from hero where id = 1 for update
然后切記：到自己的業(yè)務代碼中找出這兩條語句所在事務的其他語句。
2、找到發(fā)生死鎖的事務中所有的語句之后，對照著事務獲取到的鎖和正在等待的鎖的信息來分析死鎖發(fā)生過程。
從死鎖日志中可以看出來，SESSION A獲取了hero表聚簇索引id值為1的記錄的X型正經(jīng)記錄鎖（這其實是從SESSION B正在等待的鎖中獲取的），查看SESSION A中的語句，發(fā)現(xiàn)是下邊這個語句造成的（對照著語句加鎖分析那三篇文章）:
select * from hero where id = 1 for update;
還有SESSION B獲取了hero表聚簇索引id值為3的記錄的X型正經(jīng)記錄鎖，查看SESSION B中的語句，發(fā)現(xiàn)是下邊這個語句造成的（對照著語句加鎖分析那三篇文章）:
select * from hero where id = 3 for update;
然后看SESSION A正在等待hero表聚簇索引id值為3的記錄的X型正經(jīng)記錄鎖，這個是由于下邊這個語句造成的：
select * from hero where id = 3 for update;
然后看SESSION B正在等待hero表聚簇索引id值為1的記錄的X型正經(jīng)記錄鎖，這個是由于下邊這個語句造成的：
select * from hero where id = 1 for update;
然后整個死鎖形成過程就根據(jù)死鎖日志給還原出來了。
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對腳本之家的支持。
相關(guān)文章
本文將手把手帶您了解mysql時間溢出原理、實戰(zhàn)影響與全面解決方案,所有代碼均通過dblens for mysql數(shù)據(jù)庫工具驗證,推薦使用該工具進行可視化數(shù)據(jù)庫管理和開發(fā),感興趣的小伙伴跟著小編一起來看看吧2025-03-03
MySQL數(shù)據(jù)庫復合查詢與內(nèi)外連接圖文詳解
本文詳細介紹了在SQL中進行多表查詢的技術(shù),包括笛卡爾積、自連接、子查詢、內(nèi)連接和外連接等,文章還解釋了union和unionall的區(qū)別,以及如何在from子句中使用子查詢,這些技術(shù)對于處理復雜的數(shù)據(jù)庫查詢非常重要,可以有效地從不同表中提取和組合數(shù)據(jù),需要的朋友可以參考下2024-10-10
前段時間我的一個網(wǎng)站經(jīng)常打不開,通過檢查發(fā)現(xiàn)服務器cpu占用超過100%,通過top命令發(fā)現(xiàn)是mysql占用cpu特別高導致的,下面這篇文章主要給大家介紹了關(guān)于mysql占用CPU超過100%的詳細解決過程,需要的朋友可以參考下2023-10-10
這篇文章主要為大家詳細介紹了mysql 5.6 壓縮包版安裝方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-12-12
windows下mysql中binlog日志分析和數(shù)據(jù)恢復問題
這篇文章主要介紹了windows下mysql中binlog日志分析和數(shù)據(jù)恢復問題,本文通過示例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-06-06
這篇文章主要介紹了MySQL?中的主從同步實現(xiàn)操作，文章圍繞如何搭建主從同步詳細展開內(nèi)容，需要的小伙伴可以參考一下，希望對你有所幫助2022-03-03
這篇文章主要介紹了MySQL?根據(jù)表名稱生成完整select語句,本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-06-06
mysql正則表達式（regexp和rlike）的搜索功能實例分析
這篇文章主要介紹了mysql正則表達式（regexp和rlike）的搜索功能,結(jié)合實例形式分析了mysql正則表達式使用regexp和rlike的搜索功能相關(guān)原理與實現(xiàn)技巧,需要的朋友可以參考下2019-12-12
淺談MySQL存儲過程中declare和set定義變量的區(qū)別
下面小編就為大家?guī)硪黄獪\談MySQL存儲過程中declare和set定義變量的區(qū)別。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2016-12-12
結(jié)合PHP腳本添加和查詢MySQL數(shù)據(jù)的基本教程
這篇文章主要介紹了結(jié)合PHP腳本添加和查詢MySQL數(shù)據(jù)的基本教程,即在PHP程序中使用基本的SELECT FROM和INSERT INTO語句,需要的朋友可以參考下2015-12-12