分享一個MySQL死鎖問題解決的方法

分享一個MySQL死鎖問題解決的方法

一、環境

• CentOS, MySQL 5.6.21-70, JPA

• #問題情境：系統有定時批次更新資料狀態操作，每次更新上千筆記錄，表中總記錄數約500W。

二、錯誤日誌

2017-2-25 17:38:41 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Lock wait timeout exceeded; try restarting transaction
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
警告: SQL Error: 1213, SQLState: 40001
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Deadlock found when trying to get lock; try restarting transaction

三、檢查

Check InnoDB status for locks
mysql> SHOW ENGINE InnoDB STATUS;

Check MySQL open tables
mysql> SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions
mysql> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Check lock dependency - what blocks what
mysql> SELECT * FROM `information_schema`.`innodb_locks`;

排查後發現都是執行類似這樣的語句出現問題的：

update t_task_tel set state='iok', update_date='2017-02-27 11:03:02' where tel_id=66042 and task_id=350199;

四、分析

#搜尋相關資料後發現，原來MySQL InnoDB不一定都是行級鎖定。

相關參考資料片段如下：

鎖定選擇
1）、如果更新條件沒有走索引，例如執行」update from t1 set v2=0 where v2=5;」 ，此時會進行全表掃描，掃表的時候，要阻止其他任何的更新操作，所以上升為表鎖。
2）、如果更新條件為索引字段，但是並非唯一索引（包括主鍵索引），例如執行「update from t1 set v2=0 where v1=9;」 

#那麼此時更新會使用Next-Key Lock。使用Next-Key Lock的原因：
a）、首先要確保在符合條件的記錄上加上排他鎖，會鎖定當前非唯一索引和對應的主鍵索引的值；
b）、還要保證鎖定的區間不能插入新的資料。
3）、如果更新條件為唯一索引，則使用Record Lock（記錄鎖定）。
 
InnoDB根據唯一索引，找到對應記錄，將主鍵索引值和唯一索引值加上記錄鎖定。但不使用Gap Lock（間隙鎖）。
由於InnoDB預設是Row-Level Lock，所以只有「明確」的指定主鍵，MySQL才會執行Row lock (只鎖定被選取的資料例) ，否則MySQL將會執行Table Lock (將整個資料表單給鎖住)。

根據分析結論，猜測是在更新_task_tel表時Where條件中tel_​​id和task_id沒有建立UNIQUE(唯一索引)原因;

五、解決

據此分析，嘗試透過tel_id和task_id兩個欄位建立UNIQUE(唯一索引)來解決。 (也可以先查詢出來，然後根據主鍵ID來更新，這樣不會因表中資料量較大影響線上業務)。

透過此種方式解決後，問題沒有重現。

如果你的問題和我遇到的類似，可以嘗試據此解決。