複合主キーと複数列インデックスに遭遇した場合の MySQL 行ロックの詳細な説明

背景

今日、他のプロジェクト チームと協力してシス​​テムのストレス テストを実施しているときに、プロセス中にデッドロックの問題が時々発生しました。コードを分析した結果、複合主キーの更新があったことが判明しました。複合主キー テーブルを更新すると、1 つのフィールドのみが更新されました。同時に、トランザクション内でテーブルに挿入操作が行われ、デッドロックの問題が時々発生しました。

たとえば、テーブル t_lock_test には 2 つの主キーがあり、どちらもprimary key(a,b)ですが、更新時にはupdate t_lock_test .. where a = ?を通じて更新され、その後トランザクションでinsert into t_lock_test values(...)が行われます。

InnoDB のロック アルゴリズムは Next-Key Locking です。デッドロックは、この点が原因である可能性が非常に高いです。ただし、複合プライマリ キーの下で Next-Key Locking がトリガーされるでしょうか。複数列の結合一意インデックスの下で Next-Key Locking がトリガーされるでしょうか。答えは本には載っていないため、実際にテストする必要があります。

InnoDB のロック

ロックは、データベース システムとファイル システムを区別する重要な機能です。ロック メカニズムは、共有リソースへの同時アクセスを管理するために使用されます [図]。 InnoDB ストレージ エンジンは行レベルでテーブル データをロックします。これは良いことです。ただし、InnoDB ストレージ エンジンは、データベース内の他の多くの場所でもロックを使用して、さまざまなリソースへの同時アクセスを可能にします。たとえば、バッファ プール内の LRU リストを操作し、LRU リスト内の要素を削除、追加、移動するには、一貫性を確保するためにロックが必要です。データベース システムは、ロックを使用して共有リソースへの同時アクセスをサポートし、データの整合性と一貫性を提供します。

ロックは基本的に InnoDB ストレージ エンジンで使用されるため、MyISAM をスキップして InnoDB について直接説明します。

ロックタイプ

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

• 共有ロック（Sロック）により、トランザクションはデータ行を読み取ることができる
• 排他ロック（x lOCK）により、トランザクションでデータの一部を削除または更新できます。

トランザクション T1 がすでに行 r の共有ロックを取得している場合、読み取りによって r のデータが変更されないため、別のトランザクション T2 は行 r の共有ロックをすぐに取得できます。この状況をロック互換と呼びます。ただし、別のトランザクション T3 が行 r の排他ロックを取得した場合、T1 と T2 が行 r の共有ロックを解放するまで待機することになります。この状況はロックの非互換性と呼ばれます。

排他ロックと共有ロックの互換性:

InnoDB はデータを更新するときに行ロックを生成し、行ロックを明示的に追加することもできます (「悲観的ロック」とも呼ばれます)

select for update

ロックアルゴリズム

InnoDB には次の 3 つの行ロック アルゴリズムがあります。

レコード ロック: 単一行レコードに対するロック。文字通り行ロックです。

レコード ロックは、インデックス レコードをロックします (InnoDB の主キー インデックスはデータであるため、ここではインデックスについて説明していることに注意してください)。InnoDB ストレージ エンジン テーブルの作成時にインデックスが設定されていない場合、InnoDB ストレージ エンジンは非表示の主キーを使用してロックします。

ギャップロック: ギャップロックは範囲をロックしますが、レコード自体は含まれません

次のキーロック: ギャップロック + レコードロック、範囲とレコード自体をロックします

ギャップロックとネクストキーロックのロック間隔分割原理は同じです。

たとえば、インデックスに 10/11/13 と 20 の 4 つの値がある場合、インデックスが分割される間隔は次のようになります。

(-∞,10]
（10,11）
（11,13）
（13,20）
(20,+∞]

Next-Key Lock を使用したロック技術は、Next-Key Locking と呼ばれます。その設計目的は、次のセクションで紹介するファントム問題を解決することです。このロック技術を使用すると、ロックされるのは単一の値ではなく範囲となり、述語ロックが改良されます。

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

セカンダリ インデックス (非一意のインデックス) でのロックの例を見てみましょう。

テーブルzを作成します(a INT、b INT、主キー(a)、キー(b))。

zに挿入 SELECT 1,1;
zに挿入 SELECT 3,1;
zに挿入 SELECT 5,3;
zに挿入 SELECT 7,6;
zに挿入 SELECT 10,8;

テーブル z の列 b はセカンダリ インデックスです。トランザクション A が実行されると、次のようになります。

SELECT * FROM z WHERE b=3 FOR UPDATE

