MySQL がデフォルトの分離レベルとして繰り返し読み取りを選択する理由

多くの読者は、MySQL のトランザクション分離レベルをよくご存知だと思います。インターネット上には関連記事がたくさんあります。多くの人が、さまざまな分離レベルと、異なるレベルで解決できるいくつかの現象について知っています。

ANSI/ISO SQL では、高から低の順に、Serializable、Repeatable Reads、Read Committed、Read Uncommitted の 4 つの標準分離レベルが定義されていることがわかっています。

RU 分離レベルでは、ダーティ リード、ファントム リード、反復不可能なリードなどの問題が発生する可能性があります。 RC 分離レベルでは、ダーティ リードの問題は解決されますが、ファントム リードと非反復リードの問題は依然として存在します。 RR 分離レベルでは、ダーティ リードと非反復リードの問題は解決されますが、ファントム リードの問題は依然として存在します。シリアル化可能分離レベルでは、ダーティ リード、ファントム リード、および非反復可能リードの問題が解決されます。

これら 4 つの分離レベルは、ANSI/ISO SQL 標準によって定義されています。一般的に使用されている MySQL は、4 つの分離レベルすべてをサポートしています。ただし、Oracle データベースは Serializable と Read Committed のみをサポートしています。

しかし、 Oracle のデフォルトの分離レベルは RC であり、MySQL のデフォルトの分離レベルは RR であることを知らない人も多いかもしれません。

では、Oracle がデフォルトのレベルとして RC を選択し、MySQL がデフォルトの分離レベルとして RR を選択する理由をご存知ですか?

これは私が以前の面接で候補者に尋ねた質問です。

この質問は意味がないと考える人が多い。これは私たちに8本足のエッセイを暗記させることではないのか？

しかし、実際はそうではありません。この記事を辛抱強く読んでいただければ、私の善意がわかると思います。

Oracle 分離​​レベル

前に述べたように、Oracle は ANSI/ISO SQL で定義された Serializable と Read Committed のみをサポートしています。実際、Oracle の公式ドキュメントに記載されている紹介によると、Oracle は次の 3 つの分離レベルをサポートしています。

つまり、Oracle は Read Committed、Serializable、および Read-Only をサポートしています。

読み取り専用分離レベルはシリアル化可能分離レベルに似ていますが、読み取り専用トランザクションでは、ユーザーが SYS でない限り、トランザクション内でデータの変更は許可されません。

Oracle の 3 つの分離レベルのうち、Serializable と Read-Only はデフォルトの分離レベルとしては明らかに適していないため、残されたオプションは Read Committed のみです。

MySQL 分離レベル

Oracle と比較すると、MySQL では選択できるデフォルトの分離レベルの範囲が広くなっています。

まず、Serializable と Read Uncommitted を 4 つの分離レベルから除外します。主な理由は、これら 2 つの分離レベルのうち 1 つは高すぎ、もう 1 つは低すぎるためです。高すぎると同時実行性に影響し、低すぎるとダーティ リードが発生します。

では、残りの 2 つのタイプ、RR と RC のどちらを選択すればよいのでしょうか?

この物語はずっと昔に始まりました。

MySQL が設計されたとき、その位置付けは安定したリレーショナル データベースを提供することでした。 MySQL の単一点障害によって引き起こされる問題を解決するために、MySQL はマスター/スレーブ レプリケーション メカニズムを採用しています。

いわゆるマスタースレーブレプリケーションは、実際には MySQL クラスタを構築して、外部全体にサービスを提供します。クラスタ内のマシンは、マスターサーバー (Master) とスレーブサーバー (Slave) に分かれています。マスターサーバーは書き込みサービスを提供し、スレーブサーバーは読み取りサービスを提供します。

マスターサーバーとスレーブサーバー間のデータの一貫性を確保するには、データの同期が必要です。一般的な同期プロセスは次のとおりです。ここでは詳細には説明しません。

MySQL マスター スレーブ レプリケーションのプロセスでは、データの同期は bin ログを通じて実行されます。簡単に言えば、マスター サーバーはデータの変更を bin ログに記録し、その後、bin ログを同期的にスレーブ サーバーに送信します。スレーブ サーバーは bin ログを受信すると、その中のデータを自身のデータベース ストレージに復元します。

では、binlog には何が記録されるのでしょうか?フォーマットは何ですか?

MySQL の bin ログは、主にステートメント、行、混合の 3 つの形式をサポートしています。 MySQL はバージョン 5.1.5 で行のサポートを開始し、バージョン 5.1.8 で混合のサポートを開始しました。

ステートメントと行の最大の違いは、 binlog 形式がステートメントの場合、binlog には SQL ステートメントの元のテキストが記録されることです(この文は非常に重要です!!! 後で使用されます)。

