MySQLのネクストキーロックのロック範囲についての簡単な説明

序文

ある日、突然 MySQL の次のキーロックについて尋ねられ、私の即座の反応は次のようなものでした。

これは一体何ですか？ ? ?

このスクリーンショットでは何も見えません。

よく見ると見覚えがあります。これは「MySQL 45 Lectures」の内容ではないでしょうか。

ネクストキーロックとは

ネクストキーロックは、インデックスレコードに対するレコードロックと、インデックスレコードの前のギャップに対するギャップロックの組み合わせです。

公式サイトの説明は、大まかに言うと、次のキーロックは、インデックスレコードに対するレコードロックと、インデックスレコードの前のギャップに対するギャップロックの組み合わせである、ということです。

まず自分自身にたくさんの小さな疑問符を付けてみませんか? ? ?

主キー、一意のインデックス、共通インデックス、共通フィールドをロックする場合、どのインデックスがロックされますか?
さまざまなクエリ条件によってロックされるデータの範囲はどれですか?
共有や更新などの値クエリと範囲クエリのロックスコープは何ですか?
照会された等しい値が存在しない場合のロックスコープは何ですか?
クエリ条件が主キー、一意のインデックス、共通インデックスの場合の違いは何ですか?

何も分からないので、最初から練習しなくてはいけません！

まず、Ding Qi 氏の「MySQL 45 Lectures」の結論を見てみましょう。

この結論を読めば、ほとんどの疑問は解決するはずですが、注意が必要な非常に重要な一文が1つあります。MySQL MySQL 后面的版本可能會改變加鎖策略，所以這個規則只限于截止到現在的最新版本，即5.x 系列<=5.7.24，8.0 系列<=8.0.13

したがって、上記のルールは現在のバージョンには適用できない可能性があります。以下では、 MySQL 8.0.25を例に、次のキーのロック範囲をさまざまな角度から検証します。

環境の準備

MySQL バージョン: 8.0.25

分離レベル: 繰り返し読み取り (RR)

ストレージエンジン: InnoDB

mysql> @@global.transaction_isolation、@@transaction_isolation\G を選択します。
mysql> テーブル t\G の作成を表示します

Docker を使用して MySQL をインストールする方法については、別の記事「Docker を使用して MySQL をインストールして接続する」を参照してください。

主キーインデックス

まず、主キーインデックスの次キーロックの範囲を確認しましょう。

このときのデータベースデータは図の通りです。主キーインデックスの場合、データギャップは以下のようになります。

主キーの等価値クエリ - データが存在する

mysql> begin; select * from t where id = 10 for update;

この SQL ステートメントはid = 10をロックします。まず、どのようなロックが追加されるかを考えてみましょう。ロックされているデータは何ですか?

ロック情報はdata_locksを通じて表示できます。SQL は次のとおりです。

# mysql> performance_schema.data_locks から * を選択します。
mysql> performance_schema.data_locks\G から * を選択します

具体的なフィールドの意味については、公式ドキュメントを参照してください。

結果には主に、エンジン、ライブラリ、テーブルなどの情報が含まれます。次のフィールドに注目する必要があります。

INDEX_NAME: ロックされたインデックスの名前
LOCK_TYPE: ロックのタイプ。InnoDB の場合、許可される値は RECORD 行レベルロックと TABLE テーブルレベルロックです。
LOCK_MODE: ロックタイプ: S、X、IS、IX、ギャップロック
LOCK_DATA: ロック関連データ。InnoDB の場合、LOCK_TYPE が RECORD (行ロック) の場合、値が表示されます。ロックが主キーインデックス上にある場合、値はロックされたレコードの主キー値になります。ロックがセカンダリインデックス上にある場合、プライマリキーの値に付加されたセカンダリインデックスの値が表示されます。

結果は明らかです。ここでは、IX ロックがテーブルに追加され、主キーインデックス ID = 10 のレコードにX,REC_NOT_GAPロックが追加され、レコードのみがロックされていることを示しています。

同様に、 for share 、テーブルに IS ロックが追加され、主キーインデックス id = 10 のレコードに S ロックが追加されます。

結論として、次のことが言えます。

主キー値をロックし、その値が存在する場合、テーブルにインテンションロックが追加され、主キーインデックスに行ロックが追加されます。

主キーの等価値クエリ - データが存在しません

mysql> id = 11 の場合、更新時に t から * を選択します。

データが存在しない場合は、どのようなロックが追加されますか?ロックの範囲は何ですか?

検証する前に、データのギャップを分析します。

id = 11確かに存在しません。しかし、 for updateを追加した後は、次のキーロックが必要となり、 id = 11の区間は (10,15) の~~最初の開区間と 2 番目の閉区~~間になります。
等值查詢なので、 id = 15レコードをロックする必要はなく、次のキーロックはギャップロックに退化します。
最終区間は（10,15）の前方開放区間と後方開放区間である。

data_locks を使用してロック情報を分析します。

ロック情報X,GAPギャップロックが追加されたことを示しており、LOCK_DATA = 15 は、ロックが主キーインデックス ID = 15 の前のギャップ用であることを示しています。

このとき、別のセッションで SQL を実行すると、答えは明らかです。id = 12 は挿入できませんが、id = 15 は更新できます。

データが存在しない場合は、主キーの等価値クエリによって、主キークエリ条件が配置されているギャップがロックされることがわかります。

