ケースは次のとおりです:
Show innodb status を使用してエンジンのステータスを確認すると、デッドロックの問題が発見されました:
*** (1) TRANSACTION:
TRANSACTION 0 677833455、ACTIVE 0 sec、プロセス no 11393、OS スレッド ID 278546 インデックス読み取りの開始
使用中の mysql テーブル 1、ロック済み 1
LOCK WAIT 3 ロック構造、ヒープ サイズ 320
MySQL スレッド ID 83、クエリ ID 162348740 dcnet03 dcnet 更新の行を検索しています
update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061とMON_TIME
*** (1) このロックが許可されるのを待っています:
レコード ロック スペース ID 0 ページ番号 849384 n ビット 208 テーブル `dcnet_db/TSK_TASK` のインデックス `PRIMARY` trx id 0 677833455 lock_mode X ロックはレコードをロックしますが、ギャップは待機していません
レコード ロック、ヒープ番号 92 物理レコード: n_fields 11; コンパクト形式 0
0: 16 進数 80000000097629c; 1: 16 進数 00002866eaee; 110; ;; 3: 16 進数 800000000050b2; 4: 16 進数 800000000005426; f ;; 7: レン 23; 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: 16 進数 80000000000042;; 9: 16 進数 474bfa 2b; 10: 16 進数 800000000004; asc N$
;; * (2) トランザクション:
トランザクション 0 677833454、アクティブ 0 秒、プロセス番号 11397、OS スレッド ID 344086 更新または削除、InnoDB 内で宣言されたスレッド 499
使用中の mysql テーブル 1、ロックされた 1
3 ロック構造体、ヒープ サイズ 320、元に戻すログ エントリ 1
MySQL スレッド ID 84、クエリ ID 162348739 dcnet03 dcnet Update
TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 ページ番号 849384 n ビット 208 テーブル `dcnet_db/TSK_TASK` のインデックス `PRIMARY` trx id 0 677833454 lock_mode X は、rec をロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 92 物理レコード: n_fields 11 ; コンパクトな情報ビット 0
0: len 8; 16 進数 800000000097629c;; 1: 16 進数 00002866eaee; 2: 16 進数 00000d40040110; asc @ ;; 3: 16 進数 80000000000050b2; c P ;; 4: レン 8 ; 16 進数 80000000000502a; 5: 16 進数 8000000000005426;; 16 進数 75706c6f666972652e636e642e70687 0; asc アップロードファイア.com/hand.php;; 8: レン 800000000000042b; 8; hex 8000000000004e24; asc N$ ;;
*** (2) このロックが許可されるのを待っています:
レコード ロック スペース ID 0 ページ番号 843102 n ビット 600 テーブル `dcnet_db/TSK_TASK` のインデックス `KEY_TSK_TASK_MONTIME2` trx ID 0 lock_mode X はレコードをロックしますが
、ギャップ待ちではありません 395 PHYSICAL RECORD: n_fields 3; 圧縮形式 0
: len 8; 16 進数 8000000000 97629c; ;;
*** WE ROLL BACK TRANSACTION (1)
このデッドロックの問題には、システム監視タスクを保存するために使用される TSK_TASK テーブルが関係します。
ID: 主キー
:
STATUS_ID:タスクのステータス;
インデックス: KEY_TSKTASK_MONTIME2 (STATUS_ID、MON_TIME)。
分析の結果、関係する 2 つのステートメントに同じ TSK_TASK レコードが含まれるはずがないことがわかりました。なぜデッドロックが発生するのでしょうか?
MySQL 公式 Web サイトのドキュメントを調べたところ、これが MySQL のインデックス作成メカニズムに関連していることがわかりました。 MySQL の InnoDB エンジンは行レベルのロックを使用します。私の当初の理解では、レコードは直接ロックされていると考えられていましたが、実際にはそうではありません。
重要な点は次のとおりです。
レコードをロックする代わりに、インデックスがロックされます。UPDATE
および DELETE 操作中に、MySQL は WHERE 条件によってスキャンされたすべてのインデックス レコードをロックするだけでなく、隣接するキー値、いわゆるネクスト キーもロックします。ロック;
たとえば、ステートメント UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000 は、1000 以上の主キーを持つすべてのレコードをロックします。ステートメントが完了するまで、等しい主キーを持つレコードを操作することはできません。
非クラスター インデックスの場合は 10000
(非クラスター インデックス レコードがロックされている場合、対応する操作を完了するには、関連するクラスター インデックス レコードもロックする必要があります。
問題が発生した 2 つの SQL ステートメントを分析すると、問題を見つけるのは難しくありません。
when "update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME
「update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)」がほぼ同時に実行されるとすると、このステートメントはまず、STATUS_IDの値を更新する必要があるため、クラスタインデックス(主キー)をロックします。一部のインデックス レコードの KEY_TSKTASK_MONTIME2 の特定の部分をロックするためにも必要です。
このように、最初のステートメントは KEY_TSKTASK_MONTIME2 のレコードをロックして主キー インデックスを待機し、2 番目のステートメントは主キー インデックスのレコードをロックして KEY_TSKTASK_MONTIME2 のレコードを待機します。この場合、デッドロックが発生します。
著者は、最初のステートメントを分割することでデッドロックの問題を解決しました。
まず修飾 ID を見つけます。STATUS_ID=1061 および MON_TIME < date_sub(now(), INTERVAL 30 minutes); の TSK_TASK から ID を選択し、次にステータスを更新します。 update TSK_TASK set STATUS_ID= 1064 (….) の ID
この時点で、デッドロックの問題は完全に解決されました。