MySQL の繰り返し読み取りレベルでファントム読み取りを解決できますか?

導入

データベース理論についてさらに学んでいくうちに、さまざまな分離レベルによって起こり得る問題について学びました。理解を深めるために、これらの問題を MySQL データベースでテストし、再現しました。ダーティ リードと反復不可能なリードは、対応する分離レベルで簡単に再現できます。しかし、ファントム リードに関しては、繰り返し読み取り分離レベルでは発生しないことがわかりました。その時、MySQL がファントム リードに対処するために何かを行っているのではないかと考えました。

テスト：

テストテーブル部門を作成します:

テーブル「dept」を作成します（
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) デフォルト NULL,
 主キー (`id`)
) エンジン=InnoDB AUTO_INCREMENT=12 デフォルト文字セット=utf8

dept(name) に値("物流部門") を挿入します。

上記のプロセスによれば、トランザクション 1 の最初の選択では 1 つのデータが照会され、2 番目の選択では 2 つのデータ (トランザクション 2 によって送信されたデータを含む) が照会されることが予想されます。

しかし、実際のテストでは、2 番目の select は実際には 1 つのデータのみを照会して処理することがわかりました。ただし、データベース理論における繰り返し読み取りの実装 (排他ロックと共有ロック) によれば、これは当てはまりません。

実際の理由を理解する前に、物事の関連する理論を見直してみましょう。

データベースの原理理論

もの

トランザクションとは、一般的に、実行されることや行われるべきことを指します。コンピュータ用語では、データベース内のさまざまなデータ項目にアクセスし、場合によっては更新するプログラム実行ユニットを指します。トランザクションは、トランザクションの開始から終了までの間に実行されるすべての操作で構成されます。リレーショナル データベースでは、トランザクションは一連の SQL ステートメントまたはプログラム全体になります。

私たちはなぜ物を持っているのでしょうか？

データベース トランザクションは通常、データベースの読み取りまたは書き込みを行う一連の操作で構成されます。それは 2 つの目的のために存在します:

• データベース操作を障害から正常な状態に回復する方法を提供し、また、異常な状況下でデータベースの一貫性を維持する方法も提供します。
• 複数のアプリケーションが同時にデータベースにアクセスする場合、アプリケーション間で分離方法を提供して、操作が互いに干渉しないようにすることができます。

物事の特性

トランザクションには、原子性、一貫性、独立性、永続性という 4 つの特性があります。これら 4 つの特性は、ACID 特性と呼ばれることがよくあります。

• 原子性:
  トランザクションは分割できない作業単位である必要があり、トランザクションに含まれる操作はすべて成功するか、すべて失敗するかのいずれかになります。
• 一貫性：
  トランザクションは、データベースをある一貫した状態から別の一貫した状態に変更する必要があります。一貫性と原子性は密接に関連しています。
• 分離：
  トランザクションの実行は他のトランザクションによって妨害されることはありません。つまり、トランザクション内で使用される操作とデータは、トランザクションがコミットされる前に他の同時実行トランザクションから分離され、同時に実行されるトランザクションは互いに影響を与えることができません。
• 耐久性:
  トランザクションが正常にコミットされると、データベース内のデータに加えられた変更は永続的になります。その後の他の操作や障害は、これに何ら影響を与えないはずです。

物事間のいくつかの特性は同等の概念の集合ではありません。

一度に 1 つだけ存在する場合、それは自然に分離され、原子性が保証されている限り一貫性を実現できます。

同時実行性がある場合、一貫性を確保するために原子性と分離性を保証する必要があります。

同時データベーストランザクションの問題

トランザクションの分離を考慮しないと、次の問題が発生する可能性があります。

• ダーティ リード: ダーティ リードとは、トランザクション処理プロセス中に別のコミットされていないトランザクションからデータを読み取ることを指します。トランザクションがデータを複数回変更していて、これらの変更がこのトランザクションでまだコミットされていない場合、同時トランザクションがデータにアクセスすると、2 つのトランザクションによって取得されたデータは不整合になります。
• 反復不可能な読み取り: 反復不可能な読み取りとは、データベース内の特定のデータに対して、トランザクション内の複数のクエリが異なるデータ値を返すことを意味します (ここで、異なるとは、1 つ以上のデータの内容が一貫していないが、データ数は同じであることを意味します)。これは、クエリ間隔中に、トランザクションに必要なデータが別のトランザクションによって変更され、コミットされたためです。非反復読み取りとダーティ読み取りの違いは、ダーティ読み取りはトランザクションが別のトランザクションによってコミットされていないダーティ データを読み取るのに対し、非反復読み取りは他のトランザクションによってコミットされたデータを読み取ることです。場合によっては、繰り返し不可能な読み取りが問題にならないことに注意してください。
• ファントム リード: ファントム リードは、トランザクションが独立して実行されない場合に発生する現象です。たとえば、トランザクション T1 はテーブルのすべての行のデータ項目を「1」から「2」に変更します。 このとき、トランザクション T2 はテーブルに別の行のデータ項目を挿入しますが、このデータ項目の値は「1」のままで、データベースに送信されます。トランザクション T1 を操作しているユーザーが、変更されたばかりのデータを確認すると、変更されていない行がまだあることがわかります。実際には、この行はトランザクション T2 から追加されたものであり、錯覚のようなものです。これがファントム リードです。ファントム リードと非反復読み取りはどちらも、コミットされた別のトランザクションを読み取ります (ダーティ リードとは異なります)。違いは、非反復読み取りは更新操作と削除操作で発生する可能性があるのに対し、ファントム リードは挿入操作で発生することです。

