MySQL は ACID トランザクションをどのように実装しますか?

序文

最近、面接中に、MySQL の InnoDB エンジンがどのようにトランザクションを実装しているか、または ACID 機能をどのように実装しているかを尋ねられました。その時は良い答えができなかったため、自分なりに要約して記録しました。

ACIDトランザクションの4つの特徴

トランザクションの 4 つの主要な特性は、ACID、A-原子性、C-一貫性、I-独立性、および D-永続性です。一貫性は最終的な目標であり、原子性、分離性、永続性は一貫性を確保するための手段です。つまり、私が書いた順序は ACID に基づいていません。一貫性を最後に置いたため、順序は ADIC になります。

原子性（A）

アトミック性とは、トランザクションが分割できない作業単位であることを意味します。中間状態はなく、トランザクションの一部のみが実行されることなく、すべてが正常に実行されるか、すべてが失敗します。

MySQL の InnoDB エンジンは、元に戻すログ (ロールバックログ) を通じて実装されており、トランザクションがロールバックされたときに、正常に実行されたすべての SQL ステートメントが元に戻されることを保証できます。

UNDO ログは、SQL 実行に関連する情報を記録する論理ログです。トランザクションによってデータベースが変更されると、InnoDB は対応する UNDO ログを生成します。トランザクションの実行に失敗した場合、またはロールバックが呼び出されてトランザクションがロールバックされた場合、InnoDB エンジンは、UNDO ログ内のレコードに基づいてデータを以前の状態にロールバックします。
たとえば、挿入ステートメントが実行されると、関連する削除ステートメントの UNDO ログが生成されます。逆に、削除ステートメントを実行すると、関連する挿入ステートメントの UNDO ログも生成されます。更新ステートメントを実行する場合も同様ですが、UNDO ログのロールバックを実行するときに更新ステートメントに MVCC が関与する可能性があります。これは主に、UNDO ログを実行するときに選択するときに、どのバージョンのデータが表示されるかを確認するためです。

持続性（D）

永続性とは、トランザクションがコミットされると、データベースに対する操作が永続的になり、その後の他の操作や異常な障害がそれに影響を与えないことを意味します。
MySQL データは最終的にはディスクに保存されるため、ディスクのサイズによってデータ容量のサイズが決まるということは誰もが知っています。ただし、すべての MySQL 操作がディスクの読み取りと書き込みによって実行される場合、ディスク I/O だけでも効率が大幅に低下します。

そのため、InnoDB は MySQL 用のバッファプールを提供し、そこにディスク上のいくつかのデータページのマッピングが含まれます。
データベースからデータを読み取る場合、データは最初にバッファプールから読み取られます。データがバッファプールにない場合は、ディスクから読み取られ、バッファプールに配置されます。
データベースにデータを書き込む場合、最初にバッファプールに書き込まれます。バッファプール内の更新されたデータは、定期的にディスクに更新されます (このプロセスはフラッシュと呼ばれます)。

バッファプールは MySQL の読み取りと書き込みの効率を向上させますが、新たな問題ももたらします。つまり、データがバッファプールに更新されたばかりで、まだディスクに更新されていないときに MySQL が突然クラッシュすると、データが失われ、トランザクションの耐久性が保証されなくなります。
このキャッシュ一貫性問題を解決するために、REDO ログが登場しました。バッファプール内のデータを変更すると、その操作は REDO ログを通じて記録されます。トランザクションがコミットされると、REDO ログは fsync インターフェイスを通じてディスクにフラッシュされます。

トランザクションがコミットされると、REDO ログがディスクに同期されるため、MySQL がクラッシュしても、REDO ログをディスクから読み取ってデータを回復することができ、トランザクションの永続性が保証されます。

REDO ログは、ログを記録するために事前書き込み方式を使用します。つまり、最初にログが記録され、次にバッファープールが更新されます。これにより、データが REDO ログに保存されている限り、ディスク上に保存されることが保証されます。

