MySQL のデッドロック問題を解決する方法を共有する-mysql チュートリアル-php.cn

MySQL のデッドロック問題を解決する方法を共有する

PHPz

リリース： 2017-03-05 11:39:52

オリジナル

5599 人が閲覧しました

MySQL デッドロック問題を解決する方法を共有します

1. 環境

CentOS、MySQL 5.6.21-70、JPA
問題のシナリオ: システムには、更新されるたびにデータステータスのバッチ更新操作がスケジュールされています。数千レコードの場合、テーブル内のレコードの合計数は約 500W になります。

2. エラーログ

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

ログイン後にコピー

3. トラブルシューティング

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=&#39;iok&#39;, update_date=&#39;2017-02-27 11:03:02&#39; where tel_id=66042 and task_id=350199;

ログイン後にコピー

4. 分析

検索後関連情報については、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 を使用する理由:

a) まず、条件を満たすレコードに排他ロックが追加されていることを確認します。これにより、現在の非一意インデックスと対応する主キーインデックスの値がロックされます。

b)、ロックされた間隔では新しいデータが挿入できないことも確認します。
3) 更新条件がユニークインデックスの場合、レコードロックを使用します。

InnoDB は、一意のインデックスに基づいて対応するレコードを検索し、主キーのインデックス値と一意のインデックス値にレコードロックを追加します。ただし、Gap Lock（ギャップロック）は使用しないでください。
InnoDB はデフォルトで行レベルロックに設定されているため、主キーが「明確に」指定されている場合にのみ、MySQL は行ロック (選択されたデータのみをロック) を実行します。それ以外の場合、MySQL はテーブルロック (データフォーム全体をロックにロックします) を実行します。

分析の結論によれば、_task_telテーブルの更新時にWhere条件のtel_idとtask_idがUNIQUE(ユニークなインデックス)を確立していないことが原因と推測されます

5. 解決策