排他ロック、共有ロック

排他ロック。X ロック、書き込みロックとも呼ばれます。

共有ロックは、S ロック、読み取りロックとも呼ばれます。

読み取り/書き込みロックの関係は次のとおりです。

• トランザクションはデータ オブジェクト O に S ロックを追加します。O を読み取ることはできますが、更新することはできません。ロック期間中、他のトランザクションは O に S ロックを追加できますが、X ロックを追加することはできません。
• トランザクションはデータ オブジェクト O に X ロックを追加し、O の読み取りと更新を行うことができます。ロック期間中、他のトランザクションは O にロックを追加できません。

つまり、読み取り書き込みロックの関係は、複数の読み取りと単一の書き込みとして要約できます。

トランザクションの分離レベル

トランザクションにはいくつかの分離レベルがあります。

• コミットされていないものを読み取り: 更新の損失の問題を解決します。トランザクションが書き込み操作を開始した場合、他のトランザクションは同時に書き込むことはできませんが、他のトランザクションはこのデータ行を読み取ることができます。この分離レベルは、「排他的書き込みロック」を通じて実現できます。つまり、トランザクションが特定のデータを変更する必要がある場合、データに X ロックを追加する必要があり、データを読み取るために S ロックは必要ありません。
• Read Committed: ダーティ リード問題を解決します。データを読み取るトランザクションでは、他のトランザクションが引き続きデータ行にアクセスできますが、コミットされていない書き込みトランザクションでは、他のトランザクションが行にアクセスすることが禁止されます。これは、「インスタント共有読み取りロック」と「排他的書き込みロック」によって実現できます。つまり、トランザクションで特定のデータを変更する必要がある場合は、データに X ロックを追加し、データの読み取り時に S ロックを追加する必要があります。データが読み取られると、トランザクションの終了を待たずに S ロックがすぐに解除されます。
• 繰り返し読み取り: 繰り返し不可能な読み取りとダーティ読み取りは禁止されていますが、ファントム読み取りが発生する場合があります。データを読み取るトランザクションは書き込みトランザクションを禁止します (ただし、読み取りトランザクションは許可します)。また、書き込みトランザクションはその他のトランザクションを禁止します。 Mysql はデフォルトでこの分離レベルを使用します。これは、「共有読み取りロック」と「排他的書き込みロック」によって実現できます。つまり、トランザクションで特定のデータを変更する必要がある場合は、データに X ロックを追加し、データを読み取るときに S ロックを追加する必要があります。データが読み取られると、S ロックはすぐに解放されず、トランザクションが完了した後に解放されます。
• Serializable: ファントム読み取りの問題を解決します。厳密なトランザクション分離を提供します。トランザクションはシリアルに実行する必要があります。トランザクションは 1 つずつしか実行できず、同時に実行することはできません。トランザクションのシリアル化は、単に「行レベルのロック」だけでは実現できません。クエリ操作を実行したばかりのトランザクションによって新しく挿入されたデータにアクセスされないようにするには、他のメカニズムを使用する必要があります。

MySQL における分離レベルの実装

上記の内容はデータベース理論のいくつかの概念を説明したものですが、MySQLやORACLEなどのデータベースでは、パフォーマンス上の理由から、上で紹介した理論に完全に従って実装されているわけではありません。

MVCC

マルチバージョン同時実行制御 (MVCC) は、楽観的ロック理論に基づいて MySQL で分離レベルを実装する方法です。読み取りコミット分離レベルと反復可能読み取り分離レベルを実装するために使用されます。

実装（分離レベルは繰り返し読み取り）

実装方法について説明する前に、2 つの概念を紹介しましょう。

システム バージョン番号: 増加する番号。システム バージョン番号は、新しいトランザクションが開始されるたびに自動的に増加します。

トランザクション バージョン番号: トランザクション開始時のシステム バージョン番号。

MySQL では、テーブル内の各レコードの後に​​ 2 つのフィールドが追加されます。

バージョン番号の作成: データ行を作成するときに、現在のシステムバージョン番号を作成バージョン番号として割り当てます。

削除バージョン番号: データ行を削除すると、現在のシステムバージョン番号が削除バージョン番号として割り当てられます。

選択

選択中にデータを読み取るためのルールは、作成バージョン番号 <= 現在のトランザクション バージョン番号であり、削除バージョン番号は空または現在のトランザクション バージョン番号より大きいです。

作成バージョン番号 <= 現在のトランザクション バージョン番号により、取得されたデータには、後で開始されたトランザクションで作成されたデータが含まれないことが保証されます。これが、最初の例の後半で追加されたデータが見つからない理由です。