これらの形式の違いについてはここでは詳しく説明しません。行形式をサポートする主な理由は、ステートメント形式に多くの問題があるためです。最も明白な問題は、マスター データベースとスレーブ データベース間でデータの不整合が発生する可能性があることです。詳しい紹介については、Geek Time の Ding Qi 氏の「MySQL 実践に関する 45 の講義」を参照してください。

では、このマスター/スレーブ同期と、bin ログで説明する分離レベルとの間にはどのような関係があるのでしょうか。

はい、それは重要ですし、大きなことです。

MySQL は初期の頃はステートメント bin ログ形式しかなかったため、Read Committed と Read Uncommitted の 2 つの分離レベルを使用すると問題が発生しました。

例えば、MySQLの公式サイトでは、ある人が公式のウェブサイトに関連するバグを報告しました。

このバグを再現するプロセスは次のとおりです。

データベース テーブル t1 には、次の 2 つのレコードが含まれています。

   テーブルt1を作成します（

      int(11) デフォルト NULL、

      b int(11) デフォルト NULL,

      キーa (a)

    )ENGINE=InnoDB デフォルト文字セット=latin1;

    t1に値(10,2)、(20,1)を挿入します。

次に、2 つのトランザクションの書き込み操作の実行を開始します。

上記2つのトランザクションが実行されると、データベース内のレコードは(11, 2)と(20, 2)となり、メインデータベースのデータの変更を誰もが把握できるようになります。

トランザクション分離レベルは読み取りコミットであるため、トランザクション 1 が更新されると、行 b=2 に行レベルのロックのみが追加され、トランザクション 2 の行 b=1 への書き込み操作には影響しません。

上記の 2 つのトランザクションが実行されると、2 つのレコードが bin ログに記録されます。トランザクション 2 が最初にコミットされるため、最初にUPDATE t1 SET b=2 where b=1;が記録され、次にUPDATE t1 SET a=11 where b=2;が記録されます。(再度注意: ステートメント形式の bin ログには、SQL ステートメントの元のテキストが記録されます)

このように、バイナリ ログがスタンバイ データベースに同期された後、SQL 文が再生されると、最初にUPDATE t1 SET b=2 where b=1;が実行され、次にUPDATE t1 SET a=11 where b=2;実行されます。

このときデータベース内のデータは（11, 2）と（11, 2）になります。その結果、メイン データベースとバックアップ データベース間でデータの不整合が発生します。 ！ ！

このような問題が起きないようにするためです。 MySQL は、データベースのデフォルトの分離レベルを Repetable Read に設定します。では、Repetable Read 分離レベルはこの問題をどのように解決するのでしょうか?

これは、Repetable Read 分離レベルでは、データの更新時に更新された行に行レベルのロックが追加されるだけでなく、 GAP ロックも追加されるためです。上記の例では、トランザクション 2 が実行されると、トランザクション 1 が GAP ロックを追加するため、トランザクションの実行が停止し、トランザクション 1 がコミットまたはロールバックされるまで待機してから実行を続行する必要があります。 （GAPロックに関しては後ほど別途記事を書きます）。

デフォルトの分離レベルを設定することに加えて、MySQL では、ステートメント形式の bin ログを使用する場合、トランザクション分離レベルとして READ COMMITTED の使用も禁止されます。

ユーザーが分離レベルを積極的に変更すると、更新を試行したときにエラーが報告されます。

 エラー 1598 (HY000): バイナリ ロギングは不可能です。メッセージ: InnoDB のトランザクション レベル 'READ-COMMITTED' は、バイナリ ロギング モード 'STATEMENT' には安全ではありません。

要約する

これで、MySQL がデフォルトのデータベース分離レベルとして RR を選択する理由がわかりました。実際、これは履歴ステートメント形式の bin ログと互換性を持たせるためです。

したがって、この記事では、MySQL 分離レベルに関する知識の 1/5 未満しか説明していません。この記事を読んだ後でも、次のような疑問が残るかもしれません。

1. 行形式とステートメントの違いは何ですか?行を使用するときに RR を使用できますか?

2. 記事で言及されている RC GAP ロックとは具体的に何ですか?

3. RR と RC の違いは何ですか? RC は非反復読み取り問題をどのように解決するのでしょうか?

4. MySQL データベースはデフォルトで RR を選択するのに、Alibaba のような大規模なインターネット企業がデフォルトの分離レベルを RC に変更するのはなぜでしょうか?

上記の質問の答えをご存知ですか、あるいはどの質問に興味がありますか?ぜひメッセージをお寄せください！皆さんがより興味を持ちそうなトピックを厳選し、次回以降の記事で詳しく紹介していきたいと思います。

この質問はまだ無意味だと思いますか?

私は実際、一見無意味な質問を通じてより多くの知識を広げ、候補者についてより包括的に理解できるようにしたいと考えています。

これで、MySQL がデフォルトの分離レベルとして Repeatable Read を選択する理由に関するこの記事は終了です。MySQL の Repeatable Read のデフォルトの分離レベルの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。