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 をよろしくお願いいたします。