怎麼解決MySQL死鎖問題（實例詳解）

本篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了對常見的死鎖案例進行相關分析與探討，以及如何去盡可能避免死鎖給出一些建議，希望對大家有幫助。

推薦學習：mysql教學

#1、什麼是死鎖

死鎖是並發系統中常見的問題，同樣也會出現在資料庫MySQL的並發讀寫請求場景中。當兩個及以上的事務，雙方都在等待對方釋放已經持有的鎖或因為加鎖順序不一致造成循環等待鎖資源，就會出現「死鎖」。常見的報錯資訊為 Deadlock found when trying to get lock...。

舉例來說 A 事務持有 X1 鎖 ，申請 X2 鎖，B事務持有 X2 鎖，申請 X1 鎖。 A 和 B 事務持有鎖並且申請對方持有的鎖進入循環等待，就造成了死鎖。

如上圖，是右側的四輛汽車資源請求產生了迴路現象，即死循環，導致了死鎖。

從死鎖的定義來看，MySQL 出現死鎖的幾個要素為：

1. 兩個或兩個以上交易

2. 每個交易都已經持有鎖定並且申請新的鎖定

3. 鎖定資源同時只能被同一個交易持有或不相容

4. 交易之間因為持有鎖定和申請鎖定導致彼此循環等待

2、InnoDB 鎖定類型

為了分析死鎖，我們有必要對InnoDB 的鎖定類型有一個了解。

MySQL InnoDB 引擎實作了標準的行級鎖定：共享鎖定( S lock ) 和排他鎖定( X lock )

1. #不同交易可以同時對同一行記錄加上S 鎖定。

2. 如果一個交易對某一行記錄加 X 鎖，其他事務就不能加 S 鎖或 X 鎖，從而導致鎖等待。

如果交易T1 持有行r 的S 鎖，那麼另一個交易T2 請求r 的鎖時，會做如下處理:

1. T2 請求S 鎖定立即被允許，結果T1 T2 都持有r 行的S 鎖定

2. T2 請求X 鎖定不能被立即允許

如果T1 持有r 的X 鎖，那麼T2 請求r 的X、S 鎖都不能立即允許，T2 必須等待T1 釋放X 鎖才可以，因為X 鎖與任何的鎖都不相容。共用鎖定和排他鎖的相容性如下所示：

2.1、間隙鎖定( gap lock ) 

間隙鎖定鎖定一個間隙以防止插入。假設索引列有2, 4, 8 三個值，如果對 4 加鎖，那麼也會同時對(2,4)和(4,8)這兩個間隙加鎖。其他事務無法插入索引值在這兩個間隙之間的記錄。但是，間隙鎖有個例外:

1. 如果索引列是唯一索引，那麼只會鎖住這條記錄(只加行鎖)，而不會鎖住間隙。

2. 對於聯合索引且是唯一索引，如果 where 條件只包括聯合索引的一部分，那麼還是會加間隙鎖定。

2.2、next-key lock

next-key lock 其實就是 行鎖定 這條記錄前面的 gap lock 的組合。假設有索引值10,11,13和20,那麼可能的next-key lock 包括:

(負無窮,10],(10,11],(11,13],( 13,20],(20,正無窮)

在RR 隔離等級下，InnoDB 使用next-key lock 主要是防止幻讀問題產生。

2.3、意向鎖( Intention lock )

InnoDB 為了支援多粒度的加鎖，允許行鎖和表鎖同時存在。為了支援在不同粒度上的加鎖操作，InnoDB 支援了額外的一種鎖方式，稱為意向鎖( Intention Lock )。意向鎖是將鎖定的物件分為多個層次，意向鎖意味著事務希望在更細粒度上進行加鎖。意向鎖分為兩種:

1. 意向共享鎖定( IS )：交易有意對錶中的某些行加上共享鎖定

2. 意向排他鎖( IX )：交易有意對錶中的某些行加排他鎖定

由於InnoDB 儲存引擎支援的是行層級的鎖，因此意向鎖其實不會阻塞除全表掃描以外的任何請求。表級意向鎖與行級鎖的兼容性如下所示:

2.4、插入意向鎖( Insert Intention lock ) 

插入意向鎖是在插入一行記錄作業之前設定的一種間隙鎖，這個鎖釋放了一種插入方式的訊號，即多個事務在相同的索引間隙插入時如果不是插入間隙中相同的位置就不需要互相等待。假設某列有索引值2，6，只要​​兩個事務插入位置不同(如事務 A 插入3，事務 B 插入4)，那麼就可以同時插入。

2.5、鎖定模式相容矩陣

橫向是已持有鎖，縱向是正在請求的鎖定：

3、已閱讀死鎖日誌 

在進行具體案例分析之前，咱們先了解下如何去讀懂死鎖日誌，盡可能地使用死鎖日誌裡面的資訊來幫助我們來解決死鎖問題。

後面測試案例的資料庫場景如下：MySQL 5.7 交易隔離等級為RR

表格結構與資料如下：

測試用例如下:

 透過執行show engine innodb status 可以查看到最近一次死鎖的日誌。

