MySQLのデッドロックとログに関する詳細な説明

MySQLのデッドロックとログに関する詳細な説明

最近、MySQL オンラインでいくつかのデータ異常が発生しましたが、すべて早朝に発生しました。ビジネス シナリオは典型的なデータ ウェアハウス アプリケーションであるため、日中は負荷が少なく、再現できません。いくつかの異常は非常に奇妙なものもあり、根本的な原因を分析するには多大な労力を要しました。では、実際のビジネスでオンラインの MySQL の問題を迅速に見つけ、例外を修正するにはどうすればよいでしょうか?以下では、実際の 2 つの事例に基づいて、関連する経験と方法を共有します。

ケース1:部分的なデータ更新が失敗した

ある日、あるチャネルの同僚から、あるレポート内のいくつかのチャネルのデータが 0 であったが、ほとんどのチャネルのデータは正常であると報告されました。このデータは、統計プログラムによって毎朝定期的に更新されます。論理的には、すべてが正常であるか、すべてが失敗しているかのどちらかです。では、いくつかの個別のデータ ポイントが異常である理由は何でしょうか。

まず考えられるのは、統計タスクのログを確認することです。しかし、統計プログラムが出力したログを読んでも、SQL 更新の失敗などの異常な記述は見つかりませんでした。では、そのときデータベースに何が起こったのでしょうか。 MySQL サーバーのログを表示する前に、データベースのステータスをいつものように確認します。

早朝にこの更新デッドロックを確認しました:

スペースの制限により、ここでは多くのコンテキストを省略しています。このログから、トランザクション 1 とトランザクション 2 がそれぞれ一定数の行ロックを保持し、互いのロックを待機していることがわかります。最後に、MySQL はデッドロックを検出し、トランザクション 1 をロールバックすることを選択します。Innodb の現在のデッドロック処理方法は、行レベルの排他ロックが最も少ないトランザクションをロールバックすることです。

ここでは3つの質問があります:

1. InnoDB の行ロックは 1 行だけをロックするのではないですか?

このテーブルは InnoDB エンジンであるため、InnoDB は行ロックとテーブル ロックをサポートします。 InnoDB の行ロックは、インデックス上のインデックス項目をロックすることによって実装されます。これは、データ ブロック内の対応するデータ行をロックすることによって実装する MySQL や Oracle とは異なります。 InnoDB の行ロック実装機能により、InnoDB はインデックス条件を通じてデータが取得される場合にのみ行レベルのロックを使用します。それ以外の場合、InnoDB はテーブル ロックを使用し、スキャンされたすべての行をロックします。実際のアプリケーションでは、InnoDB 行ロックのこの機能に特別な注意を払う必要があります。そうしないと、多数のロック競合が発生し、同時実行パフォーマンスに影響する可能性があります。 MySQL の行ロックはレコードではなくインデックスに対してロックされるため、異なる行のレコードにアクセスして同じインデックス キーを使用すると、ロックの競合が発生します。等価条件ではなく範囲条件を使用してデータを取得し、共有ロックまたは排他ロックを要求すると、InnoDB は条件を満たす既存のデータ レコードのインデックス項目をロックします。さらに、ギャップ ロックによって複数の行もロックされます。範囲条件によるロック時にギャップ ロックを使用するだけでなく、存在しないレコードのロックを要求するために等価条件が使用される場合も、InnoDB はギャップ ロックを使用します。

それでは、ビジネス テーブルのインデックスを見てみましょう。

このテーブルのインデックスは非常に不合理であることがわかります。インデックスは 3 つありますが、更新ではインデックスが完全に使用されていないため、更新でインデックスが正確に使用されず、複数のデータ行をロックする必要があり、デッドロックが発生します。

原理を理解した後、4 つのフィールドを組み合わせたインデックスを慎重に構築して、更新が InnoDB インデックスを正確に通過できるようにします。実際に、インデックスを更新すると、デッドロックの問題が解決されます。

注: Innodb は、トランザクションとトランザクションによって保持および待機されているロックだけでなく、レコード自体も出力します。残念ながら、出力結果用に Innodb によって予約されている長さを超える可能性があります (出力できるのは 1 MB のコンテンツのみで、保持できるのは最新のデッドロック情報のみです)。完全な出力が表示されない場合は、任意のライブラリの下に innodb_monitor または innodb_lock_monitor テーブルを作成して、innodb ステータス情報が完全になり、15 秒ごとにエラー ログに記録されるようにすることができます。たとえば、create table innodb_monitor(a int)engine=innodb; とします。エラー ログを記録する必要がない場合は、テーブルを削除するだけです。

2. ロールバック中に一部の更新ステートメントだけが失敗するのはなぜですか?

ロールバックした場合、トランザクション全体のすべての更新が失敗するのではなく、一部の更新ステートメントだけが失敗するのはなぜですか?

これは、InnoDB がデフォルトで自動的に送信されるためです。

