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

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='iok', update_date='2017-02-27 11:03:02' 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. 解決策

を解決するには、tel_id と task_id UNIQUE (ユニークなインデックス) の 2 つのフィールドを通じて確立してみてください。 (テーブル内の大量のデータがオンライン ビジネスに影響を及ぼさないように、最初にクエリを実行してから主キー ID に基づいて更新することもできます)。

この方法で解決した後、問題は再発しませんでした。

あなたの問題が私が遭遇したものと似ている場合は、それに応じて解決してみてください。