【MySQL】死鎖案例之六

一 前言

死鎖，其實是一個很有意思也很有挑戰的技術問題，大概每個DBA和部分開發同學都會在工作過程中遇見 。關于死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。

二 案例分析

2.1 環境說明

MySQL 5.6.24 事務隔離級別為RR

create table tx (
  id int not null primary key auto_increment ,
  c1 int not null default 0,
  c2 int not null default 0,
  key idx_c1(c1)
) engine=innodb ;
insert into tx values(24,3,4),(25,3,4),
(26,3,4),(30,5,8);

2.2 測試用例

2.3 死鎖日志

----------------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-03-27 15:40:40 0x7f75cafce700
*** (1) TRANSACTION:
TRANSACTION 1850, ACTIVE 20 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 379040, OS thread handle 140143994337024, 
query id 1521958 localhost root updating
update tx set c2=8 where c1=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table 
`test`.`tx` trx id 1850 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1849, ACTIVE 32 sec updating or deleting, 
thread declared inside InnoDB 4999
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 379016, OS thread handle 140143893473024, 
query id 1521976 localhost root updating
delete from tx where id=30
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of 
table `test`.`tx` trx id 1849 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 5 n bits 72 index idx_c1 of 
table `test`.`tx` trx id 1849 lock_mode X locks rec but not 
gap waiting
*** WE ROLL BACK TRANSACTION (1)

2.4 分析死鎖日志

首先要理解的是 對同一個字段申請加鎖是需要排隊的。

其次表ty中索引idx_c1為非唯一普通索引，我們根據事務執行的時間順序來解釋，這樣比較好理解。

T1: sess2 執行select for update 操作持有記錄id=30的主鍵行鎖:PRIMARY of table test.tx lock_mode X locks rec but not gap

T2: sess1 語句update通過普通索引idx_c1更新c2,先獲取idx_c1 c1=5的X鎖lock_mode X locks rec but not gap,然后去申請對應主鍵id=30的行鎖，但是sess2 已經持有主鍵的行鎖，于是sess1 等待。

T3: sess2 執行根據主鍵id=30刪除記錄，需要申請id=30的行鎖以及c1=5的索引行鎖。但是sess1 以及持有該鎖，故會出現index idx_c1 of table test.tx trx id 1849 lock_mode X locks rec but not gap waiting

sess2(delete)等待sess1(update),sess1(update)等待sess2(select for update) 循環等待，造成死鎖。

對于RDBMS系統出現死鎖的根本原因都可以概括為:不同的事務加鎖的順序不一樣導致循環等待，進而導致死鎖。

2.5 解決方法

修改sess1 的update 為根據主鍵來更新 也即 update tx set c2=x where id=30，把加鎖方式改為順序加鎖,申請主鍵id的鎖，避免通過交叉加鎖，相互申請對方持有的鎖。

三 小結

上面的案例中出現死鎖是由于不同會話對普通索引idx_c1和主鍵相互競爭導致循環等待而出現死鎖的。生產過程中遇到高并發更新同一行的的時候可以考慮避免通過不同的索引進行更新，進而避免死鎖。

推薦閱讀

如何閱讀死鎖日志

漫談死鎖

死鎖案例之一

死鎖案例之二

死鎖案例之三

死鎖案例之四

死鎖案例之五