MySQL トランザクション分離レベルのサンプルチュートリアル-mysql チュートリアル-php.cn

この記事の実験環境: Windows 10+cmd+MySQL5.6.36+InnoDB

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

1. 原子性: トランザクションが開始されると、すべての操作が完了するか完了しないかのどちらかであり、途中で停滞することはあり得ません。トランザクションの実行中にエラーが発生した場合、トランザクションが開始される前の状態にロールバックされ、すべての操作は発生しなかったかのように行われます。つまり、物事は、化学で習う物質の基本単位である原子のように、分割できない全体であるということです。 2. 一貫性 (Consistency): トランザクションの開始および終了の前後において、データベースの整合性制約に違反していないこと。たとえば、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 つある、それは幻覚のようなものでした。これをファントムリーディングと呼びます。

概要: 反復不可能な読み取りとファントム読み取りは変更に焦点を当て、ファントム読み取りは追加または削除に焦点を当てます。反復不能読み取りの問題を解決するには、条件を満たす行をロックするだけで済みます。ファントム読み取りの問題を解決するには、テーブルをロックするだけで済みます。

トランザクション分離レベル	ダーティリード	非反復読み取り	ファントムリード
読み取り非コミット	は	です	はい
非反復読み取り (読み取りコミット)	いいえ	はい	はい
反復可能-読んでくださいいいえ	いいえ	MySQL のデフォルトのトランザクション分離レベルは繰り返し読み取りです IV. 例を使用して各分離レベルを説明します 1.未投稿を読む： (3) この時点では、クライアント B のトランザクションはまだ送信されていませんが、、クライアントAはBの更新されたデータをクエリすることができます。 350 ですが、実際には 400 です。奇妙ではありませんか? データの整合性は問われませんでした。このように考えるのは甘すぎます。アプリケーションでは 400-50=350 を使用します。他のセッションがロールバックされていることがわかります。この問題を解決するには、読み取りコミットされた分離レベルを使用できます。 2. コミットされた読み取りマッチメント >> クライアント A クライアント B のトランザクションが開始される前送信されたら、別のクライアント B を開いてテーブルアカウントを更新します: (3) この時点では、クライアント B のトランザクションはまだ送信されておらず、クライアント A はクライアント B の更新されたデータをクエリできません。これは解決されます。ダーティリードの問題: クライアントAのセッション中であると仮定して、次のことをクエリしますi の残高は 450 ですが、他の取引によりリレイの残高値は 400 に変更されます値 450 を他の処理に使用するかどうかはわかりません。操作に問題がありますが、その可能性は非常に小さいため、反復可能な読み取り分離レベルを使用できます。 3. 反復可能読み取り (1) クライアント A を開き、現在のトランザクションモードを反復可能読み取りに設定し、テーブルアカウントの初期値をクエリします: (2) クライアント A のトランザクションが送信される前に、オープン別のクライアント B は、テーブルアカウントを更新して送信します。クライアント B のトランザクションは、クライアント A のトランザクションによってクエリされた行を実際に変更できます。つまり、MySQL の反復読み取りは、トランザクションによってクエリされた行をロックしません。 SQL 標準の分離レベルは反復読み取り中、読み取りおよび書き込み操作のために行をロックする必要がありますが、mysql にはロックがありません。アプリケーション内の行をロックすることに注意してください。そうでないと、ステップ(1)のlileyの残高400を他の操作を実行するための中間値として使用することになりますステップ(1)のinでステップ(1)を実行します。クエリ: (4) ステップ(1)を実行すると、lilei の残高はまだ 400 であり、ステップ (1) のクエリ結果と一致しており、反復不可能な読み取りの問題はありません次に、update Balance = Balance を実行します。 - id = 1 の場合、残高は 400-50=350 に変わりません。リレイの残高値は (2) の 350 を使用して計算されるため、300 になります。データの整合性は崩れていません。ちょっと魔法のようで、MySQL の機能かもしれません。 Bar mysql> select * from account;+------+--------+---------+\| id \| name \| balance \|+------+--------+---------+\| 1 \| lilei \| 400 \|\| 2 \| hanmei \| 16000 \|\| 3 \| lucy \| 2400 \|+------+--------+---------+3 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: 0mysql> select * from account;+------+--------+---------+\| id \| name \| balance \|+------+--------+---------+\| 1 \| lilei \| 300 \|\| 2 \| hanmei \| 16000 \|\| 3 \| lucy \| 2400 \|+------+--------+---------+3 rows in set (0.00 sec) ログイン後にコピー 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 \|+------+--------+---------+3 rows in set (0.00 sec) ログイン後にコピー (7) クライアントAの残高の合計を計算します。値は 300+16000+2400=18700 ですクライアント B の値は含まれていないため、クライアント A が送信した後の残高の合計は 19300 であることがわかります。これは、顧客の観点からは、クライアント B の 600 が含まれているためです。 B. 世界が負けてさらに 600 ブロックあるように感じますが、これは開発者の観点からはデータの一貫性が破壊されていません。ただし、アプリケーションでは、コードがユーザーに 18700 を送信する可能性があります。このような可能性が低い状況を回避する必要がある場合は、以下で紹介するトランザクション分離レベル「シリアル化」を採用する必要があります select sum(balance) from account; 。 +---------------+ \| 合計(残高) \| +--------------- + \| -------+ セット内の 1 行 (0.00 秒) mysql>コミット; クエリ OK、影響を受ける行は 0 (0.00 秒) アカウントから合計 (残高) を選択; +-- -----------+ \| 合計(残高) \| +--------------- + \| ----+ 1 row in set (0.00 sec) 4. シリアル化 (1) クライアント A を開き、現在のトランザクションモードをシリアル化可能に設定し、テーブルアカウントの初期値をクエリします。 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) ログイン後にコピー B、現在のトランザクションモードをシリアライズ可能に設定すると、テーブルがロックされており、挿入に失敗するとエラーが報告され、 MySQLがロックされます。トランザクション分離レベルがシリアル化可能である場合、ファントム読み取りは発生しません。多くの場合、1 つのトランザクションがテーブルを占有し、他のトランザクションはそれを実行できるまで待機する必要があります。使用する前に送信が完了するため、開発で使用されることはほとんどありません。りー補足: 1. SQL仕様で規定されている標準、異なるデータベースの具体的な実装にはいくつかの違いがある可能性があります２． mysql デフォルトのトランザクション分離レベルが反復読み取りの場合、読み取った行はロックされません 3. トランザクション分離レベルがシリアル化の場合、データを読み取るとテーブル全体がロックされます 4. この記事を読むと、開発者の視点に立つと、非反復読み取りとファントム読み取りについては論理的に問題がないように感じるかもしれませんが、最終的なデータはまだ一貫していますが、開発者の観点からは。 user クライアントの観点から見ると、通常は 1 つのトランザクションしか見ることができず (クライアント A のみであり、秘密のクライアント B の存在は知りません)、同じデータが存在するとトランザクションが同時に実行されるという現象は考慮されません。何度も読み取った結果が異なっていたり、何もないところから新しいレコードが表示されたりした場合、これはユーザーエクスペリエンスの問題であると疑問を抱く可能性があります。 5. mysqlでトランザクションが実行される場合、トランザクションではmysqlが操作を実行するときに、その中間結果を使用しない可能性があるため、最終結果にはデータの一貫性の問題は発生しません。前の操作では、他の同時トランザクションの実際の状況に基づいて処理されますが、これによりデータの一貫性が確保されますが、アプリケーションでトランザクションが実行されると、その結果が次の操作で使用されます。、およびその他の計算。このため、反復読み取り中に行をロックし、シリアル化中にテーブルをロックする必要があるため、注意が必要です。そうしないと、データの一貫性が破壊されます。 6. mysqlでトランザクションが実行されると、mysqlは実際の状況に応じて各トランザクションを包括的に処理するため、データの一貫性は破壊されませんが、アプリケーションが論理ルーチンに従って実行される場合MySQL はスマートではないため、データの一貫性の問題が必然的に発生します。 7。分離レベルが高いほど、データの完全性と一貫性が保証されますが、同時実行パフォーマンスへの影響も大きくなります。。ほとんどのアプリケーションでは、データベースシステムの分離レベルを Read Committed に設定することを優先できます。これにより、ダーティリードが回避され、同時実行パフォーマンスが向上します。反復不可能な読み取りやファントム読み取りなどの同時実行性の問題が発生しますが、そのような問題が発生する可能性がある個々の状況では、アプリケーションは悲観的ロックまたは楽観的ロックを使用してそれらを制御できます。