Aurora MySQL の分離レベル

Aurora MySQL クラスターの DB インスタンスが分離のデータベースプロパティを実装する方法について説明します。このトピックでは、Aurora MySQL のデフォルトの動作で厳密な一貫性と高いパフォーマンスのバランスがどのように取られるかを説明します。この情報を使用して、ワークロードの特性に基づいて、デフォルト設定を変更するタイミングを判断することができます。

ライターインスタンスで使用可能な分離レベル

Aurora MySQL DB クラスターのプライマリインスタンスでは、分離レベル REPEATABLE READ、READ COMMITTED、READ UNCOMMITTED、および SERIALIZABLE を使用できます。これらの分離レベルは、Aurora MySQL と RDS for MySQL で同じように機能します。

リーダーインスタンスでの REPEATABLE READ の分離レベル

デフォルトでは、読み取り専用の Aurora レプリカとして設定された Aurora MySQL DB インスタンスは、常に REPEATABLE READ 分離レベルを使用します。これらの DB インスタンスは、SET TRANSACTION ISOLATION LEVEL ステートメントを無視し、引き続き REPEATABLE READ 分離レベルを使用します。

DB パラメータまたは DB クラスターパラメータを使用して Reader DB インスタンスの分離レベルを設定することはできません。

リーダーインスタンスでの READ COMMITTED の分離レベル

アプリケーションのプライマリインスタンスに書き込み集中型のワークロードが含まれ、Aurora レプリカに長時間実行されるクエリが含まれている場合、かなりのパージラグが発生する可能性があります。パージラグは、長時間実行されるクエリによって内部ガベージコレクションがブロックされた場合に発生します。確認できる症状は、SHOW ENGINE INNODB STATUS コマンドからの出力で history list length の値が高いことです。この値をモニタリングするには、CloudWatch の RollbackSegmentHistoryListLength メトリクスを使用します。大幅なパージラグが発生すると、セカンダリインデックスの有効性が低下し、全体的なクエリパフォーマンスの低下とストレージ領域の浪費が起きる可能性があります。

このような問題が発生した場合は、Aurora レプリカで READ COMMITTED 分離レベルを使用するように、Aurora MySQL セッションレベルの構成設定 aurora_read_replica_read_committed を設定します。この設定を適用すると、テーブルを変更するトランザクションと同時に長時間実行されるクエリを実行した場合に発生する可能性のある、速度低下と領域の浪費を軽減できます。

この設定を使用するには、Aurora MySQL の READ COMMITTED 分離に固有の動作を理解しておくことをお勧めします。Aurora レプリカでの READ COMMITTED の動作は、ANSI SQL スタンダードに準拠しています。ただし、分離は一般的な MySQL の READ COMMITTED の存知の動作ほど厳密ではありません。そのため、Aurora MySQL のリードレプリカでの READ COMMITTED は、Aurora MySQL のプライマリインスタンスや RDS for MySQL での READ COMMITTED の同じクエリとは異なるクエリ結果になる場合があります。非常に大規模なデータベースをスキャンする包括的なレポートなどの場合には、aurora_read_replica_read_committed 設定の使用を検討してください。逆に、精度と再現性が重要な、結果セットが小さいショートクエリでは、回避した方がよい場合があります。

READ COMMITTED 独立性レベルは、書き込み転送機能を使用する Aurora Global Database 内のセカンダリクラスター内のセッションでは使用できません。書き込み転送の詳細については、「Amazon Aurora Global Database の書き込み転送を使用する」を参照してください。

リーダーでの READ COMMITTED の使用

Aurora レプリカで READ COMMITTED 分離レベルを使用するには、aurora_read_replica_read_committed 構成設定を ON に設定します。特定の Aurora レプリカに接続しているときに、セッションレベルでこの設定を使用します。有効にするには、以下の SQL コマンドを実行します。

set session aurora_read_replica_read_committed = ON;
set session transaction isolation level read committed;

この構成設定を一時的に有効にして、インタラクティブなアドホック (1 回限りの) クエリを実行することもできます。また、READ COMMITTED 分離レベルのメリットを活かせるレポートまたはデータ分析アプリケーションを実行して、他のアプリケーションについては、デフォルト設定のままにしておくこともできます。

aurora_read_replica_read_committed 設定が有効になっているときに、SET TRANSACTION ISOLATION LEVEL コマンドを使用して、該当するトランザクションの分離レベルを指定します。

