MySQL トランザクション分離はどのように実現されますか?

MySQL トランザクション分離はどのように実現されますか?

更新、削除、および挿入ステートメントは、関連するデータに排他ロックを自動的に追加します。選択ステートメントは、デフォルトではロックを追加しません。

では、どのような状況で読み取り操作がロックされるのでしょうか?

  • 共有モードで...ロックを選択し、読み取ったレコードにSロックを追加します
  • 更新のために...を選択し、読み取ったレコードにXロックを追加します
  • トランザクションでレコードを読み取り、読み取ったレコードにSロックを追加する
  • トランザクション分離レベルが SERIALIZABLE の場合、読み取られたレコードに S ロックが追加されます。

InnoDB には 3 種類のロックがあります。

  • レコードロック: 単一のレコードをロックする
  • ギャップロック: ギャップロックは、レコードの前のギャップをロックし、レコードを挿入できないようにします。
  • 次キー ロック: データとデータの前のギャップの両方をロックします。つまり、データ内またはデータの前のギャップにレコードを挿入することはできません。

デモを書く

テーブル「girl」を作成します(
  `id` int(11) NULLではない、
  `name` varchar(255)、
  `年齢` int(11)、
  主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;
女の子の価値観に挿入する
(1、「西施」、20)、
(5、「王昭君」、23)
(8、「貂蝉」、25)
(10、「楊玉環」、26)、
(12、「陳元元」、20)

レコードロック

単一レコードのロック

たとえば、次に示すように、ID 値が 8 のデータにレコード ロックを追加します。

ここに画像の説明を挿入

レコードロックにはSロックとXロックもあり、互換性は前述の通りです。

SQL 実行時に追加されるロックの種類は、トランザクション分離レベルや実行時に使用されるインデックス (クラスター化インデックス、非クラスター化インデックスなど) など、多くの条件の影響を受けます。そのため、詳細に分析することはせず、簡単な例をいくつか示します。

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ 等値クエリに主キーを使用する -- id=8 のレコードに S タイプ レコード ロックを追加する
select * from girl where id = 8 共有モードでロックします。

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ 等値クエリに主キーを使用する -- id=8 のレコードに X タイプ レコード ロックを追加する
更新のために、ID が 8 である girl から * を選択します。

ギャップロック

レコードの前の隙間をロックし、レコードの挿入を許可しない

MySQLはMVCCと繰り返し読み取り分離レベルでのロックによりファントム読み取り問題を解決できる。

現在の読み取り: ロックされたスナップショットの読み取り: MVCC

しかし、どうやってロックするのでしょうか?読み取り操作が初めて実行されるときにはこれらのファントム レコードは存在しないため、レコード ロックを追加する方法はありません。この時点で、ギャップ ロックを追加する、つまりギャップをロックすることで問題を解決できます。

ここに画像の説明を挿入

たとえば、トランザクションが id=8 のレコードにギャップ ロックを追加すると、他のトランザクションは id=8 のレコードの前のギャップに新しいレコードを挿入できなくなります。つまり、間隔 (5, 8) の id 値を持つレコードをすぐに挿入できなくなります。範囲 (5, 8) の ID 値を持つレコードは、ギャップ ロックが設定されたトランザクションがコミットされた後にのみコミットできます。

次のSQLロックプロセスを見てみましょう

-- REPEATABLE READ は、等値クエリに主キーを使用します -- しかし、主キーの値は存在しません -- ID=8 のクラスター化インデックス レコードにギャップ ロックを追加します
SELECT * FROM girl WHERE id = 7 LOCK IN SHARE MODE;

id=7 のレコードは存在しないため、ファントム読み取り (同じトランザクションで同じステートメントを実行して得られた結果セットに id=7 のレコードが含まれることを避ける) を防ぐために、現在のトランザクションがコミットされる前に他のトランザクションが id=7 のレコードを挿入できないようにする必要があります。このとき、id=8 のレコードにギャップ ロックを追加できます。つまり、他のトランザクションは id 値が (5, 8) の範囲にある新しいレコードを挿入できません。

ここに画像の説明を挿入

質問させてください。ギャップ ロックはレコードの前のギャップしかロックできませんが、最後のレコードの後のギャップはどのようにロックするのでしょうか。

実際、MySQLのデータはページに保存されており、各ページには2つの疑似レコードがあります。

  • ページ内の最小のレコードを示す最小レコード
  • ページ内の最大のレコードを示す Upremum レコード

他のトランザクションが区間 (12, +∞) 内の id 値を持つレコードを挿入するのを防ぐために、id=12 のレコードが配置されているページの Supremum レコードにギャップ ロックを追加できます。これにより、他のトランザクションが区間 (12, +∞) 内の id 値を持つ新しいレコードを挿入するのを防ぐことができます。

ネクストキーロック

データとデータの前のギャップを同時にロックします。つまり、データまたはデータの前のギャップにレコードを挿入することはできません。 <br /> つまり、次のキーロック=レコードロック+ギャップロックは次のように理解できます。

ここに画像の説明を挿入

-- REPEATABLE READ は範囲クエリに主キーを使用します -- id=8 のクラスター化インデックス レコードに S タイプ レコード ロックを追加します
-- ID>8 のすべてのクラスター化インデックス レコード (Supremum 疑似レコードを含む) に S タイプの次キー ロックを追加します。
SELECT * FROM girl WHERE id >= 8 LOCK IN SHARE MODE;

ファントムリード問題を解決するには、他のトランザクションがid>=8のレコードを挿入することを禁止する必要があるため、

  • id=8のクラスター化インデックスレコードにSタイプのレコードロックを追加します。
  • ID>8 のすべてのクラスター化インデックス レコード (Supremum 疑似レコードを含む) に S タイプの次キー ロックを追加します。

テーブルロック

テーブルロックはSロックとXロックに分けられる。

テーブルに対して SELECT、INSERT、UPDATE、または DELETE ステートメントを実行する場合、InnoDB ストレージ エンジンはテーブル レベルの S ロックまたは X ロックをテーブルに追加しません。

テーブルに対して ALTER TABLE や DROP TABLE などの一部の DDL ステートメントを実行すると、テーブルに X ロックが追加されるため、テーブルに対して SELECT INSERT UPDATE DELETE などのステートメントを実行する他のトランザクションはブロックされます。

システム変数autocommit = 0かつinnodb_table_locks = 1の場合、InnoDBストレージエンジンによって提供されるテーブルtのSロックまたはXロックを手動で取得します。次のように記述できます。

テーブルtにテーブルレベルのSロックを追加する

ロックテーブルを読み取り

テーブルtにテーブルレベルのXロックを追加する

書き込みテーブルをロックする

トランザクションがテーブルにSロックを追加すると、

  • 他のトランザクションは引き続きテーブルのSロックを取得できる
  • 他のトランザクションは、テーブル内の特定のレコードに対して S ロックを取得し続けることができます。
  • 他のトランザクションは、テーブルの X ロックを引き続き取得することはできません。
  • 他のトランザクションは、テーブル内の特定のレコードに対して X ロックを取得し続けることはできません。

トランザクションがテーブルにXロックを追加すると、

  • 他のトランザクションは、テーブルの S ロックを引き続き取得することはできません。
  • 他のトランザクションは、テーブル内の特定のレコードに対して S ロックを取得し続けることはできません。
  • 他のトランザクションは、テーブルの X ロックを引き続き取得することはできません。
  • 他のトランザクションは、テーブル内の特定のレコードに対して X ロックを取得し続けることはできません。

したがって、オンライン テーブルを変更する場合は、多数のトランザクションがブロックされるため、注意が必要です。オンライン テーブルを変更するための成熟した方法は多数ありますが、ここでは詳しく説明しません。

分離レベル

コミットされていない読み取り: 特別な処理を行わずに毎回最新のレコードを読み取る シリアル化: トランザクションは並行性なしでシリアルに実行される

そこで、コミット読み取り繰り返し読み取りの分離実装に焦点を当てます。

これら 2 つの分離レベルは、MVCC (Multi-version Concurrency Control) を通じて実装されます。本質的に、MySQL は undolog を通じて複数のバージョンの履歴データを保存し、ルールに従って特定の履歴バージョンのデータを読み取ります。このようにして、読み取りと書き込みをロックなしで並列化できるため、データベースのパフォーマンスが向上します。

では、undolog は変更前のレコードをどのように保存するのでしょうか?

InnoDB ストレージ エンジンを使用するテーブルの場合、クラスター化インデックス レコードには、次の 2 つの必要な非表示列が含まれます。

trx_id : トランザクションがクラスター化インデックス レコードを変更するたびに、トランザクションのトランザクション ID が trx_id 隠し列に割り当てられます。

roll_pointer : クラスター化インデックス レコードが変更されるたびに、古いバージョンが UNDO ログに書き込まれます。この隠し列はポインターに相当し、これを通じてレコードが変更される前の情報を見つけることができます。

レコードの名前を Diao Chan から Wang Zhaojun と Xi Shi に変更すると、次のレコードが作成されます。複数のレコードがバージョン チェーンを構成します。

ここに画像の説明を挿入

バージョン チェーン内のどのバージョンが現在のトランザクションに表示されるかを判断するために、MySQL は ReadView の概念を設計しました。重要な内容は以下の4つです

  • m_ids : ReadView を生成するときに現在のシステムでアクティブなトランザクション ID のリスト
  • min_trx_id : ReadViewを生成するときに、システムで現在アクティブな最小のトランザクションID、つまりm_idsの最小値
  • max_trx_id : ReadViewを生成するときにシステムが次のトランザクションに割り当てるトランザクションID値
  • Creator_trx_id : ReadViewを生成したトランザクションのトランザクションID

テーブル内のレコードが変更されたり、挿入、削除、または更新ステートメントが実行されたりすると、トランザクションに一意のトランザクション ID が割り当てられます。それ以外の場合、トランザクションのデフォルトのトランザクション ID 値は 0 です。

max_trx_id は m_ids の最大値ではありません。トランザクション ID は増分的に割り当てられます。たとえば、トランザクション ID が 1、2、3 の 3 つのトランザクションがあるとします。その後、トランザクション ID が 3 のトランザクションがコミットされます。新しいトランザクションが ReadView を生成すると、m_ids の値には 1 と 2 が含まれ、min_trx_id の値は 1、max_trx_id の値は 4 になります。

画像の説明を追加してください

実行プロセスは次のとおりです。

  • アクセスされたバージョンの trx_id が Creator_id と等しい場合、現在のトランザクションが変更したレコードにアクセスしているため、そのバージョンは現在のトランザクションからアクセスできることを意味します。
  • アクセスされたバージョンの trx_id が min_trx_id より小さい場合、現在のトランザクションが ReadView を生成する前に、そのバージョンを生成したトランザクションがコミットされているため、そのバージョンに現在のトランザクションからアクセスできることを意味します。
  • アクセスされたバージョンの trx_id が max_trx_id 以上である場合、このバージョンを生成したトランザクションは現在のトランザクションが ReadView を生成した後に開かれ、このバージョンは現在のトランザクションからはアクセスできないことを示します。
  • アクセスされたバージョンの trx_id は m_ids リストにありますか?
  • 4.1 はい、ReadView が作成されると、このバージョンはまだアクティブになり、アクセスできなくなります。バージョン チェーンに従って次のバージョンのデータを見つけ、上記の手順を続けて実行して可視性を判断します。最後のバージョンが表示されない場合は、レコードが現在のトランザクションに対して完全に不可視であることを意味します。
  • 4.2 いいえ、ReadViewが作成されると、バージョンを生成したトランザクションがコミットされ、バージョンにアクセスできるようになります。

さて、バージョンの可視性を取得するためのルールはわかりましたが、コミットされた読み取りと繰り返し可能な読み取りをどのように実装するのでしょうか?

実は、ReadViewを生成するタイミングが異なるという非常に単純なものです。

たとえば、まず次のテーブルを作成します

テーブル「girl」を作成します(
  `id` int(11) NULLではない、
  `name` varchar(255)、
  `年齢` int(11)、
  主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

コミットされた読み取り

Read Committed(読み取りコミット済み)、各読み取りの前にReadViewが生成される

ここに画像の説明を挿入

以下は3つのトランザクションを実行するプロセスです。1行は1つの時点を表します。

ここに画像の説明を挿入

まず、時点5での選択の実行プロセスを分析する

  • システム内でトランザクション ID 100 と 200 の 2 つのトランザクションが実行されています。
  • 選択ステートメントを実行すると、ReadView が生成されます。mids=[100,200]、min_trx_id=100、max_trx_id=201、creator_trx_id=0 (選択トランザクションは変更操作を実行せず、トランザクション ID はデフォルトで 0 になります)
  • 最新バージョンの名前列はXishiです。このバージョンのtrx_id値は100です。midsリストでは、可視性の要件を満たしていません。roll_pointerに従って次のバージョンにジャンプします。
  • 名前の次のバージョンは Lie Wang Zhaojun です。このバージョンの trx_id 値は 100 で、mids リストにもあります。したがって、要件を満たしていません。次のバージョンにジャンプし続けます。
  • 次のバージョンの名前列は Diao Chan です。このバージョンの trx_id 値は 10 で、min_trx_id より小さいです。そのため、最後に返される名前値は Diao Chan です。

ここに画像の説明を挿入

もう一度、時点8でのselectの実行プロセスを分析してみましょう。

  • システム内でトランザクションID 200のトランザクションが実行されています(トランザクションID 100のトランザクションはコミットされています)
  • SELECT文を実行すると、ReadViewが生成されます。mids=[200]、min_trx_id=200、max_trx_id=201、creator_trx_id=0
  • 最新バージョンの名前列はYang Yuhuanです。このバージョンのtrx_id値は200です。midsリストでは、可視性の要件を満たしていません。roll_pointerに従って次のバージョンにジャンプします。
  • 次のバージョンの name 列は Xishi です。このバージョンの trx_id 値は 100 で、min_trx_id より小さいです。そのため、最後に返される name 値は Xishi です。

トランザクション ID 200 のトランザクションがコミットされると、クエリによって取得される名前列は Yang Yuhuan になります。

繰り返し読み取り

Repeatable Read は、データを初めて読み取るときに ReadView を生成します。

ここに画像の説明を挿入

繰り返し読み取りでは、データを初めて読み取るときにのみ ReadView が生成されるため、毎回同じバージョンが読み取られます。つまり、名前の値は常に Diao Chan になります。具体的なプロセスは上記で 2 回説明されているため、ここでは繰り返しません。自分で分析していただければと思います。

参考ブログ

[1] https://souche.yuque.com/bggh1p/8961260/gyzlaf
[2] https://zhuanlan.zhihu.com/p/35477890

これで、MySQL トランザクションの分離を実現する方法についての記事は終了です。MySQL トランザクションの分離の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL MVCCメカニズム原理の詳細な説明
  • MySQL トランザクション分離レベルと MVCC の詳細な説明
  • MySQL トランザクション分離レベルとロックメカニズムの問題に関する深い理解
  • MySql8.0 のトランザクション分離レベルエラーの問題を解決する
  • MySQL マルチバージョン同時実行制御 MVCC の基本原理の分析
  • MySQL マルチバージョン同時実行制御 MVCC の実装
  • Mysql MVCC マルチバージョン同時実行制御の詳細
  • MYSQL トランザクション分離レベルと MVCC

同時シナリオ

最近、私はいくつかの分散トランザクション プロジェクトに取り組んでおり、トランザクションの分離についてより深く理解しています。分散トランザクションについては、後ほど記事を書く予定です。今日は、スタンドアロントランザクションの分離がどのように実現されるかを確認しましょう。

分離の本質は、SQL ステートメントがシリアルに実行される場合に同時実行を制御することです。すると、データベースの 4 大特性における分離の概念がなくなり、ダーティ リード、非反復リード、ファントム リードなどの問題も発生しなくなります。

データベースに対する同時操作の種類は、書き込み-書き込み、読み取り-読み取り、読み取り-書き込み、書き込み-読み取りの 4 つだけです。

書き込む

トランザクション A がレコードを更新する場合、トランザクション B は同時に同じレコードを更新できますか?

答えは間違いなく「いいえ」です。そうしないと、汚い書き方の問題が発生します。では、汚い書き方を避けるにはどうすればよいでしょうか?答えはロックです

読む読む

MySQL の読み取り操作はデフォルトではロックされないため、並列で読み取ることができます。

読み取り-書き込みと書き込み-読み取り

さまざまなシナリオでの同時操作に対するさまざまな許容レベルに基づいて、MySQL は分離の概念を考案しました。ビジネス シナリオに基づいて分離レベルを選択します。

√は起こることを意味し、×は起こらないことを意味します

分離レベルダーティリード繰り返し不可能な読み取りファントムリード
コミットされていない読み取り
コミットされた読み取り×
繰り返し読み取り× ×
シリアル化可能× × ×

ご覧のとおり、MySQL はロックと分離レベルを通じて同時実行を制御します。

MySQL のロック

行レベルロック

InnoDB ストレージ エンジンには、次の 2 種類の行レベル ロックがあります。

  • 共有ロック (略して S ロック)。トランザクションがレコードを読み取る必要がある場合、まずレコードの S ロックを取得する必要があります。
  • 排他ロック (略して X ロック)。トランザクションがレコードを変更する場合、まずレコードの X ロックを取得する必要があります。

トランザクション T1 がレコードの S ロックを取得した場合、トランザクション T2 もこのレコードにアクセスしようとします。トランザクション T2 がこのレコードの S ロックを再度取得する場合、成功します。この状況をロック互換性と呼びます。トランザクション T2 がこのレコードの X ロックを再度取得する場合、トランザクション T1 がコミットして S ロックを解放するまで、この操作はブロックされます。

トランザクション T1 がレコードに対して X ロックを取得した場合、トランザクション T2 は、トランザクション 1 がコミットされるまで、レコードに対して S ロックまたは X ロックのどちらを取得するかに関係なくブロックされます。この状況をロックの非互換性と呼びます。

複数のトランザクションが同時にレコードを読み取ることができます。つまり、共有ロックは相互に排他的ではありませんが、共有ロックは排他ロックをブロックします。排他ロックは相互に排他的である

SロックとXロックの互換性関係は以下のとおりです

互換性XロックSロック
Xロック相互排除相互排除
Sロック相互排除互換性がある

<<:  Docker 経由で Spring Boot アプリケーションを公開およびデプロイするプロセスの分析

>>:  モバイルデバイス上の 1px 境界線を解決する最善の方法 (推奨)

推薦する

Win7 で IIS7 Web および FTP サービスを完全にアンインストールする方法

昨日、パソコンにPHP開発環境をセットアップした後、Apacheサーバーを再起動するとエラーが続きま...

MySqlのインストールとアンインストールに関する詳細なチュートリアル

この記事では、MySqlのインストールとアンインストールのチュートリアルを参考までに紹介します。具体...

RedHat 6.5 に MySQL 5.7 をインストールするための詳細なチュートリアル

RedHat6.5インストールMySQL5.7チュートリアル共有、参考までに、具体的な内容は次のとお...

MySQL 5.7.23 winx64 のインストールと設定方法のグラフィックチュートリアル (win10 の場合)

この記事はMySQL 5.7.23 winx64のインストールチュートリアルを記録します。具体的な内...

CSS3のwebkit-box-reflectを巧みに使用して、さまざまな動的効果を実現します。

かなり前の記事で、 -webkit-box-reflectプロパティについて説明しました。リフレクシ...

HTML テキストエスケープのヒント

今日、CSDN で HTML テキスト エスケープのちょっとしたトリックを見ましたが、とても簡単です...

Zabbixのカスタム監視項目とトリガーについて

目次1. 監視ポート関係の説明操作する2. 監視サービス関係の説明操作する3. テンプレートのインポ...

MySQLオンラインDDLの使用に関する詳細な説明

目次文章LOCKパラメータアルゴリズムパラメータCOPY TABLE プロセスIN-PLACEプロセ...

MySQL の同時実行性の問題と解決策の分析

目次1. 背景2. テーブルロックによるクエリの遅延3. オンラインでテーブル構造を変更するとどのよ...

CSS で縦書きテキスト配置を実装する方法 (概要)

HTML でのテキストのデフォルトの配置は水平ですが、特殊な場合にはテキストを垂直に配置する必要が...

Linux システム ディレクトリ sys、tmp、usr、var の詳細な説明。

Linux 初心者から Linux マスターへの成長の道: Linux システム ディレクトリ s...

Linux での Python のアップグレードと pip のインストールの詳細な説明

Linuxバージョンのアップグレード: 1. まず、Linuxオペレーティングシステムに付属するPy...

MySQL 並列レプリケーションの簡単な分析

01 並列レプリケーションの概念MySQL のマスター スレーブ レプリケーション アーキテクチャで...

Javascript における非同期待機の詳細な理解

この記事では、async/await がすべての JavaScript 開発者にとって非同期プログラ...

Docker イメージの作成、アップロード、プル、およびデプロイ操作 (Alibaba Cloud を使用)

学習プロセス中にプッシュ イメージが常にタイムアウトすることがわかったため、Alibaba Clou...