死鎖案例分析

錯誤日誌中關於此次死鎖的記錄如下，顯示的是兩個update語句產生了死鎖！

LATEST DETECTED DEADLOCK

------------------------

2021-01-15 18:12:40 7f9c01daf700

*** (1) TRANSACTION:

TRANSACTION 16175364313, ACTIVE 0 sec starting index read

mysql tables in use 3, locked 3

LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)

MySQL thread id 149648015, OS thread handle 0x7fa255658700, query id 24131125938 10.1.1.1 app_test  updating

update image_tmp_liu

SET handle_status = '0'

handle_describe = '系統異常'

where file_name = '2021011110194000010000000000170020723568.htm'

and file_type = 'LOAN_AUTHZ'

and ins_date = '20210115'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 6966 page no 1747854 n bits 472 index `idx_ins_date` of table `my_jiebei`.`image_tmp` trx id 16175364313 lock_mode X waiting

*** (2) TRANSACTION:

TRANSACTION 16175364307, ACTIVE 0 sec fetching rows

mysql tables in use 3, locked 3

9 lock struct(s), heap size 1184, 17 row lock(s), undo log entries 1

MySQL thread id 149645724, OS thread handle 0x7f9c01daf700, query id 24131125920 10.1.1.1 app_test  updating

update image_tmp_liu

SET handle_status = '1',

handle_describe = '成功'

where file_name = '2021010710194000010000000000950019899845.htm'

and file_type = 'LOAN_AUTHZ'

and ins_date = '20210115'

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 6966 page no 1747854 n bits 472 index `idx_ins_date` of table `my_jiebei`.`image_tmp` trx id 16175364307 lock_mode X

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 6966 page no 1345962 n bits 144 index `PRIMARY` of table `my_jiebei`.`image_tmp` trx id 16175364307 lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (1)

image_tmp_liu表的 ins_date列上是索引

一：產生死鎖的原因：

可以參考實戰45講的第21講

加鎖的規則如下：

原則 1：加鎖的基本單位是 next-key lock。希望你還記得，next-key lock 是前開後閉區間。

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

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

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

一個 bug：唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。

根據原則2 查詢過程中訪問到的物件才會加鎖，（給索引加鎖是給索引列上加鎖，不是給整行資料加鎖，也就是隻有訪問到的物件才會加鎖）。事務1更新的時候，透過索引 ins_date來查到主鍵值，然後回表透過主鍵來找到 handle_status和handle_describe的值，訪問到主鍵了，所以會給主鍵加鎖，然而主鍵也就是整行資料了（mysql innodb表是聚簇表，特別像oracle索引組織表），會嘗試給所有 ins_date = '20210115'的主鍵加x鎖，但是這個加鎖過程是一步一步的加的，並不是一次性加上的，又因為ins_date = '20210115'這個有多行資料（15行）。然後事務2也是會加這幾行的主鍵，同時又因為這兩個事務加鎖的步驟不通，也就是事務1加了部分的ins_date = '20210115'（例如主鍵為1 和2 的加上了x鎖）事務2加上部分ins_date = '20210115'（例如主鍵為3 和4加上x鎖 ），這樣當事務1又想去加主鍵為3 和4的資料，就需要等待，同理事務2也需要等待！

二： 可以藉助mysql 5.6的MRR特性來緩解此類的死鎖問題：

2.1 mrr緩解此案例死鎖的原理：

這裡的關鍵點在於回表訪問主鍵是一行一行的去回表的，所以給那需要加鎖的行加主鍵x鎖也不是原子的，而是一步一步的加的，如果能把這個回表的速度提升，那鎖定主鍵的時間便會降低，那產生死鎖的機率就會降低了，

MRR的最佳化在於， 並不是每次透過輔助索引就回表去取記錄， 而是將其rowid主鍵給快取起來，然後對rowid主鍵進行排序後，再去訪問記錄，這樣就能將隨機I/O轉化為順序I/O，從而大幅地提升效能。這個過程如下所示：

2.2 開啟MRR的新特性

然而，在MySQL當前版本中，基於成本的演算法過於保守，導致大部分情況下最佳化器都不會選擇MRR特性。為了確保最佳化器使用mrr特性，請執行下面的SQL語句：

set optimizer_switch='mrr=on,mrr_cost_based=off';