一次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并不一定都是行級鎖。
相關參考資料片段如下：
MySQL?InnoDB鎖機制之Gap?Lock、Next-Key?Lock、Record?Lock解析 http://www.php.cn/ 4、鎖選擇 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（間隙鎖）。
MySQL?InnoDB?鎖表與鎖行 http://www.php.cn/ 由于InnoDB預設是Row-Level?Lock，所以只有「明確」的指定主鍵，MySQL才會執行Row?lock?(只鎖住被選取的資料例)?，否則MySQL將會執行Table?Lock?(將整個資料表單給鎖住)。
根據分析結論，猜測是在更新_task_tel表時Where條件中tel_id和task_id沒有建立UNIQUE(唯一索引)原因;
五、解決
據此分析，嘗試通過tel_id和task_id兩個字段建立UNIQUE(唯一索引)來解決。 (也可以先查詢出來，然后根據主鍵ID來更新，這樣不會因表中數據量較大影響線上業務)。
通過此種方式解決后，問題沒有再重現。
如果你的問題和我遇到的類似，可以嘗試據此解決。
?以上就是MySQL死鎖問題解決的代碼詳細介紹的內容，更多相關內容請關注PHP中文網（www.php.cn）！
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
