MySQL デッドロックのトラブルシューティングプロセスの完全な記録

序文

これまで遭遇したデータベースのデッドロックはすべて、バッチ更新中のロック順序の不一致が原因でしたが、先週は非常に理解しにくいデッドロックに遭遇しました。この機会に、MySQL のデッドロックに関する知識と一般的なデッドロックのシナリオを再学習しました。多くの調査と同僚との議論を経て、私はついにデッドロック問題の原因を発見し、多くのことを得ることができました。私たちはバックエンドプログラマーですが、DBAほど深くロック関連のソースコードを分析する必要はありませんが、基本的なデッドロックのトラブルシューティング方法を習得できれば、日々の開発に大きなメリットをもたらします。

PS: この記事ではデッドロックの基礎知識は紹介しません。MySQL のロック原理については、この記事の参考資料に記載されているリンクを参照してください。

デッドロックの原因

まずはデータベースとテーブル状況を紹介します。社内の実際のデータを扱うため、以下はシミュレーションであり、具体的な分析には影響しません。

MySQL データベースバージョン 5.5 を使用し、トランザクション分離レベルはデフォルトの RR (Repeatable-Read) で、InnoDB エンジンを使用します。次のテストテーブルがあると仮定します。

テーブル「test」を作成します（
 `id` int(11) 符号なし NOT NULL AUTO_INCREMENT,
 `a` int(11) 符号なし DEFAULT NULL,
 主キー (`id`)、
 ユニークキー `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 デフォルトCHARSET=utf8;

テーブルの構造は非常にシンプルで、主キー id と別の一意のインデックス a があります。表のデータは次のとおりです。

mysql> テストから * を選択します。
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
+----+------+
セット内の 3 行 (0.00 秒)

デッドロックの原因となる操作は次のとおりです。

ステップ	取引 1	取引2
1		始める
2		a = 2 のテストから削除します。
3	始める
4	a = 2 のテストから削除します。(トランザクション 1 が停止しています)
5	デッドロックが発生しました: エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください	テスト（id、a）値（10、2）に挿入します。

次に、 SHOW ENGINE INNODB STATUS;を通じてデッドロックログを表示できます。

------------------------
最近検出されたデッドロック
------------------------
170219 13:31:31
*** （１）取引：
トランザクション 2A8BD、アクティブ 11 秒開始インデックス読み取り
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 2 ロック構造体、ヒープ サイズ 376、1 行ロック
MySQL スレッド ID 448218、OS スレッド ハンドル 0x2abe5fb5d700、クエリ ID 18923238 renjun.fangcloud.net 121.41.41.92 ルート更新中
a = 2 のテストから削除
*** (1) このロックが許可されるのを待機しています:
レコードロック スペース ID 0 ページ番号 923 n ビット 80 テーブル `oauthdemo`.`test` のインデックス `a` トランザクション ID 2A8BD ロック モード X 待機中
レコード ロック、ヒープ番号 3 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 00000002; 昇順 ;;
 1: 長さ 4; 16 進数 00000002; 昇順 ;;
*** （２）取引：
トランザクション 2A8BC、アクティブ 18 秒挿入
使用中の MySQL テーブル 1、ロックされているテーブル 1
4 つのロック構造体、ヒープ サイズ 1248、3 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 448217、OS スレッド ハンドル 0x2abe5fd65700、クエリ ID 18923239 renjun.fangcloud.net 121.41.41.92 ルート更新
テスト（id、a）の値（10、2）に挿入
*** (2) ロックを保持する:
レコード ロック スペース ID 0 ページ番号 923 n ビット 80 テーブル `oauthdemo`.`test` のインデックス `a` trx ID 2A8BC lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 3 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 00000002; 昇順 ;;
 1: 長さ 4; 16 進数 00000002; 昇順 ;;
*** (2) このロックが許可されるのを待機しています:
レコード ロック スペース ID 0 ページ番号 923 n ビット 80 テーブル `oauthdemo`.`test` のインデックス `a` トランザクション ID 2A8BC ロック モード S 待機中
レコード ロック、ヒープ番号 3 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 00000002; 昇順 ;;
 1: 長さ 4; 16 進数 00000002; 昇順 ;;
*** トランザクションをロールバックします (1)

分析する

デッドロックログの読み取り

デッドロックが発生した場合、最初のステップはデッドロックログを読むことです。デッドロックログは通常 2 つの部分に分かれています。最初の部分には、トランザクション 1 が待機しているロックが表示されます。

170219 13:31:31
*** （１）取引：
トランザクション 2A8BD、アクティブ 11 秒開始インデックス読み取り
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 2 ロック構造体、ヒープ サイズ 376、1 行ロック
MySQL スレッド ID 448218、OS スレッド ハンドル 0x2abe5fb5d700、クエリ ID 18923238 renjun.fangcloud.net 121.41.41.92 ルート更新中
a = 2 のテストから削除
*** (1) このロックが許可されるのを待機しています:
レコードロック スペース ID 0 ページ番号 923 n ビット 80 テーブル `oauthdemo`.`test` のインデックス `a` トランザクション ID 2A8BD ロック モード X 待機中
レコード ロック、ヒープ番号 3 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 00000002; 昇順 ;;
 1: 長さ 4; 16 進数 00000002; 昇順 ;;

ログから、トランザクション 1 が現在delete from test where a = 2を実行していることがわかります。このステートメントはインデックス a の X ロックを適用しているため、 lock_mode X waitingが表示されます。

ログの下半分には、トランザクション 2 が現在保持し、待機しているロックが表示されます。

*** （２）取引：
トランザクション 2A8BC、アクティブ 18 秒挿入
使用中の MySQL テーブル 1、ロックされているテーブル 1
4 つのロック構造体、ヒープ サイズ 1248、3 つの行ロック、UNDO ログ エントリ 2
MySQL スレッド ID 448217、OS スレッド ハンドル 0x2abe5fd65700、クエリ ID 18923239 renjun.fangcloud.net 121.41.41.92 ルート更新
テスト（id、a）の値（10、2）に挿入
*** (2) ロックを保持する:
レコード ロック スペース ID 0 ページ番号 923 n ビット 80 テーブル `oauthdemo`.`test` のインデックス `a` trx ID 2A8BC lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 3 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 00000002; 昇順 ;;
 1: 長さ 4; 16 進数 00000002; 昇順 ;;
*** (2) このロックが許可されるのを待機しています:
レコード ロック スペース ID 0 ページ番号 923 n ビット 80 テーブル `oauthdemo`.`test` のインデックス `a` トランザクション ID 2A8BC ロック モード S 待機中
レコード ロック、ヒープ番号 3 物理レコード: n_fields 2; コンパクト フォーマット; 情報ビット 32
 0: 長さ 4; 16 進数 00000002; 昇順 ;;
 1: 長さ 4; 16 進数 00000002; 昇順 ;;

ログのHOLDS THE LOCKS(S)ブロックから、トランザクション 2 がインデックス a に対して X ロックを保持しており、それがレコードロックであることがわかります。ロックは、ステップ 2 のトランザクション 2 によって実行される delete ステートメントによって取得されます。これは、RR 分離モード (a = 2) の一意のインデックスに基づく等価クエリであるため、次のキーロックではなくレコードロックが要求されます。

WAITING FOR THIS LOCK TO BE GRANTED 。ロックは、ステートメント insert into test (id,a) values (10,2) によって適用されます。通常の状況では、挿入ステートメントは排他ロック、つまり X ロックを適用しますが、ここでは S ロックが表示されます。これは、フィールド a が一意のインデックスであるため、挿入ステートメントは挿入前にduplicate keyチェックを実行するためです。このチェックが成功するには、他のトランザクションがフィールド a を変更できないように S ロックを適用する必要があります。

では、なぜ S ロックが失敗するのでしょうか?つまり、同じフィールドのロックのアプリケーションはキューに入れる必要があります。 S ロックの前に失敗した X ロックがあるため、S ロックは待機する必要があり、循環待機が形成され、デッドロックが発生します。

デッドロックログを読むことで、2 つのトランザクションによってどのような循環待機が形成されているかを明確に知ることができます。さらに分析すると、循環待機の原因、つまりデッドロックの原因を逆に推測できます。

デッドロック形成フローチャート

デッドロックの原因をよりよく理解できるように、デッドロックの形成プロセスを表の形式で説明します。

ステップ	取引 1	取引2
1		始める
2		delete from test where a = 2; 実行は成功しました。トランザクション 2 は a=2 で X ロックを保持しており、タイプはレコードロックです。
3	始める
4	delete from test where a = 2; トランザクション 1 は a=2 で X ロックを申請しようとしますが、トランザクション 2 がすでに X ロックを申請しており、2 つの X ロックは相互に排他的であるため、X ロックの申請はロック要求キューに入ります。
5	デッドロックが発生し、トランザクション 1 の重みが小さいため、ロールバックするように選択されます (犠牲者になります)。	insert into test (id, a) values (10, 2); フィールドには一意のインデックスがあるため、重複キーをチェックするには S ロックが必要です。挿入された a の値は依然として 2 であるため、X ロックの後にランク付けされます。ただし、以前の X ロックアプリケーションは、トランザクション 2 がコミットまたはロールバックされた後にのみ成功するため、循環待機が形成され、デッドロックが発生します。

拡大する

デッドロックのトラブルシューティングプロセス中に、同僚は、上記のシナリオでは別のデッドロックが発生することを発見しました。このデッドロックは手動では再現できず、同時実行性の高いシナリオでのみ再現できます。

このデッドロックに対応するログはここには掲載されていません。 lock_mode X locks gap before rec insert intention waiting gap before rec insert intention wait です。

デッドロック生成のプロセスを詳しく説明するために、表を使用します。

ステップ	取引 1	取引2
1		始める
2		delete from test where a = 2; 実行は成功しました。トランザクション 2 は a=2 で X ロックを保持しており、タイプはレコードロックです。
3	始める
4		[挿入フェーズ 1] insert into test (id, a) values (10, 2); トランザクション 2 は重複キーをチェックするために S ロックを申請します。チェックは成功しました。
5	delete from test where a = 2; トランザクション 1 は a=2 で X ロックを申請しようとしますが、トランザクション 2 がすでに X ロックを申請しており、2 つの X ロックは相互に排他的であるため、X ロックの申請はロック要求キューに入ります。
6	デッドロックが発生し、トランザクション 1 の重みが小さいため、ロールバックするように選択されます (犠牲者になります)。	[挿入フェーズ 2] insert into test (id, a) values (10, 2); トランザクション 2 がデータの挿入を開始し、S ロックが X ロックにアップグレードされ、タイプが挿入意図になります。同様に、X ロックがキューに入り、循環的な待機サイクルを形成してデッドロックが発生します。

要約する

デッドロックのトラブルシューティングを行うときは、まずデッドロックログに基づいて循環待機のシナリオを分析し、次に現在の各トランザクションによって実行された SQL に基づいてロックの種類とシーケンスを分析し、循環待機がどのように形成されたかを逆に推測する必要があります。このようにして、デッドロックの原因を見つけることができます。

さて、この記事はこれで終わりです。この記事の内容が皆さんの勉強や仕事に少しでも役立てば幸いです。上記の分析は経験に基づいています。他の友人が間違いや欠陥を指摘してくれることを願っています。123WORDPRESS.COM へのご支援ありがとうございます。

以下もご興味があるかもしれません: