一贩绕、前言
每個MySQL DBA和開發(fā)大概率都會遇到死鎖問題火的，本文是自己對死鎖相關知識總結,介紹死鎖是什么，MySQL如何檢測死鎖/處理死鎖淑倾，死鎖的案例馏鹤，以及如何避免死鎖。
二娇哆、死鎖
死鎖是并發(fā)系統(tǒng)中常見的問題湃累，同樣也會出現(xiàn)在數(shù)據(jù)庫系統(tǒng)的并發(fā)讀寫請求場景中。當兩個及以上的事務碍讨，雙方都在等待對方釋放已經(jīng)持有的鎖或因為加鎖順序不一致造成循環(huán)等待鎖資源治力，就會出現(xiàn)"死鎖"。
舉例來說A 事務持有X1鎖 垄开，申請X2鎖琴许，B 事務持有X2鎖，申請X1 鎖溉躲。A和B 事務持有鎖并且申請對方持有的鎖進入循環(huán)等待榜田，就造成死鎖。
從死鎖的定義來看锻梳，MySQL出現(xiàn)死鎖的幾個要素:
a. 兩個或者兩個以上事務箭券。
b. 每個事務都已經(jīng)持有鎖并且申請新的鎖。
c. 鎖資源同時只能被同一個事務持有或者不兼容疑枯。
d. 事務之間因為持有鎖和申請鎖導致彼此循環(huán)等待辩块。
三、MySQL的處理死鎖機制
死鎖機制包含兩部分:檢測和處理。
把事務等待列表和鎖等待信息列表通過事務信息進行wait-for graph 檢測废亭，如果發(fā)現(xiàn)有閉環(huán)国章，則回滾undo log 量少的事務；死鎖檢測本身也會算檢測本身所需要的成本豆村，以便應對檢測超時導致的意外情況液兽。
3.1 死鎖檢測
當InnoDB事務嘗試獲取(請求)加一個鎖，并且需要等待時掌动，InnoDB 會進行死鎖檢測. 正常的流程如下:
InnoDB初始化一個事務四啰，當事務嘗試申請加鎖，并且需要等待時(
wait_lock)粗恢，innodb會開始進行死鎖檢測(deadlock_mark)進入到
lock_deadlock_check_and_resolve()函數(shù)進行檢測死鎖和解決死鎖柑晒。檢測死鎖過程中，是由計數(shù)器來進行限制次數(shù)的眷射，在等待wait-for graph 檢測過程中遇到超時或者超過閾值匙赞，則停止檢測。
死鎖檢測的邏輯之一是等待圖的處理過程凭迹，如果通過鎖的信息和事務等待鏈構造出一個圖罚屋，如果圖中出現(xiàn)回路，就認為發(fā)生了死鎖嗅绸。
死鎖的回滾，內部代碼的處理邏輯之一是比較undo的數(shù)量撕彤，回滾undo數(shù)量少的事務鱼鸠。
3.2 如何處理死鎖
《數(shù)據(jù)庫系統(tǒng)實現(xiàn)》里面提到的死鎖處理:
超時死鎖檢測:當存在死鎖時，想所有事務都能同時繼續(xù)執(zhí)行通常是不可能的羹铅，因此蚀狰，至少一個事務必須中止并重新開始。超時是最直接的辦法职员，對超出活躍時間的事務進行限制和回滾麻蹋。
等待圖：等待圖的實現(xiàn)，是可以表明哪些事務在等待其他事務持有的鎖焊切，可以在數(shù)據(jù)庫的死鎖檢測里面加上這個機制來進行檢測是否有環(huán)的形成。
通過元素排序預防死鎖:這個想法很美好，但現(xiàn)實很殘酷,通常都是發(fā)現(xiàn)死鎖后才去想辦法解決死鎖的原因涡相。
通過時間戳檢測死鎖:對每個事務都分配一個時間戳趁尼，根據(jù)時間戳來進行回滾策略。
四嚎尤、Innodb 的鎖類型
首先我們要知道對于MySQL有兩種常規(guī)鎖模式
LOCK_S（讀鎖荔仁，共享鎖）
LOCK_X（寫鎖，排它鎖）
最容易理解的鎖模式，讀加共享鎖(in share mode)乏梁，寫加排它鎖.次洼。其次對于唯一性檢測堵塞來講一般是LOCK_S。有如下幾種鎖的屬性
LOCK_REC_NOT_GAP （記錄本身加鎖）
LOCK_GAP （本記錄和上一條記錄之間的間隙遇骑，LOCK_GAP和LOCK_GAP是兼容的）
LOCK_ORDINARY （同時鎖記錄和GAP滓玖，也即Next Key鎖）
LOCK_INSERT_INTENTION （插入意向鎖，其實是特殊的GAP鎖质蕉，用于堵塞Insert操作）
鎖的屬性可以與鎖模式任意組合势篡。例如:
lock->type_mode 可以是Lock_X 或者Lock_S
locks gap before rec 表示為gap鎖：lock->type_mode & LOCK_GAP
locks rec but not gap 表示為記錄鎖，非gap鎖：lock->type_mode & LOCK_REC_NOT_GAP
insert intention 表示為插入意向鎖：lock->type_mode & LOCK_INSERT_INTENTION
waiting 表示鎖等待：lock->type_mode & LOCK_WAIT
關于Innodb 鎖的詳細介紹 可以移步 官方文檔 或者 MySQL · 引擎特性 · InnoDB 事務鎖系統(tǒng)簡介
五模暗、鎖信息解析
下面是一個典型的唯一鍵堵塞輸出
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s),undo log entries 1MySQL thread id 16253, OS thread handle 139825964828416, query id 75730 localhost root updateinsert into testunq1 values(2,'gaop11')
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 3 n bits 72 index PRIMARY of table `txc`.`testunq1` trx id 11593 lock mode S locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000002d44; asc -D;;
2: len 7; hex c1000003450110; asc E ;;
3: len 5; hex 67616f7031; asc gaop1;;
我們下面來解析一下部分可能不太好理解的部分禁悠，以便大家以后能夠更清楚的理解它的含義：
- infimum和supremum
一個page中包含這兩個偽記錄。頁中所有的行未刪除(或刪除未purge)的行邏輯上都連接到這兩個虛擬列之間兑宇，表現(xiàn)為一個邏輯鏈表數(shù)據(jù)結構碍侦，其中supremum偽記錄的鎖始終為next_key_lock。
- LOCK WAIT 2 lock struct(s)
這是LOCK的內存結構體源碼中用lock_t表示其可以包含
lock_table_t tab_lock;/*!< table lock */
lock_rec_t rec_lock;/*!< record lock */
一般來說Innodb上鎖都會對表級加上IX隶糕，這占用一個結構體瓷产。然后分別對相關的記錄進行加鎖，每一個BLOCK會占用這樣一個結構體枚驻。
- 1 row lock(s)
這個信息描述了當前事務加鎖的行數(shù)濒旦，他是所有l(wèi)ock struct結構體中排除table lock以外所有加鎖記錄的總和。
- undo log entries 1
大約等于已經(jīng)修改的記錄數(shù)再登，每修改一行都會占用一個 undo log entries尔邓。
- n bits 72
和這個page相關的鎖位圖的大小，每一行記錄都有1 bit的位圖信息與其對應锉矢，用來表示是否加鎖梯嗽，并且始終預留64bit。例如我的表有9條數(shù)據(jù)沽损，同時包含infimum和supremum虛擬記錄即 64+9+2 bits灯节，即75bits但是必須被8整除向上取整為一個字節(jié)，結果也就是就是80 bits绵估。注意不管是否加鎖每行都會對應一個bit的位圖炎疆。
- space id 65 page no 3
物理塊所在位置。
- heap no 3
heap no存儲在fixed_extrasize 中壹士。heap no 為物理存儲填充的序號磷雇，頁的空閑空間掛載在page free鏈表中(頭插法)可以重用，但是重用此heap no不變躏救，如果一直是insert 則heap no 不斷增加唯笙，并不是按照ROWID(主鍵)排序的邏輯鏈表順序螟蒸，而是物理填充順序。
- lock mode S locks
對應前面的LOCK_S崩掘。
- locks rec but not gap waiting
對應前面的LOCK_REC_NOT_GAP七嫌，并且處于堵塞狀態(tài)。
- 逐步加鎖
如果細心的朋友應該會發(fā)現(xiàn)在show engine 中事務信息中的row lock在對大量行進行加鎖的時候會不斷的增加苞慢，因為加行鎖最終會調用lock_rec_lock逐行加鎖诵原，這也會增加了大數(shù)據(jù)量加鎖的觸發(fā)死鎖的可能性。
六挽放、Innodb 不同事務加鎖類型
例子: update tab set x=1 where id= 1 ;
索引列是主鍵绍赛，RC隔離級別,對記錄記錄加X鎖
索引列是二級唯一索引，RC隔離級別辑畦。若id列是unique列吗蚌，其上有unique索引。那么SQL需要加兩個X鎖纯出，一個對應于id unique索引上的id = 10的記錄蚯妇，另一把鎖對應于聚簇索引上的[name='d',id=10]的記錄。
索引列是二級非唯一索引暂筝，RC隔離級別 若id列上有非唯一索引箩言，那么對應的所有滿足SQL查詢條件的記錄，都會被加鎖焕襟。同時陨收，這些記錄在主鍵索引上的記錄，也會被加鎖胧洒。
-
索引列上沒有索引畏吓，RC隔離級別
若id列上沒有索引，SQL會走聚簇索引的全掃描進行過濾卫漫，由于過濾是由MySQL Server層面進行的。因此每條記錄肾砂，無論是否滿足條件列赎，都會被加上X鎖。但是镐确，為了效率考量包吝，MySQL做了優(yōu)化，對于不滿足條件的記錄源葫，會在判斷后放鎖诗越，最終持有的，是滿足條件的記錄上的鎖息堂，但是不滿足條件的記錄上的加鎖/放鎖動作不會省略嚷狞。同時块促，優(yōu)化也違背了2PL的約束。
索引列是主鍵床未，RR隔離級別 對記錄記錄加X鎖
索引列是二級唯一索引竭翠，RR隔離級別 對表加上兩個X鎖，唯一索引滿足條件的記錄上一個薇搁，對應的聚簇索引上的記錄一個斋扰。
索引列是二級非唯一索引，RR隔離級別
結論:Repeatable Read隔離級別下啃洋，id列上有一個非唯一索引传货，對應SQL:delete from t1 where id = 10;
首先，通過id索引定位到第一條滿足查詢條件的記錄宏娄，加記錄上的X鎖问裕，加GAP上的GAP鎖，然后加主鍵聚簇索引上的記錄X鎖绝编，然后返回僻澎；然后讀取下一條，重復進行十饥。直至進行到第一條不滿足條件的記錄[11,f]窟勃，此時，不需要加記錄X鎖逗堵，但是仍舊需要加GAP鎖秉氧，最后返回結束。
- 索引列上沒有索引蜒秤，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:
如果不等: 則完成插入（結束） 如果相等: 再判斷P是否有鎖漂问， a 如果沒有鎖:報1062錯誤(duplicate key),說明該記錄已經(jīng)存在，報重復值錯誤 b 加S-lock,說明該記錄被標記為刪除, 事務已經(jīng)提交女揭，還沒來得及purge c 如果有鎖: 則加S-lock,說明該記錄被標記為刪除蚤假，事務還未提交.
delete 的加鎖方式
在非唯一索引的情況下，刪除一條存在的記錄是有gap鎖吧兔，鎖住記錄本身和記錄兩邊的gap
在唯一索引和主鍵的情況下刪除一條存在的記錄磷仰，因為都是唯一值，進行刪除的時候境蔼，是不會有gap存在
非唯一索引灶平，唯一索引和主鍵在刪除一條不存在的記錄伺通，均會在這個區(qū)間加gap鎖
通過非唯一索引和唯一索引去刪除一條標記為刪除的記錄的時候，都會請求該記錄的行鎖民逼，同時鎖住記錄之前的gap
RC 情況下是沒有gap鎖的泵殴，除了遇到唯一鍵沖突的情況，如插入唯一鍵沖突拼苍。
七笑诅、如何查看死鎖
-
查看事務鎖等待狀態(tài)情況
select * from information_schema.innodb_locks; select * from information_schema.innodb_lock_waits; select * from information_schema.innodb_trx;下面的查詢可以得到當前狀況下數(shù)據(jù)庫的等待情況:
select r.trx_id wait_trx_id, r.trx_mysql_thread_id wait_thr_id, r.trx_query wait_query, b.trx_id block_trx_id, b.trx_mysql_thread_id block_thrd_id, b.trx_query block_query from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id =w.requesting_trx_id -
打開下列參數(shù)，獲取更詳細的事務和死鎖信息疮鲫。
innodb_print_all_deadlocks = ON innodb_status_output_locks = ON -
查看innodb狀態(tài)(包含最近的死鎖日志)
show engine innodb status;
八吆你、如何盡可能避免死鎖
事務隔離級別使用read committed和binlog_format=row ，避免RR模式帶來的gap鎖競爭俊犯。
合理的設計索引,區(qū)分度高的列放到組合索引前列妇多，使業(yè)務sql盡可能通過索引定位更少的行，減少鎖競爭燕侠。
調整業(yè)務邏輯SQL執(zhí)行順序者祖，避免update/delete 長時間持有鎖的SQL在事務前面，(該優(yōu)化視情況而定)绢彤，在第12節(jié)中我們也分析過如果通過binlog尋找長期不提交的事務七问。
選擇合理的事務大小，小事務發(fā)生鎖沖突的幾率也更小茫舶。
5.7.15 版本之后提供了新的功能
innodb_deadlock_detect參數(shù),可以關閉死鎖檢測械巡，提高并發(fā)TPS,但是要注意設置鎖等待時間innodb_lock_wait_timeout。
轉載自：yangyidba
原文鏈接：https://mp.weixin.qq.com/s/x5ReO1Sb0zHpd_FNuaiZ9A
