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

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

簡単な説明:

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

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

1. 取引の4つの特徴

特性特徴
原子性トランザクションは分割不可能であり、データに対する変更はすべて実行されるか、まったく実行されないかのいずれかになります。
一貫性トランザクションがコミットされる前と後の状態とデータは一貫していなければならない
分離複数のトランザクションが同時に実行されている場合、トランザクションは同時操作の影響を受けない「独立した」環境で実行されることが保証されます。つまり、トランザクション処理プロセスの中間状態は外部からは見えず、その逆も同様です。
耐久性トランザクションがコミットされると、データはディスクに保存され、失われることはありません。

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

質問現象説明する
ダーティリードトランザクション A はレコードを変更しています。トランザクション A が完了してコミットされる前は、このレコードのデータは不整合な状態です (ロールバックまたはコミットされている可能性があります)。同時に、トランザクション B も同じレコードを読み取ります。制御がない場合、トランザクション B はこれらの「ダーティ」データを読み取り、さらに処理して、コミットされていないデータを生成します。あるトランザクションが別のトランザクションによってコミットされていないデータを読み取りますが、これは一貫性の要件を満たしていません。
繰り返し不可能な読み取りトランザクションは、あるデータを読み取った後、ある時点でそのデータを読み取り、次に以前に読み取ったデータを読み取りますが、読み取ったデータが変更されているか、一部のレコードが削除されていることがわかります。トランザクションで複数回読み取られたデータは、他のトランザクションによって送信された更新によって干渉されるため一貫性がなく、分離要件を満たしません。
ファントムリードトランザクションは、同じクエリ条件で以前にクエリされたデータを再読み取りますが、他のトランザクションがそのクエリ条件を満たす新しいデータを挿入したことを検出します。挿入/削除をすでに送信した他のトランザクションからの干渉により、トランザクションで複数回読み取られたデータの一貫性がなくなり、分離要件を満たさなくなります。

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

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

分離レベルダーティリード繰り返し不可能な読み取りファントムリード
コミットされていない読み取り
コミットされた読み取り×
繰り返し読み取り (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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL のロックの仕組みと使用法の分析
  • MySQL InnoDB のロック機構の詳細な説明
  • MySQLのロック機構の詳細な説明
  • MySQLデータベースのロック機構の分析
  • MySQLのロック機構に関する最も包括的な説明

<<:  ウェブデザイナー職の面接でよくある質問と回答

>>:  CSSテーマを簡単に切り替える方法の詳細な説明

推薦する

JSにおけるデータ型の正しい判定方法の例

目次序文typeof は型を正しく判別できますか? instanceof は型を正しく判別できますか...

tomcat9.exeをクリックするとクラッシュする問題を解決する方法

ある読者から連絡があり、ダウンロードが終了し、操作がまだ開始されていないのに、なぜ Tomcat の...

Dockerコンテナでユーザーを分離する方法

前回の記事「Docker コンテナの UID と GID を理解する」では、Docker コンテナ内...

ReactにおけるRefの相互利用の詳細な説明

目次1. まずRefとは何かを説明しましょう2. フックでのrefの使用1. HTMLDomフックで...

SVNサービスバックアップ操作手順の共有

SVN サービスのバックアップ手順1. ソースサーバーとターゲットサーバーを準備するソースサーバー:...

Centos7 への MySQL8 のインストールチュートリアル

MySQL 8 の新機能: MySQL をバージョン 5.x から 8.x に直接アップグレードする...

JSONObject の使用方法の詳細な説明

JSONObject は単なるデータ構造であり、JSON 形式のデータ構造 ( key-value構...

Docker でコンテナのポート マッピングを動的に変更する方法

前書き: Docker のポート マッピングは、多くの場合、Docker Run コマンド中に -p...

シンプルなビデオ連射機能を実装する JavaScript CSS3

この記事では、最も単純なビデオ連射機能をシミュレートするデモを作成します。アイデア:再生する動画と同...

Vueは指定された日付間のカウントダウンを実装します

この記事では、指定された日付間のカウントダウンを実装するためのVueの具体的なコードを例として紹介し...

ブラウザの自動更新を実装するReactサンプルコード

目次フロントエンドルーティングとは何ですか?フロントエンドルーティングを実装するにはどうすればいいで...

CentOS7 は rpm を使用して MySQL 5.7 をインストールするチュートリアル図

1. 4つのrpmパッケージをダウンロードする mysql-コミュニティクライアント-5.7.26-...

Ubuntu20.04 VNCのインストールと設定の実装

VNC はリモート デスクトップ プロトコルです。 VNC を使用して Ubuntu 20.04 を...

フォント宝庫 50 種類の素晴らしい無料英語フォントリソース パート 1

デザイナーは独自のフォント ライブラリを持っているため、プロジェクトの設計時にすぐに使用できます。今...

Vue3でelement-plusを使用する方法の詳細な説明

目次1. インストール2. main.jsにインポートする3. 使用Vue3がリリースされてからしば...