MySQL InnoDB のトランザクション特性を確保するにはどうすればよいですか?

MySQL InnoDB のトランザクション特性を確保するにはどうすればよいですか?

序文

「データベース トランザクションの特徴は何ですか?」と尋ねられたら、 ACID 特性である原子性、一貫性、独立性、永続性はすぐに答えられるでしょう。では、InnoDB がこれらのトランザクション特性をどのように保証するかをご存知ですか?ご存知の方はこの記事を読み飛ばしていただいても大丈夫です(#^.^#)

まず結論を述べます。

  • REDOログはトランザクションの永続性を保証するために使用されます
  • UNDOログロールバックログはトランザクションのアトミック性を保証する
  • UNDOログとREDOログによりトランザクションの一貫性が確保される
  • ロック(共有、排他)はトランザクションの分離を保証するために使用されます

再実行ログ

REDO ログ REDO ログは 2 つの部分に分かれています。1 つはメモリ内の失われやすい REDO ログ バッファであり、もう 1 つは永続的な REDO ログ ファイルです。 InnoDB は、コミット時にログを強制するメカニズムを通じて永続性を実現します。コミット時には、永続性のためにすべてのトランザクション ログをまず REDO ログ ファイルに書き込む必要があり、コミット操作が完了した後にのみトランザクションが完了したとみなされます。

InnoDB は、次の状況で、REDO ログ バッファの内容を REDO ログ ファイルに書き込みます。

  • マスター スレッドは、REDO ログ バッファを 1 秒ごとに REDO ログ ファイルにフラッシュします。
  • 各トランザクションがコミットされるとき
  • REDOログバッファプールの残りスペースが1/2未満になった場合

各ログが確実に REDO ログ ファイルに書き込まれるようにするには、ログ バッファーが REDO ログ ファイルに書き込まれるたびに、InnoDB ストレージ エンジンが fsync (フラッシュ) 操作を呼び出す必要があります。しかし、これは絶対的なものではありません。ユーザーは、innodb_flush_log_at_trx_commoit パラメータを変更することで、REDO ログをディスクにフラッシュする戦略を制御できます。このパラメータは、大量のトランザクションをコミットする際の最適化ポイントとして使用できます。

  • デフォルト値 1 は、トランザクションがコミットされたときに fsync 操作を呼び出す必要があることを示します。
  • 0 は、トランザクションがコミットされたときに、REDO ログ キャッシュがすぐに REDO ログ ファイルに書き込まれず、マスター スレッドの間隔で fsync 操作が実行されることを意味します。
  • 2 は、トランザクションがコミットされたときに REDO ログが REDO ログ ファイルに書き込まれるが、ファイル システム キャッシュにのみ書き込まれ、fsync 操作は実行されないことを意味します。
    fsync の効率はディスクのパフォーマンスに依存するため、ディスクのパフォーマンスによってトランザクション送信のパフォーマンス、つまりデータベースのパフォーマンスが決まります。したがって、MySQL データベースを最適化する方法を尋ねられたら、ハードウェアを忘れないでください。ハードディスクの構成を改善し、SSD ソリッド ステート ドライブに切り替えるように依頼してください。REDO ログは 512 バイトのブロックに保存され、これを REDO ログ ブロックと呼びます。これはディスク セクターと同じサイズであるため、REDO ログの書き込みによってアトミック性が保証され、二重書き込みテクノロジは必要ありません。次の3つの機能があります。
  • RedoログはInnoDB層で生成される
  • REDO ログは、各ページへの変更を記録する物理形式のログです。
  • REDO ログはトランザクション中に継続的に書き込まれ、順番に書き込まれます。

ロールバックログ取り消しログ

トランザクションのアトミック性を保証するために、データを操作する前に、まずデータをある場所にバックアップし(データのバックアップが保存される場所は Undo ログと呼ばれます)、その後データを変更します。エラーが発生した場合、またはユーザーが ROLLBACK ステートメントを実行した場合、システムは Undo ログ内のバックアップを使用して、トランザクションが開始される前の状態にデータを復元できます。

UNDO ログは、トランザクションの分離を保証するために、マルチバージョン同時実行制御 (MVCC) を実装します。

ロールバック ログは、REDO ログとは異なります。これは、データベースへのすべての変更を論理的に取り消す論理ログです。トランザクションがロールバックされると、実際には以前に実行した処理の逆の処理が実行されます。各 INSERT に対して、InnoDB ストレージ エンジンは DELETE を実行し、各 UPDATE に対して、InnoDB ストレージ エンジンは逆 UPDATE を実行します。

トランザクションがコミットされた直後に UNDO ログを削除することはできません。これは、UNDO ログを使用して行レコードの以前のバージョンを取得する必要がある他のトランザクションが存在する可能性があるためです。ストーリー タスクが送信されると、元に戻すログがリンク リストに格納されます。元に戻すログを削除できるかどうかは、次の 2 つの状況によって異なります。

  • 挿入元に戻すログ: 挿入操作の記録はトランザクション自体にのみ表示され、他のトランザクションには表示されません (これはトランザクション分離の要件です)。したがって、トランザクションがコミットされた後、元に戻すログを直接削除できます。パージ操作は必要ありません。
  • 更新元に戻すログ: 削除および更新操作によって生成された元に戻すログを記録します。 UNDO ログは MVCC メカニズムを提供する必要がある可能性があるため、トランザクションがコミットされたときに削除することはできません。送信時に、それは UNDO ログのリンク リストに配置され、パージ スレッドが最終的な削除を実行するのを待機します。

ロック

トランザクション分離の実装原理はロックであるため、分離は同時実行制御、ロックなどとも呼ばれます。トランザクションの分離では、各読み取り/書き込みトランザクションのオブジェクトを他のトランザクションの操作オブジェクトから分離する必要があります。さらに、たとえば、バッファ プール内の LRU リストを操作する場合、LRU リスト内の要素を削除、追加、移動するときには、一貫性を確保するためにロックが必要になります。

ロックの種類

InnoDBには、行レベルロックと意図ロックという2つの主なロックタイプがあります。

行レベルのロック:

  • 共有ロック (読み取りロック S) を使用すると、トランザクションはデータ行を読み取ることができます。トランザクションは、行の共有 S ロックを取得した後にのみレコードの行を読み取ることができ、他のトランザクションが X ロックを追加するのを防ぎます。共有ロックの目的は、読み取りと読み取りの同時実行性を向上させることです。
  • 排他ロック (書き込みロック X) を使用すると、トランザクションはデータ行を削除または更新できます。トランザクションは、行に対する排他的 X ロックを取得した後にのみ、行を変更または削除できます。排他ロックの目的は、データの一貫性を確保することです。

行レベルのロックのうち、S と S を除く他のすべてには互換性がありません。

意図ロック:

  • 意図的な共有ロック (読み取りロック IS): トランザクションがテーブル内の複数のデータ行に対して共有ロックを取得する場合、トランザクションはデータ行に共有ロックを追加する前に、まずテーブルの IS ロックを取得する必要があります。
  • 意図的な排他ロック (書き込みロック IX)。トランザクションがテーブル内の複数のデータ行に対して排他ロックを取得する必要がある場合、トランザクションはデータ行に排他ロックを追加する前に、まずテーブルの IX ロックを取得する必要があります。

意図ロックを説明する

IX ロックと IS ロックの主な目的は、誰かがテーブル内の行をロックしていること、または行をロックしようとしていることを示すことです。

意図ロックの主な目的は、トランザクションがデータ行をロックしていること、またはロックする予定であることを表現することです。たとえば、トランザクション A がレコード r の行に X ロックを適用する場合、InnoDB は最初にテーブルに IX ロックを適用し、次にレコード r に X ロックを適用します。トランザクション A が完了する前に、トランザクション B は完全なテーブル操作を実行しようとします。このとき、テーブル レベルの IX は、テーブル内の各行にロックがあるかどうかを判断することなく、トランザクション B に直接待機するように指示します。意図的な排他ロックの価値は、InnoDB のロック配置と処理パフォーマンスを節約することにあります。また、意図ロックは完全なテーブルスキャンを除いてブロックされないことにも注意してください。

ロックアルゴリズム

InnoDB には 3 つの行ロック アルゴリズムがあります。

  • レコードロック: 単一行レコードをロックする
  • ギャップロック: ギャップロックはレコード自体ではなく範囲をロックします
  • 次のキー ロック: ギャップ ロックとレコード ロックを組み合わせて、範囲とレコード自体をロックします。解決すべき主な問題は、REPEATABLE READ 分離レベルでのファントム リードです。トランザクション分離レベルの詳細については、この記事を参照してください。

ここでは主に Next-Key Lock について説明します。Next-key Lock は単一の値ではなく範囲をロックします。その目的は、複数のトランザクションが同じ範囲にレコードを挿入してファントム読み取りが発生するのを防ぐことです。

一意のインデックスが使用されている場合、次のキー ロックはレコード ロックにダウングレードされます。つまり、範囲ではなくインデックス自体のみがロックされます。つまり、Next-Key Lock の前提条件は、トランザクション分離レベルが RR であり、照会されたインデックスが非一意インデックスまたは主キー インデックスであることです。

例を使って詳しく説明しましょう。

まずテーブルを作成します。

テーブル T を作成します (id int、f_id int、主キー (id)、キー (f_id)) エンジン = InnoDB デフォルト文字セット = utf8
T SELECT 1,1 に挿入します。
T SELECT 3,1 に挿入します。
T SELECT 5,3 に挿入します。
T SELECT 7,6 に挿入します。
T SELECT 10,8 に挿入します。

トランザクション A は次のステートメントを実行します。

SELECT * FROM T WHERE f_id = 3 更新用

このとき、SQL ステートメントは一意でないインデックスを使用するため、Next-Key Locking が使用され、2 つのインデックスが存在するため、個別にロックする必要があります。

クラスター化インデックスの場合、ID が 5 のインデックスにのみレコード ロックが追加されます。補助インデックスの場合、範囲 (1,3) をロックするために Next-Key Lock が追加されます。InnoDB ストレージ エンジンは、補助インデックスの次のキー値にギャップ ロック、つまり範囲 (3.6) のロックも追加することに注意することが重要です。

したがって、新しいセッションで次のステートメントを実行すると、エラー[Err] 1205 - Lock wait timeout exceeded; try restarting transactionが報告されます。

select * from T where id = 5 lock in share MODE -- 実行できません。トランザクション A が id = 5 の値に X ロックを追加したため、実行がブロックされます。INSERT INTO T SELECT 4,2 -- 実行できません。補助インデックスの値は 2 で、範囲 (1,3) 内にあるため、実行がブロックされます。INSERT INTO T SELECT 6,5 -- 実行できません。ギャップ ロックによって範囲 (3,6) がロックされるため、実行がブロックされます。

ここで、トランザクション A が f_id = 5 のレコードをロックするとします。通常、(5, 6) をロックするギャップ ロックがあります。(5, 6) にギャップ ロックがない場合、ユーザーはインデックス f_id 5 のレコードを挿入できます。その後、トランザクション A は再度クエリを実行すると異なるレコードを返し、ファントム リードが発生します。

同様に、トランザクション A がselect * from T where f_id = 10 FOR UPDATEを実行すると、テーブルにデータは見つかりません。ただし、Next-Key Lock に基づいて、(8, +∞) はロックされます。INSERT INSERT INTO T SELECT 6,11正常に挿入することはできませんが、これによりファントム リードの問題が根本的に解決されます。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません:
  • Mysql InnoDBとMyISAMの違いの分析
  • MySQL innodb B+ツリーの高さを取得する方法
  • MySQL MyISAM と InnoDB の違い
  • MySQL InnoDBストレージエンジンについて簡単に説明します
  • MySQL Innodb ストレージ構造と Null 値の保存の詳細な説明
  • MySQL InnoDB row_id 境界オーバーフロー検証方法の手順
  • MySQL 起動エラー InnoDB: ロックできません/ibdata1 エラー
  • MySQL InnoDB のロック機構の詳細な説明
  • MySQL (InnoDB) がデッドロックを処理する方法の詳細な説明
  • MySQL 学習 (VII): Innodb ストレージ エンジン インデックスの実装原理の詳細説明
  • MySQL slow_log テーブルを InnoDB エンジンに変更することはできません。詳細な説明
  • MySQL InnoDB の重要なコンポーネントの概要

<<:  ECMAScript のイテレータの詳細な説明

>>:  CentOS での samba フォルダ共有サーバー構成の詳細な説明

推薦する

Vue はウェブページの言語切り替えの国際化を実装します

1. 基本的な手順1: yarn add vue-i18nをインストールするこのパスに新しい .js...

MySQL の暗黙的な型変換によって発生するインデックス障害の解決策

目次質問再生暗黙的な変換要約する参照する質問仕事中、1 つの SQL クエリ ステートメントのみを実...

HTML フォーム送信アクションと URL ジャンプアクションの違い

フォームのアクションは URL ジャンプとは異なります。フォームはバックグラウンドにデータを渡すこと...

MySQL SQL文の特殊処理文のまとめ(必読)

1.テーブル全体を更新します。データ行の列の値が空の場合は、別の列フィールドの値と同じにします。 ...

Mysql は null 値の first/last メソッドの例を実装します

序文MySQL が SQL SELECT コマンドと WHERE 句を使用してテーブルからデータを読...

MySQLトリガーの使用

トリガーにより、ステートメントの実行前または実行後に他の SQL コードを実行できます。トリガーは、...

CSSでイメージマッピングを実装する方法

1. はじめにイメージマップを使用すると、画像の領域をホットスポットとして指定できます。この領域にマ...

Nginx サーバーで URL リンクを設定する方法

LNMPのようなアーキテクチャを持つウェブサイトは、一般的にPHPフレームワークに基づいて開発されて...

Windows と Linux 間のリモート デスクトップ接続

Linux へのリモート デスクトップ接続といえば、まず VNC の使用を思い浮かべるかもしれません...

MySQL でデータ復旧に binlog を使用する方法

序文最近、オンラインでデータが誤って操作されました。データベースが直接変更されたため、それを回復する...

MySQLクエリキャッシュに関するヒント

目次序文QueryCache の概要クエリキャッシュ構成QueryCache の使用queryCac...

Rails APIを使用してReactアプリケーションを構築するための詳細な手順

目次バックエンド: Rails API部分フロントエンド: React部分Reactコンポーネントa...

React useEffect の理解と使用

目次繰り返しレンダリングループを避ける副作用の除去についてReact16.8 の新しい useEff...

HTML タグ マーキーはさまざまなスクロール効果を実現します (JS 制御なし)

ページの自動スクロール効果は JavaScript で実現できますが、今日偶然、JS 制御なしでさま...

ウェブページの読み込み速度を上げる6つのヒント

第二に、キーワードのランキングは、Webページの表示速度にも関係しています(参照:キーワードランキン...