3.1、日誌分析如下:

1.***** (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read

 事務編號為2322，活躍6秒，starting index read 表示事務狀態為根據索引讀取資料。常見的其他狀態有:

mysql tables in use 1 說明目前的交易使用一個表格。

locked 1 表示表上有一個表格鎖，而對DML 語句為LOCK_IX

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

LOCK WAIT 表示正在等待鎖， 2 lock struct(s) 表示trx->trx_locks 鎖鍊錶的長度為2，每個鍊錶節點代表該事務所持有的一個鎖結構，包括錶鎖，記錄鎖以及自增鎖等。本用例中 2locks 表示 IX 鎖定和lock_mode X (Next-key lock)

1 row lock(s) 表示目前交易持有的行記錄鎖定/ gap 鎖定的數量。

MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

MySQL thread id 37 表示執行該交易的執行緒ID 為37 (即show processlist; 所顯示的ID )

#delete from student where stuno= 5 表示事務1正在執行的sql，比較難受的事情是 show engine innodb status 是看不到完整的sql 的，通常顯示目前正在等待鎖定的sql。

 ***** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

RECORD LOCKS 表示記錄鎖， 此條內容表示交易 1 正在等待表 student 上的 idx_stuno 的 X 鎖，本案例中其實是 Next-Key Lock 。

事務2的log 和上面分析類似:

2.***** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

顯示交易2 的insert into student(stuno,score) values(2,10) 持有了a=5 的Lock mode X

 LOCK_gap，不過我們從日誌裡面看不到事務2執行的delete from student where stuno=5;

這點也是造成DBA 僅根據日誌難以分析死鎖的問題的根本原因。

3.***** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw**** .****student trx id 2321 lock_mode X locks gap before rec insert intention waiting

表示交易2 的insert 語句正在等待插入意向鎖lock_mode X locks gap before rec insert intention waiting (lock_mode X locks gap before rec insert intention waiting ( CK_mode X LOCK_ap before rec insert intention waiting ( CK_mode X LOCK_pap before rec insert intention waiting_CK_mode X LOCK_ap before.

#4、經典案例分析

4.1、事務並發insert 唯一鍵衝突

表格結構與資料如下所示:

##測試用例如下:

 日誌分析如下:

交易T2 insert into t7(id,a) values (26,10) 語句insert 成功，持有a=10 的 
1. 排他行鎖定( Xlocks rec but no gap )

2. ##事務T1 insert into t7(id,a) values (30,10), 因為T2的第一條insert 已經插入a=10 的記錄,事務T1 insert a=10 則發生唯一鍵衝突,需要申請對衝突的唯一索引加上S Next-key Lock( 即lock mode S waiting ) 這是一個間隙鎖定會申請鎖住(,10],(10,20]之間的gap 區域。

3. 事務T2 insert into t7(id,a) values (40，9)該語句插入的a=9 的值在事務T1 申請的 #gap 鎖定4-10之間， 故需事務T2 的第二條insert 語句要等待事務T1 的 S-Next-key Lock 鎖定釋放,在日誌中顯示lock_mode X locks gap before rec insert intention waiting 。

 4.2、先update 再insert 的並發死鎖問題

 表結構如下，無資料:

測試用例如下:

 死鎖分析:
可以看到兩個事務update 不存在的記錄，先後得到間隙鎖定( gap 鎖定) ，gap 鎖之間是相容的所以在update環節不會阻塞。都持有 gap 鎖，然後去競爭插入意向鎖。當有其他會話持有 gap 鎖的時候，當前會話申請不了插入意向鎖，導致死鎖。

5、如何盡可能避免死鎖

1. 合理的設計索引，區分度高的列放到組合索引前面，讓業務SQL 盡可能透過索引定位更少的行，減少鎖定競爭。

2. 調整業務邏輯 SQL 執行順序， 避免 update/delete 長時間持有鎖定的 SQL 在交易前面。

3. 避免大事務，盡量將大事務拆成多個小事務來處理，小事務發生鎖定衝突的幾率也更小。

4. 以固定的順序存取表格和行。例如兩個更新資料的事務，事務 A 更新資料的順序為 1，2;事務 B 更新資料的順序為 2，1。這樣更可能會造成死鎖。

5. 在並發比較高的系統中，不要明確加鎖，特別是在事務裡明確加鎖。如 select … for update 語句，如果是在事務裡（運行了 start transaction 或設定了autocommit 等於0）,那麼就會鎖定所查找到的記錄。

6. 盡量按主鍵/索引去查找記錄，範圍查找增加了鎖定衝突的可能性，也不要利用資料庫做一些額外額度計算工作。例如有的程式會用到 “select … where … order by rand();”這樣的語句，由於類似這樣的語句用不到索引，因此將導致整個表的資料都被鎖住。

7. 最佳化 SQL 和表格設計，減少同時佔用太多資源的情況。比方說，減少連線的表格，將複雜 SQL 分解為多個簡單的 SQL。

推薦學習：mysql學習教學

以上是怎麼解決MySQL死鎖問題（實例詳解）的詳細內容。更多資訊請關注PHP中文網其他相關文章！