MySQL の 4 つのトランザクション分離レベルの詳細な説明

この実験のテスト環境: Windows 10+cmd+MySQL5.6.36+InnoDB

1. トランザクションの基本要素 (ACID)

1. アトミック性: トランザクションの開始後、すべての操作が完了するか、まったく実行されないかのどちらかになります。トランザクションが途中で停止することはあり得ません。トランザクションの実行中にエラーが発生した場合、トランザクションが開始する前の状態にロールバックされ、すべての操作は何も起こらなかったかのようになります。言い換えれば、物事は、化学で学んだ物質の基本単位である原子と同じように、分割できない全体なのです。

2. 一貫性: トランザクションの開始前と終了前、および終了後にデータベースの整合性制約に違反しません。例えば、AがBに送金した場合、Aがそのお金を差し引くことは不可能ですが、Bはそれを受け取りません。

3. 分離: 同時に同じデータを要求できるトランザクションは 1 つだけであり、異なるトランザクション間で干渉は発生しません。たとえば、A が銀行カードからお金を引き出す場合、B は A が引き出しプロセスを完了するまでこのカードにお金を送金することはできません。

4. 耐久性: トランザクションが完了すると、トランザクションによってデータベースに対して行われたすべての更新がデータベースに保存され、ロールバックできなくなります。

概要: 原子性はトランザクション分離の基礎です。分離と永続性は手段です。最終的な目標はデータの一貫性を維持することです。

2. トランザクションの同時実行性の問題

1. ダーティリード: トランザクションAはトランザクションBによって更新されたデータを読み取り、その後Bは操作をロールバックするため、Aによって読み取られたデータはダーティデータになります。

2. 反復不可能な読み取り: トランザクション A は同じデータを複数回読み取ります。トランザクション A が複数回読み取りを行っている間に、トランザクション B がデータを更新してコミットするため、トランザクション A が同じデータを複数回読み取ったときに結果が不一致になります。

3. ファントム リード: システム管理者 A は、データベース内のすべての学生の成績を特定のスコアから ABCDE の成績に変更しましたが、システム管理者 B は、このとき特定のスコアのレコードを挿入しました。システム管理者 A が変更を終えると、まるで錯覚が起こったかのように、変更されていないレコードがまだ残っていることに気付きます。これをファントム リードと呼びます。

概要: 非反復読み取りとファントム読み取りは混同されやすいです。非反復読み取りは変更に重点を置いていますが、ファントム読み取りは追加または削除に重点を置いています。非反復読み取りの問題を解決するには、条件を満たす行のみをロックする必要があります。ファントム読み取りの問題を解決するには、テーブルをロックする必要があります。

4. 各隔離レベルの状況を例を使って説明する

1. コミットされていないものを読み取ります。

（1）クライアントAを開き、現在のトランザクションモードをコミットされていない読み取りに設定します。アカウントテーブルの初期値を照会します。

（２）クライアントAのトランザクションがコミットされる前に、別のクライアントBが開かれ、アカウントテーブルが更新されます。

（３）この時点では、クライアントBのトランザクションはまだコミットされていないが、クライアントAはBが更新したデータを照会することができる。

（4）何らかの理由でクライアントBのトランザクションがロールバックされると、すべての操作が元に戻され、クライアントAによって照会されたデータは実際にはダーティデータになります。

(5) クライアント A が更新ステートメント update account set balance = balance - 50 where id = 1 を実行すると、Lilei の残高は 350 ではなく 400 になります。おかしいと思いませんか? データの一貫性は問題ではありません。そう思うなら、あなたはあまりにもナイーブです。アプリケーションでは、400-50=350 を使用し、他のセッションがロールバックされたことを知りません。この問題を解決するには、読み取りコミット分離レベルを使用できます。

2. 提出された内容を読む

（1）クライアントAを開き、現在のトランザクションモードをコミット読み取りに設定します。アカウントテーブルの初期値を照会します。

（２）クライアントAのトランザクションがコミットされる前に、別のクライアントBが開かれ、アカウントテーブルが更新されます。

