MySQL的死鎖機(jī)制以及避免死鎖的方法

本篇內(nèi)容主要講解“MySQL的死鎖機(jī)制以及避免死鎖的方法”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL的死鎖機(jī)制以及避免死鎖的方法”吧!

一 前言 
   死鎖是每個(gè)MySQL DBA 都會(huì)遇到的技術(shù)問(wèn)題，本文自己針對(duì)死鎖學(xué)習(xí)的一個(gè)總結(jié),了解死鎖是什么，MySQL如何檢測(cè)死鎖，處理死鎖，死鎖的案例，如何避免死鎖。
二 死鎖
   死鎖 是并發(fā)系統(tǒng)中常見(jiàn)的問(wèn)題，同樣也會(huì)出現(xiàn)在Innodb系統(tǒng)中。當(dāng)兩個(gè)及以上的事務(wù)，雙方都在等待對(duì)方釋放已經(jīng)持有的鎖或者因?yàn)榧渔i順序不一致造成循環(huán)等待鎖資源，就會(huì)出現(xiàn)"死鎖"。舉例來(lái)說(shuō)A 事務(wù)持有x1鎖 ，申請(qǐng)x2鎖，B 事務(wù)持有x2鎖，申請(qǐng)x1 鎖。A和B 事務(wù)持有鎖并且申請(qǐng)對(duì)方持有的鎖進(jìn)入循環(huán)等待，就造成死鎖。

從死鎖的定義來(lái)看，MySQL出現(xiàn)死鎖的幾個(gè)要素:

1. a 兩個(gè)或者兩個(gè)以上事務(wù)。
   

2. b 每個(gè)事務(wù)都已經(jīng)持有鎖并且申請(qǐng)新的鎖。
   

3. c 鎖資源同時(shí)只能被同一個(gè)事務(wù)持有或者不兼容。
   

4. d 事務(wù)之間因?yàn)槌钟墟i和申請(qǐng)鎖導(dǎo)致了循環(huán)等待。

三 MySQL的死鎖機(jī)制
死鎖機(jī)制包含兩部分:檢測(cè)和處理。
把事務(wù)等待列表和鎖等待信息列表通過(guò)事務(wù)信息進(jìn)行wait-for graph 檢測(cè)，如果發(fā)現(xiàn)有閉環(huán)，則回滾undo log 量少的事務(wù)；死鎖檢測(cè)本身也會(huì)算檢測(cè)本身所需要的成本，以便應(yīng)對(duì)檢測(cè)超時(shí)導(dǎo)致的意外情況。

3.1 死鎖檢測(cè)
當(dāng)InnoDB事務(wù)嘗試獲取(請(qǐng)求)加一個(gè)鎖，并且需要等待時(shí)，InnoDB 會(huì)進(jìn)行死鎖檢測(cè). 正常的流程如下:

1. 1.InnoDB的初始化一個(gè)事務(wù)，當(dāng)事務(wù)嘗試申請(qǐng)加一個(gè)鎖，并且需要等待時(shí)(wait_lock)，innodb會(huì)開(kāi)始進(jìn)行死鎖檢測(cè)(deadlock_mark)
   

2. 2.進(jìn)入到lock_deadlock_check_and_resolve()函數(shù)進(jìn)行檢測(cè)死鎖和解決死鎖。
   

3. 3.檢測(cè)死鎖過(guò)程中，是有計(jì)數(shù)器來(lái)進(jìn)行限制的，在等待wait-for graph 檢測(cè)過(guò)程中遇到超時(shí)或者超過(guò)閾值，則停止檢測(cè)。
   

4. 4.死鎖檢測(cè)的邏輯之一是等待圖的處理過(guò)程，如果通過(guò)鎖的信息和事務(wù)等待鏈構(gòu)造出一個(gè)圖，如果圖中出現(xiàn)回路，就認(rèn)為發(fā)生了死鎖。
   

5. 5.死鎖的回滾，內(nèi)部代碼的處理邏輯之一是比較undo的數(shù)量，回滾undo數(shù)量少的事務(wù)。

3.2 如何處理死鎖
《數(shù)據(jù)庫(kù)系統(tǒng)實(shí)現(xiàn)》里面提到的死鎖處理

1. 1.超時(shí)死鎖檢測(cè):當(dāng)存在死鎖時(shí)，想所有事務(wù)都能同時(shí)繼續(xù)執(zhí)行通常是不可能的，因此，至少一個(gè)事務(wù)必須中止并重新開(kāi)始。超時(shí)是最直接的辦法，對(duì)超出活躍時(shí)間的事務(wù)進(jìn)行限制和回滾
   

2. 2.等待圖：等待圖的實(shí)現(xiàn)，是可以表明哪些事務(wù)在等待其他事務(wù)持有的鎖，可以在數(shù)據(jù)庫(kù)的死鎖檢測(cè)里面加上這個(gè)機(jī)制來(lái)進(jìn)行檢測(cè)是否有環(huán)的形成。
   

3. 3.通過(guò)元素排序預(yù)防死鎖:這個(gè)想法很美好，但現(xiàn)實(shí)很殘酷,通常都是發(fā)現(xiàn)死鎖后才去想辦法解決死鎖的原因
   

