MySQLオンラインデッドロック分析練習

序文

MySQL を学習する際に、MySQL のロック メカニズムについて簡単に理解したことがあると思います。ロックが存在するため、デッドロックの問題は避けられません。実際、MySQL では、ほとんどのシナリオ (同時実行性が高くなく、SQL がそれほどひどく書かれていない場合など) ではデッドロックの問題は発生しませんが、同時実行性が高いビジネス シナリオでは、注意しないとデッドロックが発生する可能性があり、このデッドロックを分析するのはより困難です。

以前、ある会社でインターンをしていたとき、かなり奇妙な行き詰まりに遭遇しました。それまでは、きちんと解決する時間がありませんでした。最近、それを再現する時間ができたので、経験を積むことができました。

ビジネスシナリオ

簡単に事業背景をお話しさせていただきますと、当社はEコマース・ライブストリーミング事業を手掛けており、私はアンカー関連の事業を担当しております。このデッドロックは、アンカーがバックグラウンドで製品情報を更新するときに発生します。

弊社の製品の 1 つには、2 つの ID が関連付けられています。いずれかの ID から一意の製品を識別することはできません (つまり、ID と製品の関係は 1 対多です)。両方の ID を同時にクエリすることによってのみ、製品を識別できます。そのため、商品情報を更新する場合は、WHERE条件に2つのIDを同時に指定する必要があります。デッドロックSQL(匿名)の構造は以下のとおりです。

test_table を更新し、`name`="zhangsan" を設定します。WHERE class_id = 10 AND teacher_id = 8;

この SQL は非常にシンプルです。2 つの等しい条件に基づいてフィールドを更新します。

このSQLを見たら戸惑うのではないでしょうか。常識的に考えれば、デッドロックはトランザクション内に複数のSQLがある場合にのみ発生する可能性があります。この1つのSQLでなぜデッドロックが発生するのでしょうか？

はい、当時は私も同じ疑問を抱いていましたし、警報システムが何か間違ったことを報告しているのではないかとさえ疑っていました（結局それは真実ではなかったのですが…）、当時は本当に混乱していました。また、データベースの権限が原因で、デッドロックのログを見ることができませんでした。仕事が終わる時間が近づいていて、DBAを探すのが面倒だったので、検索エンジンで検索してみました...（キーワード：更新デッドロックシングルSQL）、そして最終的に、MySQLのインデックスマージ最適化、つまりインデックスマージが原因であることがわかりました。以下では、デッドロックのシナリオを詳しく説明し、再現します。

インデックスの結合

インデックス マージは、MySQL 5.0 で導入された最適化機能であり、主に SQL ステートメントが複数のインデックスを使用する状況を最適化するために使用されます。

class_idとteacher_id 2 つの共通インデックスであると仮定して、今の SQL を見てみましょう。

test_table を更新し、`name`="zhangsan" を設定します。WHERE class_id = 10 AND teacher_id = 8;

インデックス マージの最適化がない場合、MySQL がデータをクエリする手順は次のとおりです。

• class_idまたはteacher_id（オプティマイザは実際のデータ状況に基づいてどのインデックスを使用するかを決定します。ここではclass_idインデックスが使用されていると想定しています）に応じて、対応するデータの主キーIDがセカンダリインデックスで照会されます。
• クエリされた主キーIDに基づいてバックインデックスクエリ（つまり、クラスター化インデックスをクエリ）を実行し、対応するデータ行を取得します。
• データ行からteacher_idを取得し、それが8に等しいかどうかを確認します。条件を満たしている場合は、

このプロセスから、MySQL が 1 つのインデックスのみを使用していることが簡単にわかります。複数のインデックスが使用されない理由については、複数のインデックスが複数のツリー上にあるため、強制的に使用するとパフォーマンスが低下するという単純な理由です。

インデックス マージ最適化が導入された後の MySQL のデータ クエリの手順を見てみましょう。

• class_idに基づいて対応する主キーをクエリし、次に主キーに基づいて対応するデータ行をクエリします（結果セット A として記録されます）。
• teacher_idに基づいて対応する主キーをクエリし、次に主キーに基づいて対応するデータ行をクエリします（結果セット B として記録されます）。
• 結果セットAと結果セットBの交差演算を実行して、条件を満たす最終結果セットを取得します。

ここで、インデックス マージを使用すると、MySQL は 2 つのインデックスをそれぞれ使用して SQL ステートメントを 2 つのクエリ ステップに分割し、交差操作を使用してパフォーマンスを最適化することがわかります。

デッドロック分析

インデックスマージの手順を分析した後、振り返ってデッドロックが発生する理由について考えてみましょう。

インデックス マージは SQL クエリを 2 つのステップに分割することを覚えていますか? ここで問題が発生します。 UPDATEステートメントによって行レベルの排他ロックが追加されることはわかっています。ロック手順を分析する前に、次のようなデータ テーブルがあると仮定します。

