MySQL のファントムリード問題を解決する方法

序文

繰り返し読み取り分離レベルでは、MySQL は他のトランザクションによって送信されたコンテンツを参照できないことがわかっています。コミット可能な分離レベルでは、他のトランザクションがコミットされていることを確認できます。ビジネス シナリオで、トランザクション内の同じ 2 つのクエリで確認する必要があるデータが一貫しており、他のトランザクションの影響を受けないという場合は、繰り返し読み取り分離レベルを使用します。この場合、RR レベルでの通常のクエリ (スナップショット読み取り) は、MVCC に依存して「ファントム読み取り」問題を解決します。「現在の読み取り」状況の場合、「ファントム読み取り」問題を解決するために何に依存する必要がありますか?それがこのブログ投稿の目的です。

これについて説明する前に、前回のブログ記事 (MySQL はトランザクション分離をどのように実装するのか?) を読んでください。この投稿では、主に分離レベルの具体的な技術的詳細が紹介されています。この投稿を読んだ後にこの記事を読むと、より役立つかもしれません。

注: このブログ記事で説明されている「ファントム リード」は、「繰り返し読み取り」分離レベルで実行されるものを指します。

1. ファントムリーディングとは何ですか?

次のような構造のテーブルtがあるとします。初期データ行は(0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5)です。

テーブル `t` を作成します
（
    `id` INT(11) NULLではない、
    `key` INT(11) デフォルト NULL,
    `value` INT(11) デフォルト NULL,
    主キー (`id`)、
    キー `値` (`値`)
)エンジン = InnoDB;
tに挿入
値 (0, 0, 0)、
       （１、１、１）、
       （2、2、2）、
       （３、３、３）、
       （4、4、4）、
       （5、5、5）

更新のために select * from where value=1 を実行し、この行のみをロックし (これは単なる仮定であることに注意してください)、他の行はロックしないと仮定すると、次のシナリオが発生します。

セッション A の 3 つのクエリ Q1 ～ Q3 はすべて、更新のために select * from where value=1 を実行し、value=1 であるすべての行をクエリします。

• T1: Q1は1行(1,1,1)のみを返します。
• T2: セッション B は id=0 の値を 1 に更新します。この時点で、テーブル t には value=1 のデータが 2 行あります。
• T3: Q3は2行(0,0,1)、(1,1,1)を返す。
• T4: セッション C は行 (6,6,1) を挿入します。この時点で、テーブル t には値 = 1 の行が 3 つあります。
• T5: Q3は3行(0,0,1)、(1,1,1)、(6,6,1)を返す。
• T6: セッション A のトランザクションがコミットされます。

Q3 が値 = 1 を読み取る現象はファントム リードと呼ばれます。ファントム リードとは、トランザクションが同じ範囲を 2 回クエリすると、後者のクエリでは前のクエリでは表示されなかった行が表示されることを意味します。

まず、「ファントムリーディング」について、次のように説明しましょう。

• 繰り返し読み取り分離レベルでは、通常のクエリはスナップショット読み取りであり、他のトランザクションによって挿入されたデータは表示されません。したがって、ファントム リードは「現在の読み取り」の下にのみ表示されます (3 つのクエリすべてでの更新は現在の読み取りを示します)。
• 上記のセッション B の更新結果は、セッション A の後の SELECT ステートメントで「現在の読み取り」を使用して表示されますが、これはファントム読み取りとは言えません。ファントム読み取りは「新しく挿入された行」のみを参照します。

2. ファントムリーディングの問題点は何ですか?

（１）別途解決する必要がある

ご存知のとおり、select ...for update ステートメントは対応するデータ行をロックします。たとえば、時刻 T1 のセッション A の Q1 クエリ ステートメント: select * from where value=1 for update は、value=1 のデータ行をロックします。ただし、上記のシナリオが発生すると、for update のセマンティクスは破棄されます (value=1 のデータ行はロックされません)。

すべてのレコードがロックされていても、新しいレコードの挿入を防ぐことはできないため、「ファントム リード」の問題は別途解決する必要があります。これは、MVCC または行ロック メカニズムでは解決できません。ここで、もう 1 つのロック機構である「ギャップ ロック」について説明します。

