【MySQL】死鎖案例之八

一 前言

死鎖其實是一個很有意思也很有挑戰的技術問題，大概每個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 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 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 分析死鎖日誌

T1 s1 執行insert操作，檢查唯一性且插入成功,持有c1=4記錄行的行鎖。

T2 s2 insert遇到唯一鍵衝突，申請加鎖Lock S Next-key Lock 日誌顯示為index uc1 of table test.ty trx id 1870 lock mode S waiting

T3 與s2相同,s3 insert遇到唯一鍵衝突，申請加鎖Lock S Next-key Lock 日誌顯示為index uc1 of table test.ty trx id 1870 lock mode S waiting

T4 sess1 執行commit操作, 此時sess2 和sess3 同時獲取Lock S Next-key Lock。

T5 應用收到唯一鍵衝突，sess2執行update 操作需要申請c=4的行鎖，與sess3的持有的Lock S Next-key Lock不相容，等待sess3釋放Lock S Next-key Lock。

T6 與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其實比較複雜，需要多動手實踐，理清加鎖流程。

推薦閱讀

如何閱讀死鎖日誌

漫談死鎖

死鎖案例之一

死鎖案例之二

死鎖案例之三

死鎖案例之四

死鎖案例之五

死鎖案例之六

死鎖案例之七