死鎖案例八
文 | 楊一 on 運維
一、前言
死鎖其實是一個很有意思也很有挑戰的技術問題，大概每個 DBA 和部分開發朋友都會在工作過程中遇見。關于死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。
二、案例分析
2.1 業務場景
業務上的主要邏輯:
首先執行插入資料，如果插入成功，則送出。如果插入的時候報唯一鍵沖突，則執行更新。 如果同時出現三個并發在執行資料初始化動作，sess1 插入成功，sess2 和 sess3 插入遇到唯一鍵沖突，插入失敗，則都執行執行更新，于是出現死鎖。
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 測試用例
為了友善分析死鎖日志，三個會話插入的 c3 的值分别為1 2 3 ，生産上其實是相同的值。
2.4 死鎖日志
-
2018-03-28 10:04:52 0x7f75bf2d9700
-
*** (1) TRANSACTION:
-
TRANSACTION 1870, ACTIVE 76 sec starting index read
-
mysql tables in use 1, locked 1
-
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
-
MySQL thread id 399265, OS thread handle 12, query id 9 localhost root updating
-
update ty set c3=5 where c1=4
-
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-
RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1870 lock_mode X locks rec but not gap waiting
-
*** (2) TRANSACTION:
-
TRANSACTION 1871, ACTIVE 32 sec starting index read, thread declared inside InnoDB 5000
-
mysql tables in use 1, locked 1
-
3 lock struct(s), heap size 1136, 2 row lock(s)
-
MySQL thread id 399937, OS thread handle 16, query id 3 localhost root updating
-
update ty set c3=5 where c1=4
-
*** (2) HOLDS THE LOCK(S):
-
RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1871 lock mode S
-
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-
RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1871 lock_mode X locks rec but not gap waiting
-
*** WE ROLL BACK TRANSACTION (2)
其實單單從日志上檢視隻看到兩個事務的 update 互相競争，在缺乏業務邏輯場景的情況下，很難得到有效思路。
2.5 分析死鎖日志
T2 s1 執行 insert 操作，檢查唯一性且插入成功,持有 c1=4 記錄行的行鎖。
T3 s2 insert遇到唯一鍵沖突，申請加鎖 Lock S Next-key Lock 日志顯示為 index uc1 of table test.ty trx id 1870 lock mode S waiting
T4 與 s2 相同, s3 insert 遇到唯一鍵沖突，申請加鎖 Lock S Next-key Lock 日志顯示為 index uc1 of table test.ty trx id 1870 lock mode S waiting
T5 sess1 執行 commit 操作, 此時 sess2 和 sess3 同時擷取 Lock S Next-key Lock。
T6 應用收到唯一鍵沖突，sess2 執行 update 操作需要申請 c=4 的行鎖，與 sess3的持有的 Lock S Next-key Lock 不相容，等待 sess3 釋放Lock S Next-key Lock。
T7 與sess2 類似 sess3 執行update 操作需要申請 c=4 的行鎖，與 sess2 的持有的 Lock S Next-key Lock 不相容，等待 sess2 釋放 Lock S Next-key Lock 。出現循環等待，發生死鎖。
2.6 解決方法
本案例的解決方式其實和前文 死鎖案例之七 一緻，使用 insert on duplicate key。案例七與本案例導緻死鎖業務邏輯極為相似，為什麼呢？因為都是同一組開發哥哥寫的。
三、小結
導緻死鎖的根本原因是不同僚務申請鎖的順序不一樣出現循環等待，開發同學在設計高并發的業務場景時，需要着重思考這一點，并且盡量規避業務場景設計不合理導緻死鎖。
另外就是 insert 的加鎖機制相對 update 其實比較複雜，需要多動手實踐，理清加鎖流程。
死鎖案例七
死鎖，其實是一個很有意思也很有挑戰的技術問題，大概每個 DBA 和部分開發同學都會在工作過程中遇見 。關于死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。
業務開發同學想同步資料，他們的邏輯是通過 update 更新操作，如果更新記錄傳回的 affect_rows為0，然後就調用 insert 語句進行插入初始化。如果插入失敗則再進行更新操作，多個會話并發操作的情況下就出現死鎖。
2.2 環境說明
-
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);
-
2018-03-27 17:59:23 0x7f75bf39d700
-
*** (1) TRANSACTION:
-
TRANSACTION 1863, ACTIVE 76 sec inserting
-
mysql tables in use 1, locked 1
-
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
-
MySQL thread id 382150, OS thread handle 56640, query id 28 localhost root update
-
insert into ty (c1,c2,c3) values(3,4,2)
-
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-
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
-
*** (2) TRANSACTION:
-
TRANSACTION 1864, ACTIVE 65 sec inserting, thread declared inside InnoDB 5000
-
mysql tables in use 1, locked 1
-
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
-
MySQL thread id 382125, OS thread handle 40032, query id 62 localhost root update
-
insert into ty (c1,c2,c3) values(3,4,2)
-
*** (2) HOLDS THE LOCK(S):
-
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
-
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-
RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1864 lock mode S waiting
-
*** WE ROLL BACK TRANSACTION (2)
首先我們要再次強調 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 鎖釋放） 構成循環等待，進而導緻死鎖。
從業務場景的處理邏輯上看，業務需要發送兩次請求一次 update，一次 insert 才能完成業務邏輯，不夠友好和優化。
其實我們可以和開發同學溝通好，确認業務的幂等性，使用 insert on duplicate key的方式，沒有就插入，存在就更新，一次調用即可完成之前 2 次操作的功能，提高性能。
最後想說關于解決死鎖問題的思路:
1. 具備紮實的鎖相關的基礎知識。
2. 單單根據死鎖日志其實比較難以判斷具體的 sql 執行情況，需要和開發同學溝通好，理清業務執行 sql 的邏輯，然後去模拟測試。