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の活動

推薦する

Centos7にGitLabサーバーをインストールして展開する方法

私はここでCentOS 7 64ビットシステムを使用しています。CentOS 64ビットシステムを試...

JavaScript で H5 ゴールド コイン関数を実装する (サンプル コード)

今日は春節の金貨の赤い封筒のアクティビティを作りました。なかなか良い出来だと思います。皆さんと共有し...

MySql テーブル、データベース、シャーディング、パーティショニングの知識ポイントの紹介

1. はじめにデータベース内のデータ量が一定レベルに達すると、システムパフォーマンスのボトルネックを...

純粋なJSを使用してセカンダリメニュー効果を実現します

この記事の例では、セカンダリメニュー効果を実現するためのJSの具体的なコードを参考までに共有していま...

TypeScript でオブジェクト キーの値の範囲を制限する方法

TypeScript を使用する場合、TypeScript が提供する型システムを使用してコードのあ...

JavaScriptでカレンダー効果を素早く実装

この記事では、カレンダー効果を素早く実現するためのJavaScriptの具体的なコードを例として紹介...

CSS3 の transition、transform、translate の違いと機能の簡単な分析

変換して翻訳するTransform は、変換と変形を意味します。他の幅属性や高さ属性と同様に、CSS...

XHTML の珍しいが便利なタグ

Xhtml には、あまり使用されないが非常に便利なタグが多数あります。半分の労力で 2 倍の結果を達...

Docker で Redis センチネル モードを構成する方法 (複数のサーバー上)

目次序文状態DockerをインストールするRedisのマスターノードとスレーブノードを構成する序文以...

Linux で so または実行可能プログラムの依存ライブラリを表示します

Linux で実行可能プログラムまたは so の依存ライブラリを表示します。 Linux の実行可能...

JSONP クロスドメインシミュレーション Baidu 検索

目次1. JSONPとは何か2. JSONPクロスドメインリクエスト3. Baidu検索をシミュレー...

MySQL の binlog_format モードと設定の詳細な分析

MySQL レプリケーションには、SQL ステートメント ベースのレプリケーション (SBR)、行ベ...

MySQL 8.0.22 のインストールと設定方法のグラフィックチュートリアル

この記事ではMySQL 8.0.22のインストールと設定について記録します。具体的な内容は以下のとお...

MySQLクエリステートメント内のユーザー変数のコード分析

前回の記事では、MySQL 最適化の概要 - クエリの合計数を紹介しました。この記事では、クエリ ス...

MySQLのバージョンアップ方法を超詳しく解説

目次1. はじめに2. データベースをバックアップする3. オリジナルのMysqlをアンインストール...