これには説明が必要です。REDO ログもディスクに書き込まれ、フラッシュもディスクに書き込まれます。直接フラッシュするのではなく、最初に REDO ログを記録する必要があるのはなぜですか?

主な理由は、REDO ログがフラッシュよりもはるかに高速であることです。

1 つ目のポイントは、REDO ログは追加操作ログであり、シーケンシャル IO であるのに対し、ダーティフラッシュは、毎回更新されるデータが必ずしも隣接しているわけではない、つまりランダムであるため、ランダム IO であるということです。

2 つ目のポイントは、ダーティフラッシュがデータページ単位で行われることです (つまり、毎回少なくとも 1 ページ分のデータがディスクからメモリに読み込まれるか、少なくとも 1 ページ分のデータがディスクにフラッシュされます)。MySQL のデフォルトのページサイズは 16KB です。ページを変更する場合は、ページ全体をディスクにフラッシュする必要があります。また、REDO ログには、実際にディスクに書き込む必要がある操作ログのみが含まれます。

MySQL には、binlog と呼ばれる操作を記録するログもあります。では、redo ログと binlog の違いは何でしょうか?

機能上の最初の違い:

REDO ログは、MySQL がクラッシュしてもトランザクションの永続性が影響を受けないように更新キャッシュを記録するために使用されます。バイナリログは、主に時点を指定して、どの操作がいつ実行されたかを記録し、データを特定の時点に復元できることを保証するために使用されます。また、データのマスタースレーブ同期にも使用されます。

2 番目の違いは次のレベルにあります。

REDO ログはストレージエンジン InnoDB によって実装されます (MyISAM には REDO ログがありません)。一方、バイナリログは MySQL サーバーレベルの他のストレージエンジンでも使用できます。
保存内容の観点から見ると、REDO ログはディスクデータページに基づく物理ログであり、Binlog は実行された SQL 文を保存する論理ログです。

3つ目のポイントは、書くタイミングの違いです。

デフォルトでは、トランザクションがコミットされると、REDO ログはディスクにフラッシュされます。パラメータ innodb_flush_log_at_trx_commit によってポリシーを変更できるため、REDO ログは、トランザクションがコミットされるまで待機せずにディスクにフラッシュされます。
たとえば、1 秒ごとに 1 回送信するように設定できます。
バイナリログはトランザクションがコミットされたときに書き込まれます。

隔離（I）

アトミック性と耐久性はどちらも単一のトランザクションに基づく尺度ですが、独立性は複数のトランザクションが互いに分離され、互いに影響を及ぼさないという特性です。
トランザクションの最も厳密な分離レベルは Serializable であることは誰もが知っていますが、分離レベルが高くなるほどパフォーマンスが低下するため、Serializable 分離レベルは通常は使用されません。
分離については、次の 2 つの状況について説明します。

あるトランザクションの書き込み操作が別のトランザクションの書き込み操作に与える影響。
あるトランザクションの書き込み操作が別のトランザクションの読み取り操作に与える影響。

まず、トランザクション間の書き込み操作は実際には MySQL ロックメカニズムによって分離されますが、トランザクション間の書き込み操作と読み取り操作は MVCC メカニズムによって実装されます。

ロック機構

MySQLのロックは主に

機能に応じて：読み取りロックと書き込みロック。アクションの範囲に応じて：テーブルレベルロックと行レベルロック。
インテンションロック、ギャップロックなどもあります。

読み取りロック: 「共有ロック」とも呼ばれ、複数のトランザクションがロックを共有し、データにアクセスすることはできても変更することはできないことを意味します。

書き込みロック: 「排他ロック」とも呼ばれ、他のトランザクションとデータを共有できません。トランザクションがデータの一部に対して排他ロックを取得した場合、他のトランザクションは、共有ロックや排他ロックなど、その行に対して他のロックを取得できません。

テーブルレベルロック: テーブル全体をロックすることを意味しますが、パフォーマンスは低下します。ストレージエンジンによってサポートされるロックの粒度は異なります。MyISAM エンジンはテーブルレベルロックをサポートし、InnoDB エンジンはテーブルレベルロックと行レベルロックの両方をサポートします。

