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 分ごとにログをサンプリングできます。一般ログは簡単に有効にしないでください。そうしないと、データベースのパフォーマンスが大幅に低下します。