上記の表のデータは、記事の冒頭で述べた特性を満たしています。class_id とteacher_id class_id単一のフィールドに基づいてデータを一意に識別することは不可能です。2 つのフィールドを組み合わせることによってのみデータを識別でき、 class_idとteacher_idそれぞれ 2 つの共通インデックスとして設定されます。

次の 2 つの SQL 文が同時に実行され、そのパラメータが完全に異なるとします。直感的にはデッドロックは発生しないはずですが、直感はしばしば間違っています。

// スレッド A は UPDATE test_table SET `name`="zhangsan" WHERE class_id = 2 AND teacher_id = 1; を実行します。

// スレッド B は UPDATE test_table SET `name`="zhangsan" WHERE class_id = 1 AND teacher_id = 2; を実行します。

次に、インデックス マージの最適化により、上記の SQL が同時に実行されると、MySQL のロック手順は次のようになります。

最終的に、2 つのトランザクションが互いに待機することになり、デッドロックが発生します。

解決

このデッドロックは基本的にインデックス マージの最適化によって発生するため、このシナリオでデッドロックの問題を解決するには、MySQL がインデックス マージの最適化を実行しないようにするだけで済みます。

解決策1

手動で SQL 文を複数の SQL 文に分割し、論理層で交差操作を実行して、MySQL の愚かな最適化動作を防止します。たとえば、ここでは最初にclass_idに基づいて対応する主キーをクエリし、次にteacher_idに基づいて対応する主キーをクエリし、最後に交差後の主キーに基づいてデータをクエリできます。

解決策2

結合インデックスを作成します。たとえば、 class_idとteacher_idの結合インデックスを作成できます。その場合、MySQL は Index Merge を使用しません。

オプション3

単一のインデックスを強制するには、テーブル名の後にfor index(class_id)を追加して、ステートメントが class_id インデックスのみを使用することを指定します。

オプション4

インデックスマージの最適化をオフにする:

• 永続的に無効にするには: SET [GLOBAL|SESSION] optimizer_switch='index_merge=off';
• 一時閉鎖: UPDATE /*+ NO_INDEX_MERGE(test_table) */ test_table SET name ="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

シーン再現

データ準備

テストを容易にするために、Navicat を使用してインポートし、必要なテスト データを取得できる SQL スクリプトを次に示します。

ダウンロードアドレス: https://cdn.juzibiji.top/file/index_merge_student.sql

インポート後、次の形式で 10,000 件のテスト データが取得されます。

テストコード

スペースの制限により、ここではコードの Gist リンクのみを示します: https://gist.github.com/juzi214032/17c0f7a51bd8d1c0ab39fa203f930c60

上記のコードは主に 100 個のスレッドを開始し、データ変更 SQL ステートメントを実行してオンライン同時実行をシミュレートします。数秒間実行すると、次のエラーが発生します。

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

これはデッドロック例外が発生したことを意味します。

デッドロック分析

上記のコードを使用してデッドロックを構築しました。次に、MySQL に入り、デッドロック ログを表示します。MySQL で次のコマンドを実行して、デッドロック ログを表示します。

エンジン INNODB ステータスを表示します。 

ログには、最後に生成されたデッドロックを示すLATEST DETECTED DEADLOCKという行があります。次に、それを分析し始めます。

29行目から、トランザクション1で実行されたSQLにはclass_id = 6 、 teacher_id = 16という条件があることがわかります。現在、行ロックが保持されています。34行目から39行目はこの行のデータです。34行目は主キーの16進数表現で、10進数に変換すると1616になります。同様に、行 45 を見てください。ロックを待機しているデータは、主キー ID 1517 のデータです。

次に、同じ方法を使用してトランザクション 2 を分析します。トランザクション 2 は、主キー ID が 1317、1417、および 1517 のデータ行の 3 つのロックを保持しており、1616 を待機していることがわかります。

この時点で、トランザクション 1 は 1616 を保持して 1517 を待機し、トランザクション 2 は 1517 を保持して 1616 を待機しているため、デッドロックが形成されていることがわかります。このとき、MySQL の処理方法は、最小限のロックでトランザクションをロールバックすることであり、JDBC は前述の MySQLTransactionRollbackException ロールバック例外をスローします。

要約する

このデッドロックは、実はトラブルシューティングが非常に困難です。MySQL の Index Merge を知らないと、トラブルシューティング時に何をすればよいかわかりません。目の前には非常に単純な SQL 文しかないからです。デッドロック ログを見ても、まだ理解できません。

したがって、この種の問題に対処するには、知識と経験が試されることになります。一度この問題に遭遇したら、今後 SQL を書くときにはもっと注意を払うようにしてください。

これで、MySQL の実践的なオンライン デッドロック分析に関するこの記事は終了です。MySQL のオンライン デッドロック分析に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。