Categories
程式開發

手把手教你分析Mysql死鎖問題


前言

發生死鎖了,如何排查和解決呢?本文將跟你一起探討這個問題

準備好數據環境模擬死鎖案發分析死鎖日誌分析死鎖結果

環境準備

數據庫隔離級別:

mysql>選擇@@ tx_isolation; + —————– + | @@ tx_isolation | + —————– + | REPEATABLE-READ | + —————– + 1已設置行,1警告(0.00秒)

自動提交關閉:

mysql> set autocommit = 0;查詢確定,受影響0行(0.00秒)mysql> select @@ autocommit; + ————– + | @@ autocommit | + ————– + | 0 | + ————–已設置+1行(0.00秒)

表結構:

//id是自增主鍵,name是非唯一索引,balance普通字段CREATE TABLE `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `balance` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

表中的數據:

手把手教你分析Mysql死鎖問題 1

模擬並發

開啟兩個終端模擬事務並發情況,執行順序以及實驗現像如下:

手把手教你分析Mysql死鎖問題 2

1)事務A執行更新操作,更新成功

mysql>更新帳戶集餘額= 1000,其中名稱=’Wei’;查詢確定,影響1行(0.01秒)

2)事務B執行更新操作,更新成功

mysql>更新帳戶集餘額= 1000,其中名稱=’Eason’;查詢確定,影響1行(0.01秒)

3)事務A執行插入操作,陷入阻塞~

mysql>插入帳戶值(null,’Jay’,100);

手把手教你分析Mysql死鎖問題 3

這時候可以用select * from information_schema.innodb_locks;查看鎖情況:

手把手教你分析Mysql死鎖問題 4

4)事務B執行插入操作,插入成功,同時事務A的插入由阻塞變為死鎖error。

mysql>插入帳戶值(null,’Yan’,100);查詢確定,影響1行(0.01秒)

手把手教你分析Mysql死鎖問題 5

鎖介紹

在分析死鎖日誌前,先做一下鎖介紹,哈哈~

手把手教你分析Mysql死鎖問題 6

主要介紹一下兼容性以及鎖模式類型的鎖:

共享鎖與排他鎖

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鎖與任何的鎖都不兼容。

手把手教你分析Mysql死鎖問題 7

意向鎖

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

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

InnoDB存儲引擎中鎖的兼容性如下表:

手把手教你分析Mysql死鎖問題 8

記錄鎖(Record Locks)

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

記錄鎖的事務數據(關鍵詞:lock_mode X locks rec but not gap),記錄如下:

記錄鎖定空間ID 58頁號3 n位72表`test`的索引`PRIMARY`。`t`trx id 10078 lock_mode X鎖定記錄但不鎖定gapRecord鎖,堆沒有2物理記錄:n_fields 3; 緊湊格式信息位0 0:len 4; 十六進制8000000a; asc ;; 1:倫6; 十六進制00000000274f; asc’O ;; 2:倫7; 十六進制b60000019d0110; asc ;;

間隙鎖(Gap Locks)

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

間隙鎖的事務數據(關鍵詞:gap before rec),記錄如下:

RECORD LOCKS空間ID 177頁號4 N位80表id“ test2”的IDx_name。ʻaccount` trx id 38049 lock_mode X在recRecord鎖之前鎖定間隙,堆號6物理記錄:n_fields 2; 緊湊格式信息位0 0:len 3; 十六進制576569; asc魏;; 1:倫4; 十六進制80000002; asc ;;

下一鍵鎖

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

插入意向鎖(Insert Intention)

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

事務數據類似於下面:

記錄鎖定空間id 31頁號3 n位72表`test`的索引`PRIMARY`。`child`trxid 8731 lock_mode X在記錄插入意圖等待之前鎖定間隙記錄鎖,堆號3物理記錄:n_fields 3; 緊湊格式信息位0 0:len 4; 十六進制80000066; asc f ;; 1:倫6; 十六進制000000002215; asc“ ;; 2:len 7; hex 9000000172011c; asc r ;; …

鎖模式兼容矩陣(橫向是已持有鎖,縱向是正在請求的鎖):

手把手教你分析Mysql死鎖問題 9

如何讀懂死鎖日誌?

顯示引擎innodb狀態

可以用show engine innodb status,查看最近一次死鎖日誌哈~,執行後,死鎖日誌如下:

2020-04-11 00:35:55 0x243c ***(1)TRANSACTION:TRANSACTION 38048,正在活動92秒插入正在使用的mysql表1,已鎖定1LOCK WAIT 4個鎖定結構,堆大小1136,4個行鎖),撤消日誌條目2MySQL線程ID 53,OS線程句柄2300,查詢ID 2362 localhost :: 1 root updateinsert到帳戶值中(null,’Jay’,100)***(1)等待此鎖被授予: RECORD LOCKS空間ID 177頁號4 N位80表ID“ test2”的IDx_name。ʻaccount` trx ID 38048 lock_mode X在記錄插入意圖等待之前鎖定間隙記錄鎖定,堆號6物理記錄:n_fields 2; 緊湊格式信息位0 0:len 3; 十六進制576569; asc魏;; 1:倫4; 十六進制80000002; asc ;; ***(2)TRANSACTION:TRANSACTION 38049,ACTIVE 72 sec插入,在InnoDB 5000mysql表內部聲明使用中的線程1,已鎖定15個鎖定結構,堆大小1136,4個行鎖定,撤消日誌條目2 MySQL線程ID 52,OS線程句柄9276,查詢ID 2363 localhost :: 1 root update插入帳戶值(null,’Yan’,100)***(2)保持鎖:記錄鎖空間ID 177帳號trx id 38049 page_test表2的第4位n位80索引idx_name。 緊湊格式信息位0 0:len 3; 十六進制576569; asc魏;; 1:倫4; 十六進制80000002; asc ;; ***(2)等待授予此鎖:記錄鎖空間ID 177頁號4 n位80索引表test2`的idx_name。ʻaccount` trx id 38049 lock_mode X插入意圖WaitingRecordRecord否1物理記錄:n_fields 1; 緊湊格式信息位0 0:len 8; 六角形73757072656d756d; asc至上;; ***我們回滾交易(1)

