mysqlギャップロックのロックルールは何ですか?

mysql ギャップ ロック ロックに関する 11 のルール

ギャップ ロックは反復読み取り分離レベルでのみ有効です:ネクストキー ロックは、実際にはギャップ ロックと行ロックによって実装されます。 read-committed分離レベル(read-committed)で説明すると分かりやすいですが、途中でギャップロック部分が削除され、ロウロック部分だけが残ります。読み取りコミット分離レベルではギャップ ロックが存在しないため、データとログ間の不一致を解決するには、バイナリ ログ形式を行に設定する必要があります。つまり、多くの企業の構成は、読み取りコミット分離レベルに binlog_format=row を加えたものになります。ビジネスでは繰り返し読み取りの保証は必要ないため、読み取りサブミット時の操作データのロック範囲が狭い (ギャップ ロックがない) ことを考慮すると、この選択は合理的です。

ネクストキーロックのロックルール

要約されたロックルールには、2 つの「原則」、2 つの「最適化」、および 1 つの「バグ」が含まれます。

原則 1: ロックの基本単位はネクストキーロックです。 next-key ロックは、開いた間隔と閉じた間隔です。
原則 2: 検索プロセス中にアクセスされたオブジェクトのみがロックされます。セカンダリ インデックスのロック、またはインデックスのない列のロックは、最終的には主キーまで遡り、ロックは主キーにも追加されます。
最適化 1: インデックスに対する同等のクエリの場合、一意のインデックスをロックすると、ネクストキー ロックは行ロックに縮退します。つまり、InnoDB が主キーまたは一意のインデックスをスキャンする場合、InnoDB は行ロックのみを使用してロックします。
最適化 2: インデックス (必ずしも一意のインデックスである必要はありません) に対する同等のクエリを、右トラバーサル中および最後の値が取得されたときに実行します。が等価条件を満たさない場合、next-keylock はギャップ ロックに縮退します。
バグ: 一意のインデックスに対する範囲クエリは、条件を満たさない最初の値にアクセスします。

ケース分析

テーブル テストを例にとります。テーブル作成ステートメントと初期化ステートメントは次のとおりです: id は主キーのインデックスです

CREATE TABLE `test` (
id` int(11) NOT NULL,
col1` int(11) DEFAULT NULL,
col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`col1`)
) ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

ケース 1: 一意インデックス等しい値クエリ ギャップ ロック

テーブル テストには id=7 のレコードがないため

原則 1 に従って、ロック ユニットは次のとおりです。キーロック、セッション A のロック範囲は (5,10];

同時に、最適化 2 によれば、これは同等のクエリ (id=7) となり、id=10 は条件を満たしません。クエリ条件を変更し、次キー ロックはギャップ ロックに縮退するため、最終的にロック範囲は (5,10)

ケース 2: 非一意インデックスと同等のクエリ ロック

ここでセッション A は、col1 をインデックスに追加する必要があります。col1 =5 この行に読み取りロックを追加します。

原則 1 によると、ロック ユニットはネクスト キー ロック、左です。オープンと右クローズ、5 がクローズされているため、(0,5] が追加されますネクストキーロック

c は通常のインデックスであるため、レコード c=5 へのアクセスのみをすぐに停止できないことに注意してください。 (col1=5 の他のレコードがある可能性があります),

は必須です 右にトラバースし、c=10 が見つかった場合のみあきらめます 2 番目の原則に従って、すべてのアクセスをロックする必要があるため、次-key lock を間隔 (5,10] に追加する必要があります。

ただし、これは最適化 2: 等価性の判断、右への移動、最後の値が条件を満たさないことと一致します。 Col1=5 の等価条件なので、ギャップロック (5,10) に縮退します。

原則 2 に従って、オブジェクトへのアクセスのみがロックされます。このクエリはカバーインデックスを使用しており、ロックする必要はありません。主キー インデックスにアクセスします。

したがって、主キー インデックスにはロックが追加されず、セッション B の更新ステートメントが正常に実行できる理由が説明されます。

ただし、セッション C がアクセスしたい場合は、レコード (7,7,7) を挿入すると、セッション A のギャップ ロック (5,10) によってロックされます。この例は、インデックスにロックが追加されることを示しています。