主キー範囲クエリ（キーポイント）

mysql> begin; select * from t where id >= 10 and id < 11 for update;

「MySQL 45 Lectures」の分析によると、次のような結果が得られました。

id >= 10 10 が位置する区間 (10,+∞) を検索します。
>= は等値判定なので、値 10 を含める必要があり、閉区間 [10,+∞] になります。
id < 11後続の範囲を制限するため、11 に基づいて、次の間隔は 15 の~~最初の開区間と 2 番目の閉区~~間として決定されます。
これらを合わせると[10,15]となる。（完全に正しいわけではない）

data_locks の初見

テーブルロックに加えて、id = 10 の行ロック ( X,REC_NOT_GAP ) と、主キーインデックス id = 15 の前のギャップロック ( X,GAP ) もあることがわかります。

したがって、実際には id = 15 を更新できます。つまり、前開后閉區間問題があるということです。個人的には、 id < 11の条件判定が 15 行目の不要なロックにつながるのではないかと思います。

結果の検証も正しく、id = 12 は挿入がブロックされ、id = 15 は正常に更新されます。

範囲の右側に等しい値のクエリが含まれている場合はどうなりますか?

mysql> begin; select * from t where id > 10 and id <= 15 for update;

この SQL を分析してみましょう:

id > 10は、10 が位置する区間 (10, +∞) に位置します。id id <= 15 (-∞, 15] に位置します。これらを組み合わせると、(10, 15] になります。

また、data_locksもご覧ください

主キーインデックス id = 15 に対して X ロックが 1 つだけ追加されていることがわかります。

id = 15 が更新できることを確認しますか? id = 16 を挿入できるかどうかを再度確認しますか?

問題はないことがわかりました！

もちろん、ここにいる友人の中には、「MySQL 45 Lectures」に次のキーをロックするバグがあると言う人もいるでしょう。

このバグは修正されたようです。修正されたバージョンはMySQL 8.0.18です。しかし、完全には直っていません！！！

参考リンクアドレス:

8.0 リリースノート

検索キーワード: バグ #29508068)

それぞれ 8.0.17 で再現できます:

8.0.17 では、 id <= 15 id = 20 のデータもロックしますが、バージョン 8.0.25 ではロックしません。したがって、このバグは修正されました。

前開后閉前開后開開いているのかという疑問について考えてみましょう。厳密に言えば、8.0.17 と 8.0.18 を比較してみましょう。

現在、 Bug #29508068がバージョン 8.0.18 で修正されたときに、この前開后閉前開后開に最適化された可能性が高いと推定しています。

data_locks データを比較します。

赤い下線部分に注意してください。バージョン 8.0.17 では、 id < 17の場合、 LOCK_MODE はXですが、バージョン 8.0.25 ではX,GAPです。

要約する

この記事では、主に主キーをロックする際の次キーのロック範囲を実際の操作を通じて検証し、資料を参照したりバージョンを比較したりしてさまざまな結論を導き出します。

結論1:

ロックする場合、まず意図ロック IX または IS がテーブルに追加されます。
複数の範囲がある場合は、複数のロックが個別に追加され、各範囲にロックが設定されます。(これは、id < 20 の場合に実行できます)
主キーの等値クエリの場合、データが存在すると、行ロックX,REC_NOT_GAP主キーインデックスの値に追加されます。
主キーの等値クエリの場合、データが存在しない場合は、クエリ条件の主キー値が配置されているギャップにギャップロックX,GAPが追加されます。
主キーの等価値クエリと範囲クエリはより複雑です。
- バージョン 8.0.17 は前開き、後ろ閉じでしたが、バージョン 8.0.18 以降は最適化され、主キーが不等と判断され、後ろ閉じた区間はロックされなくなります。
- 重要な<=クエリを実行すると、8.0.17 では次の次のキーの最初のオープンインターバルと最後のクローズインターバルがロックされますが、8.0.18 以降のバージョンではこのバグが修正されています。

最適化後、遅く開きます。これは、最適化後、主キーの間隔が直接遅く開くためなのか、バグなのかはわかりません。特定の友達が試すことができます。

結論2

select * from performance_schema.data_locks;と操作方法を使用すると、LOCK_MODE と LOCK_DATE の関係を確認できます。

ロックモード	ロックデータ	ロック範囲
X、記録ギャップなし	15	15 そのデータの行ロック
X、ギャップ	15	15 そのデータの前のギャップ（15は含まない）
バツ	15	15 そのデータのギャップには15が含まれています

LOCK_MODE = Xはオープンファーストクローズ間隔です。X X,GAPオープンファーストクローズ間隔 (ギャップロック) です。X X,REC_NOT_GAPは行ロックです。

主キーの次キーロック範囲は大体把握できました。なお、使用バージョンは 8.0.25 です。

疑い

ユニークインデックスの次のキーロック範囲は何ですか?
インデックスがカバーされている場合のロック範囲とロックされたインデックスは何ですか?
このバグが完全に修正されていないと言った理由は、このバグが主キー以外の一意のインデックスで再現されたためです。

この記事の長さは限られているので、まずは自分で考えて、自分で試してみるといいでしょう。練習すれば完璧になります。具体的な答えについては、次の記事で検証し、結論をまとめる必要があるでしょう。

これで、MySQL の next-key lock のロック範囲に関するこの記事は終了です。MySQL の next-key lock のロック範囲の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: