MySQLの分離レベルとロックメカニズムの詳細な説明

簡単な説明:

MySQL は通常、複数のトランザクションを同時に実行し、複数のトランザクションが同じデータまたは同じデータ バッチに対して CRUD 操作を同時に実行する場合があります。これにより、通常ダーティ リード、非反復リード、ファントム リードと呼ばれる問題が発生する可能性があります。

これらの問題の本質は、MySQL のマルチトランザクション同時実行性にあります。マルチトランザクション同時実行性の問題を解決するために、MySQL はロック メカニズム、MVCC マルチバージョン同時実行性制御分離メカニズム、およびトランザクション分離メカニズムを設計し、一連のメカニズムを使用して、マルチトランザクション同時実行性によって発生する問題を解決しました。

1. 取引の4つの特徴

2. 複数の同時トランザクションによって発生する問題

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

ダーティ リード、非反復リード、ファントム リードは、実際には MySQL の読み取り一貫性の問題であり、特定のトランザクション分離メカニズムを提供するデータベースによって解決する必要があります。

現在のデータベースのトランザクション分離レベルを表示します: 'tx_isolation' などの変数を表示します。

トランザクション分離レベルを設定します: set tx_isolation='isolation level'

4. 異なる分離レベルでの問題を実証する

MySQL バージョン: 5.7.34

関係するテーブル:

2つのMySQLクライアント

クライアント A <====================>クライアント B (以下の各写真の 2 つのクライアントは、最初の写真にちなんで名付けられています)

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

1.1 トランザクション分離レベルを設定する set tx_isolation='read-uncommitted';

1.2 クライアント A とクライアント B はそれぞれトランザクションを開きます。

1.3 クライアント A はクエリのみを実行し、クライアント B は id = 1 のレコードを変更します。

1.4 両方のトランザクションがコミットされていない場合、トランザクションAはトランザクションBによって変更されたデータを読み取る

1.5 何らかの理由でクライアント B のトランザクションがロールバックされると、クライアント A によってクエリされたデータは実際にはダーティ データとなり、一貫性の要件を満たさなくなります。

コミットされた読み取り

2.1 分離レベルをコミット読み取りに設定します: set tx_isolation='read-committed';

2.2 クライアント A とクライアント B はそれぞれトランザクションを開きます。

2.3 クライアント A はクエリのみを実行し、クライアント B は id = 1 のレコードを変更します。

2.4 クライアントBがトランザクションを送信していない場合、クライアントAはクライアントBが送信していないデータを照会することができず、ダーティリードの問題が解決される。

2.5 クライアント B がトランザクションをコミットした後、クライアント A がテーブルを再度クエリすると、結果が前のステップと一致しません。これは、非反復読み取りの問題が発生し、分離要件を満たさないことを意味します。

繰り返し読み取り

3.1 分離レベルを繰り返し読み取りに設定します: set tx_isolation='repeatable-read';

3.2 クライアント A とクライアント B はそれぞれトランザクションを開きます。

3.3 クライアント B はテーブル内のデータを変更して送信します。

3.4 クライアントAはテーブル内のデータを照会し、前のステップとの矛盾がないことを発見し、非反復読み取りの問題を解決します。

3.5 クライアント A で、update account set balance = balance - 100 (id = 1) を実行します。残高は 800-100=700 にはなりません。代わりに、クライアント B によって送信されたデータを使用して計算されるため、600 になります。データの一貫性は破壊されません。MVCCメカニズムは繰り返し読み取り分離レベルで使用され、選択操作ではバージョン番号が更新されません。これはスナップショット読み取り (履歴バージョン) であり、同じトランザクションでの繰り返し読み取りを保証します。挿入/更新/削除ではバージョン番号が更新され、これは現在の読み取り (現在のバージョン) であり、データの一貫性を保証します。

3.6 クライアントBはトランザクションを再開し、データを挿入してコミットする

3.7 クライアントAのテーブルデータを再クエリすると、クライアントBによって追加されたデータは表示されず、ファントムリードは発生しません。

3.8 ファントム リードを確認する: クライアント A で、ID = 4 のデータを変更します。更新は成功します。再度クエリを実行して、クライアント B によって追加された新しいデータを検索します。これはファントム リードの問題を示しており、分離要件を満たしていません。

シリアル化

4.1 分離レベルをシリアル化可能に設定します: set tx_isolation='serializable';

4.2 クライアント A とクライアント B はそれぞれトランザクションを開きます。

4.3 クライアントAは最初にid = 1のテーブル内のデータをクエリします。

4.4 クライアント A のトランザクションがコミットされていない場合、クライアント B は id = 1 のテーブル内のデータを更新します。クライアント A のトランザクションがコミットされていないため、クライアント B の更新アクションは、クライアント A がトランザクションをコミットするかタイムアウトするまでブロックされます。タイムアウトが発生した場合、SQL エラーは次のようになります: ロック待機タイムアウトを超えました。トランザクションを再起動してください。

