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

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_idteacher_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_idteacher_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_idteacher_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 = 6teacher_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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL の意図的な共有ロック、意図的な排他ロック、デッドロックを 1 つの記事で学習します。
  • Mysql ロック機構の行ロック、テーブルロック、デッドロックの実装
  • Ali インタビュー MySQL デッドロック問題の処理
  • RC レベルでの MySQL デッドロック問題の解決
  • MySQLでデッドロックログを出力する方法
  • MySQL のデッドロックとデータベースおよびテーブル シャーディングの問題の詳細な説明
  • MySQL のロック待機とデッドロック問題の分析
  • MySQL デッドロック問題の超詳細な説明

<<:  Dockerイメージ構築原理の分析(Dockerをインストールしなくてもイメージを構築できる)

>>:  W3Cチュートリアル(16):その他のW3Cの活動

推薦する

Vueコンポーネント通信方法事例まとめ

目次1. 親コンポーネントが子コンポーネントに値を渡す(props) 2. サブコンポーネントは親コ...

CSS マルチカラムレイアウトソリューション

1. 固定幅+適応型期待される効果: 左側は固定幅、右側は適応幅 共通コード: html: <...

Vue命令の動作原理と実装方法

Vue の紹介現在のビッグフロントエンドの時代は、混乱と衝突の時代です。世界は多くの派閥に分かれてお...

MySQL で 1 つのテーブルのフィールドを使用して別のテーブルのフィールドを更新する方法

1. 1列を変更する 学生の更新、都市c s.city_name = c.name を設定します こ...

ブラウザが登録できるイベントの概要

HTML イベント リスト一般イベント: onClick HTML: マウスクリックイベント。主にオ...

MySql インポート CSV ファイルまたはタブ区切りファイル

別のライブラリから別のライブラリにデータをインポートする必要がある場合があり、このデータは CSV ...

Vue で Alibaba のアイコンフォント ベクター アイコンを使用する方法について

インターネット上には多くのインポート方法があり、公式も3つのインポート方法を提供していますが、インポ...

React Native APPのアップデートに関する簡単な説明

目次アプリ更新プロセス大まかなフローチャートアプリ情報の更新1. まず取得する必要があるファイルアド...

ホバー生成の境界線によって生じる要素の移動を解決する方法

序文hover疑似クラスが要素に境界線を追加すると、要素内のコンテンツがずれることがあります。box...

Mysql5.7 のルートパスワードを忘れた場合の対処法 (シンプルで効果的な方法)

前回の記事では、MySQL 5.7でルートパスワードを忘れた場合と、MySQL 5.7でルートパスワ...

nginx サーバーでの 502 不正なゲートウェイ エラーの原因のトラブルシューティング

パブリックアカウントのファンデータを同期してバッチプッシュするときに、サーバーがエラー502を報告し...

テーブルセルの幅tdの設定は無効であり、内部コンテンツによって常に引き伸ばされます

テーブルページを作成するときに、td に設定された幅が無効になることがあります。td の幅は常に内部...

ウェブページの色特性の分類

色特性の分類あらゆる色は、赤、緑、青の三原色から構成されます。三原色の中で暖色なのは赤だけなので、作...

MySQL 最適化: キャッシュ最適化

何人かのブロガーが私の記事を評価してくれたのは嬉しいです。マークと知り合ってからは、私は彼をフォロー...