The underlying principle of ACID

ACID トランザクションの基礎原理

A（原子性）とは、すべての操作が完全に実行されるか、あるいは全く実行されないかのいずれかであることを意味します。この仕組みは undo ログによって実現されます。トランザクションがデータベースを変更する際、InnoDB は対応する undo ログを生成します。undo ログには複数のバージョンがあり、各バージョンには前のバージョンと逆の操作が保存されています。SQL の実行に関連する情報が記録され、SQL の実行が失敗してロールバックが発生した場合、InnoDB は undo ログの内容に基づいて逆の処理を実行します。例えば、insert 操作を実行した場合、ロールバック時には delete という逆操作が実行されます。update に対応するのは、ロールバック時に実行される逆の update です。これが原子性の基本的な実装原理です。

一貫性の基本原理

トランザクションが完了すると、成功または失敗にかかわらず、データは一貫した状態になります。つまり、部分的に完了して部分的に失敗するという状態にはなりません。トランザクションの実行前後で、データベースの整合性制約は侵害されず、トランザクション実行前後ともに正当なデータ状態が保たれます。トランザクションの AID はデータベースの特性であり、データベースの具体的な実装に依存します。しかし、この C（一貫性）のみはアプリケーション層、つまり開発者に依存します。ここでの一貫性とは、データがある正しい状態から別の正しい状態へ遷移することを指します。

例：アカウント A からアカウント B へ 1000 を振り込む場合、A の振り込み額は自身の口座残高以下でなければなりません。つまり、トランザクションコミット時に A の口座残高が負になることはなく、アカウント金額フィールドの値が 0 以上であることをデータベース制約で保証できます。

InnoDB による一貫性非ロック読み取りの実装方法（MVCC の原理）

一貫性非ロック読み取り（consistent nonlocking read）とは、InnoDB ストレージエンジンが行のマルチバージョン管理を通じて、データベース内の対象行の現在の実行時点でのデータを読み取ることを意味します。読み取り対象の行が DELETE または UPDATE 操作を実行中の場合、読み取り操作は行ロックの解放を待機しません。その代わりに、InnoDB ストレージエンジンはその行のスナップショットを読み取ります。アクセス対象の行に対する X ロックの解放を待機する必要がないため、非ロック読み取りと呼ばれます。スナップショットデータとは、行の前のバージョンのデータを指し、undo セグメントを介して実装されます。undo はトランザクション内のデータをロールバックするために使用されるため、スナップショットデータ自体に追加のオーバーヘッドはありません。さらに、スナップショットデータの読み取りにはロックが不要です。どのトランザクションも履歴データを変更する必要がないためです。スナップショットデータは実際には現在の行データの前の履歴バージョンであり、各行のレコードには複数のバージョンが存在する可能性があります。右図に示すように、各行のレコードには複数のスナップショットデータが存在する可能性があり、この技術は一般に行マルチバージョン技術と呼ばれます。これにより生成される同時実行制御は、マルチバージョン同時実行制御と呼ばれます。

READ COMMITTED のトランザクション分離レベルでは、常にその行の最新バージョンを読み取り、行がロックされている場合はその行バージョンの最新スナップショット（freshsnapshot）を読み取ります。REPEATABLE READ のトランザクション分離レベルでは、常にトランザクション開始時点の行データを読み取ります。

READ COMMITTED のトランザクション分離レベルは、データベース理論の観点からは、トランザクション ACID の I（分離）の特性に違反します。

undo ログバージョンチェーンとは、1 行のデータが複数のトランザクションによって連続して変更された後、各トランザクションの変更後に、MySQL が変更前のデータ undo ロールバックログを保持し、2 つの隠しフィールド trx_id と roll_pointer を使用してこれらの undo ログを直列に接続し、履歴バージョンチェーンを形成することを意味します。

