トランザクション分離レベルのMySQLケース分析

多くの友人は、MySQL の分離レベルについて常に混乱しています。実際、この質問はまったく難しくありません。重要なのは、それをどのように説明するかです。理論だけ見ると、間違いなく目が回りそうですが、実際の SQL を使ってデモンストレーションしてみると、とても簡単だということがわかります。

今日は、いくつかの簡単なケースを通して、MySQL のトランザクション分離レベルの問題を説明したいと思います。

1. 理論

MySQL には、次の 4 種類のトランザクション分離レベルがあります。

• シリアル化可能
• 繰り返し読み取り
• コミットされた読み取り
• コミットされていない読み取り

4 つの異なる分離レベルの意味は次のとおりです。

シリアル化可能

分離レベルがシリアル化されている場合、ユーザーは現在のトランザクションを次々に順番に実行します。この分離レベルでは、トランザクション間の分離が最大限に実現されます。

繰り返し読み取り

繰り返し可能な読み取り分離レベルでは、トランザクションはシーケンスとは見なされません。ただし、現在実行中のトランザクションの変更は、外部からは見えません。つまり、ユーザーが別のトランザクションで同じ SELECT ステートメントを複数回実行しても、結果は常に同じになります。 (実行中のトランザクションによって生成されたデータの変更は外部からは確認できないため)。

コミットされた読み取り

READ COMMITTED 分離レベルは、REPEATABLE READ 分離レベルよりも安全性が低くなります。 READ COMMITTED レベルのトランザクションは、他のトランザクションによってデータに加えられた変更を確認できます。つまり、トランザクション処理中に他のトランザクションが対応するテーブルを変更すると、同じトランザクション内の複数の SELECT ステートメントが異なる結果を返す可能性があります。

コミットされていない読み取り

READ UNCOMMITTED は、トランザクション間の分離を最小限に抑えます。この分離レベルのトランザクションは、ファントム読み取り操作や非反復読み取り操作が発生しやすいだけでなく、他のトランザクションがまだコミットしていないデータを読み取ることもできます。このトランザクションが他のトランザクションからのコミットされていない変更を計算の基準として使用し、それらのコミットされていない変更が親トランザクションによって元に戻されると、大量のデータ変更が発生します。

MySQLデータベースでは、デフォルトのトランザクション分離レベルはREPEATABLE READです。

2. SQLの練習

次に、いくつかの簡単な SQL ステートメントを通じて、上記の理論を読者に検証します。

2.1 分離レベルを確認する

次の SQL を使用すると、データベース インスタンスのデフォルトのグローバル分離レベルと現在のセッションの分離レベルを表示できます。

MySQL 8 より前では、次のコマンドを使用して MySQL 分離レベルを表示します。

@@GLOBAL.tx_isolation、@@tx_isolationを選択します。

クエリ結果は次のとおりです。

ご覧のとおり、デフォルトの分離レベルは、グローバル分離レベルと現在のセッション分離レベルの両方で REPEATABLE-READ です。

MySQL 8 以降では、次のコマンドを使用して MySQL のデフォルトの分離レベルを表示します。

@@GLOBAL.transaction_isolation、@@transaction_isolation を選択します。

キーワードのみが変更され、その他はすべて同じままです。

分離レベルは次のコマンドで変更できます (開発者はグローバル分離レベルではなく、現在のセッション分離レベルを変更することをお勧めします)。

セッショントランザクション分離レベルを設定する READ UNCOMMITTED

上記の SQL 文は、現在のセッションのデータベース分離レベルが READ UNCOMMITTED に設定されていることを示しています。設定が成功した後、分離レベルが再度照会され、図 1-2 に示すように、現在のセッションの分離レベルが変更されていることがわかります。

現在のセッションの分離レベルのみを変更すると、セッションの変更後に分離レベルはデフォルトの分離レベルに復元されるため、テスト時には現在のセッションの分離レベルのみを変更する必要があります。

2.2 コミットされていない読み取り

2.2.1 テストデータの準備

READ UNCOMMITTED は最も低い分離レベルです。この分離レベルには、ダーティ リード、非反復読み取り、ファントム リードの問題があるため、まずこの分離レベルを見て、これら 3 つの問題が何であるかを理解しましょう。

以下、順に紹介します。

まず、簡単なテーブルを作成し、次のように 2 つのデータを事前設定します。