行レベルのロック: 操作が必要な対応する行がロックされ、パフォーマンスが向上します。

インテンションロック: インテンションロックはテーブルレベルのロックです。トランザクションがすでにテーブル内の特定のデータに排他ロックまたは共有ロックを追加している場合は、インテンションロックを追加できます。このようにして、次のトランザクションがテーブルをロックしようとしたときに、インテンションロックがすでに存在することが判明すると、最初にブロックされます。インテンションロックが追加されていない場合、2 番目のトランザクションがテーブルをロックしようとしたときに、ロックされているデータがあるかどうかを確認するために、一度に 1 行ずつトラバースする必要があります。

ギャップロック: ギャップロックは、ファントムリードを防ぐために追加されるロックです。ギャップロックは、2 つのインデックスレコード間、または最初のインデックスレコードの前または最後のインデックスレコードの後のスペース (現在のレコードは含まれません) にある、存在しない空き領域に追加されます。これにより、ギャップロックが実行されると、新しく追加されたデータがブロックされ、トランザクション内の 2 つのクエリによって取得されるレコード数が一定になります。

次のキーロック: 次のキーロックは行レベルロックとギャップロックの組み合わせです。ギャップロックは現在のレコードをロックしませんが、次のキーロックは現在のレコードもロックします。

たとえば、テーブルに 3 つのレコードがある場合:

id	名前	番号
1	シャオミン	16
2	リトルレッド	17
3	シャオ・チャン	20
4	シャオ・ワン	20

次に、SQL を実行すると: select * from table where number = 17 for update、ギャップロックがロックされます。番号の範囲は (16, 17)、(17, 20) ですが、Next-Key Lock は次のようにロックします:
間隔 16、17、(17、20) はギャップでロックされ、数値 = 17 はレコードでロックされます。

ロックメカニズムは複数のトランザクション間の書き込み操作の分離を保証しますが、複数のトランザクション間の読み取りおよび書き込み操作の保証は MVCC メカニズムによって保証される必要があります。

MVCC メカニズム

MVCC の正式名称は [Multi-Version ConCurrency Control] で、マルチバージョン制御プロトコルです。

MVCC は主に、各レコード行に隠し列を追加し、元に戻すログを使用することで実装されます。隠し列には主に、データ行によって作成されたバージョン番号 (増分)、削除時間、元に戻すログへのポインタなどが含まれます。

では、MVCC はどのようにして読み取りと書き込みの分離を保証するのでしょうか?主に、スナップショット読み取りと現在の読み取りという 2 つの操作を使用します。

スナップショットの読み取り:

同時実行の効率性を確保するために、MVCC はデータの読み取り時にロックを行いません。select (ロックなしの通常の select) を実行すると、最初に現在のデータのバージョン番号が読み取られます。select が結果を返す前にトランザクションがこのデータ行を変更すると、バージョン番号は select が実行されたときよりも大きくなります。したがって、select によって読み取られるデータの一貫性を確保するために、現在のバージョン以下のデータのみが読み取られます。この履歴バージョンのデータは、undo ログから取得されます。

現在読んでいる本:

挿入、更新、または削除を実行すると、最新バージョンのデータが読み取られ、操作中に他のトランザクションによってバージョン番号が変更されないように、現在のレコードがロックされます。

たとえば、通常の選択はスナップショット読み取りであり、読み取られるのはデータの履歴バージョンである可能性があります。

挿入、更新、削除、選択...共有モードでロックし、選択...更新の場合は現在のデータを読み取ります。つまり、最新バージョンのデータを読み取ります。

実際、分離レベルを Serializable に設定することでも読み取り/書き込み分離を実現できますが、同時実行効率が大幅に低下するため、一般的にはほとんど使用されません。ただし、MVCC は読み取り時にロックせず、書き込み時にのみロックするため、同時実行効率が向上します。

MVCC メカニズムは、複数のトランザクション間の読み取り/書き込み分離を保証し、それによってトランザクション分離を実現します。

一貫性（C）