4.5 id = 2 のデータはクライアント B で正常に更新できます。つまり、シリアル化された分離レベルでは、InnoDB クエリもロックされます。

4.6 クライアント A が範囲クエリを実行すると、各行レコードが配置されているギャップ間隔範囲を含む範囲内のすべての行がロックされます (行が挿入されていない場合でもロックされます。これをギャップロックと呼びます) 。このとき、クライアント B が範囲内のデータに対して何らかの操作を実行すると、ブロックされるため、ファントム読み取りが回避されます。

4.7シリアル化 この分離レベルでは同時実行性が非常に低いため、実際の開発ではほとんど使用されません。これは、MySQL がデフォルトの分離レベルとして繰り返し読み取りを使用する重要な理由でもあります。

5. ロック機構

MySQL のデフォルトの分離レベルは繰り返し読み取りですが、それでもファントム読み取りが発生する可能性があります。ギャップ ロックは、場合によってはファントム読み取りを解決できます。

ギャップロック

概要: ギャップ ロックは 2 つの値間のギャップをロックします。

表のデータが次のとおりであると仮定します。

すると、3つのギャップ（4,10）、（10,15）、（15, 正の無限大）が存在します。

1.1 分離レベルを繰り返し読み取りに設定します: set tx_isolation='repeatable-read';

1.2 クライアント A とクライアント B はそれぞれトランザクションを開きます。

1.3 クライアント A で、update account set balance = 1000 where id > 5 and id < 13 を実行します。

1.4 クライアント A がリクエストを送信していない場合、クライアント B は範囲内のすべての行 (ギャップ行を含む) と行が配置されているギャップに対して挿入/更新操作を実行できません。つまり、4<id<=15 の範囲のデータを変更することはできず、id = 15 も変更できません。

1.5ギャップロックは繰り返し読み取り分離レベルでのみ有効です

一時ロック

概要: 一時ロックは、行ロックとギャップ ロックの組み合わせです。たとえば、上記の 4<id<=15 は一時ロックです。

非インデックス行ロックはテーブルロックにアップグレードされます

3.1 クライアント A とクライアント B はそれぞれトランザクションを開きます。

3.2 クライアント A で、update account set balance = 1000 where name = 'Li Si' を実行します。

3.3 クライアント A が送信していない場合、クライアント B は update account set balance = 800 where id = 15 を実行します。クライアント A が送信するかタイムアウトするまで、これもブロックされます。

3.4 MySQL のロックは主にインデックス フィールドにロードされます。インデックス以外のフィールドで使用する場合、行ロックはテーブル ロックにアップグレードされます。

排他ロック

4.1 クライアント A とクライアント B はそれぞれトランザクションを開きます。

4.2 クライアント A で update に対して select * from account where id = 1 を実行します。

4.3 クライアント A が送信していない場合、クライアント B は update account set balance = 800 where id = 1 を実行します。クライアント A が送信するかタイムアウトするまでブロックされます。

結論: Innodb エンジンは行ロックを実装します。行ロック メカニズムの実装によって生じるパフォーマンスの低下はテーブル ロックよりも大きい可能性がありますが、全体的な同時処理能力はテーブル ロックよりも確実に強力です。システムの同時実行性が高い場合、行ロックはテーブル ロックよりも明らかに有利です。ただし、行ロックはテーブル ロックよりも使用が複雑です。不適切に使用すると、行ロックのパフォーマンスはテーブル ロックよりも良くないだけでなく、さらに悪くなる可能性があります。

行ロックの粒度が小さい場合、行ロックのオーバーヘッドがテーブルロックのオーバーヘッドよりも大きいのはなぜですか?

テーブル レベルのロックでは、ロックする現在のテーブルのみを見つける必要があるのに対し、行ロックでは、ロックする行が見つかるまでテーブル内のレコードをスキャンする必要があるため、行ロックのコストはテーブル レベルのロックよりも大きくなります。

実際の開発状況におけるロックの最適化に関するいくつかの提案:

• インデックスフィールドロックを適切に使用してロック範囲を狭める
• 非インデックス行ロックがテーブル ロックにエスカレートするのを回避するために、可能な限りすべてのロックをインデックス フィールドに追加します。
• 大きなギャップによるギャップロックを回避するために、クエリスコープを可能な限り最小化します。
• トランザクションの分離を最小限に抑える
• トランザクション サイズを可能な限り制御し、ロックされるリソースの量を減らし、トランザクション ロックに関係する SQL をトランザクションの最後に配置して、ロック時間を短縮します。

要約する

これで、MySQL の分離レベルとロック メカニズムに関するこの記事は終了です。MySQL の分離レベルとロック メカニズムの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。