（３）この時点では、クライアントBのトランザクションはまだコミットされておらず、クライアントAはBが更新したデータを照会することができず、ダーティリード問題は解決される。

（4）クライアントBの取引提出

（5）クライアントAは前のステップと同じクエリを実行しますが、結果は前のステップの結果と一致しないため、非反復読み取り問題が発生します。アプリケーションでは、クライアントAのセッションでlileiの残高が450であるが、他のトランザクションによってlileiの残高値が400に変更されたとします。これはわかりません。450という値を使用して他の操作を実行すると、問題が発生します。ただし、この確率は非常に小さいです。この問題を回避するには、反復読み取り分離レベルを使用できます。

3. 繰り返し読み取り

（1）クライアントAを開き、現在のトランザクションモードを繰り返し読み取りに設定し、アカウントテーブルの初期値を照会します。

（2）クライアントAのトランザクションがコミットされる前に、別のクライアントBが開かれ、アカウントテーブルが更新されてコミットされます。クライアントBのトランザクションは、クライアントAのトランザクションによって照会された行を実際に変更できます。つまり、MySQLの繰り返し読み取りは、トランザクションによって照会された行をロックしません。これは私の予想を超えていました。SQL標準によると、トランザクション分離レベルが繰り返し読み取りの場合、読み取りおよび書き込み操作は行をロックする必要があります。MySQLはそれらをロックしませんでした。なんてことだ。アプリケーションでは、必ず行をロックしてください。そうしないと、手順 (1) の残高 400 が他の操作を実行するための中間値として使用されます。

（３）ステップ（１）のクエリをクライアントAで実行する。

(4) 手順(1)を実行します。Lileiの残高は400のままで、手順(1)のクエリ結果と一致しています。非反復読み取りの問題はありません。次に、id = 1でupdate balance = balance - 50を実行します。残高は400-50=350にはなりません。Lileiの残高値は手順(2)で350を使用して計算されるため、300になります。データの一貫性は損なわれません。これは少し魔法のようです。おそらくMySQLの機能なのでしょう。

mysql> アカウントから * を選択します。
+------+--------+---------+
| ID | 名前 | 残高 |
+------+--------+---------+
| 1 | リレイ | 400 |
| 2 | ハンメイ | 16000 |
| 3 | ルーシー | 2400 |
+------+--------+---------+
セット内の行数 (0.00 秒)

mysql> アカウントを更新し、balance = balance - 50 に設定します (id = 1)。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> アカウントから * を選択します。
+------+--------+---------+
| ID | 名前 | 残高 |
+------+--------+---------+
| 1 | リレイ | 300 |
| 2 | ハンメイ | 16000 |
| 3 | ルーシー | 2400 |
+------+--------+---------+
セット内の行数 (0.00 秒)

（５）クライアントAでトランザクションを開始し、アカウントテーブルの初期値を照会する

mysql> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> アカウントから * を選択します。
+------+--------+---------+
| ID | 名前 | 残高 |
+------+--------+---------+
| 1 | リレイ | 300 |
| 2 | ハンメイ | 16000 |
| 3 | ルーシー | 2400 |
+------+--------+---------+
セット内の行数 (0.00 秒)

（6）クライアントBでトランザクションを開き、残高フィールド値が600の新しいデータエントリを追加してコミットする

mysql> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> アカウントに値(4,'lily',600)を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

(7) クライアント A が残高の合計を計算すると、値は 300+16000+2400=18700 になります。クライアント B の値は考慮されていません。クライアント A がリクエストを送信した後、残高の合計が再度計算され、驚いたことに 19300 になります。これは、クライアント B の 600 が考慮されるためです。クライアントの観点からは、クライアントはクライアント B を見ることができず、600 ドル余計に加算された空想だと考えるでしょう。これはファントム リードです。開発者の観点からは、データの一貫性は損なわれません。ただし、アプリケーションでは、コードが 18700 をユーザーに送信してしまう可能性があります。この可能性が低い状況を回避する必要がある場合は、以下で紹介するトランザクション分離レベル「シリアル化」を採用する必要があります。