列 b は補助インデックスなので、このとき Next-Key Locking アルゴリズムが使用され、ロック範囲は (1,3] です。InnoDB は補助インデックスの次の値にもギャップ ロックを追加することに注意してください。つまり、(3,6] の補助インデックス範囲にもロックがあります。したがって、新しいトランザクション B で次の SQL を実行すると、ブロックされます。

1. SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE; //S ロック 2. INSERT INTO z SELECT 4,2;
3. zに挿入して6,5を選択します。

トランザクション A で実行された SQL ステートメントによって、クラスター化インデックスの列 a=5 の値に X ロックがすでに追加されているため、最初の SQL ステートメントは実行できず、実行がブロックされます。

2 番目の SQL 文は、主キーに 4 を挿入します。問題はありません。ただし、挿入されたセカンダリ インデックス値 2 はロックされた範囲 (1,3] 内にあるため、実行もブロックされます。

3 番目の SQL では、挿入された主キー 6 はロックされておらず、5 は範囲 (1,3] 内にありません。ただし、挿入された b 列の値 5 は次のギャップ ロック範囲 (3,6) 内にあるため、これも待機する必要があります。

次の SQL ステートメントは、ネクストキー ロックおよびギャップ ロックの範囲内にないためブロックされず、すぐに実行できます。

zに挿入 8,6を選択します。
zに挿入 SELECT 2,0;
zに挿入 6,7を選択します。

上記の例から、ギャップ ロックの目的は、複数のトランザクションを整理して同じ範囲にデータを挿入することであり、これがファントム問題につながることがわかります。この例では、トランザクション A は b=3 でレコードをロックしています。この時点でギャップロック（3,6）がない場合、他のトランザクションがインデックスb列3のレコードを挿入することができ、トランザクションAのユーザーが同じクエリを再度実行したときに異なるレコードが返され、ファントムリードが発生します。

ユーザーは、次の 2 つの方法でギャップ ロックを明示的にオフにすることもできます (ただし、推奨されません)。

• トランザクション分離レベルをREAD COMMITEDに設定する
• パラメータinnodb_locks_unsafe_for_binlogを1に設定する

InnoDB では、挿入操作の場合、挿入する次のレコードがロックされているかどうかがチェックされます。ロックされている場合、挿入は許可されません。上記の例では、トランザクション A がテーブル z の b=3 のレコードをロックしています。つまり、範囲 (1,3) をロックしています。このとき、他のトランザクションで次の挿入が実行されると、これもブロックが発生します。

zに挿入 2,0を選択

補助インデックス列bに値2のレコードを挿入すると、次のレコード3がインデックスされていることが検出されます。列bの値を変更した後、次のように実行できます。

zに挿入 2,0を選択

幻影問題

ファントム リードとは、同じトランザクションで同じ SQL ステートメントを 2 回続けて実行すると、異なる結果になる可能性があることを意味します。2 番目の SQL ステートメントでは、以前は存在しなかった行が返される場合があります。

デフォルトのトランザクション分離レベル (REPEATABLE READ) では、InnoDB ストレージ エンジンは、ファントム読み取りの問題を回避するために Next-Key Locking メカニズムを使用します。

複合（ジョイント）主キーとロック

上記のロック メカニズムの紹介 (「MySQL Technology Insider InnoDB Storage Engine 2nd Edition」から抜粋) は補助インデックスとクラスター化インデックスのみを対象としていますが、複合主キーの下方ロックの現れは何でしょうか。本の中に答えがなかったので、実際にやってみました。

まず複合主キーを持つテーブルを作成します

テーブル `composite_primary_lock_test` を作成します (
 `id1` int(255) NULLではない、
 `id2` int(255) NULLではない、
 主キー (`id1`,`id2`)
) エンジン=InnoDB デフォルト文字セット=utf8 COLLATE=utf8_bin;

`composite_primary_lock_test`(`id1`, `id2`) に値 (10, 10) を挿入します。
`composite_primary_lock_test`(`id1`, `id2`) に値 (1, 8) を挿入します。
`composite_primary_lock_test`(`id1`, `id2`) に値 (3, 6) を挿入します。
`composite_primary_lock_test`(`id1`, `id2`) に値 (5, 6) を挿入します。
`composite_primary_lock_test`(`id1`, `id2`) に値 (3, 3) を挿入します。
`composite_primary_lock_test`(`id1`, `id2`) に値 (1, 1) を挿入します。
`composite_primary_lock_test`(`id1`, `id2`) に値 (5, 1) を挿入します。
`composite_primary_lock_test`(`id1`, `id2`) に値 (7, 1) を挿入します。