更新を実行すると、システムは次にデータを更新する必要があると判断し、主キー インデックスの条件を満たす行に行ロックを追加します。データが更新されないように行に読み取りロックを適用する場合、カバー インデックスは主キー インデックスにアクセスせず、主キー インデックスをロックしないため、カバー インデックスの最適化をバイパスする必要があります。

ケース 3 : 主キーのインデックス範囲 クエリ ロック

#実行を開始するとき、id=10 の最初の行を見つける必要があるため、ネクスト キー ロック(5, 10]。最適化 1 では、主キー id ​​の等価条件を行ロックに縮退し、行 id=10 の行ロックのみを追加します。

範囲クエリです。範囲内を検索したい場合は、id=15 の行が見つかるまで検索を続けて終了します。条件を満たさないため、

next-key lock( 10,15].
セッション A この時点で、ロックのスコープは主キー インデックス、行ロック ID=10 およびネクスト キー ロック (10,15] です。セッション A が ## で行を見つけたとき#id=10 で初めて同等クエリと判定され、id=15 まで右にスキャンする場合は範囲​​クエリ判定が使用されます。

ケース 4: 非一意のインデックス範囲クエリ ロック

Col1=10 を使用して初めてレコードを検索する場合、(5,10] が追加されます) to インデックス c この次のキー ロックの後、インデックス Col1 は非専用インデックスであるため、最適化ルールはありません。つまり、行ロックに変換されません。したがって、セッション A に追加される最終ロックは (5 ,10] のインデックス c. と
(10,15] これら 2 つの次のキーロック。
InnoDB は、それを知る前に、col1=15 までスキャンする必要があるため、スキャンを停止する前に、col1=15 までスキャンするのが合理的です。続行する必要はありません。
見つかりました。

ケース 5: 一意のインデックス範囲クエリ ロックのバグ

セッション A は範囲クエリです。原則 1 によれば、(10,15] のネクストキー ロックのみがインデックス ID に追加され、

が一意のキーであるため、ループは id= の行に到達したときに停止する必要があります。 15.
ただし、実装の観点から、InnoDB は条件を満たさない最初の動作、つまり id=20 までスキャンします。また、これは範囲スキャンであるため、(15,インデックス ID の 20] もロックされます。論理的に言えば、ここで id=20 行をロックする動作は、実際には
不要です。id=15 をスキャンした後は、確認する必要がないことが確認できるためです。

ケース 6: 一意でないインデックスに対する " " 同等の " " の例

ここでは、テーブル t に新しいレコードを挿入します。 ,30); つまり、テーブルには c=10

の行が 2 つありますが、主キー値 ID が異なるため (それぞれ 10 と 30)、c= の 2 つのレコードの間にギャップが生じます。 10.

今回は、delete ステートメントを使用して検証します。delete ステートメントのロック ロジックは、実際には select ... for update,

と似ていることに注意してください。これは記事で述べたことです。2 つの「原則」、2 つの「最適化」、および 1 つの「バグ」から始めましょう。

このとき、セッション A は、トラバース時に最初のcol1=10 レコードに最初にアクセスします。同様に、原則に従って1 、ここで追加されるのは、
(col1=5,id=5) から (col1=10,id=10) までのネクストキー ロックです。
c は通常のインデックスなので、検索を続けます。突き当たるまで右へ ループは(col1=15,id=15)の行で終了します。最適化 2 によると、これは
と同等のクエリですが、条件を満たさない行が右側にあるため、(col1=10,id=10) から (col1=15,) までの隙間に縮退します。 id=15)
ロックします。

インデックス c 上のこの削除ステートメントのロック範囲は、上図の青い領域で覆われた部分です。この青い領域の左右に

の点線があり、開いた区間、つまり (col1=5,id=5) と (col1=15,id=15) を示しています。これらの 2 行

ケース 7: 制限ステートメントがロックされている

セッション A の削除ステートメントは制限 2 でロックされています。テーブル t には実際には c=10 のレコードが 2 つしかないことがわかっているため、制限 2 を追加または削除しても効果は同じです。ただし、ロックの効果は異なります。

これは、ケース 7 の delete ステートメントが明らかに制限 2 を追加しているためです。そのため、行 (col1=10, id=30) まで移動した後、

は条件を満たします。すでに 2 つのステートメントがあり、ループは終了します。したがって、インデックス Col1 のロック範囲は、次の図に示すように、(col1=5,id=5)
から (col1=10,id=30) までのフロントオープンおよびバッククローズの範囲になります。

