MySQL の 4 つのトランザクション分離レベルの詳細な紹介 (画像とテキスト)

この記事では主に MySQL の 4 つのトランザクション分離レベルの関連情報を詳しく紹介します。興味のある方は参考にしてください。

この記事の実験のテスト環境: Windows 10+cmd+MySQL5

。

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

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

2. 整合性: トランザクションの開始と終了の前後で、データベース

constraints の整合性が破壊されていません。たとえば、A が B にお金を送金した場合、A がそのお金を差し引くが、B が受け取らないということは不可能です。

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

4. 耐久性: トランザクションが完了すると、トランザクションによるデータベースへのすべての更新はデータベースに保存され、ロールバックすることはできません。

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

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

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

2. 反復不可能読み取り: トランザクション A は同じデータを複数回読み取り、トランザクション B はトランザクション A の複数回読み取り中にデータを更新およびコミットします。その結果、トランザクション A が同じデータを複数回読み取ると、結果は一貫性がなくなります。

3. ファントムリーディング：システム管理者Aは、データベース内の全生徒のスコアを特定のスコアからABCDEの成績に変更しましたが、システム管理者Bはこのとき、特定のスコアのレコードを挿入し、その後、システム管理者Aが結果を変更しました。まるで幻覚を見たかのように、変更されていない記録が 1 つあることがわかりました。これをファントム リーディングと呼びます。

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

3. MySQL トランザクション分離レベル

MySQL のデフォルトのトランザクション分離レベルは繰り返し読み取りです

4. 例を使用して各分離レベルを説明します

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

(1) クライアント A を開き、現在の トランザクション モード

を設定します。コミットされていない読み取り (アンコミット読み取り) を行うには、

クエリ

テーブルアカウントの初期値:

(2) クライアント A のトランザクションがコミットされる前に、別のクライアント B を開いてテーブルアカウントを更新します:

(3)今回は、クライアント B のトランザクションはまだ送信されていませんが、クライアント A は B の更新されたデータをクエリできます。

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

(5) クライアント A で更新ステートメントを実行します。アカウントの更新セットの残高 = 残高 - 50 (ID =1 の場合)、lilei の残高は 350 にならず、予想外に 400 になりました。データの整合性は問われません

そう考えると、アプリケーションでは 400-50=350 を使用します。この問題を解決するには、読み取りコミット分離レベル

を使用できます。 2. 読み取りコミット

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

(2) クライアント A のトランザクションがコミットされる前に、別のクライアント B を開いてテーブル アカウントを更新します:

(3) この時点では、クライアント 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) で lilei の残高 400 を他の操作を行うための中間値として使用することになります

(3) クライアント A クエリでステップ (1) を実行します:

(4) ステップ (1) を実行します。lilei の残高は依然として 400 であり、ステップ (1) のクエリ結果と一致しており、反復不可能な読み取りの問題はありません。その後、更新残高 = 残高 - 50 を実行します。ここで、id = 1。残高は 400-50=350 に変わりません。lilei の残高値は、手順 (2) の 350 を使用して計算されるため、300 になります。データの整合性は崩れていません。ちょっと魔法のような、おそらく mysql の機能

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei |  400 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei |  300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(5) クライアント A でトランザクションを開き、テーブル アカウントの初期値をクエリします

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(6) クライアント B でトランザクションを開き、残高フィールド値を含むデータは 600 で、

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(4,'lily',600);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

を送信します。(7) クライアント A の残高の合計を計算します。値は 300+16000+2400=18700 です。クライアント B の値は含まれません。顧客 A が提出した後に計算され、実際の残高の合計は 19300 になりました。 これは、顧客から見ると、顧客 B のことが見えず、世界があるように感じられるためです。 600 元は無駄です。開発者から見ると、データの整合性は失われません。ただし、アプリケーションでは、コードがユーザーに 18700 を送信する可能性があります。このような可能性が低い状況を回避する必要がある場合は、以下で紹介するトランザクション分離レベル「シリアル化」を採用する必要があります

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 18700 |
+--------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 19300 |
+--------------+
1 row in set (0.00 sec)

(1)。クライアント A を開き、現在のトランザクション モードをシリアル化可能に設定し、テーブル アカウントの初期値をクエリします:

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 10000 |
| 2 | hanmei | 10000 |
| 3 | lucy | 10000 |
| 4 | lily | 10000 |
+------+--------+---------+
rows in set (0.00 sec)

(2) クライアント B を開き、現在のトランザクション モードをシリアル化可能に設定すると、エラーが発生します。レコードの挿入時にテーブルがロックされ、挿入が失敗することが報告されます。mysql のトランザクション分離レベルがシリアル化可能である場合、テーブルはロックされるため、この分離レベルでは同時実行性が非常に低くなります。トランザクションがテーブルを占有すると、他の何千ものトランザクションはただぼんやりと眺めることしかできず、完了して送信されるまで待ってから使用できるようになります。これは開発ではほとんど使用されません。

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(5,'tom',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

補足:

1. SQL仕様で規定されている標準に従って、異なるデータベースの特定の実装にはいくつかの違いがある可能性があります

2。 lock はロックされます

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

4. 開発者の視点に立った場合、次のように感じるかもしれません。読み取りとファントム読み取りには論理的な問題はありませんが、ユーザーの観点からは、通常、1 つのトランザクションしか認識できません (アンダーカバーの存在はクライアント A のみ)。エージェント) は、トランザクションの同時実行の現象を考慮せずに、同じデータが複数回読み取られて結果が異なったり、新しいレコードが突然現れたりすると、これはユーザー エクスペリエンスの問題であると疑問を抱く可能性があります。

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

6. mysql でトランザクションが実行される場合、mysql は実際の状況に応じて各トランザクションを包括的に処理するため、データの一貫性は崩れません。ただし、アプリケーションが論理ルーチンに基づいている場合、それほどスマートではありません。 mysql として使用するため、データの整合性の問題が発生することは避けられません。

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

以上がMySQL の 4 つのトランザクション分離レベルの詳細な紹介 (画像とテキスト)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。