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 をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません: