一 背景
最近線上消費MetaQ的服務頻繁報SQL死鎖異常，雖然最終可以基於事務自動回滾和邏輯重試保證最終正確性，但若一直放任不管，海量報警日誌會掩蓋真正需要緊急處理的異常，同時頻繁回滾也會降低消費端的吞吐量。個人通過分析線上服務日誌、MySQL死鎖日誌、梳理MySQL在RR級別下的鎖機制，找到了真正的問題所在，並對業務處理邏輯進行了優化，特在此整理出來，互相學習提升，如果文中有錯誤的地方歡迎指正。
二 知識儲備
正所謂“工欲善其事，必先利其器”，在具體介紹CASE背景和解決方案前，先對需要系統瞭解的知識點進行詳細介紹，以便大家能夠快速理解解決方案。
死鎖通常是因為兩個及以上事務發生了死循環鎖依賴，此時不得不回滾來釋放鎖，那麼事務是個什麼東西？
1 事務
為什麼需要事務？
我們在業務實現時，經常需要保證某一批SQL能夠具備ACID特性，如果沒有事務，在應用裡自己保證將會變得非常複雜，InnoDB引擎引入事務機制，極大簡化了我們在此方面的編程模型。
ACID的實現機制是什麼？
-
原子性（Atomicity）：事務內SQL要麼同時成功要麼同時失敗 ，基於UndoLog實現。
-
一致性（Consistency）：系統從一個正確態轉移到另一個正確態，由應用通過AID來保證，並非數據庫的責任。
-
隔離性（Isolation）：控制事務併發執行時數據的可見性，基於鎖和MVCC實現。
-
持久性（Durability）：提交後一定存儲成功不會丟失，基於RedoLog實現。
下面簡單說下RedoLog、UndoLog在整個執行過程中的流程（此部分可以掠過）：
為什麼需要UndoLog？
InnoDb為支持回滾和MVCC，需要舊數據存檔，UndoLog就負責存儲這些數據，當更新BufferPool數據前，先將之前數據存入UndoLog。
為什麼需要RedoLog？
BufferPool是隨機IO以頁為單位，性能損耗很大，不可每次提交都同步刷盤，需要後續異步進行。不能同步刷就會有一個問題，如果MySQL宕機，而事務已提交在BufferPool的數據還沒有刷到磁盤，就會導致數據丟失持久性無法保證。為此引入RedoLog，這個文件IO是順序追加IO且以修改為單位，性能很高，每次事務提交持久化RedoLog到磁盤也不會對性能造成太大影響，如果宕機可以通過重啟從redoLog恢復丟失數據。
RedoLog高性能？
映射一段連續的存儲空間，保證順序IO，數據先寫入Buffer，後一次性批量將事務數據寫入磁盤。
2 鎖
下面咱們說說InnoDB鎖機制（此處重點關注）。
為了控制事務併發時的數據安全，在不同隔離級別下會通過不同的協同機制進行處理。傳統隔離機制，完全由鎖(LBCC)來處理，但是這樣只能滿足讀讀併發，會對性能造成很大影響，故而出現了支持讀寫併發的MVCC。因為MVCC不涉及此次背景，也不想羅列鎖各種類型（避免讓大家直接暈在這裡），就簡單直接的列出update、delete、insert的加鎖情況（RC和RR不一樣）。
Update & Delete語句加鎖
1）聚簇索引（查詢命中）
UPDATE students SET score = 100 WHERE id = 15;
RC、RR都是對聚簇索引加X鎖。
2）聚簇索引（查詢未命中）
UPDATE students SET score = 100 WHERE id = 16;
RC不加鎖，RR在16之前和之後的範圍里加GAP鎖。
3）二級唯一索引（查詢命中）
UPDATE students SET score = 100 WHERE no = 'S0003';
RC、RR會對二級和聚簇索引都加X鎖（防止其他事務通過聚簇改數據）。
4）二級唯一索引（查詢未命中）
UPDATE students SET score = 100 WHERE no = 'S0008';
RC不加鎖，RR只在二級索引加GAP。
5）二級非唯一索引（查詢命中）
UPDATE students SET score = 100 WHERE name = 'Tom';
RC對二級和聚簇加X鎖，RR對二級加X鎖和Gap對聚簇加X鎖。
6）二級非唯一索引（查詢未命中）
UPDATE students SET score = 100 WHERE name = 'John';
RC不加鎖，RR只在二級索引加GAP。
注：以上圖片源自https://zhuanlan.zhihu.com/p/245584417
INSERT語句加鎖
-
為了防止幻讀，如果記錄之間加有GAP鎖，此時不能INSERT。
-
如果INSERT的記錄和已有記錄造成唯一鍵衝突，此時不能INSERT。
三 線上CASE
1 分析服務線上日誌
發現死鎖是兩個事務對同一個表先delete後insert交叉進行引起的：
delete from db.table where creativeid=102(且刪除條數為0)
delete fromdb.tablewhere creativeid=103(且刪除條數為0)
insert intodb.table (creativeid) values (102)
insert intodb.table (creativeid) values (103)2 分析MySQL死鎖日誌
可見事務1要對一個已被間隙鎖控制的記錄進行插入意向鎖錄入，遂進入阻塞等待間隙鎖釋放，而恰巧另一個事務也同樣要對一個被間隙鎖控制的記錄進行插入意向鎖錄入，阻塞等待，當兩個事務間隙鎖碰巧有交集時就進入了死循環最後死鎖。
3 梳理解決方案
-
降低隔離級別為RC，避免間隙鎖（降級後會有不可重複讀和幻讀問題）。
-
設置InnoDB在RR級別下不使用間隙鎖（關閉後會有幻讀問題）。
-
刪除前先判斷是否存在，存在再刪除，可以完全避免死鎖（會導致重複數據錄入）。
在極端情況下，兩個事務同時執行Select都不存在然後Insert，導致重複數據錄入。
解決方案：
-
方案1：select for update（會降低併發度）。
-
方案2：加唯一索引，捕獲異常回滾不執行。
-
方案3：若允許極端少數重複數據（僅文案展示），則無需處理。
另外也要注意儘量避免大事務，它不僅會降低併發還會提高死鎖機率。
最終解決方案採用先判斷再刪除，目前涉及表為文案展示，允許極端情況下少量數據重複，故而暫不做絕對唯一處理。
4 方案3原理詳解
還原線上場景：假設表中有1，6兩條數據，兩個事務分別要對不存在的2、5進行先刪後插，且交叉執行。
假設表中不存在2和5對應記錄，只有1和6
可見T1和T2的插入意向鎖都要等待對方釋放Gap鎖，死循環。
現在我們修改邏輯，在刪除前先判斷，只有存在記錄才進行delete操作。
假設表中2和5都存在
可見事務1和事務2的間隙鎖範圍不重疊，都可以成功施加插入意向鎖。
我們再羅列另外一種情況，就是2或5只存在一個，會不會出現死鎖呢？
假設表中2存在
可見雖然事務2可能插入意向鎖記錄被事務1佔據，但是不會有死循環發生，等到事務1執行完釋放鎖就可以繼續進行了。
綜上所述，方案3可以完全避免死鎖問題。
四 死鎖場景分享
死鎖案例一
死鎖案例二
25和26記錄都不存在，A和B並沒有更新任何記錄，但是由於數據庫隔離級別為RR，所以會在 (20, 30) 之間加上間隙鎖。之後A和B分別執行 INSERT 要插入25和26，需要在 (20, 30) 之間加插入意向鎖，插入意向鎖和間隙鎖衝突，所以兩個事務互相等待，最後形成死鎖。
死鎖案例三
加鎖是一條記錄一條記錄挨個加鎖，如果兩條 SQL 語句的加鎖順序不一樣，也可能會導致死鎖。A 的加鎖順序為：id = 20 -> 30，B 的加鎖順序為：id = 30 -> 20，正好相反，所以會導致死鎖。
死鎖案例四
REPLACE INTO和INSERT ON DUPLICATE UPDATE。
這兩個語句雖然原子化“存在則更新，不存在則插入”的語義，但在MySQL內部還是被拆為多個操作步驟，且在某些版本(5.7)會引入GAP鎖來保證數據完整性，從而導致高併發情況下產生死鎖。