MySQL死鎖問題解決的程式碼詳細介紹

黄舟
發布: 2017-03-04 14:47:35
原創
1564 人瀏覽過

一次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)!


#
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
最新問題
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板