トランザクションAは最初にid2=6の列を照会し、行ロックを追加します。

select * from complex_primary_lock_test where id2 = 6 共有モードでロック

この時点でロックはレコードロックにダウングレードされますか?トランザクション B は、次のキー ロック範囲 (id1=1、id2=8) 内のデータの一部を更新します。これを証明してください。

`composite_primary_lock_test` SE を更新します。ここで、`id1` = 1 かつ `id2` = 8 です。

その結果、UPDATE がブロックされるので、ロック時に where ステートメントに両方の主キーを追加してみましょう。

id2 = 6、id1 = 5 の場合、composite_primary_lock_test から * を選択して共有モードでロックします。

UPDATEを実行する

`composite_primary_lock_test` SE を更新します。ここで、`id1` = 1 かつ `id2` = 8 です。

その結果、UPDATEはブロックされない

上記でロックされた id2=6 のデータには、複数のレコードがあります。次に、1 つの主キーのみに基づいて id2=8 の一意のデータをロックしてみます。行レベルのロックにダウングレードされますか?

select * from complex_primary_lock_test where id2 = 8 lock in share mode;

`composite_primary_lock_test` SE を更新します。ここで、`id1` = 12 かつ `id2` = 10 です。

結果もブロックされ、実験により次のことが証明されます。

複合主キーでは、ロック時にすべての主キーが含まれていない場合、InnoDB は Next-Key Locking アルゴリズムを使用します。すべての主キーが含まれている場合は、一意のインデックスとして扱われ、現在のレコードのみをロックするレコード ロックにダウングレードされます。

複数列インデックス（結合インデックス）とロック

上記は複合主キーの下のロック メカニズムのみを検証しています。複数列のインデックスの場合はどうでしょうか。複合インデックス メカニズムと同じでしょうか。複数列の一意のインデックスはどうですか?

新しいテストテーブルを作成し、データを初期化します

テーブル `multiple_idx_lock_test` を作成します (
 `id` int(255) NULLではない、
 `idx1` int(255) NULLではない、
 `idx2` int(255) デフォルト NULL,
 BTREE を使用した主キー (`id`,`idx1`)
) エンジン=InnoDB デフォルト文字セット=utf8 COLLATE=utf8_bin;

ALTER TABLE `multiple_idx_lock_test` 
BTREE を使用して一意のインデックス `idx_multi`(`idx1`, `idx2`) を追加します。

`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (1, 1, 1) を挿入します。
`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (5, 2, 2) を挿入します。
`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (7, 3, 3) を挿入します。
`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (4, 4, 4) を挿入します。
`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (2, 4, 5) を挿入します。
`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (3, 5, 5) を挿入します。
`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (8, 6, 5) を挿入します。
`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (6, 6, 6) を挿入します。

トランザクション A はクエリに S ロックを追加します。クエリ中は idx1 列のみが使用され、左端の原則に従います。

select * from multiple_idx_lock_test where idx1 = 6 lock in share mode;

次に、Next-Key Lock 範囲内にデータを挿入します。

`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (9, 6, 7) を挿入します。

結果はブロックされています。複数列インデックス内のすべてのフィールドをロックするには、もう一度試してください。

select * from multiple_idx_lock_test where idx1 = 6 and idx2 = 6 lock in share mode;

次のキー ロック範囲にデータを挿入します。

`multiple_idx_lock_test`(`id`, `idx1`, `idx2`) に値 (9, 6, 7) を挿入します。

その結果、ブロックは発生しない

このことから、複数列の一意のインデックスを使用する場合、ロックにはロックする行 (つまり、ロック時に使用されるインデックスのすべての列) を指定する必要があることがわかります。その場合にのみ、InnoDB はレコードを一意の値と見なし、ロックはレコード ロックにダウングレードされます。それ以外の場合は、Next-Key Lock アルゴリズムを使用して範囲内のデータをロックします。

要約する

MySQL でロックを使用する場合、特にデータを更新/削除する場合は注意してください。主キー更新を使用するようにしてください。複合主キー テーブルで更新する場合は、デッドロックや、ロック範囲が広いことによるその他の問題を回避するために、すべての主キーを更新してください。

さて、今回の記事は以上です。この記事の内容が皆さんの勉強や仕事に少しでも参考になれば幸いです。123WORDPRESS.COM をよろしくお願いいたします。

参照する

「Mysql テクノロジー インサイダー InnoDB ストレージ エンジン 第 2 版」 - Jiang Chengyao