set transaction isolation level read committed;

Aurora レプリカでの READ COMMITTED の動作の違い

aurora_read_replica_read_committed 設定は、Aurora レプリカで READ COMMITTED 分離レベルを使用可能にし、長時間実行されるトランザクション用に最適化された一貫性動作を実現します。Aurora レプリカでの READ COMMITTED 分離レベルの厳密性は、Aurora プライマリインスタンスでの厳密性より劣ります。そのため、クエリが特定のタイプの一貫性のない結果を受け入れられることがわかっている Aurora レプリカでのみ、この設定を有効にしてください。

aurora_read_replica_read_committed 設定がオンのときに、クエリで特定の種類の読み取り異常が発生する可能性があります。アプリケーションコードについて理解して処理するためには、2 種類の異常が特に重要です。クエリの実行中に別のトランザクションがコミットすると、繰り返し不可のリードが発生します。長時間実行されるクエリでは、クエリのスタート時に、終了時に表示されるデータとは異なるデータが表示されることがあります。ファントムリードは、クエリの実行中に他のトランザクションによって既存の行が再編成され、1 つ以上の行がクエリによって 2 回読み取られると発生します。

ファントムリードの結果として、クエリで一貫性のない行カウントが実行される場合があります。繰り返し不可のリードが原因で、クエリから不完全または一貫性のない結果が返されることもあります。例えば、結合操作が SQL ステートメント (INSERT、DELETE など) によって同時に変更されるテーブルを参照するとします。この場合、結合クエリは、あるテーブルの行を読み取りますが、別のテーブルの対応する行は読み取らない可能性があります。

ANSI SQL スタンダードでは、READ COMMITTED 分離レベルでこれらの両方の動作が許可されます。ただしこれらの動作は、READ COMMITTED の一般的な MySQL 実装とは異なります。そのため、aurora_read_replica_read_committed 設定を有効にする前に、既存の SQL コードを調べて、よりあいまいな整合性モデルで期待どおりに動作するかどうかを確認します。

この設定が有効になっている場合、READ COMMITTED 分離レベルでは、行カウントとその他の結果の一貫性があまりない場合があります。したがって通常は、大量のデータを集計し、絶対的な精度を必要としない分析クエリを実行しているときにのみ、設定を有効にします。これらの種類の長時間実行クエリを、書き込み集中型のワークロードと組み合わせて使用しない場合、aurora_read_replica_read_committed 設定は不要である可能性があります。長時間実行されるクエリと書き込み集中型のワークロードの組み合わせがなければ、履歴リストの長さに問題が発生することはほとんどありません。

例 Aurora レプリカでの READ COMMITTED の分離動作を示すクエリ

次の例は、トランザクションが関連するテーブルを同時に変更した場合に、Aurora レプリカで実行された READ COMMITTED クエリが繰り返し不可能な結果を返す方法を示しています。テーブル BIG_TABLE には、クエリスタートの前に 100 万行が含まれています。他のデータ操作言語 (DML) ステートメントは、実行中に行を追加、削除、または変更します。

Aurora プライマリインスタンスにおける READ COMMITTED 分離レベルでのクエリは、予測可能な結果を生成します。ただし、長時間実行されるすべてのクエリのライフタイムにわたって一貫した読み取りビューを維持するオーバーヘッドにより、後で高コストのガベージコレクションが発生する可能性があります。

Aurora レプリカにおける READ COMMITTED 分離レベルでのクエリは、このガベージコレクションのオーバーヘッドを最小限に抑えるように最適化されます。トレードオフとして、クエリの実行中にコミットされるトランザクションによって追加、削除、または再編成された行をクエリが取得するかどうかによって結果が異なる場合があります。クエリではこれらの行を考慮することができますが、必須ではありません。デモンストレーションの目的で、クエリは COUNT(*) 関数を使用してテーブル内の行数のみをチェックします。

