故障分析 | MySQL死鎖案例分析

一  背景

二 案例分析

2.1 業務邏輯

2.2 環境說明

事務隔離級別   REPEATABLE-READ

create table dl(
id int auto_increment primary key,
c1 int not null ,
c2 int not null,
key idx_c1(c1));

insert into dl(c1,c2) values (3,1),(3,2),(3,2),(3,3),(4,4),(5,5);

2.3 測試用例

2.4  死鎖日誌

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-12-03 16:43:59 140261132850944
*** (1) TRANSACTION:
TRANSACTION 1416764, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 15, OS thread handle 140261086668544, query id 283 localhost msandbox updating
update dl set c2=10 where c1=5

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` trx id 1416764 lock_mode X
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` trx id 1416764 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

*** (2) TRANSACTION:
TRANSACTION 1416759, ACTIVE 23 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140261085611776, query id 286 localhost msandbox updating
delete from dl where id=6

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` trx id 1416759 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` trx id 1416759 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (2)

2.5 死鎖分析

1. sess1 開啟一個事務，在T2 時刻執行 select for update ，持有 id=6 的 lock_mode X record lock.

2. sess2 在T3 時刻執行根據 c1=5 的更新，但是其加鎖順序是先在索引 idx_c1 上加鎖，順利加鎖，然後到申請加主鍵上 id=6 的鎖，發現 sess1 已經持有主鍵 id=6 的X的鎖，因此需要等待。如日誌中 (1) TRANSACTION: 中 WAITING FOR的提示RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table test.dl trx id 1416764 lock_mode X locks rec but not gap waiting

3. sess1 執行 delete id=6 的操作，由於事務本身已經持有了主鍵上的鎖，刪除記錄同時要對索引idx_c1上的記錄加上 lock_mode X record lock，發現該鎖已經被 sess2 持有，形成了死鎖條件，sess1 報錯，發生回滾。

2.6 如何解決

三 小結

原則 1：加鎖的基本單位是 next-key lock。

原則 2：查詢過程中訪問到的物件才會加鎖。

最佳化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。

最佳化 2：索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖。

在讀提交隔離級別下還有一個最佳化，即：語句執行過程中加上的行鎖，在語句執行完成後，就要把“不滿足條件的行”上的行鎖直接釋放了，不需要等到事務提交

本文關鍵字：#死鎖#  #MySQL#