インタビュアー: MySQL はどのように ACID を実装しますか?-＆＃＆面接の質問-php.cn

面接では、面接官は MySQL の ACID について質問するだけで、すぐに 8 部構成のエッセイを暗唱できます (まだ答えられない人もいるかもしれません)。さらに腹立たしいのは、一部の面接官がルーチンに従わず、「MySQL は ACID をどのように実装するのですか?」と質問し続けることです。

私は混乱しています。正直に言うと、この質問は 95% の人を説得できます。

今日の記事では、MySQL InnoDB エンジンにおける ACID の実装原理について主に説明し、トランザクションとは何か、分離レベルの意味などの基礎知識についてはあまり詳しく説明しません。。

ACID

リレーショナルデータベースとして、MySQL は最も一般的な InnoDB エンジンを使用して ACID を保証します。

（原子性 ）原子性: トランザクションは最小の実行単位であり、分割は許可されません。アトミック性により、アクションが完全に完了するか、まったく影響がないことが保証されます;
(一貫性) 一貫性: データはトランザクションの実行前後で一貫性を保ちます。
##(分離) 分離: データベースに同時にアクセスする場合、トランザクションは他のトランザクションによって干渉されません。
(耐久性) 耐久性: トランザクションがコミットされた後。データベース内のデータへの変更は、データベースに障害が発生した場合でも保持されます。

分離

まずは分離について説明しましょう。最初は 4 つの分離レベルです。

#分離レベル説明##コミットされていない読み取り送信を読む


いつトランザクションが送信されていない場合、その変更は他のトランザクションから確認できます
トランザクションが送信された後、その変更は他のトランザクションからのみ確認できます。他のトランザクションで表示されます。他のトランザクションで表示されます
反復読み取り	トランザクションでは、他のトランザクションがデータを操作しているかどうか、トランザクションがコミットされているかどうかに関係なく、同じデータを読み取った結果は常に同じです。 InnoDB のデフォルトレベル。
シリアル化	トランザクションはシリアルに実行されます。各読み取りにはテーブルレベルの共有ロックが必要です。読み取りと書き込みは相互にブロックされます。分離レベルは最高であり、犠牲になります。システムの同時実行性。

さまざまな分離レベルは、さまざまな問題を解決するために設計されています。つまり、ダーティリード、ファントムリード、およびノンリピータブルリードです。

#未コミットの読み取り表示される可能性がある表示される可能性がある表示される可能性がある送信の読み取り許可されません発生する可能性があります発生する可能性があります繰り返し読み取り許可されません許可されません表示できます

隔离级别	脏读	不可重复读	幻读



シリアル化	許可されません	許可されません	許可されません

では、分離レベルが異なると、分離はどのように達成されるのでしょうか。また、異なるものが互いに干渉しないのはなぜでしょうか?答えは Locks と MVCC です。

ロック

まずロックについて話しましょう。MySQL にはいくつのロックがありますか?

粒度

粒度の観点から見ると、テーブルロック、ページロック、および行ロックです。テーブルロックには、意図的な共有ロック、意図的な排他ロック、自己増加ロックなどが含まれます。行ロックは、各エンジンによってエンジンレベルで実装されます。ただし、すべてのエンジンが行ロックをサポートしているわけではなく、たとえば、MyISAM エンジンは行ロックをサポートしていません。

行ロックの種類

InnoDB トランザクションでは、行ロックはインデックス上のインデックスエントリをロックすることによって実装されます。これは、InnoDB がインデックス条件を通じてデータを取得する場合にのみ行レベルのロックを使用し、それ以外の場合はテーブルロックを使用することを意味します。行レベルのロックも、共有ロックと排他ロック、およびロックする前に取得する必要がある意図共有ロックと意図排他ロックの 2 つのタイプに分類されます。

共有ロック: 読み取りロック。他のトランザクションは S ロックを追加できますが、他のトランザクションは X ロックを追加できません。つまり、他のトランザクションは読み取りのみでき、書き込みはできません。 選択...共有モードでロック ロックします。
排他ロック: 書き込みロック。他のトランザクションは S ロックまたは X ロックを追加できません。 挿入、更新、削除、更新用ロック。

行ロックは必要に応じて 追加されますが、必要がなくなってもすぐには解放されず、トランザクションが終了するまで解放されません。これは 2 フェーズロックプロトコルです。

行ロックの実装アルゴリズム

レコードロック

単一行レコードのロックは、常にインデックスレコードをロックします。

ギャップロック

ギャップロック、ファントム読み取りの理由を考えてください。実際、行ロックは行をロックすることしかできませんが、新しいレコードを挿入するときに更新する必要があるのは、「「レコード間のギャップ」。 そこで、ギャップロックを追加してファントム読み取りを解決します。

ネクストキーロック

ギャップロック記録ロック、開いたまま、閉じたままにします。

ロックと分離