表のデータは非常にシンプルです。javaboy と itboyhub という 2 人のユーザーがおり、それぞれのアカウントには 1,000 人民元があります。ここで、これら 2 人のユーザー間の転送操作をシミュレートしてみましょう。

Navicat を使用している場合、異なるクエリ ウィンドウは異なるセッションに対応していることに注意してください。SQLyog を使用している場合、異なるクエリ ウィンドウは同じセッションに対応しています。したがって、SQLyog を使用している場合は、新しい接続を開き、新しい接続でクエリ操作を実行する必要があります。

2.2.2 ダーティリード

トランザクションが別のトランザクションによってコミットされていないデータを読み取ることをダーティ リードと呼びます。具体的な操作は以下のとおりです。

まず、SQL 操作ウィンドウを 2 つ開きます (それぞれ A と B とします)。ウィンドウ A に次の SQL を入力します (入力後に実行しないでください)。

トランザクションを開始します。
アカウントを更新します。残高をbalance+100に設定します。name='javaboy';
アカウントを更新します。balance=balance-100 で、name='itboyhub' です。
専念;

次のように、ウィンドウ B で次の SQL を実行して、デフォルトのトランザクション分離レベルを READ UNCOMMITTED に変更します。

セッショントランザクション分離レベルを設定する READ UNCOMMITTED

次に、ウィンドウ B に次の SQL を入力します。入力後、最初の行を実行してトランザクションを開始します (実行する必要があるのは 1 行だけであることに注意してください)。

トランザクションを開始します。
アカウントから*を選択します。
専念;

次に、ウィンドウ A の最初の 2 つの SQL ステートメントを実行してトランザクションを開始し、javaboy アカウントに 100 元を追加します。

ウィンドウ B に入り、ウィンドウ B で 2 番目のクエリ SQL (SELECT * from user;) を実行します。結果は次のようになります。

ウィンドウ A のトランザクションはコミットされていませんが、関連するデータの変更はウィンドウ B で照会できることがわかります。

これはダーティリードの問題です。

2.2.3 繰り返し不可能な読み取り

非反復読み取りとは、トランザクションが同じレコードを 2 回読み取るが、2 回読み取られたデータが異なることを意味し、非反復読み取りと呼ばれます。具体的な手順は次のとおりです (操作前に両方のアカウントの金額を 1000 に戻します)。

1. まず、2 つのクエリ ウィンドウ A と B を開き、B のデータベース トランザクション分離レベルを READ UNCOMMITTED に設定します。具体的な SQL については上記を参照してください。ここでは繰り返しません。
2. ウィンドウ B に次の SQL を入力し、最初の 2 つの SQL のみを実行してトランザクションを開始し、javaboy アカウントを照会します。

トランザクションを開始します。
name='javaboy' のアカウントから * を選択;
専念;

最初の 2 つの SQL ステートメントの実行結果は次のとおりです。

次のように、ウィンドウ A で次の SQL を実行して、javaboy アカウントに 100 元を追加します。

トランザクションを開始します。
アカウントを更新します。残高をbalance+100に設定します。name='javaboy';
専念;

4. 再びウィンドウ B に戻り、ウィンドウ B で 2 番目の SQL ステートメントを実行して、javaboy のアカウントを表示します。結果は次のようになります。

javaboy アカウントが変更されました。つまり、javaboy アカウントを 2 回チェックした結果が矛盾しており、繰り返し読み取りが不可能です。

ダーティ リードと非反復可能リードの違いは、ダーティ リードは他のトランザクションがコミットしていないデータを参照するのに対し、非反復可能リードは他のトランザクションがコミットしたデータを参照することです (現在の SQL もトランザクション内にあるため、他のトランザクションがコミットしたデータを参照したくない場合があります)。

2.2.4 ファントムリード

ファントム リードは非反復読み取りと非常によく似ており、名前を見ただけでも錯覚が生じることがわかります。

簡単な例を挙げてみましょう。

ウィンドウ A に次の SQL を入力します。

トランザクションを開始します。
アカウント(名前、残高)に値('zhangsan'、1000)を挿入します。
専念;

次に、ウィンドウ B に次の SQL を入力します。

トランザクションを開始します。
アカウントから*を選択します。
名前が 'zhangsan' であるアカウントから削除します。
専念;

次の手順を実行します。