我們如何分析以上死鎖日誌呢?

第一部分

1)找到關鍵詞TRANSACTION,事務38048

手把手教你分析Mysql死鎖問題 10

2)查看正在執行的SQL

插入帳戶值(null,“ Jay”,100)

3)正在等待鎖釋放(WAITING FOR THIS LOCK TO BE GRANTED),插入意向排他鎖(lock_mode X locks gap before rec insert intention waiting),普通索引(idx_name),物理記錄(PHYSICAL RECORD),間隙區間(未知, Wei);

手把手教你分析Mysql死鎖問題 11

第二部分

1)找到關鍵詞TRANSACTION,事務38049

手把手教你分析Mysql死鎖問題 12

2)查看正在執行的SQL

插入帳戶值(null,“ Yan”,100)

3)持有鎖(HOLDS THE LOCK),間隙鎖(lock_mode X locks gap before rec),普通索引(index idx_name),物理記錄(physical record),區間(未知,Wei);

手把手教你分析Mysql死鎖問題 13

4)正在等待鎖釋放(waiting for this lock to be granted),插入意向鎖(lock_mode X insert intention waiting),普通索引上(index idx_name),物理記錄(physical record),間隙區間(未知,+∞) ;

手把手教你分析Mysql死鎖問題 14

5)事務1回滾(we roll back transaction 1);

查看日誌結果

手把手教你分析Mysql死鎖問題 15

查看日誌可得:

事務A正在等待的插入意向排他鎖(事務A即日誌的事務1,根據insert語句來對號入座的哈),正在事務B的懷裡~事務B持有間隙鎖,正在等待插入意向排它鎖

這裡面,有些朋友可能有「疑惑」,

事務A持有什麼鎖呢?日誌根本看不出來。它又想拿什麼樣的插入意向排他鎖呢?事務B拿了具體什麼的間隙鎖呢?它為什麼也要拿插入意向鎖?死鎖的死循環是怎麼形成的?目前日誌看不出死循環構成呢?

我們接下來一小節詳細分析一波,一個一個問題來~

死鎖分析

死鎖死循環四要素

手把手教你分析Mysql死鎖問題 16

互斥條件:指進程對所分配到的資源進行排它性使用,即在一段時間內某資源只由一個進程佔用。如果此時還有其它進程請求資源,則請求者只能等待,直至佔有資源的進程用畢釋放。請求和保持條件:指進程已經保持至少一個資源,但又提出了新的資源請求,而該資源已被其它進程佔有,此時請求進程阻塞,但又對自己已獲得的其它資源保持不放。不剝奪條件:指進程已獲得的資源,在未使用完之前,不能被剝奪,只能在使用完時由自己釋放。環路等待條件:指在發生死鎖時,必然存在一個進程——資源的環形鏈,即進程集合{P0,P1,P2,···,Pn}中的P0正在等待一個P1佔用的資源; P1正在等待P2佔用的資源,……,Pn正在等待已被P0佔用的資源。

事務A持有什麼鎖呢?它又想拿什麼樣的插入意向排他鎖呢?

為了方便記錄,例子用W表示Wei,J表示Jay,E表示Eason哈~

我們先來分析事務A中update語句的加鎖情況~

更新帳戶集餘額= 1000,其中name =“ Wei”;

「間隙鎖:」

Update語句會在非唯一索引的name加上左區間的間隙鎖,右區間的間隙鎖(因為目前表中只有name=”Wei”的一條記錄,所以沒有中間的間隙鎖~),即(E, W) 和(W,+∞)為什麼存在間隙鎖?因為這是RR的數據庫隔離級別,用來解決幻讀問題用的~