リピータブルリードの分離レベルでは、トランザクション開始時に、任意のクエリ SQL に対して現在のトランザクションの一貫性ビュー read-view が生成され、これはトランザクション終了まで変更されません（READ COMMITTED の分離レベルの場合、SQL クエリを実行するたびに再生成されます）。このビューは、クエリ実行時のすべての未コミットトランザクション ID 配列（配列内の最小 ID は min_id）と、既に作成された最大のトランザクション ID（max_id）で構成されます。トランザクション内の任意の SQL クエリ結果は、対応するバージョンチェーン内の最新データから取得し、read-view と 1 つずつ比較して最終的なスナップショット結果を取得する必要があります。バージョンチェーンの比較ルールは次のとおりです。

行の trx_id が緑色の部分（trx_id < min_id）に該当する場合、このバージョンはコミット済みのトランザクションによって生成されたことを意味し、このデータは可視です。

行の trx_id が赤色の部分（trx_id > max_id）に該当する場合、このバージョンは将来開始されたトランザクションによって生成されたものであり、不可視です（行の trx_id が現在のトランザクション自身の ID である場合は可視）。

行の trx_id が黄色の部分（min_id <= trx_id <= max_id）に該当する場合、2 つのケースがあります。

a. 行の trx_id がビュー配列内にある場合、このバージョンはまだコミットされていないトランザクションによって生成されたことを意味し、不可視です（行の trx_id が現在のトランザクション自身の ID である場合は可視）。

b. 行の trx_id がビュー配列内にない場合、このバージョンは既にコミットされたトランザクションによって生成されたことを意味し、可視です。

削除のケースは、update の特殊なケースと見なすことができます。バージョンチェーン上の最新データがコピーされ、trx_id が削除操作の trx_id に変更されます。同時に、レコードヘッダー内の deleted_flag（レコードヘッダー）のマークビットが true に設定され、現在のレコードが削除されたことを示します。クエリ時に、前述のルールに従って対応するレコードを検索します。deleted_flag のマークビットが true の場合、そのレコードは削除済みを意味し、データは返されません。

注意：begin/start transaction コマンドはトランザクションの開始点ではありません。これらを実行した後、InnoDB テーブルを変更・操作する最初の文がトランザクションを開始し、その後 MySQL にトランザクション ID を申請します。MySQL はトランザクションの開始シーケンスに厳密に従ってトランザクション ID を割り当てます。

まとめ：MVCC メカニズムは、read-view メカニズムと undo バージョンチェーン比較メカニズムを介して実装され、異なるトランザクションがバージョンチェーン上で同じデータに対して異なるバージョンを読み取るようになります。

BufferPool キャッシュメカニズム

なぜ MySQL はディスク上のデータを直接更新せず、このような複雑なメカニズムを設けて SQL を実行するのでしょうか。

1 つのリクエストがディスクファイルに対して直接ランダムな読み取り・書き込みを行い、ディスクファイル内のデータを更新する場合、パフォーマンスは非常に低くなる可能性があります。

ディスクのランダムな読み取り・書き込みのパフォーマンスが非常に低いため、ディスクファイルを直接更新してもデータベースが高い同時実行性に対応できません。MySQL のメカニズムは複雑に見えますが、各更新リクエストがメモリ BufferPool を更新し、ログファイルを順次書き込むことを保証しつつ、さまざまな異常条件下でのデータ整合性も保証します。メモリの更新パフォーマンスは非常に高く、ディスク上のログファイルを順次書き込むパフォーマンスも非常に高く、ディスクファイルのランダムな読み書きよりもはるかに高いです。このメカニズムを通じて、MySQL データベースはより高い構成のマシンで 1 秒間に数千の読み取り・書き込みリクエストに耐えることができます。

永続化の実装原理

