一、前言
死鎖，其實是一個很有意思也很有挑戰的技術問題，大概每個 DBA 和部分開發同學都會在工作過程中遇見 。關于死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。
二、案例分析
2.1 業務場景
業務開發同學想同步資料，他們的邏輯是通過 update 更新操作，如果更新記錄傳回的 affect_rows為0，然後就調用 insert 語句進行插入初始化。如果插入失敗則再進行更新操作，多個會話并發操作的情況下就出現死鎖。
2.2 環境說明
MySQL 5.6.24 事務隔離級别為 RR
-
create table ty (
-
id int not null primary key auto_increment ,
-
c1 int not null default 0,
-
c2 int not null default 0,
-
c3 int not null default 0,
-
unique key uc1(c1),
-
unique key uc2(c2)
-
) engine=innodb ;
-
insert into ty(c1,c2,c3)
-
values(1,3,4),(6,6,10),(9,9,14);
2.3 測試用例
2.4 死鎖日志
1. 2018-03-27 17:59:23 0x7f75bf39d700
2. *** (1) TRANSACTION:
3. TRANSACTION 1863, ACTIVE 76 sec inserting
4. mysql tables in use 1, locked 1
5. LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
6. MySQL thread id 382150, OS thread handle 56640, query id 28 localhost root update
7. insert into ty (c1,c2,c3) values(3,4,2)
8. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
9. RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1863 lock_mode X locks gap before rec insert intention waiting
10. *** (2) TRANSACTION:
11. TRANSACTION 1864, ACTIVE 65 sec inserting, thread declared inside InnoDB 5000
12. mysql tables in use 1, locked 1
13. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
14. MySQL thread id 382125, OS thread handle 40032, query id 62 localhost root update
15. insert into ty (c1,c2,c3) values(3,4,2)
16. *** (2) HOLDS THE LOCK(S):
17. RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1864 lock_mode X locks gap before rec
18. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
19. RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1864 lock mode S waiting
20. *** WE ROLL BACK TRANSACTION (2) 2.5 分析死鎖日志
首先我們要再次強調 insert 插入操作的加鎖邏輯。
第一階段: 唯一性限制檢查，先申請 LOCK_S + LOCK_ORDINARY
第二階段: 擷取階段一的鎖并且 insert 成功之後,插入的位置有 GAP 鎖：LOCK_INSERT_INTENTION，為了防止其他 insert 唯一鍵沖突。
新資料插入完成之後：LOCK_X + LOCK_REC_NOT_GAP
對于 insert 操作來說，若發生唯一限制沖突，則需要對沖突的唯一索引加上 S Next-key Lock。從這裡會發現，即使是 RC 事務隔離級别，也同樣會存在 Next-Key Lock 鎖，進而阻塞并發。然而，文檔沒有說明的是，對于檢測到沖突的唯一索引，等待線程在獲得 S Lock 之後，還需要對下一個記錄進行加鎖，在源碼中由函數row_ins_scan_sec_index_for_duplicate 進行判斷.
其次 我們需要了解鎖的相容性矩陣。
從相容性矩陣我們可以得到如下結論:
INSERT 操作之間不會有沖突。
GAP，Next-Key 會阻止 Insert。
GAP 和 Record，Next-Key 不會沖突。
Record 和 Record、Next-Key 之間互相沖突。
已有的 Insert 鎖不阻止任何準備加的鎖。
已經持有的 GAP 鎖會阻塞插入意向鎖 INSERT_INTENTION。
另外 對于通過唯一索引更新或者删除不存在的記錄，會申請加上 GAP 鎖。
分析
了解上面的基礎知識，我們開始對死鎖日志進行分析:
T1: sess1 通過唯一鍵更新資料，由于 c2=4 不存在，傳回 affect row 為 0，MySQL 會申請(3,6)之間的 GAP 鎖。
T2: sess2 的情況和 sess1 類似，也會申請(3,6)之間的 GAP 鎖，從上面的相容性矩陣來看兩個 GAP 鎖并不會沖突。
T3: sess1 根據 update 語句傳回 affect row 為 0，執行 insert 操作，此時需要申請插入意向鎖，sess2 會話持有的 GAP 鎖和 sess1 申請的插入意向鎖沖突，出現等待。
index uc2 of table test.ty trx id 1863 lock_mode X locks gap before rec insert intention waiting
T4:sess2 與 sess1類似，根據 update 語句傳回 affect row 為 0，執行 insert 操作。申請的插入意向鎖與sess1 的 update 語句持有的 GAP 鎖沖突。sess1（持有 GAP 鎖），sess2（持有 GAP 鎖），sess1（插入意向鎖等待 sess2 的 GAP 鎖釋放） sess2（插入意向鎖等待 sess1 的 GAP 鎖釋放） 構成循環等待，進而導緻死鎖。
2.6 解決方法
從業務場景的處理邏輯上看，業務需要發送兩次請求一次 update，一次 insert 才能完成業務邏輯，不夠友好和優化。
其實我們可以和開發同學溝通好，确認業務的幂等性，使用 insert on duplicate key的方式，沒有就插入，存在就更新，一次調用即可完成之前 2 次操作的功能，提高性能。