（２）ギャップロックによる並行性

ギャップ ロックを導入すると、同じステートメントでより広い範囲がロックされる可能性があり、同時実行性に影響する可能性があります。詳細は下記紹介をご覧ください

3. ファントムリーディングを解決するには？

ファントム リードが発生する理由は、行ロックでは行しかロックできませんが、新しいレコードを挿入するアクションによってレコード間の「ギャップ」が更新されるためです。したがって、ファントム リード問題を解決するために、InnoDB はギャップ ロックという新しいロックを導入する必要がありました。

ギャップ: たとえば、テーブルに 0、5、10、15、20、25 の 6 つのレコードを追加します。その結果、7 つのギャップが生じます。

行ごとのスキャン処理中に、行に行ロックが追加されるだけでなく、行の両側のスペースにギャップ ロックも追加されます。これにより、新しいレコードが挿入されなくなります。

ギャップ ロックと行ロックは、まとめてネクスト キー ロックと呼ばれます。各ネクスト キー ロックは、オープン ファースト クローズ インターバル (ギャップ ロック オープン インターバル、ネクスト キー ロック オープン ファースト クローズ インターバル) です。

ギャップ ロック間に競合はありません。競合はギャップにレコードを挿入することです。

テーブル t にはデータ値 = 7 がないため、Q1 はギャップ ロック (1,5) を追加し、Q2 もこのギャップ ロックを追加します。この 2 つは互いに競合せず、どちらもギャップが挿入されるのを防ぎます。

テーブル t が初期化された後、テーブル内のデータが次のようになっていると仮定します。

更新を実行するためにselect * fromを使用すると、テーブル全体のすべてのレコードがロックされ、7つの次のキーロック、すなわち(-∞,0]、(0,2]、(2,4]、(4,6]、(6,8]、(8, 10]、(10、+supremum]が形成されます。

ギャップ ロックを導入すると、同じステートメントでより広い範囲がロックされる可能性があり、同時実行性に影響します。

次のシナリオを想定します。

その後、明らかにデッドロックが発生しました。分析は次のようになります。

• Q1: select ...for update ステートメントを実行します。id=9 の行が存在しないため、ギャップ ロック (8,10) が追加されます。
• Q2: select ...for update ステートメントを実行すると、ギャップ ロック (8,10) も追加されます。ギャップ ロック間に競合はないため、このステートメントは正常に実行できます。
• セッション B は行 (9,9,9) を挿入しようとしますが、セッション A のギャップ ロックによってブロックされ、待機する必要があります。
• セッション A は行 (9,9,9) を挿入しようとしますが、セッション B のギャップ ロックによってブロックされます。

前述のように、ギャップ ロックの導入により、同じステートメントでより広い範囲がロックされる可能性があり、これは実際には同時実行性に影響します。

ファントム リード問題を解決するには、読み取りコミット可能分離レベルを使用できます。ギャップ ロックは、繰り返し読み取り分離レベルでのみ有効になります。したがって、分離レベルがコミットされた読み取りに設定されている場合、ギャップ ロックは発生しません。ただし、同時に、データとログ間の不整合の可能性を解決したい場合は、binlog 形式を row に設定する必要があります。つまり、「RC 分離レベル + ログ形式 binlog_format=row」の組み合わせを使用します。

結論

• ギャップ ロックは RR 分離レベルでのみ有効であり、RC 分離レベルではギャップ ロックは存在しません。
• RR 分離レベルでの「ファントム読み取り」問題を解決するには、「スナップショット読み取り」は MVCC 制御に依存し、「現在の読み取り」はギャップ ロックによって解決します。
• ギャップ ロックと行ロックは、まとめてネクスト キー ロックと呼ばれます。各ネクスト キー ロックは、最初に開いて閉じる間隔です。
• ギャップ ロックを導入すると、同じステートメントでより広い範囲がロックされ、同時実行性に影響する可能性があります。

MySQL のファントム リード問題を解決する方法については、これで終わりです。MySQL ファントム リードの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。