MySQLテクノロジーにおけるInnoDBロックの詳細な説明

序文

7月に転職してから、MySQLについて勉強したり、ビデオ講座を聞いたりしていましたが、講師がどこで知識を身につけたのかずっと気になっていました。そこで私は本の中で答えを見つけようと考えました。結局、

ビデオを見ることも解決策ではありません。独自の知識を形成するのに役立たないからです。そこで、知識を得るために本を読もうと考え、「Mysql in Simple Terms」、「High Performance Mysql」、「Mysql Technology Insider」など、MySQL に関する本をいくつか読みました。

先生が教えてくれた内容はまさにその本に載っていたので、本を読むことが知識を得るための正しい方法だと確信しました。この映画は主に MySQL のロック メカニズムの研究を記録します。

1. ロックとは何ですか?

ロックは、複数のプロセスまたはスレッドを調整してリソースに同時にアクセスするためのコンピューター メカニズムです。データベースでは、従来のコンピューティング リソース (CPU、RAM、I/O など) の競合に加えて、データも多くのユーザーによって共有されるリソースです。

同時データ アクセスの一貫性と有効性をどのように確保するかは、すべてのデータベースが解決しなければならない問題です。ロックの競合も、データベースの同時アクセス パフォーマンスに影響を与える重要な要素です。

他のデータベースと比較すると、MySQL のロック メカニズムは比較的単純です。最も注目すべき特徴は、異なるストレージ エンジンが異なるロック メカニズムをサポートしていることです。たとえば、MyISAM および MEMORY ストレージ エンジンはテーブル レベルのロックを使用します。

BDB ストレージ エンジンはページ レベルのロックを使用しますが、テーブル レベルのロックもサポートします。InnoDB ストレージ エンジンは行レベルのロックとテーブル レベルのロックの両方をサポートしますが、デフォルトでは行レベルのロックを使用します。

これら 3 種類の MySQL ロックの特徴は、大まかに次のようにまとめることができます。

• テーブル レベルのロック: オーバーヘッドが低く、ロックが高速で、デッドロックがなく、ロックの粒度が大きく、ロック競合の可能性が最も高く、同時実行性が最も低い。
• 行レベル ロック: オーバーヘッドが高く、ロックが遅く、デッドロックが発生する可能性があります。ロックの粒度は最小で、ロック競合の可能性は最も低く、同時実行性は最も高くなります。
• ページ ロック: オーバーヘッドとロック時間はテーブル ロックと行ロックの中間です。デッドロックが発生する可能性があります。ロックの粒度はテーブル ロックと行ロックの中間で、同時実行性は平均的です。

3つのロック使用角度:

• テーブル レベルのロックは、Web アプリケーションなど、クエリ指向でインデックス条件に基づいてのみデータを更新するアプリケーションに適しています。
• 行レベルのロックは、一部のオンライン トランザクション処理 (OLTP) システムなど、インデックス条件と同時クエリに基づいて少量の異なるデータを大量に同時に更新するアプリケーションに適しています。
• BDB のページ ロックは InnoDB に置き換えられたため、ここでは説明しません。

2. InnoDBストレージエンジンのロック

2.1 ロックの種類

InnoDB ストレージ エンジンは、次の 2 つの標準の行レベル ロックを実装します。

• 共有ロック (S ロック) を使用すると、トランザクションはデータの行を読み取ることができます。
• 排他ロック (X ロック) を使用すると、トランザクションはデータ行を削除または更新できます。

トランザクションT1がすでに行rの共有ロックを取得している場合、別のトランザクションT2は行rの共有ロックをすぐに取得できます。これは、読み取りによって行rのデータが変更されないためです。この状況は、

ロック対応（ロック対応）です。ただし、別のトランザクション T3 が行 r の排他ロックを取得したい場合は、トランザクション T1 と T2 が行 r の共有ロックを解放するまで待機する必要があります。この状況はロックの非互換性と呼ばれます。

さらに、InnoDB ストレージ エンジンはマルチ粒度ロックをサポートしており、トランザクションは行レベルのロックとテーブル ロックを同時に保持できます。異なる粒度のロック操作をサポートするために、InnoDB

ストレージ エンジンは、インテンション ロックと呼ばれる追加のロック メソッドをサポートしています。インテンション ロックは、ロックされたオブジェクトを複数のレベルに分割します。インテンション ロックとは、トランザクションがより細かい粒度でロックすることを意味します。 InnoDB ストレージ

ストレージ エンジンはシンプルな設計のインテンション ロックをサポートしており、そのインテンション ロックはテーブル レベルのロックです。これは主に、トランザクションの次の行に対して取得されるロックの種類を明らかにするために設計されています。次の 2 種類の意図ロックをサポートします。

• 意図的な共有ロック（ISロック）、トランザクションはテーブル内の特定の行に対して共有ロックを取得したい
• 意図的な排他ロック（IXロック）、トランザクションはテーブル内の特定の行に対して排他ロックを取得したい