mysql> アカウントから合計(残高)を選択します。
+--------------+
| 合計(残高) |
+--------------+
| 18700 |
+--------------+
セット内の 1 行 (0.00 秒)

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> アカウントから合計(残高)を選択します。
+--------------+
| 合計(残高) |
+--------------+
| 19300 |
+--------------+
セット内の 1 行 (0.00 秒)

4. シリアル化

（1）クライアントAを開き、現在のトランザクションモードをシリアライズ可能に設定し、アカウントテーブルの初期値を照会します。

mysql> セッショントランザクション分離レベルを serializable に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> アカウントから * を選択します。
+------+--------+---------+
| ID | 名前 | 残高 |
+------+--------+---------+
| 1 | リレイ | 10000 |
| 2 | ハンメイ | 10000 |
| 3 | ルーシー | 10000 |
| 4 | ユリ | 10000 |
+------+--------+---------+
セット内の行数 (0.00 秒)

(2) クライアント B を開き、現在のトランザクション モードを serializable に設定します。レコードを挿入するとエラーが発生します。テーブルがロックされ、挿入は失敗します。MySQL のトランザクション分離レベルが serializable の場合、テーブルがロックされるため、ファントム リードは発生しません。この分離レベルでは、同時実行性が非常に低くなります。多くの場合、1 つのトランザクションがテーブルを占有し、他の何千ものトランザクションはただ見ているだけです。トランザクションが終了してコミットされるまで、使用できません。この分離レベルは、開発ではほとんど使用されません。

mysql> セッショントランザクション分離レベルを serializable に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> アカウントに値を挿入します(5, 'tom',0);
エラー 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。

補充:

1. SQL 仕様で指定される標準は、データベースごとに異なる場合があります。

2. MySQL のデフォルトのトランザクション分離レベルは繰り返し読み取りであり、読み取られた行はロックされません。

3. トランザクション分離レベルがシリアル化されている場合、データの読み取りによりテーブル全体がロックされます。

4. この記事を読んで、開発者の視点に立つと、非反復読み取りとファントム読み取りに論理的な問題はなく、最終的にデータの一貫性が保たれると感じるかもしれません。しかし、ユーザーの観点から見ると、通常は 1 つのトランザクションしか見えず (クライアント A しか見えず、隠れたクライアント B の存在に気付いていない)、同時トランザクション実行の現象を考慮することはありません。同じデータが複数回読み取られて異なる結果になったり、新しいレコードが突然現れたりすると、疑問を抱くかもしれません。これはユーザー エクスペリエンスの問題です。

5. MySQL でトランザクションを実行すると、最終結果にデータの一貫性の問題は発生しません。これは、トランザクションでは、MySQL が操作を実行するときに前の操作の中間結果を使用しない可能性があるためです。他の同時トランザクションの実際の状況に基づいて処理します。非論理的に思えますが、データの一貫性を保証します。ただし、アプリケーションでトランザクションを実行すると、操作の結果が次の操作で使用され、他の計算が実行されます。これについては注意が必要です。繰り返し読み取り中は行をロックし、シリアル化中はテーブルをロックする必要があります。そうしないと、データの一貫性が損なわれます。

6. MySQL でトランザクションを実行すると、MySQL は各トランザクションの実際の状況に基づいて総合的に処理するため、データの一貫性が損なわれることはありません。ただし、アプリケーション プログラムは論理ルーチンに従って実行されますが、MySQL ほどスマートではないため、データの一貫性の問題は避けられません。

7. 分離レベルが高いほど、データの整合性と一貫性が保証されますが、同時実行パフォーマンスへの影響も大きくなります。両方を同時に実現することはできません。ほとんどのアプリケーションでは、データベース システムの分離レベルを Read Committed に設定することをお勧めします。これにより、ダーティ リードが回避され、同時実行パフォーマンスが向上します。非反復読み取りやファントム読み取りなどの同時実行の問題が発生する可能性がありますが、そのような問題が発生する可能性がある個々のケースでは、アプリケーションは悲観的ロックまたは楽観的ロックを使用してそれを制御できます。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。