削除バージョン番号が空であるか、現在のトランザクション バージョン番号より大きいため、少なくともトランザクションが開始される前にはデータが削除されておらず、チェックアウトする必要があるデータであることが保証されます。

入れる

挿入時に、作成バージョン番号フィールドに現在のシステム バージョン番号を割り当てます。

アップデート

新しいレコードを挿入し、現在のトランザクション バージョン番号を行作成バージョン番号として保存し、現在のトランザクション バージョン番号を元々削除された行に保存します。実際、ここでの更新は削除と挿入を通じて実装されます。

消去

削除時には、コミットの実行時にデータが実際に削除されない場合でも、どのトランザクションでデータ行が削除されるかを示すために、現在のシステム バージョン番号が削除バージョン番号フィールドに割り当てられます。選択ルールに従ってデータを開いたとしても、データは見つかりません。

MVCC は本当にファントムリーディングを解決できるのでしょうか?

当初のテスト例と上記の理論的裏付けから、MySQL の MVCC によってファントム リードの問題が解決されたようです。シリアル リードは意味がないと思われるため、疑問を抱きながらテストを続けます。

事前テストデータ:

上記の結果に基づいて、次の結果が予想されます。

ID名
1 財務部
2 研究開発部門

しかし実際のところ、私たちの経験では、

当初は最初のデータの部門を財務に変更することを希望していましたが、最終的には両方のデータが変更されました。この結果は、MySQL の繰り返し読み取り分離レベルはファントム読み取りの問題を完全に解決するわけではないが、データ読み取り時のファントム読み取りの問題を解決することを示しています。ただし、変更操作ではファントム リードの問題が依然として存在しており、これは MVCC のファントム リードに対するソリューションが完全ではないことを意味します。

スナップショット読み取りと現在の読み取り

上記のような状況が発生した場合、なぜそれが起こるのかを知る必要があります。いくつかの情報を調べた後、RR レベルでは、MVCC メカニズムによってデータが繰り返し可能になるものの、読み取るデータはデータベース内の最新のデータではなく、履歴データである可能性があることがわかりました。履歴データを読み取るこの方法はスナップショット読み取りと呼ばれ、データベースの最新バージョンを読み取る方法は現在の読み取りと呼ばれます。

スナップショット読み取りを選択

選択操作を実行すると、InnoDB はデフォルトでスナップショット読み取りを実行し、この選択の結果を記録します。このスナップショットのデータは、後続の選択で返されます。他のトランザクションがコミットされても、現在の選択のデータには影響しないため、繰り返し読み取りが実現されます。スナップショットは、select が初めて実行されたときに生成されます。つまり、A がトランザクションを開始し、その後何も操作を行わなかったとします。この時点で、B はデータを挿入してコミットします。この時点で、A は select を実行し、返されるデータには B によって追加されたデータが含まれます。スナップショットが生成され、後続の選択はスナップショットに基づいて行われるため、その後に他のトランザクションがコミットされても問題ありません。

現在の読書

データを変更する操作 (更新、挿入、削除) では、現在の読み取りモードが使用されます。これらの操作を実行すると、最新のレコードが読み取られ、他のトランザクションによって送信されたデータも照会できます。レコードを更新したいが、別のトランザクションでデータが削除されコミットされている場合、更新すると競合が発生するため、更新時には最新のデータを知る必要があります。まさにこのために、上でテストした状況が発生しました。

選択の現在の読み取りを手動でロックする必要があります。

select * from table where ? lock in share mode;
更新のために、? のテーブルから * を選択します。

説明すべき問題がある

テスト開始当初は、begin 文を使うのはトランザクションを開始するためだと思っていました。そのため、上記の 2 番目のテストでは、トランザクション 1 が最初に開始されたため、トランザクション 2 で新しく追加されたデータがトランザクション 1 で見つかりました。このとき、これは以前の MVCC の選択ルールと矛盾していると思い、次のテストを行いました。

SELECT * FROM information_schema.INNODB_TRX //現在実行中のトランザクションを照会するために使用されます

begin ステートメントを実行するだけでは、トランザクションは実際には開始されないことがわかります。

次に、begin の後に select ステートメントを追加します。

したがって、データの追加、削除、変更、チェックなどの操作が実行された後にのみ、トランザクションが実際に開かれることを理解することが重要です。

ファントムリードを解決する方法

明らかに、繰り返し読み取り分離レベルではファントム読み取りの問題を完全に解決することはできません。プロジェクトでファントム読み取りを解決する必要がある場合、次の 2 つの方法があります。

• シリアル化可能な読み取り分離レベルの使用
• MVCC+ネクストキーロック: ネクストキーロックは、レコードロック(インデックスロック)とギャップロック(ギャップロック、毎回使用するデータだけでなく、そのデータの近くのデータもロックする)で構成されます。

実際、上記の 2 つの方法は多くのプロジェクトで使用されていません。シリアル読み取りのパフォーマンスが悪すぎるため、ファントム読み取りでもまったく問題ないことがよくあります。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。