トランザクションが完了すると、どのようなシステムエラーが発生してもその結果は影響を受けず、トランザクションの結果は永続ストレージに書き込まれます。実装原理は次のとおりです。Redo ログメカニズムにより実現されます。MySQL のデータはディスクに保存されますが、データを読み取るたびにディスク IO を経由する必要があるため、効率が非常に低くなります。InnoDB が提供するキャッシュバッファーを使用します。このバッファーには、ディスク上の一部のデータページのマッピングが含まれています。データベースにアクセスするためのバッファーとして、データベースからデータを読み取る際、まずこのバッファーから取得を試みます。バッファーにデータがない場合は、ディスクから読み取った後にこのバッファーに格納します。データベースがデータを書き込む際、まずこのバッファーにデータを書き込み、定期的にバッファー内のデータをディスクにリフレッシュして永続化操作を行います。バッファー内のデータがディスクに同期される前に MySQL がダウンした場合、バッファー内のデータは失われ、データ損失が発生して永続性が保証されなくなります。この問題を解決するために redo ログ を使用します。データベース内のデータを追加または変更する必要がある場合、バッファー内のデータを変更するだけでなく、この操作は redo ログ にも書き込まれます。MySQL がダウンした場合、redo ログ を使用してデータを復元できます。Redo ログ は先行書き込みログです。まずすべての変更をログに書き込み、その後バッファーに更新します。これにより、データが失われないことが保証され、データの永続性が確保されます。Redo ログ は主にデータのコミットまたは復元のための変更操作を記録します。

トランザクション分離は、前述のロックによって実現されます。Redo ログは redo ログと呼ばれ、トランザクションの耐久性を保証するために使用されます。Redo は通常物理ログであり、ページの物理的な変更操作を記録します。Redo ログはトランザクションの永続化、つまりトランザクション ACID の D を実現するために使用されます。これは 2 つの部分で構成されます。1 つはメモリ内の redo ログバッファー（redo logbuffer）で、揮発性です。もう 1 つは redo ログファイル（redologfile）で、永続的です。InnoDB はトランザクション用のストレージエンジンであり、Force Log at Commit メカニズムを通じてトランザクションの永続化を実装します。つまり、トランザクションがコミット（COMMIT）される際、そのトランザクションのすべてのログがまず redo ログファイルに書き込まれて永続化される必要があります。COMMIT 操作が完了して初めて完了と見なされます。各ログが redo ログファイルに書き込まれることを保証するために、InnoDB ストレージエンジンは redo ログバッファーが redo ログファイルに書き込まれるたびに fsync 操作を呼び出す必要があります。redo ログファイルは O_DIRECT オプションなしで開かれているため、redo ログバッファーはまずファイルシステムキャッシュに書き込まれます。redo ログがディスクに書き込まれることを保証するために、fsync 操作を実行する必要があります。fsync の効率はディスクのパフォーマンスに依存するため、ディスクのパフォーマンスがトランザクションコミットのパフォーマンス、つまりデータベースのパフォーマンスを決定します。

redo ログのディスクフラッシュ戦略

redo ログのディスクフラッシュ戦略は、パラメーター innodb_flush_log_at_trx_commit によって制御されます。

このパラメーターのデフォルト値は 1 で、トランザクションコミット時に fsync 操作を 1 回呼び出す必要があることを意味します。このパラメーターの値を 0 または 2 に設定することもできます。

0 は、トランザクションコミット時に redo ログ操作は書き込まれないことを意味します。この操作はマスタースレッドでのみ完了し、redo ログファイルの fsync 操作はマスタースレッドで 1 秒ごとに実行されます。

2 は、トランザクションコミット時に redo ログが redo ログファイルに書き込まれるが、ファイルシステムのキャッシュにのみ書き込まれ、fsync 操作は実行されないことを示します。この設定では、MySQL データベースがダウンしてもオペレーティングシステムがダウンしない場合、トランザクションは失われません。オペレーティングシステムがダウンした場合、ファイルシステムキャッシュから redo ログファイルにフラッシュされていないトランザクションの部分は、データベース再起動後に失われます。

例：50 万件のデータを 1 件ずつ挿入します。innodb_flush_log_at_trx_commit = 1：2 分 13 秒。50 万回の redo ログ書き込み、50 万回の fsync 操作。innodb_flush_log_at_trx_commit = 0：23 秒。約 23 回の redo ログ書き込み、約 23 回の fsync 操作。innodb_flush_log_at_trx_commit = 2：35 秒。50 万回の redo ログ書き込み（キャッシュのみ）、0 回の fsync 操作。