下部ロックの一般的な概要を参照できます。ロックを使用すると、トランザクションがデータを書き込んでいる間、他のトランザクションは書き込みロックを取得できず、データを書き込むことができなくなり、トランザクション間の分離がある程度確保されます。しかし、前述したように、書き込みロックを追加した後、他のトランザクションもデータを読み取ることができるのはなぜですか? 読み取りロックが取得できないためではないでしょうか? ?

MVCC

前述したように、ロックを使用すると、現在のトランザクションは書き込みロックなしではデータを変更できませんが、データの行を変更した場合でも読み取りは可能です。他のトランザクションによって送信された場合でも、同じ値を繰り返し読み取ることができます。これは MVCC、マルチバージョン同時実行制御、Multi-Version Concurrency Control です。

バージョンチェーン

Innodb の行レコードの保存形式には、いくつかの追加フィールドがあります: DATA_TRX_ID および DATA_ROLL_PTR。

DATA_TRX_ID: データ行のバージョン番号。この行のレコードを最近変更したトランザクション ID を識別するために使用されます。
DATA_ROLL_PTR: 行のロールバックセグメントへのポインタ。この行に記録されている古いバージョンはすべて、undo log 内のリンクリストの形式で編成されています。

#undo ログ: データが変更される前にログを記録します。これについては後で詳しく説明します。

ReadView

は各 SQL の先頭で作成され、いくつかの重要な属性があります:

trx_ids: 現在のシステム内のアクティブな (コミットされていない) トランザクションのバージョン番号のコレクション。
low_limit_id: 現在の読み取りビューを作成するときの「現在のシステムの最大 トランザクションバージョン番号 1」。
#up_limit_id: 現在の読み取りビューの作成時に「システムは アクティブなトランザクションです最小バージョン番号です」
creator_trx_id: 現在の読み取りビューのトランザクションバージョン番号を作成します;

次にクエリを開始します。選択が行われ、データ行が見つかります。

DATA_TRX_ID
DATA_TRX_ID >= low_limit_id：
現在の読み取りビューが作成された後にデータが生成され、データが表示されません。

DATA_ROLL_PTR によれば、見つからない場合は空になります。

low_limit_id:

分離レベルによって異なります。

#RR レベルのファントム読み取り

ロックと MVCC により、トランザクション分離性が解決されます。ここでもう少し詳しく説明しますが、デフォルトの RR レベルはファントムリーディングを解決しますか?通常、ファントム読み取りは

INSERT をターゲットにし、 非再現性は UPDATE をターゲットにします。

##begin 始める部門から * を選択#- commit#更新部門セット名 = "B"#commit# ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

私たちはそれを予想していました

id  name
1   A
2   B

ログイン後にコピー

実際は

id  name
1   B
2   B

ログイン後にコピー

でした

実際、MySQL反復読み取りの分離レベルはファントム読み取りの問題を完全に解決するわけではありませんが、解決します。データ読み取り時のファントム読み取りの問題。変更操作にはファントムリードの問題が依然として存在します。これは、MVCC がファントムリードの解決に完全ではないことを意味します。

原子性

原子性について話しましょう。前述したように、元に戻すログはログをロールバックします。分離 MVCC は、アトミック性と同様に、実際に達成するためにこれに依存します。アトミック性を実現する鍵は、トランザクションがロールバックされたときに、正常に実行されたすべての SQL ステートメントを元に戻せることです。

トランザクションがデータベースを変更すると、InnoDB は対応する Undo ログを生成します。トランザクションの実行が失敗するかロールバックが呼び出され、トランザクションがロールバックされると、Undo ログ内の情報を使用してロールバックできます。データを変更前の状態に戻します。 Undo ログは、SQL の実行に関連する情報を記録する論理ログです。ロールバックが発生すると、InnoDB は元に戻すログの内容に基づいて前の作業の逆を実行します。

挿入のたびに、削除はロールバック中に実行されます;
削除のたびに、挿入はロールバック中に実行されます;
更新ごとに、ロールバック中に反対の更新が実行され、データが元に戻ります。

更新操作を例に挙げます。トランザクションが更新を実行すると、生成された元に戻すログには、変更された行の主キーが含まれます (どの行が変更されたかを知るため) ) およびどの列が変更されたか、変更前後のこれらの列の値、およびその他の情報を使用すると、ロールバック時にデータを更新前の状態に復元するために使用できます。

永続性

Innnodb には多くのログがあり、永続性は REDO ログに依存しています。

SQL 更新ステートメントを実行する方法

永続性は間違いなく書き込みに関連しています。WAL テクノロジは MySQL でよく言及されています。WAL の正式名は Write-Ahead Logging です。その重要な点は次のとおりです。最初にログを書き込み、次にディスクに書き込みます。小さなお店で商売をするのと同じように、ピンクのボードと家計簿があり、来客時はまずピンクのボードに書き、暇なときは家計簿を書きます。

redo ログ