「記錄鎖」

因為name是索引,所以該update語句肯定會加上W的記錄鎖

「Next-Key鎖」

Next-Key鎖=記錄鎖+間隙鎖,所以該update語句就有了(E,W]的Next-Key鎖

「綜上所述,事務A執行完update更新語句,會持有鎖:」

下一鍵鎖定:(E,W]間隙鎖定:(W,+∞)

我們再來分析一波事務A中insert語句的加鎖情況

插入帳戶值(null,’Jay’,100);

「間隙鎖:」

因為Jay(J在E和W之間),所以需要請求加(E,W)的間隙鎖

「插入意向鎖(Insert Intention)」

插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖,這個鎖釋放了一種插入方式的信號,即事務A需要插入意向鎖(E,W)

「因此」,事務A的update語句和insert語句執行完,它是持有了「(E,W]的Next-Key鎖」,「(W,+∞)的Gap鎖」,想拿到「( E,W)的插入意向排它鎖」,等待的鎖跟死鎖日誌是對上的,哈哈~

手把手教你分析Mysql死鎖問題 11

事務B擁有了什麼間隙鎖?它為什麼也要拿插入意向鎖?

同理,我們再來分析一波事務B,update語句的加鎖分析:

更新帳戶集餘額= 1000,其中名稱=“ Eason”;

「間隙鎖:」

Update語句會在非唯一索引的name加上左區間的間隙鎖,右區間的間隙鎖(因為目前表中只有name=”Eason”的一條記錄,所以沒有中間的間隙鎖~),即(-∞ ,E)和(E,W)

「記錄鎖」

因為name是索引,所以該update語句肯定會加上E的記錄鎖

「Next-Key鎖」

Next-Key鎖=記錄鎖+間隙鎖,所以該Update語句就有了(-∞,E]的Next-Key鎖

「綜上所述,事務B執行完update更新語句,會持有鎖:」

下一鍵鎖定:(-∞,E]間隙鎖定:(E,W)

我們再來分析一波B中insert語句的加鎖情況

插入帳戶值(null,’Yan’,100);

「間隙鎖:」

因為Yan(Y在W之後),所以需要請求加(W,+∞)的間隙鎖

「插入意向鎖(Insert Intention)」

插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖,這個鎖釋放了一種插入方式的信號,即事務A需要插入意向鎖(W,+∞)

「所以」,事務B的update語句和insert語句執行完,它是持有了「(-∞,E]的Next-Key鎖」,「(E,W)的Gap鎖」,想拿到「( W,+∞)的間隙鎖,即插入意向排它鎖」,加鎖情況跟死鎖日誌也是對上的~

手把手教你分析Mysql死鎖問題 13

手把手教你分析Mysql死鎖問題 14

死鎖真相還原

接下來呢,讓我們一起還原死鎖真相吧~哈哈~

手把手教你分析Mysql死鎖問題 20

事務A執行完Update Wei的語句,持有(E,W]的Next-key Lock,(W,+∞)的Gap Lock ,插入成功~事務B執行完Update Eason語句,持有(-∞,E ]的Next-Key Lock,(E,W)的Gap Lock,插入成功~事務A執行Insert Jay的語句時,因為需要(E,W)的插入意向鎖,但是(E,W)在事務B懷裡,所以它陷入心塞~事務B執行Insert Yan的語句時,因為需要(W,+∞) 的插入意向鎖,但是(W,+∞) 在事務A懷裡,所以它也陷入心塞。事務A持有(W,+∞)的Gap Lock,在等待(E,W)的插入意向鎖,事務B持有(E,W)的Gap鎖,在等待(W,+∞) 的插入意向鎖,所以形成了死鎖的閉環~(Gap鎖與插入意向鎖會衝突的,可以看回鎖介紹的鎖模式兼容矩陣哈~)事務A,B形成了死鎖閉環後,因為Innodb的底層機制,它會讓其中一個事務讓出資源,另外的事務執行成功,這就是為什麼你最後看到事務B插入成功了,但是事務A的插入顯示了Deadlock found ~

總結

最後,遇到死鎖問題,我們應該怎麼分析呢?

模擬死鎖場景show engine innodb status;查看死鎖日誌找出死鎖SQLSQL加鎖分析,這個可以去官網看哈“分析死鎖日誌(持有什麼鎖,等待什麼鎖)熟悉鎖模式兼容矩陣,InnoDB存儲引擎中鎖的兼容性矩陣。

個人公眾號

歡迎關注公眾號,撿田螺的小男孩

覺得寫得好的小伙伴給個點贊+關注啦,謝謝~如果有寫得不正確的地方,麻煩指出,感激不盡。同時非常期待小伙伴們能夠關注我公眾號,後面慢慢推出更好的干貨~嘻