4. 4.通過(guò)時(shí)間戳檢測(cè)死鎖:對(duì)每個(gè)事務(wù)都分配一個(gè)時(shí)間戳，根據(jù)時(shí)間戳來(lái)進(jìn)行回滾策略。

四 Innodb 的鎖類型
首先我們要知道對(duì)于MySQL有兩種常規(guī)鎖模式

1. LOCK_S（讀鎖，共享鎖）
   

2. LOCK_X（寫鎖，排它鎖）

最容易理解的鎖模式，讀加共享鎖(in share mode)，寫加排它鎖.
有如下幾種鎖的屬性

1. LOCK_REC_NOT_GAP      （鎖記錄）
   

2. LOCK_GAP              （鎖記錄前的GAP）
   

3. LOCK_ORDINARY         （同時(shí)鎖記錄+記錄前的GAP，也即Next Key鎖）
   

4. LOCK_INSERT_INTENTION   （插入意向鎖，其實(shí)是特殊的GAP鎖）

鎖的屬性可以與鎖模式任意組合。例如.

1. lock->type_mode       可以是Lock_X 或者Lock_S
   

2. locks gap before rec  表示為gap鎖：lock->type_mode & LOCK_GAP
   

3. locks rec but not gap 表示為記錄鎖，非gap鎖：lock->type_mode & LOCK_REC_NOT_GAP
   

4. insert intention      表示為插入意向鎖：lock->type_mode & LOCK_INSERT_INTENTION
   

5. waiting               表示鎖等待：lock->type_mode & LOCK_WAIT

注 關(guān)于Innodb 鎖的詳細(xì)介紹 可以移步 官方文檔 或者 MySQL · 引擎特性 · InnoDB 事務(wù)鎖系統(tǒng)簡(jiǎn)介
五 Innodb 不同事務(wù)加鎖類型
實(shí)例 update tab  set  x=1 where  id= 1 ; 
1 索引列是主鍵，RC隔離級(jí)別 
  對(duì)記錄記錄加X(jué)鎖
2 索引列是二級(jí)唯一索引，RC隔離級(jí)別
  若id列是unique列，其上有unique索引。那么SQL需要加兩個(gè)X鎖，一個(gè)對(duì)應(yīng)于id unique索引上的id = 10的記錄，另一把鎖對(duì)應(yīng)于聚簇索引上的[name=’d’,id=10]的記錄。
3 索引列是二級(jí)非唯一索引，RC隔離級(jí)別
  若id列上有非唯一索引，那么對(duì)應(yīng)的所有滿足SQL查詢條件的記錄，都會(huì)被加鎖。同時(shí)，這些記錄在主鍵索引上的記錄，也會(huì)被加鎖。
4 索引列上沒(méi)有索引，RC隔離級(jí)別
  若id列上沒(méi)有索引，SQL會(huì)走聚簇索引的全掃描進(jìn)行過(guò)濾，由于過(guò)濾是由MySQL Server層面進(jìn)行的。因此每條記錄，無(wú)論是否滿足條件，都會(huì)被加上X鎖。但是，為了效率考量，MySQL做了優(yōu)化，對(duì)于不滿足條件的記錄，會(huì)在判斷后放鎖，最終持有的，是滿足條件的記錄上的鎖，但是不滿足條件的記錄上的加鎖/放鎖動(dòng)作不會(huì)省略。同時(shí)，優(yōu)化也違背了2PL的約束。
5 索引列是主鍵，RR隔離級(jí)別
  對(duì)記錄記錄加X(jué)鎖
6 索引列是二級(jí)唯一索引，RR隔離級(jí)別
  對(duì)表加上兩個(gè)X鎖，唯一索引滿足條件的記錄上一個(gè)，對(duì)應(yīng)的聚簇索引上的記錄一個(gè)。
7 索引列是二級(jí)非唯一索引，RR隔離級(jí)別
  結(jié)論:Repeatable Read隔離級(jí)別下，id列上有一個(gè)非唯一索引，對(duì)應(yīng)SQL:delete from t1 where id = 10; 
  首先，通過(guò)id索引定位到第一條滿足查詢條件的記錄，加記錄上的X鎖，加GAP上的GAP鎖，然后加主鍵聚簇索引上的記錄X鎖，然后返回；然后讀取下一條，重復(fù)進(jìn)行。直至進(jìn)行到第一條不滿足條件的記錄[11,f]，此時(shí)，不需要加記錄X鎖，但是仍舊需要加GAP鎖，最后返回結(jié)束。
8 索引列上沒(méi)有索引，RR隔離級(jí)別
  則鎖全表
這里需要重點(diǎn)說(shuō)明insert 和delete的加鎖方式，因?yàn)槟壳坝龅降拇蟛糠职咐蛘卟糠蛛y以分析的案例都是和delete，insert 操作有關(guān)。
insert 的加鎖方式
對(duì)于并發(fā)insert造成唯一鍵沖突的時(shí)候 insert的加鎖策略是 

1. 第一階段 唯一性約束檢查，先申請(qǐng)LOCK_S + LOCK_ORDINARY
   

