InnoDB ストレージ エンジンの 3 つの行ロック アルゴリズムの図解と例の分析

[[415025]]

この記事はWeChatの公開アカウント「Flying Veal」から転載したもので、著者はFeitian Vealです。記事を転載する場合は飛天牛肉公式アカウントまでご連絡ください。

先ほども述べたように、InnoDB の場合、ロックはいつでもかけられます (ロックのための SQL 文についてはここでは説明しません。忘れてしまった場合は前回の記事を参照してください) が、ロック解除はいつでもできるわけではありません。具体的には、InnoDB は 2 フェーズ ロック プロトコルを使用します。つまり、トランザクションの実行中はいつでもロック操作を実行できますが、ロックはトランザクションが COMMIT または ROLLBACK を実行したときにのみ解除され、すべてのロックは同時に解除されます。

さらに、行レベル ロックはストレージ エンジン レベルでのみ実装されます。InnoDB ストレージ エンジンの場合、3 種類の行レベル ロック、つまり 3 つの行レベル ロック アルゴリズムがあります。

• レコードロック: レコードロック
• ギャップロック: ギャップロック
• ネクストキーロック: ネクストキーロック

次に、これら 3 つの行ロック アルゴリズムについて詳しく説明します。

レコードロック

名前が示すように、レコード ロックはレコードの行をロックします。実際には、行のインデックス レコードがロックされます。テーブルの作成時にインデックスが設定されていない場合、InnoDB ストレージ エンジンはロックに「暗黙の主キー」を使用します。

いわゆる暗黙的な主キーとは、テーブルの作成時に主キーが指定されていない場合、InnoDB ストレージ エンジンは最初の空でない列を主キーとして使用することを意味します。そうでない場合は、6 バイトの主キーが自動的に生成されます。

レコード ロックはインデックスに基づいているため、SQL ステートメントの条件によってインデックスが失敗する (またはを使用するなど) 場合、または条件にインデックスまたは主キーがまったく含まれていない場合、行レベルのロックはテーブル ロックに退化します。

レコードロックの例

インデックス フィールドをクエリする例を見てみましょう。データベースは次のとおりで、id は主キー インデックスです。