一貫性とは、トランザクションが実行される前と実行された後のデータの一貫性を指します。トランザクションの前後のデータの整合性は破壊されておらず、データの状態は合法です。

一貫性指標には次のものが含まれます。

インデックスの整合性（一意のインデックス、重複なしなど）、データ列の完了（フィールドタイプ、長さ、サイズが要件を満たしている）、外部キー制約など。

一貫性を実現するための対策:

原子性、永続性、独立性を保証します。これらの特性が保証できない場合、一貫性も保証できません。データベースレベルでは、上記の機能の保証に加えて、フィールドの一貫性を確保するための対策があります。たとえば、整数文字を渡すことはできませんし、文字列の長さや時間形式などは列制限を超えることはできません。ただし、開発者自身がアプリケーションレベルでこれを保証する必要があります。
たとえば、金額の一部が A から B に転送される場合、A から差し引かれる金額が B に追加される金額と同じであることを確認する必要があります。B の金額を追加せずに A の金額のみが差し引かれる場合、一貫性は保証されません。

さらに、MySQL は 2 フェーズトランザクションコミットを通じて、REDO ログと binlog 間のデータの一貫性も保証します。

上で永続性を紹介したとき、REDO ログと binlog の違いについて説明しました。3 番目の違いは、デフォルトでは、トランザクションがコミットされると、REDO ログと binlog の両方が書き込まれるという点です。では、どのように一貫性が調整されるのでしょうか。トランザクションが正常にコミットされたかどうかを判断するために書き込まれるログはどれですか?
MySQL は、2 フェーズコミットを通じて 2 つのログのデータの一貫性を保証します。

第一段階の提出、

再実行ログをディスクに送信し、状態を準備状態に変更します。バイナリログに対して操作を実行しないでください。

第2段階の提出、

1. トランザクション操作のバイナリログを生成し、そのバイナリログをディスクに書き込みます。

2. エンジンのトランザクションコミットインターフェイスを呼び出して、REDO ログのステータスを準備からコミットに変更し、トランザクションがコミットされます。
上記の 2 段階の送信により、トランザクションデータの一貫性が保証されます。
トランザクションがコミットされるときに、REDO ログが準備段階にある場合、MySQL がクラッシュまたは異常終了すると、トランザクションはロールバックされます。
トランザクションがREDOログをコミットし、コミット段階にある場合、クラッシュが発生するとトランザクションリカバリが実行されます。ローカルトランザクションはREDOログを通じてリカバリされます。マスタースレーブデータベースの場合は、コミット段階でバイナリログに基づいてスレーブデータベースからデータが復元されます。
これは、バイナリログの正常な書き込みに基づいてトランザクションを正常に送信するための基礎となります。 binlong は通常、クラッシュ回復時の回復に使用されるためです。binlog が生成されていない場合は、REDO ログのみが書き込まれます。リカバリ中、REDO ログはデータのバージョンを復元しますが、バイナリログを通じて復元されたスレーブデータは以前の時点のデータのバイナリログバージョンになるため、データの不整合が発生します。

要約する

MySQL トランザクションの ACID と一貫性が最終的な目標です。
一貫性を確保するための対策には以下が含まれます。

AAtomicity: UNDO ログによって保証されます (例外または実行失敗後のロールバック)。
D 永続性: REDO ログによって保証されます (MySQL がクラッシュしたり停電が発生しても、データは最終的に REDO ログを通じてディスクに保存されます)。
分離: トランザクション間の読み取り操作と書き込み操作は、MySQL のロックメカニズムによって分離され、トランザクション間の書き込み操作は、MVCC メカニズム (スナップショット読み取り、現在の読み取り) によって分離されます。
C 一貫性: トランザクションの最終的な目標は、データベースレベルとアプリケーションレベルの両方で一貫性を確保することです。さらに、MySQL は 2 フェーズコミットトランザクションを使用して、永続化中のトランザクションの一貫性を確保します。

上記は、MySQL がトランザクションの ACID を実装する方法の詳細です。MySQL がトランザクションの ACID を実装する方法の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

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