• まず、ウィンドウ B の最初の 2 行を実行してトランザクションを開始し、同時にデータベース内のデータを照会します。この時点で、照会されるデータは javaboy と itboyhub のみです。
• ウィンドウ A の最初の 2 行を実行して、zhangsan という名前のユーザーをデータベースに追加します。トランザクションをコミットする必要がないことに注意してください。
• ウィンドウ B の 2 行目を実行します。ダーティ リードの問題により、この時点ではユーザー zhangsan が見つかります。
• ウィンドウ B の 3 行目を実行して、zhangsan という名前のレコードを削除します。この時点で削除に問題が発生します。zhangsan はウィンドウ B で見つかりますが、このレコードはコミットされていません。これはダーティ リードのため表示されるため、削除できません。この瞬間、幻覚が起こりました。明らかに張山がありましたが、消すことができませんでした。

これはファントムリーディングです。

上記の事例を読んだ後、誰もがダーティ リード、非反復リード、ファントム リードが何を意味するのか理解できるはずです。

2.3 コミットされた読み取り

READ UNCOMMITTED と比較すると、READ COMMITTED は主にダーティ リードの問題を解決しますが、非反復読み取りとファントム読み取りの問題は解決しません。

トランザクション分離レベルをREAD COMMITTED変更した後、ダーティ リード ケースで上記のテストを繰り返すと、ダーティ リードの問題は存在しなくなることがわかります。一方、非反復読み取りケースで上記のテストを繰り返すと、非反復読み取りの問題が依然として存在することがわかります。

上記のケースはファントムリーディングテストには適していません。ファントムリーディングテストケースに変更しましょう。

まだ2つのウィンドウAとBがあり、ウィンドウBの分離レベルをREAD COMMITTED変更します。

次に、ウィンドウ A に次のテスト SQL を入力します。

トランザクションを開始します。
アカウント(名前、残高)に値('zhangsan'、1000)を挿入します。
専念;

ウィンドウ B に次のテスト SQL を入力します。

トランザクションを開始します。
アカウントから*を選択します。
アカウント(名前、残高)に値('zhangsan'、1000)を挿入します。
専念;

テスト方法は次のとおりです。

• まず、ウィンドウ B で SQL の最初の 2 行を実行し、トランザクションを開始してデータを照会します。この時点では、javaboy と itboyhub の 2 人のユーザーのみが見つかります。
• ウィンドウ A で SQL の最初の 2 行を実行してレコードを挿入しますが、トランザクションはコミットしません。
• ウィンドウ B で SQL の 2 行目を実行します。現在、ダーティ リードの問題は発生していないため、この時点ではウィンドウ A に追加されたデータは見つかりません。
• ウィンドウ B で SQL の 3 行目を実行します。名前フィールドは一意であるため、ここでは挿入を実行できません。ここで錯覚が起きます。zhangsan というユーザーは存在しないのに、zhangsan を挿入することができません。

2.4 繰り返し読み取り

READ COMMITTED と比較すると、REPEATABLE READ は非反復読み取りの問題をさらに解決しますが、ファントム読み取りは解決されません。

REPEATABLE READ のファントムリードテストは、基本的に前のセクションと同じです。違いは、2 番目のステップで挿入 SQL を実行した後にトランザクションをコミットする必要があることです。

REPEATABLE READ は非反復読み取りの問題を解決しているため、2 番目のステップでトランザクションがコミットされても、3 番目のステップではコミットされたデータが見つからず、4 番目のステップでデータを挿入するとエラーが発生します。

REPEATABLE READ は、InnoDB エンジンのデフォルトのデータベース トランザクション分離レベルでもあることに注意してください。

2.5 シリアル化可能

SERIALIZABLE はトランザクション間の分離を最大限に実現します。この分離レベルではトランザクションは次々に順番に実行され、ダーティリード、非反復リード、ファントムリードは発生しません。最も安全です。

現在のトランザクション分離レベルが SERIALIZABLE に設定されている場合、この時点で他のトランザクションを開始すると、それらのトランザクションはブロックされ、現在のトランザクションがコミットされるまで待機してから、他のトランザクションを正常に開始できます。したがって、以前のダーティ リード、非反復読み取り、およびファントム読み取りの問題はここでは発生しません。

3. 結論

一般に、分離レベルとダーティ リード、非反復リード、ファントム リードとの対応は次のとおりです。

パフォーマンスの関係は図に示されています。

さて、この記事はこれで終わりです。数行の SQL を書いて試してみるのもいいかもしれません。

これで、MySQL のトランザクション分離レベルのケース分析に関するこの記事は終了です。MySQL のトランザクション分離レベルに関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。