【MySQL】死鎖案例之七

一前言

死鎖，其實是一個很有意思也很有挑戰的技術問題，大概每個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 測試用例

	sess1	sess2
	begin;	begin;
T1	update ty set c3=2 where c2=4;
T2		update ty set c3=2 where c2=4;
T3	insert into ty (c1,c2,c3) values(3,4,2);
T4		insert into ty (c1,c2,c3) values(3,4,2); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2.4 死鎖日誌

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)

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鎖釋放) 構成迴圈等待，進而導致死鎖。