## この例の実践における重要な指針は、データを削除するときに制限を追加しようとすることです。

これにより、削除されるデータの数が制御され、操作が安全になるだけでなく、ロックの範囲も縮小されます。

ケース 8: デッドロックの例

セッション A は、トランザクションの開始後にクエリ ステートメントを実行し、共有モードでロックを追加し、 Index Col1 -keylock(5,10] および

gap lock(10,15) (右へのインデックス トラバーサルはギャップ ロックに縮退します); セッション B の更新ステートメントにも next-key lock( 5 ,10]、ロック待機に入ります。これは実際には 2 つのステップに分かれています。

最初に (5,10) のギャップ ロックを追加すると、ロックは成功します。次に、col1=10 の行ロックを追加します。 row has been added in sessionA The read

lock is locked. この時点で、デッドロック アプリケーションはブロックされます
次に、セッション A は、行 (8,8,8) を再度挿入しようとしますが、これは、セッション B のギャップ ロック。デッドロックのため、InnoDB は
セッション B をロールバックします

ケース 9: インデックスソートギャップロックによる順序 1

次のようなステートメント
次の図は、このテーブルのインデックス ID の概略図です。
begin;
select * from test where id>9 and id

# まず第一に、このクエリのセマンティクスステートメントは id desc 順に並べられているため、条件を満たすすべての行を取得するには、オプティマイザーはまず「
番目の ID この処理はインデックスツリーの検索処理で得られるもので、エンジン内では本当はid=12の値を見つけたいのですが、最終的に
は見つからず、ギャップ(10,15)が発生してしまいました。 ） 発見された。 (id=15 は条件を満たさないため、ネクストキー ロックはギャップ ロック (10,
15) に縮退します。)
次に、左にトラバースします。トラバース プロセス中に、同等のクエリは存在せず、 id=5 の行でスキャンされ、間隔が左側で開き、右側で閉じているため、ネクスト キー ロック (0,5] が追加されます。つまり、実行プロセス中に、レコードはツリー検索によって検索されます
、「等価クエリ」メソッドが使用されます。

ケース 10: インデックス ソート ギャップ ロックによる順序付け 2

これは、col1 desc 順であるため、最初に検索されるのは、インデックス Col1 の「右端」の Col1=20 行です。これは、非一意インデックスの同等のクエリです。

最初の結合はオープンで行われます。左側の間隔は、(15,20] 間隔を形成する Next-key ロックです。右側にトラバースすると、col1=25 は条件を満たさず、ギャップ ロックに縮退するため、ギャップ ロック (20,25) と next-キー ロック (15,20] が追加されます。

col1=10 まで移動するときに、左側のインデックスのスキャンを停止します。次のキー ロック (next-keylock) は、右開き左閉区間に適用されます(5,10)

これがまさに、セッション B の挿入ステートメントをブロックする理由です。スキャン プロセス中に、3 つの行に値が存在します。col1=20、col1=15、col1=10 . select *なので、プライマリキー

id ​​が追加されます. 3 つの行ロック. したがって、セッション A の select ステートメントのロックの範囲は、

Indexcol1 on (5, 25) となります。 ;
主キーインデックス id=15 と 20 に対する 2 つの行ロック

ケース 11: データを変更する更新の例 - 最初に挿入し、次に削除します

注: Col1>5 に従って見つかった最初のレコードは Col1=10 なので、(0,5] のネクストキー ロックが追加されます。

セッション A のロック範囲は (5) ,10]、(10,15]、(15,20]、(20,25] インデックス Col1 および (25、最高値)。
セッション B の最初の更新ステートメントの後、col1=5 を変更する必要があります。
このレコードを挿入 (col1=1, id=5 );
レコードを削除 (col1=5, id=5) という 2 つのステップとして理解できます。
この操作により、セッション A のロック範囲は図 7 のようになります。

OK、次のセッション B は update t set Col1 = 5 を実行する必要があります。 Col1 = 1 このステートメントは 2 つのステップに分割できます:

レコードの挿入 (col1=5, id=5);
レコードの削除 (col1=1, id=5)。最初のステップは、ギャップ ロックが設定されている (1,10) にデータを挿入しようとしたため、ブロックされました
。

以上がmysqlギャップロックのロックルールは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。