序文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をインストールしなくてもイメージを構築できる)
目次1. 親コンポーネントが子コンポーネントに値を渡す(props) 2. サブコンポーネントは親コ...
1. 固定幅+適応型期待される効果: 左側は固定幅、右側は適応幅 共通コード: html: <...
Vue の紹介現在のビッグフロントエンドの時代は、混乱と衝突の時代です。世界は多くの派閥に分かれてお...
1. 1列を変更する 学生の更新、都市c s.city_name = c.name を設定します こ...
HTML イベント リスト一般イベント: onClick HTML: マウスクリックイベント。主にオ...
別のライブラリから別のライブラリにデータをインポートする必要がある場合があり、このデータは CSV ...
インターネット上には多くのインポート方法があり、公式も3つのインポート方法を提供していますが、インポ...
<tr> <th width="12%">あああ<...
目次アプリ更新プロセス大まかなフローチャートアプリ情報の更新1. まず取得する必要があるファイルアド...
序文hover疑似クラスが要素に境界線を追加すると、要素内のコンテンツがずれることがあります。box...
前回の記事では、MySQL 5.7でルートパスワードを忘れた場合と、MySQL 5.7でルートパスワ...
パブリックアカウントのファンデータを同期してバッチプッシュするときに、サーバーがエラー502を報告し...
テーブルページを作成するときに、td に設定された幅が無効になることがあります。td の幅は常に内部...
色特性の分類あらゆる色は、赤、緑、青の三原色から構成されます。三原色の中で暖色なのは赤だけなので、作...
何人かのブロガーが私の記事を評価してくれたのは嬉しいです。マークと知り合ってからは、私は彼をフォロー...