2. 第二階段 獲取階段一的鎖并且insert成功之后,插入的位置有Gap鎖:LOCK_INSERT_INTENTION,為了防止其他insert 唯一鍵沖突。
   

3. 新數(shù)據(jù)插入:LOCK_X + LOCK_REC_NOT_GAP
   

4. 對(duì)于insert操作來(lái)說(shuō)，若發(fā)生唯一約束沖突，則需要對(duì)沖突的唯一索引加上S Next-key Lock。從這里會(huì)發(fā)現(xiàn)，即使是RC事務(wù)隔離級(jí)別，也同樣會(huì)存在Next-Key Lock鎖，從而阻塞并發(fā)。然而，文檔沒(méi)有說(shuō)明的是，對(duì)于檢測(cè)到?jīng)_突的唯一索引，等待線程在獲得S Lock之后，還需要對(duì)下一個(gè)記錄進(jìn)行加鎖，在源碼中由函數(shù)row_ins_scan_sec_index_for_duplicate進(jìn)行判斷

引自 死鎖案例之三 
delete 的加鎖方式

1. 1 在非唯一索引的情況下，刪除一條存在的記錄是有g(shù)ap鎖，鎖住記錄本身和記錄之前的gap
   

2. 2 在唯一索引和主鍵的情況下刪除一條存在的記錄，因?yàn)槎际俏ㄒ恢?，進(jìn)行刪除的時(shí)候，是不會(huì)有g(shù)ap存在
   

3. 3 非唯一索引，唯一索引和主鍵在刪除一條不存在的記錄，均會(huì)在這個(gè)區(qū)間加gap鎖
   

4. 4 通過(guò)非唯一索引和唯一索引去刪除一條標(biāo)記為刪除的記錄的時(shí)候，都會(huì)請(qǐng)求該記錄的行鎖，同時(shí)鎖住記錄之前的gap
   

5. 5 RC 情況下是沒(méi)有g(shù)ap鎖的，除了遇到唯一鍵沖突的情況，如插入唯一鍵沖突。

引自文章 MySQL DELETE 刪除語(yǔ)句加鎖分析
六 死鎖案例
 關(guān)于死鎖的案例不在本文做詳細(xì)分析，這里給出我做的幾個(gè)例子(后續(xù)會(huì)有其他案例分享)，四個(gè)案例基本均和RR 模式下的gap鎖有關(guān)。
死鎖案例之一   delete申請(qǐng)gap鎖與insert 的gap鎖沖突導(dǎo)致死鎖
死鎖案例之二   并發(fā)delete不存在記錄申請(qǐng)gap鎖導(dǎo)致死鎖
死鎖案例之三   兩個(gè)事務(wù)并發(fā)insert 唯一鍵沖突 和gap鎖一起導(dǎo)致的死鎖案例
死鎖案例之四   三個(gè)并發(fā)insert 語(yǔ)句導(dǎo)致的死鎖
七  如何查看死鎖
1. 查看事務(wù)鎖等待狀態(tài)情況
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;
下面的查詢可以得到當(dāng)前狀況下數(shù)據(jù)庫(kù)的等待情況:via《innodb技術(shù)內(nèi)幕中》

1. select r.trx_id       wait_trx_id,
   

2. r.trx_mysql_thread_id wait_thr_id,
   

3. r.trx_query           wait_query,
   

4. b.trx_id              block_trx_id,
   

5. b.trx_mysql_thread_id block_thrd_id,
   

6. b.trx_query           block_query
   

7. from information_schema.innodb_lock_waits w
   

8. inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id
   

9. inner join information_schema.innodb_trx r on r.trx_id =w.requesting_trx_id

2. 打開(kāi)下列參數(shù)，獲取更詳細(xì)的事務(wù)和死鎖信息。
  innodb_print_all_deadlocks  = ON
   innodb_status_output  =ON
   innodb_status_output_locks = ON
3. 查看innodb狀態(tài)(包含最近的死鎖日志)
   show engine innodb status;

八 如何盡可能避免死鎖
1 事務(wù)隔離級(jí)別使用read committed和binlog_format=row ，避免RR模式帶來(lái)的gap鎖競(jìng)爭(zhēng)。
2 合理的設(shè)計(jì)索引,區(qū)分度高的列放到組合索引前列，使業(yè)務(wù)sql盡可能的通過(guò)索引定位更少的行，減少鎖競(jìng)爭(zhēng)。
3 調(diào)整業(yè)務(wù)邏輯 SQL執(zhí)行順序，避免update/delete 長(zhǎng)時(shí)間持有鎖sql在事務(wù)前面，(該優(yōu)化視情況而定)
4 選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率也更?。?br/>5 訪問(wèn)相同的表時(shí)，應(yīng)盡量約定以相同的順序訪問(wèn)表，對(duì)一個(gè)表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機(jī)會(huì)；
6 5.7.15 版本之后提供了新的功能 innodb_deadlock_detect 參數(shù),可以關(guān)閉死鎖檢測(cè)，提高并發(fā)TPS。

到此，相信大家對(duì)“MySQL的死鎖機(jī)制以及避免死鎖的方法”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！