死鎖是每個 MySQL DBA 都會遇到的技術問題，本文自己針對死鎖學習的一個總結，了解死鎖是什麼，MySQL 如何檢測死鎖，處理死鎖，死鎖的案例，如何避免死鎖。
轉：https://mp.weixin.qq.com/s/oF6rro5HjrrUJp8YNbfj9w
文 | 楊一 on 運維
轉 | 來源：公衆号yangyidba
死鎖是并發系統中常見的問題，同樣也會出現在 Innodb 系統中。當兩個及以上的事務，雙方都在等待對方釋放已經持有的鎖或者因為加鎖順序不一緻造成循環等待鎖資源，就會出現"死鎖"。
舉例來說 A 事務持有 x1鎖 ，申請 x2 鎖，B 事務持有 x2 鎖，申請 x1 鎖。A 和 B 事務持有鎖并且申請對方持有的鎖進入循環等待，就造成死鎖。
從死鎖的定義來看，MySQL 出現死鎖的幾個要素：
a 兩個或者兩個以上事務。 b 每個事務都已經持有鎖并且申請新的鎖。 c 鎖資源同時隻能被同一個事務持有或者不相容。 d 事務之間因為持有鎖和申請鎖導緻了循環等待。
死鎖機制包含兩部分：檢測和處理。
把事務等待清單和鎖等待資訊清單通過事務資訊進行 wait-for graph 檢測，如果發現有閉環，則復原 undo log 量少的事務；死鎖檢測本身也會算檢測本身所需要的成本，以便應對檢測逾時導緻的意外情況。
當 InnoDB 事務嘗試擷取(請求)加一個鎖，并且需要等待時，InnoDB 會進行死鎖檢測。正常的流程如下:
1）InnoDB 的初始化一個事務，當事務嘗試申請加一個鎖，并且需要等待時 (wait_lock)，innodb 會開始進行死鎖檢測 (deadlock_mark)
2）進入到 lock_deadlock_check_and_resolve() 函數進行檢測死鎖和解決死鎖
3）檢測死鎖過程中，是有計數器來進行限制的，在等待 wait-for graph 檢測過程中遇到逾時或者超過門檻值，則停止檢測。
4）死鎖檢測的邏輯之一是等待圖的處理過程，如果通過鎖的資訊和事務等待鍊構造出一個圖，如果圖中出現回路，就認為發生了死鎖。
5）死鎖的復原，内部代碼的處理邏輯之一是比較 undo 的數量，復原 undo 數量少的事務。
《資料庫系統實作》裡面提到的死鎖處理：
1）逾時死鎖檢測：當存在死鎖時，想所有事務都能同時繼續執行通常是不可能的，是以，至少一個事務必須中止并重新開始。逾時是最直接的辦法，對超出活躍時間的事務進行限制和復原
2）等待圖：等待圖的實作，是可以表明哪些事務在等待其他事務持有的鎖，可以在資料庫的死鎖檢測裡面加上這個機制來進行檢測是否有環的形成
3）通過元素排序預防死鎖：這個想法很美好，但現實很殘酷,通常都是發現死鎖後才去想辦法解決死鎖的原因
4）通過時間戳檢測死鎖：對每個事務都配置設定一個時間戳，根據時間戳來進行復原政策
首先我們要知道對于 MySQL 有兩種正常鎖模式
LOCK_S（讀鎖，共享鎖）
LOCK_X（寫鎖，排它鎖）
最容易了解的鎖模式，讀加共享鎖(in share mode)，寫加排它鎖。
有如下幾種鎖的屬性：
<code>LOCK_REC_NOT_GAP （鎖記錄）</code>
<code>LOCK_GAP （鎖記錄前的GAP）</code>
<code>LOCK_ORDINARY （同時鎖記錄+記錄前的GAP，也即Next Key鎖）</code>
<code>LOCK_INSERT_INTENTION （插入意向鎖，其實是特殊的GAP鎖）</code>
鎖的屬性可以與鎖模式任意組合。例如：
<code>lock->type_mode 可以是Lock_X 或者Lock_S</code>
<code>locks gap before rec 表示為gap鎖：lock->type_mode & LOCK_GAP</code>
<code>locks rec but not gap 表示為記錄鎖，非gap鎖：lock->type_mode & LOCK_REC_NOT_GAP</code>
<code>insert intention 表示為插入意向鎖：lock->type_mode & LOCK_INSERT_INTENTION</code>
<code>waiting 表示鎖等待：lock->type_mode & LOCK_WAIT</code>
關于 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 N
找到大于 N 的第一條記錄 M，以及前一條記錄 P
如果 M 上面沒有 gap/next-key lock，進入第三步驟，否則等待(對其 next-rec 加 insert intension lock，由于有 gap 鎖，是以等待)
檢查 P：判斷 P 是否等于 N：
<code>如果不等: 則完成插入（結束）</code>
<code>如果相等: 再判斷P是否有鎖，</code>
<code> a 如果沒有鎖:報1062錯誤(duplicate key),說明該記錄已經存在，報重複值錯誤</code>
<code> b 加S-lock,說明該記錄被标記為删除, 事務已經送出，還沒來得及purge</code>
<code> c 如果有鎖: 則加S-lock,說明該記錄被标記為删除，事務還未送出.</code>
該結論引自: http://keithlan.github.io/2017/06/21/innodblocksalgorithms/
delete 的加鎖方式
1）在非唯一索引的情況下，删除一條存在的記錄是有 gap 鎖，鎖住記錄本身和記錄之前的 gap
2）在唯一索引和主鍵的情況下删除一條存在的記錄，因為都是唯一值，進行删除的時候，是不會有 gap 存在
3）非唯一索引，唯一索引和主鍵在删除一條不存在的記錄，均會在這個區間加 gap 鎖
4）通過非唯一索引和唯一索引去删除一條标記為删除的記錄的時候，都會請求該記錄的行鎖，同時鎖住記錄之前的 gap
5）RC 情況下是沒有 gap 鎖的，除了遇到唯一鍵沖突的情況，如插入唯一鍵沖突。
引自文章 MySQL DELETE 删除語句加鎖分析
1. 檢視事務鎖等待狀态情況
<code> select * from information_schema.innodb_locks;</code>
<code> select * from information_schema.innodb_lock_waits;</code>
<code> select * from information_schema.innodb_trx;</code>
<code> ```</code>
下面的查詢可以得到目前狀況下資料庫的等待情況：via《innodb技術内幕中》
<code>select r.trx_id wait_trx_id,</code>
<code>r.trx_mysql_thread_id wait_thr_id,</code>
<code>r.trx_query wait_query,</code>
<code>b.trx_id block_trx_id,</code>
<code>b.trx_mysql_thread_id block_thrd_id,</code>
<code>b.trx_query block_query</code>
<code>from information_schema.innodb_lock_waits w</code>
<code>inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id</code>
<code>inner join information_schema.innodb_trx r on r.trx_id =w.requesting_trx_id</code>
2. 打開下列參數，擷取更詳細的事務和死鎖資訊
<code> innodb_print_all_deadlocks = ON</code>
<code> innodb_status_output_locks = ON</code>
3. 檢視 innodb 狀态(包含最近的死鎖日志)
show engine innodb status;
事務隔離級别使用 read committed 和 binlog_format=row ，避免 RR 模式帶來的 gap 鎖競争。
合理的設計索引,區分度高的列放到組合索引前列，使業務 sql 盡可能的通過索引定位更少的行，減少鎖競争。
調整業務邏輯 SQL 執行順序，避免 update/delete 長時間持有鎖 sql 在事務前面，(該優化視情況而定)。
選擇合理的事務大小，小事務發生鎖沖突的幾率也更小；
通路相同的表時，應盡量約定以相同的順序通路表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會；
5.7.15 版本之後提供了新的功能 innodb_deadlock_detect 參數,可以關閉死鎖檢測，提高并發TPS。
__EOF__
歡迎轉載，但請注明出處！
歡迎大家一起交流學習！如果有什麼疑問，大家可以在評論區一起交流！
如果您覺得文章對您有幫助，可以點選文章右下角【推薦】一下。您的鼓勵是我的最大動力！