複数の更新または挿入ステートメントの場合、各 SQL ステートメントが実行された後、InnoDB は変更を永続化するために 1 回コミットし、ロックを解除します。このため、この例ではデッドロックによってトランザクションがロールバックされた後、いくつかのステートメントのみが失敗します。

通常、一部のステートメントがロールバックされる原因となる別の状況もあるため、それには特別な注意を払う必要があることに注意してください。 innodbにはinnodb_rollback_on_timeoutというパラメータがあります。

公式マニュアルでは次のように説明されています:

MySQL 5.1 では、InnoDB はデフォルトでトランザクション タイムアウト時に最後のステートメントのみをロールバックします。–innodb_rollback_on_timeout が指定されている場合、トランザクション タイムアウトにより InnoDB はトランザクション全体を中止し、ロールバックします (MySQL 4.1 と同じ動作)。この変数は MySQL 5.1.15 で追加されました。

説明: このパラメータがオフになっているか存在しない場合、タイムアウトが発生すると、トランザクションの最後のクエリのみがロールバックされます。オンになっている場合、タイムアウトが発生すると、トランザクション全体がロールバックされます。

3. InnoDB デッドロックの可能性を減らすにはどうすればよいでしょうか?

行ロックとトランザクションのシナリオではデッドロックを完全に排除することは困難ですが、次のようなテーブル設計と SQL 調整によってロックの競合とデッドロックを軽減できます。

より低い分離レベルを使用するようにしてください。たとえば、ギャップ ロックが発生した場合、それを回避するために、セッションまたはトランザクションのトランザクション分離レベルを RC (読み取りコミット) に変更できます。ただし、binlog_format を行形式または混合形式に設定する必要があります。

インデックスを慎重に設計し、可能な限りインデックスを使用してデータにアクセスし、ロックをより正確にしてロックの競合の可能性を減らします。

適切なトランザクション サイズを選択します。トランザクションが小さいと、ロックの競合が発生する可能性が低くなります。

レコードセットを明示的にロックする場合は、一度に十分なレベルのロックを求めるのが最適です。たとえば、データを変更する場合は、最初に共有ロックを適用し、変更時に排他ロックを要求する (デッドロックが発生しやすい) のではなく、排他ロックを直接適用するのが最適です。

異なるプログラムが一連のテーブルにアクセスする場合、同じ順序でテーブルにアクセスするようにする必要があります。テーブルの場合は、固定された順序で行にアクセスするようにします。これにより、デッドロックの可能性が大幅に減少します。

同時挿入に対するギャップ ロックの影響を回避するために、同等の条件でデータにアクセスするようにしてください。

実際に必要なレベルよりも高いロック レベルを適用しないでください。必要な場合を除き、クエリ時にロックを表示しないでください。

特定のトランザクションでは、テーブル ロックを使用して処理速度を上げたり、デッドロックの可能性を減らしたりすることができます。

ケース2:奇妙なロック待機タイムアウト

数日連続で、午前 6 時と午前 8 時にタスクが失敗しました。ローカル infile のデータをロードすると、Java SQL 例外「ロック待機タイムアウトを超えました。トランザクション innodb を再起動してください」が報告されました。プラットフォームの同僚とやり取りした結果、これは自社のビジネス データベースのロック時間が短すぎるか、ロックの競合が発生していたためであることがわかりました。しかし、振り返ってみると、そうすべきではないでしょうか?これまでもずっとこれで良かったんじゃないの?さらに、これらは基本的に単一フォームのタスクであり、複数人での競合はありません。

誰の問題であるかに関係なく、まずは自分のデータベースに問題がないか確認してみましょう。

デフォルトのロック タイムアウトは 50 秒ですが、これは決して短い時間ではありません。調整しても無駄だと思います。実際、最後の手段として試してみましたが、うまくいきませんでした。 。 。

そして今回は、SHOW ENGINE INNODB STATUS\G でデッドロック情報は表示されませんでした。そこで、その瞬間の前後にデータがどのような操作を実行していたかをログから確認しようと、MySQL サーバーのログに注目しました。 MySQL ログ ファイル システムの構成について簡単に説明します。

(a) エラー ログ: mysqld の起動、実行、停止時に発生した問題を記録します。デフォルトで有効になっています。
(b) 一般ログ: すべてのステートメントと命令を記録する一般クエリ ログ。これを有効にすると、データベースのパフォーマンスが約 5% 低下します。
(c) binlog: バイナリ形式で、データを変更するすべてのステートメントを記録し、主にスレーブレプリケーションとデータリカバリに使用されます。
(d) スロー ログ: 実行に long_query_time 秒より長い時間がかかるすべてのクエリ、またはインデックスを使用しないクエリを記録します。デフォルトでは無効になっています。
(e) Innodb ログ: データの回復と操作の取り消しに使用される、Innodb Redo ログ、Undo ログ。

