一 背景
死鎖，其實是一個很有意思也很有挑戰的技術問題，大概每個DBA和部分開發同學都會在工作過程中遇見 。
本次分享的一個死鎖案例是 涉及通過輔助索引的更新以及通過主鍵刪除導致的死鎖。希望能夠對想了解死鎖的朋友有所幫助。
二 案例分析
2.1 業務邏輯
select for update 表記錄并加上 x 鎖，查詢數據，做業務邏輯處理，然后刪除該記錄。還有其他業務邏輯要更新記錄，導致死鎖。
2.2 環境說明
數據庫 MySQL 8.0.30
事務隔離級別 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 死鎖分析
- sess1 開啟一個事務，在T2 時刻執行 select for update，持有id=6的lock_mode X record lock.
- 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
- sess1 執行 delete id=6 的操作，由于事務本身已經持有了主鍵上的鎖，刪除記錄同時要對索引idx_c1上的記錄加上 lock_mode X record lock，發現該鎖已經被sess2持有，形成了死鎖條件，sess1 報錯，發生回滾。
2.6 如何解決
本文中死鎖的原因是因為 sess2 通過輔助索引進行更新，因此推薦的避免死鎖方案是把sess2 使用輔助索引的更新改成基于主鍵進行更新，從而避免申請idx_c1上的加鎖造成循環等待產生死鎖。
三 小結
敲黑板 ，重點: 死鎖是因為不同事務對表記錄加鎖的順序不一致導致相互等待對方持有的鎖導致的。大家在分析死鎖的時候能基于該原則去分析理清業務的sql 邏輯，基本上都能解決大部分的問題場景。
另外文章的最后我們再次復習一下 MySQL 的加幾個基本原則，方便大家后面遇到死鎖案例進行分析:
原則 1：加鎖的基本單位是 next-key lock。
原則 2：查找過程中訪問到的對象才會加鎖。
優化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。
優化 2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖。
一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。
在讀提交隔離級別下還有一個優化，即：語句執行過程中加上的行鎖，在語句執行完成后，就要把“不滿足條件的行”上的行鎖直接釋放了，不需要等到事務提交
