【MySQL】漫談死鎖

楊奇龍發表於2017-10-08
MySql
一 前言 
   死鎖是每個MySQL DBA 都會遇到的技術問題，本文自己針對死鎖學習的一個總結,瞭解死鎖是什麼，MySQL如何檢測死鎖，處理死鎖，死鎖的案例，如何避免死鎖。
二 死鎖
   死鎖 是併發系統中常見的問題，同樣也會出現在Innodb系統中。當兩個及以上的事務，雙方都在等待對方釋放已經持有的鎖或者因為加鎖順序不一致造成迴圈等待鎖資源，就會出現"死鎖"。舉例來說A 事務持有x1鎖 ，申請x2鎖，B 事務持有x2鎖，申請x1 鎖。A和B 事務持有鎖並且申請對方持有的鎖進入迴圈等待，就造成死鎖。

從死鎖的定義來看，MySQL出現死鎖的幾個要素:
  1. a 兩個或者兩個以上事務。
  2. b 每個事務都已經持有鎖並且申請新的鎖。
  3. c 鎖資源同時只能被同一個事務持有或者不相容。
  4. d 事務之間因為持有鎖和申請鎖導致了迴圈等待。
三 MySQL的死鎖機制
死鎖機制包含兩部分:檢測和處理。
把事務等待列表和鎖等待資訊列表通過事務資訊進行wait-for graph 檢測，如果發現有閉環，則回滾undo log 量少的事務；死鎖檢測本身也會算檢測本身所需要的成本，以便應對檢測超時導致的意外情況。

3.1 死鎖檢測
當InnoDB事務嘗試獲取(請求)加一個鎖，並且需要等待時，InnoDB 會進行死鎖檢測. 正常的流程如下:
  1. 1.InnoDB的初始化一個事務，當事務嘗試申請加一個鎖，並且需要等待時(wait_lock)，innodb會開始進行死鎖檢測(deadlock_mark)
  2. 2.進入到lock_deadlock_check_and_resolve()函式進行檢測死鎖和解決死鎖。
  3. 3.檢測死鎖過程中，是有計數器來進行限制的，在等待wait-for graph 檢測過程中遇到超時或者超過閾值，則停止檢測。
  4. 4.死鎖檢測的邏輯之一是等待圖的處理過程，如果通過鎖的資訊和事務等待鏈構造出一個圖，如果圖中出現迴路，就認為發生了死鎖。
  5. 5.死鎖的回滾，內部程式碼的處理邏輯之一是比較undo的數量，回滾undo數量少的事務。
3.2 如何處理死鎖
《資料庫系統實現》裡面提到的死鎖處理
  1. 1.超時死鎖檢測:當存在死鎖時，想所有事務都能同時繼續執行通常是不可能的，因此，至少一個事務必須中止並重新開始。超時是最直接的辦法，對超出活躍時間的事務進行限制和回滾
  2. 2.等待圖：等待圖的實現，是可以表明哪些事務在等待其他事務持有的鎖，可以在資料庫的死鎖檢測裡面加上這個機制來進行檢測是否有環的形成。
  3. 3.通過元素排序預防死鎖:這個想法很美好，但現實很殘酷,通常都是發現死鎖後才去想辦法解決死鎖的原因
  4. 4.通過時間戳檢測死鎖:對每個事務都分配一個時間戳，根據時間戳來進行回滾策略。
四 Innodb 的鎖型別
首先我們要知道對於MySQL有兩種常規鎖模式
  1. LOCK_S（讀鎖，共享鎖）
  2. LOCK_X（寫鎖，排它鎖）
最容易理解的鎖模式，讀加共享鎖(in share mode)，寫加排它鎖.
有如下幾種鎖的屬性
  1. LOCK_REC_NOT_GAP      （鎖記錄）
  2. LOCK_GAP              （鎖記錄前的GAP）
  3. LOCK_ORDINARY         （同時鎖記錄+記錄前的GAP，也即Next Key鎖）
  4. LOCK_INSERT_INTENTION （插入意向鎖，其實是特殊的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
注 關於Innodb 鎖的詳細介紹 可以移步 官方文件 或者 MySQL · 引擎特性 · InnoDB 事務鎖系統簡介
五 Innodb 不同事務加鎖型別
例項 update tab  set  x=1 where  id= 1 ; 
1 索引列是主鍵，RC隔離級別 
  對記錄記錄加X鎖
2 索引列是二級唯一索引，RC隔離級別
  若id列是unique列，其上有unique索引。那麼SQL需要加兩個X鎖，一個對應於id unique索引上的id = 10的記錄，另一把鎖對應於聚簇索引上的[name=’d’,id=10]的記錄。
3 索引列是二級非唯一索引，RC隔離級別
  若id列上有非唯一索引，那麼對應的所有滿足SQL查詢條件的記錄，都會被加鎖。同時，這些記錄在主鍵索引上的記錄，也會被加鎖。
4 索引列上沒有索引，RC隔離級別
  若id列上沒有索引，SQL會走聚簇索引的全掃描進行過濾，由於過濾是由MySQL Server層面進行的。因此每條記錄，無論是否滿足條件，都會被加上X鎖。但是，為了效率考量，MySQL做了優化，對於不滿足條件的記錄，會在判斷後放鎖，最終持有的，是滿足條件的記錄上的鎖，但是不滿足條件的記錄上的加鎖/放鎖動作不會省略。同時，優化也違背了2PL的約束。