時間	Aurora プライマリインスタンスでの DML ステートメント	Aurora プライマリインスタンスでの READ COMMITTED を使用したクエリ	Aurora レプリカでの READ COMMITTED を使用したクエリ
T1	INSERT INTO big_table SELECT * FROM other_table LIMIT 1000000; COMMIT;
T2		Q1: SELECT COUNT(*) FROM big_table;	Q2: SELECT COUNT(*) FROM big_table;
T3	INSERT INTO big_table (c1, c2) VALUES (1, 'one more row'); COMMIT;
T4		Q1 が終了すると、結果は 1,000,000 になります。	Q2 が終了すると、結果は 1,000,000 または 1,000,001 になります。
T5	DELETE FROM big_table LIMIT 2; COMMIT;
T6		Q1 が終了すると、結果は 1,000,000 になります。	Q2 が終了すると、結果は 1,000,000、1,000,001、999,999、999,998 のいずれかになります。
T7	UPDATE big_table SET c2 = CONCAT(c2,c2,c2); COMMIT;
T8		Q1 が終了すると、結果は 1,000,000 になります。	Q2 が終了すると、結果は 1,000,000、1,000,001、999,999、またはそれより大きい値になります。
T9		Q3: SELECT COUNT(*) FROM big_table;	Q4: SELECT COUNT(*) FROM big_table;
T10		Q3 が終了すると、結果は 999,999 になります。	Q4 が終了すると、結果は 999,999 になります。
T11		Q5: SELECT COUNT(*) FROM parent_table p JOIN child_table c ON (p.id = c.id) WHERE p.id = 1000;	Q6: SELECT COUNT(*) FROM parent_table p JOIN child_table c ON (p.id = c.id) WHERE p.id = 1000;
T12	INSERT INTO parent_table (id, s) VALUES (1000, 'hello'); INSERT INTO child_table (id, s) VALUES (1000, 'world'); COMMIT;
T13		Q5 が終了すると、結果は 0 になります。	Q6 終了すると、結果は 0 または 1 になります。

他のトランザクションが DML ステートメントを実行してコミットする前にクエリが迅速に終了する場合、結果は予測可能であり、プライマリインスタンスでも Aurora レプリカでも同じ結果になります。最初のクエリから始めて、動作の違いを詳しく調べてみましょう。

プライマリインスタンスでの READ COMMITTED は REPEATABLE READ 分離レベルと同様の強力な整合性モデルを使用するため、Q1 の結果の予測可能性が非常に高くなります。

Q2 の結果は、クエリの実行中にコミットされるトランザクションに応じて異なる場合があります。例えば、他のトランザクションが DML ステートメントを実行し、クエリの実行中にコミットするとします。この場合、Aurora レプリカでの READ COMMITTED 分離レベルを使用したクエリでは、変更が考慮される場合と考慮されない場合があります。行数は、REPEATABLE READ 分離レベルの場合と同じ方法では予測できません。さらに、プライマリインスタンスまたは RDS for MySQL インスタンスにおいて READ COMMITTED 分離レベルで実行されるクエリほど予測可能ではありません。

T7 の UPDATE ステートメントは、実際にはテーブル内の行数を変更しません。ただし、可変長列の長さを変更すると、このステートメントによって行が内部的に再編成される可能性があります。長時間実行される READ COMMITTED トランザクションでは、古いバージョンの行が表示され、同じクエリ内で後から同じ行の新しいバージョンが表示される場合があります。クエリでは、行の古いバージョンと新しいバージョンの両方をスキップすることもできます。そのため、行カウントが予想と異なる場合があります。

Q5 と Q6 の結果は、同じである場合と、わずかに異なる場合があります。Aurora レプリカにおける READ COMMITTED でのクエリ Q6 は、クエリの実行中にコミットされた新しい行を表示できますが、表示する必要はありません。また、一方のテーブルの行は表示され、もう一方のテーブルの行は表示されないこともあります。結合クエリは、両方のテーブルで一致する行を見つけられない場合、ゼロのカウントを返します。クエリは、PARENT_TABLE と CHILD_TABLE の両方で新しい行を検出した場合、カウント 1 を返します。長時間実行されるクエリでは、結合されたテーブルからの検索が行われる時間に大きな分離が生じる可能性があります。

注記

これらの動作の違いは、トランザクションがコミットされるタイミングと、基になるテーブル行をクエリが処理するタイミングによって異なります。したがって、このような違いが見られる可能性が最も高くなるのは、数分または数時間かかるレポートクエリ、および OLTP トランザクションを同時に処理する Aurora クラスターで実行されるレポートクエリです。これらは、Aurora レプリカでの READ COMMITTED 分離レベルのメリットを最も享受する種類の混合ワークロードです。