死鎖案例十五
- 2020 年 2 月 13 日
- 筆記
一 前言
死鎖，其實是一個很有意思也很有挑戰的技術問題，大概每個DBA和部分開發同學都會在工作過程中遇見 。關於死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。
二 案例分析
2.1 業務場景
業務上2條update語句，每條更新多行導致死鎖。
2.2 環境說明
MySQL 5.7.22 事務隔離級別為RC模式。
2.3 死鎖日誌
LATEST DETECTED DEADLOCK ------------------------ 2020-01-10 18:10:18 0x7fc8b6843700 *** (1) TRANSACTION: TRANSACTION 429649221, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 8 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id 8853551, OS thread handle 140496048645888, query id 1105998873 10.210.106.46 test updating UPDATE x SET select_state = 1, updated = UNIX_TIMESTAMP() where state = 1 AND iid = 245464472 AND gid=454733404 AND sid=36280812 AND actid=0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 243 page no 37236 n bits 104 index PRIMARY of table `test`.`x` trx id 429649221 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 429649224, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4997 mysql tables in use 3, locked 3 8 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 8844064, OS thread handle 140500032304896, query id 1105998875 10.210.105.14 test updating UPDATE x SET select_state = 1, updated = UNIX_TIMESTAMP() where state = 1 AND iid = 245464472 AND gid=454731534 AND sid=36279265 AND actid=0 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 243 page no 37236 n bits 104 index PRIMARY of table `test`.`x` trx id 429649224 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 243 page no 11188 n bits 792 index idx_identify_state of table `test`.`x` trx id 429649224 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2) ------------
2.4 分析死鎖日誌
這個死鎖案例比較特別2個事務各只有一條update導致死鎖。不像其他案例，一個事務有2條或者2條以上。分析起來有點小困難，比較難模擬。
事務二 持有 主鍵記錄的行鎖，位置在 space id243pageno37236 ，並申請二級索引 idx_identify_state的行鎖。其中 idx_identify_state 是(iid,state)的組合索引。
事務一 申請 主鍵記錄的行鎖 位置在 space id243pageno37236,該主鍵的行鎖被事務二持有，其實事務一還持有輔助索引 idx_identify_state的行鎖，只是沒有顯示出來，否則事務二不會等待申請 idx_identify_state的行鎖。但是如果2條sql 都是通過 idx_identify_state 索引訪問記錄就不會導致死鎖了，因為通過相同的索引訪問記錄，2個事務加鎖的順序一樣，串行加鎖導致其中一個sql等待而非死鎖 ，導致死鎖的核心邏輯是2個或者以上的事務訪問相同記錄加鎖的順序不一致，產生循環等待導致死鎖。
分析到這裡，根據經驗猜測，要麼2個sql選擇了不同的輔助索引(但是如果是不同的索引，死鎖日誌裏面應該是等待主鍵的行鎖，不應該出現等待輔助索引的行鎖)，要麼是遇到 index_merge 導致的死鎖。經過業務死鎖復現 ，確定是 index_merge 導致的死鎖。sql 的執行計劃如下:
從執行計劃來 Usingintersect(idx_identify_gid,idx_identify_state) SQL通過2個索引訪問記錄然後取交集。
2.5 場景分析
為了更透徹的理解該案例死鎖的加鎖順序，接下來我們繼續分析，把數據脫敏之後得到的2個事務訪問的數據集合:
|id st gid | | ---+---+-------+-------+ |1 | 2 | 47812 | 事務二 | |2 | 2 | 42870 | 事務一 | |3 | 2 | 42870 | 事務一 | |4 | 2 | 47812 | 事務二 | |5 | 1 | 47812 | 事務二 | |6 | 1 | 42870 | 事務一 |
通俗一點的來說MySQL是通過輔助索引訪問多條數據，逐行加鎖，先對輔助索引加鎖，然後針對對應的主鍵記錄加鎖。
把上面的數據的輔助索引和主鍵記錄抽象出來如下關係圖(畫的有點丑，不影響核心意義 ^_^).
通過執行計劃推測MySQL 先通過 idx_identify_gid訪問數據，然後再通過 idx_identify_state 訪問數據。
事務一的加鎖記錄順序
1. 針對輔助索引 gid=42870 三行記錄加上行鎖。 2. 通過輔助索引包含的主鍵，鎖定主鍵為 2 3 6 的記錄。 3. 針對輔助索引 state=1 的兩行記錄加上行鎖。 4. 針對state=1 對應的主鍵 5 6 加行鎖。
事務二的加鎖記錄順序
1. 針對輔助索引 gid= 47812 三行記錄加上行鎖。 2. 通過輔助索引包含的主鍵，鎖定主鍵為 1 4 5 的記錄。 3. 針對輔助索引 state=1 的兩行記錄加上行鎖。 4. 針對state=1 對應的主鍵 5 6 加行鎖。
死鎖產生時序圖
如何解決呢？
1 關閉index_merge 特性。
2 優化索引。
3 或者強制走其中一個索引。
三 小結
MySQL是否會發生死鎖，並不在於事務中有多少條SQL語句，而是在於:兩個(或以上)的Session加鎖的順序不一致。分析死鎖要充分理解死鎖日誌，遇到比較難的場景，可以根據核心信息多做推測。當然找開發分析業務流程會更有效果。
死鎖系列我已經寫了差將近20篇文章了，包括死鎖日誌分析，insert加鎖，還有十幾篇案例分析，等收集完20篇案例，除非遇到特別有意思的案例,就封筆不寫嘍。