5 索引列是主鍵，RR隔離級別
  對記錄記錄加X鎖
6 索引列是二級唯一索引，RR隔離級別
  對錶加上兩個X鎖，唯一索引滿足條件的記錄上一個，對應的聚簇索引上的記錄一個。
7 索引列是二級非唯一索引，RR隔離級別
  結論:Repeatable Read隔離級別下，id列上有一個非唯一索引，對應SQL:delete from t1 where id = 10; 
  首先，通過id索引定位到第一條滿足查詢條件的記錄，加記錄上的X鎖，加GAP上的GAP鎖，然後加主鍵聚簇索引上的記錄X鎖，然後返回；然後讀取下一條，重複進行。直至進行到第一條不滿足條件的記錄[11,f]，此時，不需要加記錄X鎖，但是仍舊需要加GAP鎖，最後返回結束。
8 索引列上沒有索引，RR隔離級別
  則鎖全表
這裡需要重點說明insert 和delete的加鎖方式，因為目前遇到的大部分案例或者部分難以分析的案例都是和delete，insert 操作有關。
insert 的加鎖方式
對於併發insert造成唯一鍵衝突的時候 insert的加鎖策略是 
  1. 第一階段 唯一性約束檢查，先申請LOCK_S + LOCK_ORDINARY
  2. 第二階段 獲取階段一的鎖並且insert成功之後,插入的位置有Gap鎖:LOCK_INSERT_INTENTION,為了防止其他insert 唯一鍵衝突。
  3. 新資料插入:LOCK_X + LOCK_REC_NOT_GAP
  4. 對於insert操作來說，若發生唯一約束衝突，則需要對衝突的唯一索引加上S Next-key Lock。從這裡會發現，即使是RC事務隔離級別，也同樣會存在Next-Key Lock鎖，從而阻塞併發。然而，文件沒有說明的是，對於檢測到衝突的唯一索引，等待執行緒在獲得S Lock之後，還需要對下一個記錄進行加鎖，在原始碼中由函式row_ins_scan_sec_index_for_duplicate進行判斷
引自 死鎖案例之三 
delete 的加鎖方式
  1. 1 在非唯一索引的情況下，刪除一條存在的記錄是有gap鎖，鎖住記錄本身和記錄之前的gap
  2. 2 在唯一索引和主鍵的情況下刪除一條存在的記錄，因為都是唯一值，進行刪除的時候，是不會有gap存在
  3. 3 非唯一索引，唯一索引和主鍵在刪除一條不存在的記錄，均會在這個區間加gap鎖
  4. 4 通過非唯一索引和唯一索引去刪除一條標記為刪除的記錄的時候，都會請求該記錄的行鎖，同時鎖住記錄之前的gap
  5. 5 RC 情況下是沒有gap鎖的，除了遇到唯一鍵衝突的情況，如插入唯一鍵衝突。
引自文章 MySQL DELETE 刪除語句加鎖分析
六 死鎖案例
 關於死鎖的案例不在本文做詳細分析，這裡給出我做的幾個例子(後續會有其他案例分享)，四個案例基本均和RR 模式下的gap鎖有關。
死鎖案例之一   delete申請gap鎖與insert 的gap鎖衝突導致死鎖
死鎖案例之二   併發delete不存在記錄申請gap鎖導致死鎖
死鎖案例之三   兩個事務併發insert 唯一鍵衝突 和gap鎖一起導致的死鎖案例
死鎖案例之四   三個併發insert 語句導致的死鎖
七  如何檢視死鎖
1. 檢視事務鎖等待狀態情況
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;
下面的查詢可以得到當前狀況下資料庫的等待情況:via《innodb技術內幕中》
  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. 開啟下列引數，獲取更詳細的事務和死鎖資訊。
  innodb_print_all_deadlocks  = ON
   innodb_status_output  = ON
   innodb_status_output_locks = ON
3. 檢視innodb狀態(包含最近的死鎖日誌)
   show engine innodb status;

八 如何儘可能避免死鎖
1 事務隔離級別使用read committed和binlog_format=row ，避免RR模式帶來的gap鎖競爭。
2 合理的設計索引,區分度高的列放到組合索引前列，使業務sql儘可能的通過索引定位更少的行，減少鎖競爭。
3 調整業務邏輯 SQL執行順序，避免update/delete 長時間持有鎖sql在事務前面，(該優化視情況而定)
4 選擇合理的事務大小，小事務發生鎖衝突的機率也更小；
5 訪問相同的表時，應儘量約定以相同的順序訪問表，對一個表而言，儘可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會；
6 5.7.15 版本之後提供了新的功能 innodb_deadlock_detect 引數,可以關閉死鎖檢測，提高併發TPS。

參考文章
1 官方文件
2 MySQL · 引擎特性 · InnoDB 事務鎖系統簡介
3 mysql insert鎖機制
4 MySQL 加鎖分析
5 一個最不可思議的MySQL死鎖分析
6 談談MySQL死鎖 一
7 談談MySQL死鎖之二 死鎖檢測和處理原始碼分析
如果您覺得能從本文收益，可以請北在南方一瓶飲料 ^_^

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-2145736/，如需轉載，請註明出處，否則將追究法律責任。

相關文章