上記の紹介から、この問題のログは d と b にある可能性があることがわかります。確認したところ、d にはログがないため、b のみを有効にすることができます。ただし、b はデータベースのパフォーマンスに一定の損失をもたらします。完全なログであるため、ボリュームが非常に大きいため、有効にするときは注意が必要です。

毎日、問題発生の 30 分前と 30 分後に完全なログをオンにしたところ、ビジネス データベースへの MySQL クライアント リクエストは見つかりませんでした。ログ形式は次のようになり、すべての接続とコマンドが記録されます。

問題は基本的に確認されました。上記の例外は、クライアントのリクエストが当社に届く前にスローされました。プラットフォームとの繰り返しの通信と確認の後、プラットフォームは最終的に、挿入を実行する前に SQL タスク テーブルから SQL を取得し、タスク ステータスを更新する必要があったためであることを確認しました。その結果、このテーブルではその時間に大量の同時挿入と更新が行われ、ロックを待機している間に一部の SQL がタイムアウトになりました。 。 。

MySQL ログ分析スクリプト

早朝はデータ ウェアハウスのビジネス ピーク時間帯であるため、多くの問題がこの時間帯に発生します。一部の奇妙な問題は永久に消えてしまい、日中には再現できないことがよくあります。問題を素早く特定するために、重要なログをキャプチャする方法は非常に重要です。ここでは、crontab 展開用の小さなスクリプトを作成しました。開始する時間範囲を選択し、1 分ごとにログをサンプリングできます。一般ログは簡単に有効にしないでください。そうしないと、データベースのパフォーマンスが大幅に低下します。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

以下もご興味があるかもしれません:
  • MySQL のデッドロック状況とデッドロックの対処方法の詳細な説明
  • MySQL デッドロックのトラブルシューティング プロセスの完全な記録
  • MySQL データベースのデッドロックの原因と解決策
  • MySQL スレッドでデッドロックの ID を見つける方法
  • MySQL データベースのデッドロック インスタンスの分析
  • MySQL デッドロック シナリオ例の分析
  • MYSQL パフォーマンス最適化共有 (データベースとテーブルのシャーディング)
  • MyBatis は、Mysql データベースのサブライブラリとサブテーブルの操作と概要を実装します (推奨)
  • MYSQL データベースのデータ分割の概要: サブライブラリとサブテーブル
  • MySQL のデッドロックとデータベースおよびテーブル シャーディングの問題の詳細な説明

<<:  過去の Linux イメージの問題を修正するためのサンプル分析

>>:  react setStateの詳細な説明

推薦する

MySQL で not in を使用して null 値を含める問題を解決する

知らせ! ! ! uid が (a,b,c,null) に含まれないユーザーから * を選択します。...

IE7でソースファイルを含むページを開くとページが空白になる問題の解決方法

質問:私のブログのエンコーディングは utf-8 です。ページを開くと空白になっていることもあります...

複数のdiv内のテーブルのtdwidth設定は同じで、揃えることができません

最近、複数のdivにあるテーブルのTDを同じ幅に調整しても、揃えることができず、幅にパターンがないこ...

Zenコーディングリソース更新機能強化

公式サイト: http://code.google.com/p/zen-coding/ Zen コー...

今日は、珍しいけれど役に立つJSテクニックをいくつか紹介します

1. 戻るボタンhistory.back() を使用してブラウザの「戻る」ボタンを作成します。 &l...

MySQL の int、char、varchar のパフォーマンスを比較する

インターネットには、真実のように見える「噂」がたくさんあります。もちろん、悪意のあるものではありませ...

DockerにJava環境をインストールするための実装手順

この記事は Linux centos8 をベースにして、docker をインストールし、イメージをプ...

VPSサーバーでよく使われるパフォーマンステストスクリプトの概要

これは、VPS サーバー用の一般的なワンクリック パフォーマンス テスト スクリプトです。マシンの構...

Linux システムでのユーザー管理の概要

目次1. ユーザーとユーザーグループの重要性1) ユーザーの存在意義2) ユーザーグループの重要性2...

VMware 仮想マシン (CentOS7 イメージ) を使用して Linux をインストールする

1. VMwareのダウンロードとインストールリンク: https://www.jb51.net/s...

スネークゲームのウェブ版を実装するためのJavaScript

この記事では、ウェブページのスネークゲームを実装するためのJavaScriptの具体的なコードを参考...

HTMLシールドの右クリックメニューと左クリック入力機能の例

右クリックメニューを無効にする <body oncontextmenu=self.event....

初心者のための HTML コーディングガイドライン 30 選

1. HTMLタグは常に閉じる前のページのソース コードでは、次のような記述がよく見られます。 &l...

Chromeの最小フォントサイズ制限12pxに対する最終的な解決策

ウェブサイトを作成するユーザーの多くが、このような問題に遭遇すると思います。Chrome のデフォル...