2.2 一貫性のある非ロック読み取り

一貫性のある非ロック読み取りは、InnoDB ストレージ エンジンがマルチバージョンを使用して現在の実行時にデータベース内の行を読み取る方法です。

データ。読み取り行が削除または更新操作を実行している場合、読み取り操作は行ロックが解除されるまで待機しません。代わりに、InnoDB ストレージ エンジンは行のスナップショットを読み取ります。

バージョン。下記の通りです。

上の図は、InnoDB ストレージ エンジンの一貫した非ロック読み取りを直感的に示しています。アクセスされた行の X ロックが解除されるのを待つ必要がないため、非ロック読み取りと呼ばれます。スナップショットデータは行の以前のバージョンを参照します

データは UNDO セグメントに保存されます。 Undo はトランザクション内のデータをロールバックするために使用されるため、スナップショット データ自体に追加のオーバーヘッドは発生しません。また、スナップショットデータをロックする必要もありません。

履歴データを変更する必要があるトランザクションがないためです。

上の図から、スナップショット データは実際には現在の行データの前の履歴バージョンであることがわかります。各行レコードには複数のバージョンがある場合があります。このテクノロジは一般に行マルチバージョン テクノロジと呼ばれます。結果として生じる同時実行制御

これは、マルチバージョン同時実行制御 (MVCC) と呼ばれます。

トランザクション分離レベル READ COMMITTED および REPEATABLE READ では、InnoDB ストレージ エンジンはロックなしの一貫性のある読み取りを使用します。ただし、スナップショット データの定義は異なります。 READで

COMMITTED トランザクション分離レベルでは、スナップショット データの場合、不整合読み取りでは常にロックされた行の最新のスナップショット データが読み取られます。 REPEATABLE READトランザクション分離レベルでは、スナップショットの場合

データの場合、不整合な読み取りでは常にトランザクションの開始時の行データ バージョンが読み取られます。次の表に例を示します。

id = 1 の元のレコードが存在すると仮定すると、上記の表の時系列順に該当するセッションを実行し、2 つの違いを比較して検証できます。

2.3 一貫性のあるロック読み取り

デフォルト構成では、トランザクション分離レベルが REPEATABLE READ モードの場合、InnoDB ストレージ エンジンの選択操作は一貫性のある非ロック読み取りを使用します。ただし、場合によっては、ユーザーは

データ ロジックの一貫性を確保するために、データベースの読み取り操作はロックされます。これには、読み取り専用の選択操作の場合でも、データベースがロック ステートメントをサポートする必要があります。 InnoDBストレージエンジンは2つの

一貫性のあるロック読み取り操作:

• 更新するには···を選択してください
• 選択···共有モードでロック

select ··· for update は、読み取られた行レコードに X ロックを追加します。他のトランザクションは、ロックされた行にロックを追加できません。共有モードでの選択···ロックは、読み取り行レコードにSロックを追加し、他のトランザクションは

ロックされた行に S ロックを追加できますが、X ロックを追加するとブロックされます。

一貫性のある非ロック読み取りの場合、行に対して select... for update が実行されていても、読み取り行を読み取ることができます。さらに、更新のために···を選択するか、共有モードで···ロックを選択する必要があります

トランザクションでは、トランザクションがコミットされると、ロックが解除されます。したがって、上記の 2 つの選択ロック ステートメントを使用する場合は、必ず begin、start transaction を追加するか、autocommit=0 を設定してください。

3 ロックアルゴリズム

3.1 3行ロックアルゴリズム

InnoDB ストレージ エンジンには、次の 3 つの行ロック アルゴリズムがあります。

• レコードロック: 単一行レコードをロックする
• ギャップロック: ギャップロックは範囲をロックしますが、レコード自体は含まれません
• 次のキーロック: ギャップロック + レコードロック、範囲をロックし、レコード自体をロックします

レコードロックは常に主キーインデックスレコードをロックします。InnoDBストレージエンジンテーブルが作成時に主キーまたは一意の空でないインデックスセットを持っていない場合、InnoDBストレージエンジンは暗黙的に

ロックするためのマスターキー。

ネクストキー ロックは、ギャップ ロックとレコード ロックを組み合わせたロック アルゴリズムです。ネクストキー ロック アルゴリズムでは、InnoDB は行クエリにこのロック アルゴリズムを使用します。インデックスが10、11の場合

、13、20 の場合、このインデックスの次のキー ロックの可能な範囲は次のようになります。

(-無限大、10]、(10、11]、(11、13]、(13、20]、(20、+無限大)

Next-Key Lock を使用したロック技術は、Next-Key Locking と呼ばれます。ファントムリーディング問題を解決するために設計されています。このロック手法では、ロックされるのは単一の値ではなく、範囲です。 しかし、