redo ログはこのピンク色のボードです。レコードを更新する必要がある場合、InnoDB エンジンはまずレコードを REDO ログに書き込みます (そしてメモリを更新します)。アップデートが完了しました。この操作記録は適切なタイミングでディスクに更新されますが、この更新は、店主が閉店後に行うときのように、システムが比較的アイドル状態のときに行われることがよくあります。

REDO ログには 2 つの特徴があります:

固定サイズ、循環書き込み
クラッシュセーフ

REDO ログには 2 つの段階があります: コミット「2 フェーズコミット」を使用しない場合、データベースの状態は、そのログを使用して復元されたライブラリの状態と一致しない可能性があります。では、最初にここに行き、もう 1 つを見てみましょう。

バッファプール

InnoDB はキャッシュも提供します。バッファプールには、データベースにアクセスするためのバッファとしてディスク上のいくつかのデータページのマッピングが含まれています。

データを読み取るときは、最初にバッファプールから読み取られます。バッファプールにない場合は、ディスクから読み取られてからバッファプールに置かれます。
データをデータベースに書き込む場合、まずバッファプールに書き込まれ、バッファプール内の変更されたデータが定期的にディスクに更新されます。

バッファプールを使用すると、データの読み取りと書き込みの効率が大幅に向上しますが、新しい問題も発生します。MySQL がダウンすると、バッファプール内の変更されたデータが更新されないということです。現時点では、ディスクにコピーするとデータが失われるため、トランザクションの耐久性は保証できません。

そこで、REDO ログを追加しました。 データが変更されると、バッファープール内のデータの変更に加えて、その操作が REDO ログにも記録されます。

トランザクションが送信されると、fsync インターフェイスが呼び出されます。 REDO ログをフラッシュします。

MySQL がダウンした場合、再起動時に REDO ログ内のデータを読み取り、データベースを復元できます。

REDO ログは WAL (先行書き込みログ、先行書き込みログ) を使用します。すべての変更は最初にログに書き込まれ、次にバッファプールに更新されるため、MySQL が原因でデータが失われることはありません。ダウンタイムが発生しないため、耐久性要件が満たされます。これを行うことには 2 つの利点があります:

ダーティページのブラッシングはランダム IO、REDO ログはシーケンシャル IO
ダーティページのブラッシングはページに基づいており、ページ上の変更全体を書き込む必要があります; REDO ログには本当に書き込む必要があるものだけが含まれるため、無効な IO が減少します。

binlog

これについて言えば、書き込み操作やデータの回復にも使用される bin ログがあるのではないかと疑問に思われるかもしれません。違い？

レベル: redo ログは innoDB エンジンに固有であり、サーバー層は binlog (アーカイブログ) と呼ばれます
内容: redolog は物理ログです、「特定のデータページにどのような変更が行われたか」を記録します; binlog は論理ログであり、「ID=2 の行の c フィールドに 1 を追加する」などのステートメントの元のロジックです
Write : redolog はループ内に書き込まれ、多くの書き込み機会があります。binlog はトランザクションがコミットされたときに追加されて書き込まれます

binlog と redo log

ステートメント update T set c=c 1 where ID=2;

の場合

エグゼキュータは、まずエンジンを検索して行 ID=2 を取得します。 ID は主キーであり、ツリー検索を使用して直接見つけることができます。 ID = 2 の行が配置されているデータページがメモリ内にある場合は、そのデータページがエグゼキュータに直接返されます。それ以外の場合は、最初にディスクからメモリに読み取ってから返す必要があります。
エグゼキュータは、エンジンによって指定された行データを取得し、この値に 1、N 1 を加算し、新しいデータ行を取得して、エンジンインターフェイスを呼び出してこの新しい行を書き込みます。データの。
エンジンは、この新しいデータ行をメモリに更新し、更新操作を REDO ログに記録します。この時点で、REDO ログは準備状態になります。次に、実行が完了し、いつでもトランザクションを送信できることを実行者に通知します。
エグゼキューターは、この操作のバイナリログを生成し、そのバイナリログをディスクに書き込みます。
#エグゼキューターはエンジンのコミットトランザクションインターフェイスを呼び出し、エンジンは書き込まれたばかりの REDO ログをコミット状態に変更し、更新が完了します

なぜ最初に REDO ログを書き込むのでしょうか?

最初にやり直してから bin を実行します。binlog が失われ、1 つの更新が欠落しており、回復後も 0 のままです。
最初のビンとその後のやり直し: もう 1 つのトランザクションがあり、回復後は 1 になります。

一貫性

一貫性は、トランザクションによって追求される最終的な目標です。前の質問で述べたアトミック性と耐久性です。セキュリティと分離は、実際にはデータベース状態の一貫性を確保するために行われます。もちろん、上記はすべてデータベースレベルでの保証であり、整合性の実装にはアプリケーションレベルでの保証も必要です。

つまり、あなたのビジネスです。たとえば、購入操作では、在庫を減らさずにユーザーの残高のみが差し引かれます。ステータスの一貫性を保証することは絶対に不可能です。