作成する テーブル`test` (
  `id` int (11)ではない  NULL AUTO_INCREMENT、
  `username` varchar (255)デフォルト  NULL 、
主要な キー(`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6デフォルトCHARSET=utf8;

初期データは次のとおりです。

2 つの新しいトランザクションを作成し、最初にトランザクション T1 の最初の 2 行を実行します。つまり、コミットは実行しません。

コミットが実行されないため、トランザクション T1 はこの時点ではロックを解除せず、id = 1 の行をロックします。この時点で、トランザクション 2 が実行され、id = 2 の行に適用されます。

ご覧のとおり、異なるレコード行がロックされているため、2 つのレコード ロックは相互に排他的ではありません。次に、テーブル内のデータを見てみましょう。トランザクション 1 はコミットされていないため、ID = 2 のユーザー名のみを変更する必要があります。

予想通りよかったです。トランザクション 1 のコミットを再度実行すると、id = 1 のユーザー名が変更されます。

行ロックがテーブルロックに退化する例

インデックスを使用しない例を見てみましょう。

同様に、2 つの新しいトランザクションを作成し、最初にトランザクション T1 の最初の 2 行を実行します。つまり、コミットは実行しません。 select ... for update を使用して、username = "user_three" の行をロックしようとしますが、username は主キーでもインデックスでもないため、トランザクション T1 は実際にはテーブル全体をロックします。

コミットは実行されないため、トランザクション T1 はこの時点でロックを解除せず、テーブル全体をロックします。この時点で、トランザクション 2 を再度実行し、id = 5 のレコードにロックを適用しようとすると、トランザクション T2 がスタックし、最終的にタイムアウトしてトランザクションが閉じられることがわかります。

2 つの異なるレコードに同じインデックスがある場合、ロックの競合が発生しますか?

この質問に対する答えは非常に簡単です。上で強調したように、行ロックはレコードではなくインデックスをロックします (通常はどのレコードがロックされているかを言うので、理解しやすいだけです)。したがって、2 つのトランザクションが同じインデックスを持つ 2 つの異なるレコードを操作する場合、行ロックが別のトランザクションによって占有されているため、1 つのトランザクションは待機することになります。

ギャップロック

ここでは簡単に触れ、以下で詳しく説明します。一意のインデックスに基づくレコード ロックとは異なり、ギャップ ロックと次のキー ロックはどちらも一意でないインデックスに基づいています。

さらに、特定のインデックス レコードをロックするレコード ロックとは異なり、ギャップ ロックと次キー ロックは、インデックス レコードの範囲をロックします。

 IDが1から10のテストから*を選択 アップデート;

上記の SQL ステートメントでは、間隔 (1, 10) (左開きと右開き) 内のすべてのレコード行がギャップ ロックされ、ID が 2、3、4、5、6、7、8、9 のデータ行の挿入がブロックされますが、操作対象の 2 つのインデックス レコード 1 と 10 はロックされません。

注意！これは、範囲 (1, 10) 内のすべての ID をロックすることを意味します。つまり、特定の ID が現在テーブルに存在しない場合でも (たとえば、ID = 6)、ID = 6 の新しいレコードを挿入したい場合、残念ながらそれは不可能です。

ネクストキーロック

Next-Key Lock は、ギャップ ロックとレコード ロックを組み合わせたロック アルゴリズムです。主な目的は、ファントム リード問題を解決することです。

たとえば、インデックスには 10、11、13、20 という 4 つの値があります。ロック操作は、これら 4 つのインデックスに対してそれぞれ実行されます。すると、これらの 4 つの操作に対応する Next-Key Lock によってロックされる間隔は次のようになります。

• （-∞, 10]
• （10、11]
• （11、13）
• （13、20）
• （20, +∞]

注意深い学生なら、ギャップ ロックとの違いは、ネクスト キー ロックによってロックされる間隔は左側が開いており、右側が閉じていること、つまり、現在操作されているインデックス レコードが含まれていることであることに気付いたはずです。

InnoDB のデフォルトの分離レベル REPEATABLE-READ では、行ロックに使用されるデフォルトのアルゴリズムは Next-Key Lock です。ただし、操作対象のインデックスが一意のインデックスまたは主キーの場合、InnoDB はネクストキー ロックを最適化し、範囲ではなくインデックス自体のみをロックするレコード ロックにダウングレードします。

主キーも一意のインデックスであるため、レコード ロックは一意のインデックスに基づいており、次キー ロックは一意でないインデックスに基づいていると言えます。

操作対象のインデックスが非一意インデックスの場合、InnoDB は非一意インデックスをロックするのではなく、まず対応する一意インデックスをレコード ロックでロックし、次に非一意インデックスをネクスト キー ロックとギャップ ロックで処理することに注意してください。具体的な例を見てみましょう。

ネクストキーロックの例

上記のテスト テーブルに新しいフィールドを追加し、それを一意でないインデックスとして設定するとします。

作成する テーブル`test` (
  `id` int (11)ではない  NULL AUTO_INCREMENT、
  `username` varchar (255)デフォルト  NULL 、
  `class` int (11)ではない  NULL 、
主要な キー(`id`)、
キー`index_class` (`class`) USING BTREE コメント'非一意インデックス'  
 ) ENGINE=InnoDB AUTO_INCREMENT=6デフォルトCHARSET=utf8;

データを挿入します:

トランザクション 1 を開き、次のステートメントを実行します。

クラス = 3 のテストから*を選択します アップデート;

この場合、InnoDB は実際には 3 種類の行ロックを追加します (select * ... from update は行レベルの書き込みロック、つまり X ロックを追加します)。

1) 主キーインデックスID = 105にレコードロックを追加します。

2) 非一意インデックスクラス = 3 の場合、次キーロックが追加され、ロック範囲は (1, 3] になります。

3) さらに、InnoDB ストレージ エンジンは、非一意のインデックス クラスの次のキー値にもギャップ ロックを追加することに特に注意する必要があります (テーブル内のクラス = 3 の次のキー値は 6 です)。そのため、クラス インデックス範囲が (3, 6) のギャップ ロックも存在します。

2)と3)をまとめると、このSQL文では、InnoDBストレージエンジンによってロックされるクラスインデックスの範囲は(1, 6)です。

実際に理論を検証してみましょう。別のトランザクション 2 を開き、次のステートメントを実行します。

予想どおり、トランザクション 1 で実行された SQL ステートメントによって、主キー インデックスの列 a=105 のレコードに X ロックがすでに追加されているため、ここでこのレコードの X ロックを取得しようとするとブロックされます。

別のトランザクションを使用して、次の SQL ステートメントを実行します。

主キー 104 を挿入することには問題はありませんが、挿入されたクラス インデックス値 2 はロックされた範囲 (1、6) 内にあるため、実行もブロックされます。

上記の分析の後、次の SQL ステートメントが正常に実行できることがわかります。

注意

すべての一意のインデックス列を操作する場合にのみ、Next-Key Lock が Record Lock にダウングレードされることに注意してください。一意のインデックスが複数の列で構成され、操作が複数の一意のインデックス列のうちの 1 つに対してのみ行われる場合、InnoDB ストレージ エンジンはロックに Next-Key Lock を使用します。