ユーザーはパラメーター innodb_flush_log_at_trx_commit を 0 または 2 に設定することでトランザクションコミットのパフォーマンスを向上させることができますが、この設定方法ではトランザクションの ACID 特性が失われることに注意する必要があります。前述のストアドプロシージャの場合、トランザクションのコミットパフォーマンスを向上させるためには、テーブルに 50 万行を挿入した後に 1 回の COMMIT 操作を実行すべきであり、各レコードを挿入するたびに COMMIT 操作を実行するのではなく、これによりトランザクションメソッドがロールバック時にトランザクションの初期の確実な状態にロールバックできるという利点もあります。

正しい方法：innodb_flush_log_at_trx_commit = 1、50 万件のデータを 1 つのトランザクションまたは複数のトランザクションで分散コミットし、fsync の回数を削減します。

分離の実装原理

複数のトランザクションが同じデータを並行して処理するため、各トランザクションは他のトランザクションから分離されている必要があり、データ破損を防ぎます。実装原理：書き込み・書き込み操作：ロックを通じて行われ、その原理は Java のロックメカニズムと同じです。書き込み・読み取り操作：MVCC マルチバージョン同時実行制御。デフォルトでは、1 行のデータの読み取りと書き込みという 2 つの操作は、ロックと相互排他を通じて分離を保証するのではなく、頻繁なロックと相互排他を回避します。1 行のデータが複数のトランザクションによって連続して変更された後、各トランザクションの変更後に、MySQL は変更前のデータ undo ロールバックログを保持し、2 つの隠しフィールド trx_id と roll_pointer を使用してこれらの undo ログを連結し、履歴レコードバージョンチェーンを形成します。

リピータブルリードの分離レベルでは、トランザクション開始時に任意の SQL クエリを実行すると、現在のトランザクションの一貫性ビュー read-view が生成されます。つまり、最初の select でバージョンが生成され、read-view ビューはトランザクション終了まで変更されません。リードコミッティドの分離レベルの場合、SQL クエリを実行するたびにビュー read-view が再生成されます。つまり、select を実行するたびにバージョンが生成されます。クエリ実行時に、対応するバージョンチェーン内の最新データから開始して read-view と 1 つずつ比較し、現在のトランザクション ID と readview ビュー配列内の作成済み最小トランザクション ID および作成済み最大トランザクション ID を比較します。ここでは 3 つのケースがあります。1 つ目のケースは、現在のトランザクションの ID が配列内の最小 ID よりも小さい場合、このバージョンはコミット済みのトランザクションによって生成されたことを示し、データが可視であることを意味します。2 つ目は、現在のトランザクション ID が作成済みの最大トランザクション ID よりも大きい場合、このバージョンはまだトランザクションが開始されていないことを示し、不可視であることを意味します。3 つ目は、この範囲内にある場合で、アクセス対象のトランザクション ID が最小トランザクション ID と最大トランザクション ID の間にある場合、2 つのケースがあります。1 つ目は、このバージョンがまだコミットされていないトランザクションによって生成されたものであり、不可視です。2 つ目は、このバージョンが既にコミットされたトランザクションによって生成されたものであり、可視です。この比較を行って最終的なスナップショット結果を取得します。このメカニズムにより分離が保証されます。

分離レベル

SQL 標準で定義された 4 つの分離レベルは次のとおりです。
❑READ UNCOMMITTED（ダーティリードを引き起こす）
❑READ COMMITTED（ファントムリードを引き起こす）
❑REPEATABLE READ（デフォルトで使用され、ファントムリードとダーティリードを回避する）
❑SERIALIZABLE（より高い分離レベル、ファントムリードを回避し、ダーティリードを回避する）

