序文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 ステートメントが複数のインデックスを使用する状況を最適化するために使用されます。 test_table を更新し、`name`="zhangsan" を設定します。WHERE class_id = 10 AND teacher_id = 8; インデックス マージの最適化がない場合、MySQL がデータをクエリする手順は次のとおりです。
このプロセスから、MySQL が 1 つのインデックスのみを使用していることが簡単にわかります。複数のインデックスが使用されない理由については、複数のインデックスが複数のツリー上にあるため、強制的に使用するとパフォーマンスが低下するという単純な理由です。 インデックス マージ最適化が導入された後の MySQL のデータ クエリの手順を見てみましょう。
ここで、インデックス マージを使用すると、MySQL は 2 つのインデックスをそれぞれ使用して SQL ステートメントを 2 つのクエリ ステップに分割し、交差操作を使用してパフォーマンスを最適化することがわかります。 デッドロック分析インデックスマージの手順を分析した後、振り返ってデッドロックが発生する理由について考えてみましょう。 インデックス マージは SQL クエリを 2 つのステップに分割することを覚えていますか? ここで問題が発生します。 上記の表のデータは、記事の冒頭で述べた特性を満たしています。class_id と 次の 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 の 解決策2 結合インデックスを作成します。たとえば、 オプション3 単一のインデックスを強制するには、テーブル名の後に オプション4 インデックスマージの最適化をオフにする:
シーン再現データ準備 テストを容易にするために、Navicat を使用してインポートし、必要なテスト データを取得できる SQL スクリプトを次に示します。 ダウンロードアドレス: https://cdn.juzibiji.top/file/index_merge_student.sql インポート後、次の形式で 10,000 件のテスト データが取得されます。 テストコード スペースの制限により、ここではコードの Gist リンクのみを示します: https://gist.github.com/juzi214032/17c0f7a51bd8d1c0ab39fa203f930c60 上記のコードは主に 100 個のスレッドを開始し、データ変更 SQL ステートメントを実行してオンライン同時実行をシミュレートします。数秒間実行すると、次のエラーが発生します。
これはデッドロック例外が発生したことを意味します。 デッドロック分析 上記のコードを使用してデッドロックを構築しました。次に、MySQL に入り、デッドロック ログを表示します。MySQL で次のコマンドを実行して、デッドロック ログを表示します。 エンジン INNODB ステータスを表示します。 ログには、最後に生成されたデッドロックを示す 29行目から、トランザクション1で実行されたSQLには 次に、同じ方法を使用してトランザクション 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 をよろしくお願いいたします。 以下もご興味があるかもしれません:
|
<<: Dockerイメージ構築原理の分析(Dockerをインストールしなくてもイメージを構築できる)
私はここでCentOS 7 64ビットシステムを使用しています。CentOS 64ビットシステムを試...
今日は春節の金貨の赤い封筒のアクティビティを作りました。なかなか良い出来だと思います。皆さんと共有し...
1. はじめにデータベース内のデータ量が一定レベルに達すると、システムパフォーマンスのボトルネックを...
この記事の例では、セカンダリメニュー効果を実現するためのJSの具体的なコードを参考までに共有していま...
TypeScript を使用する場合、TypeScript が提供する型システムを使用してコードのあ...
この記事では、カレンダー効果を素早く実現するためのJavaScriptの具体的なコードを例として紹介...
変換して翻訳するTransform は、変換と変形を意味します。他の幅属性や高さ属性と同様に、CSS...
Xhtml には、あまり使用されないが非常に便利なタグが多数あります。半分の労力で 2 倍の結果を達...
目次序文状態DockerをインストールするRedisのマスターノードとスレーブノードを構成する序文以...
Linux で実行可能プログラムまたは so の依存ライブラリを表示します。 Linux の実行可能...
目次1. JSONPとは何か2. JSONPクロスドメインリクエスト3. Baidu検索をシミュレー...
MySQL レプリケーションには、SQL ステートメント ベースのレプリケーション (SBR)、行ベ...
この記事ではMySQL 8.0.22のインストールと設定について記録します。具体的な内容は以下のとお...
前回の記事では、MySQL 最適化の概要 - クエリの合計数を紹介しました。この記事では、クエリ ス...
目次1. はじめに2. データベースをバックアップする3. オリジナルのMysqlをアンインストール...