クエリされたインデックスに一意の属性が含まれている場合、InnoDB ストレージ エンジンは Next-Key Lock を最適化し、範囲ではなくインデックス自体のみをロックする Record Lock にダウングレードします。以下に例を示します。

mysql> テーブル t を作成します (int 主キー)。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
​
mysql> t に挿入して 1 を選択します。
クエリは正常、1 行が影響を受けました (0.00 秒)
記録: 1 重複: 0 警告: 0
​
mysql> t に挿入 2 を選択します。
クエリは正常、1 行が影響を受けました (0.00 秒)
記録: 1 重複: 0 警告: 0
​
mysql> t に挿入 5 を選択します。
クエリは正常、1 行が影響を受けました (0.01 秒)
記録: 1 重複: 0 警告: 0

次に、以下の表に示す時系列順に操作を実行します。

表 t には 1、2、5 の 3 つの値があります。上記の例では、セッション A で、最初に a=5 が X ロックされます。 a は主キーであり一意であるため、範囲 (2,5) ではなく値 5 のみがロックされます。

値 4 を B に挿入するとブロックされず、すぐに挿入されて返されます。つまり、ロックは Next-Key Lock アルゴリズムから Record Lock にダウングレードされ、アプリケーションの同時実行性が向上します。

前述のように、クエリされた列が一意のインデックスである場合にのみ、Next-Key Lock は Record Lock にダウングレードされます。補助索引の場合は状況が全く異なります。同様に、まずテスト用のテスト テーブル z を作成します。

mysql> テーブル z (a int 、b int 、主キー (a)、キー (b)) を作成します。
mysql> z に挿入して 1,1 を選択します。
mysql> z に挿入して 3,1 を選択します。
mysql> z に挿入して 5,3 を選択します。
mysql> z に挿入して 7,6 を選択します。
mysql> z に挿入して 10,8 を選択します。

テーブル z の列 b はセカンダリ インデックスです。セッション A で次の SQL ステートメントが実行されると、

mysql> select * from z where b = 3 更新用;

明らかに、SQL ステートメントはインデックス列 b を介してクエリを実行するため、従来の Next-Key Locking テクノロジを使用してロックされます。また、インデックスが 2 つあるため、それらを個別にロックする必要があります。クラスター化インデックスの場合、列のみ

a は、インデックス 5 に Record Lock を加えた値に等しくなります。補助インデックスの場合、Next-Key Lock が追加され、ロック範囲は (1, 3) になります。InnoDB ストレージ エンジンは補助インデックスの次のキーもロックすることに注意することが重要です。

キー値とギャップ ロックを加算すると、補助インデックス範囲 (3, 6) のロックも存在することになります。したがって、次の SQL ステートメントが新しいセッション B で実行されると、ブロックされます。

mysql> select * from z where a = 5 lock in share mode;
mysql> z に挿入して 4,2 を選択します。
mysql> z に挿入して 6,5 を選択します。

最初の SQL ステートメントは、セッション A で実行された SQL ステートメントによってクラスター化インデックスの列 a=5 の値に X ロックが追加されているため実行できず、実行がブロックされます。 2番目のSQL文は主キー4を挿入しますが、これは問題ありません。

補助インデックス値 2 はロックされた範囲 (1,3) 内にあるため、実行もブロックされます。 3 番目の SQL ステートメントでは、挿入された主キー 6 はロックされておらず、5 は範囲 (1,3) 内にありません。しかし、挿入された値5は別のロックされた値にあります

範囲 (3,6) 内にあるため、これも待機する必要があります。次の SQL ステートメントはブロックされず、すぐに実行できます。

mysql> z に挿入して 8,6 を選択します。
mysql> z に挿入して 2,0 を選択します。
mysql> z に挿入して 6,7 を選択します。

上記の例からわかるように、ギャップ ロックの目的は、複数のトランザクションが同じ範囲にレコードを挿入してファントム リードの問題が発生するのを防ぐことです。上記の例で、セッションAのユーザーがロックした場合

b=3 のレコード。この時点でギャップ ロック (3,6) がない場合、ユーザーはインデックス b 列 3 のレコードを挿入できます。これにより、セッション A のユーザーが同じクエリを再度実行したときに異なるレコードが返され、ファントム リードが発生します。

ここでの主な焦点は、InnoDB ストレージ エンジンのテーブル ロック メカニズムです。少なくとも、MySQL の行ロック メカニズムは理解しています。読者の皆さんに質問がある場合は、メッセージを残してください。次回はMysqlのトランザクション機能とその内部実装の仕組みについて記録します。

MySQLの内部アーキテクチャ、InnoDBバッファプール、REDOログ、UNDOログなどの詳細な説明が含まれています。現時点では、知識の確認のみで、まだまとめていません。

要約する

MySQL 技術の裏話 - InnoDB ロックに関するこの記事はこれで終わりです。MySQL InnoDB ロックの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。