InnoDB ストレージエンジンがサポートするデフォルトの分離レベルは REPEATABLE READ ですが、標準 SQL とは異なり、InnoDB ストレージエンジンは REPEATABLE READ トランザクション分離レベルの下で Next-Key Lock アルゴリズムを使用するため、ファントムリードを回避します。これは Microsoft SQL Server データベースなどの他のデータベースシステムとは異なります。したがって、InnoDB ストレージエンジンは、REPEATABLE READ というデフォルトのトランザクション分離レベルの下で、トランザクションの分離要件を完全に保証できます。つまり、SQL 標準の SERIALIZABLE 分離レベルに相当します。分離レベルが低いほど、トランザクションがリクエストまたは保持するロックは少なくなり、保持期間も短くなります。これが、ほとんどのデータベースシステムのデフォルトのトランザクション分離レベルが READ COMMITTED である理由です。

SERIALIZABLE のトランザクション分離レベルでは、InnoDB ストレージエンジンは各 SELECT 文に自動的に LOCK IN SHARE MODE を追加します。つまり、各読み取り操作に共有ロックを追加します。したがって、このトランザクション分離レベルの下では、読み取りはロックを占有し、一貫性非ロック読み取りはもはやサポートされません。

ダーティリード／反復不能読み取り／ファントムリード

トランザクションの分離が考慮されない場合、いくつかの問題が発生します。

1 つ目の問題はダーティリードです。あるトランザクション内で、別の未コミットトランザクションのデータが読み取られます。たとえば、会社で給与が支払われ、リーダーが私のアカウントに 4 万円を送金したが、トランザクションは未コミットでした。私が偶然口座を確認したところ、給与が既に到着しており、4 万円であることがわかりました。私はとても幸せでした。しかし残念ながら、リーダーは私に支払った給与の金額が間違っており、3 万 5 千円であったことに気づき、急いで金額を修正してこの件をコミットしました。結局、私の実際の給与は 3 万 5 千円だけであり、無駄に喜んだだけでした。

2 つ目の問題は反復不能読み取りです。トランザクションの範囲内で特定のデータを複数回クエリしますが、異なる結果が返されます。簡単に言うと、トランザクション T1 がデータを読み取り、トランザクション T2 が直ちにそのデータを変更してトランザクションをデータベースにコミットします。トランザクション T1 がこのデータを再度読み取ると、異なる結果が取得され、反復不能読み取りが発生します。たとえば、私の給与カードで買い物をした際、システムにはカードに確かに 100 円あることが読み取れました。この時、私のガールフレンドがちょうど私の給与カードを使ってオンラインで振り込みを行い、100 円を私の給与カードから別のアカウントに送金し、私の前にトランザクションをコミットしました。私が代金を引き落とした時、システムは私の給与カードに残高がないことを確認し、引き落としは失敗しました。カードにお金があったのに、なぜなのか非常に困惑しました。

3 つ目の問題はファントムリードです。トランザクション T1 があるテーブルのデータを「1」から「2」に変更します。この時、トランザクション T2 がこのテーブルに別のデータを挿入し、このデータの値はまだ「1」であり、データベースにコミットします。トランザクション T1 を操作するユーザーが先ほど変更したデータを確認すると、まだ 1 行が変更されていないことがわかります。たとえば、私の給与カードで消費する際、一度システムが給与カードの情報を読み取りトランザクションが開始されると、私のガールフレンドがその記録を変更することはできません。つまり、私のガールフレンドはこの時に振り込みを行うことはできません。これにより反復不能読み取りが回避されます。私のガールフレンドが銀行部門で働いており、銀行の内部システムを通じて私の給与カードの消費記録を頻繁に確認していると仮定します。ある日、彼女は私のクレジットカードの今月の総消費額（select sum(amount) from transaction where month = this month）を照会しており、80 円でした。この時、私がちょうど外で飲食した後にレジで決済しており、1,000 円、つまり 1,000 円の新しい消費記録（insert transaction ...）が追加され、トランザクションがコミットされました。その後、私のガールフレンドは私の月間給与カード消費の詳細を A4 用紙に印刷しましたが、総消費額は 1,080 円であることがわかりました。私のガールフレンドは非常に驚き、幻覚を見たと思いました。ファントムリードがまさにこれです。