ショック！ 1 つの SQL ステートメントに非常に多くのロックがあります...

推奨 (無料): SQL チュートリアル

#ギャップ ロックと行ロックを併用すると、安全かどうかを簡単に判断できます。ロック待ちの問題が発生したときにエラーが発生しました。

ギャップ ロックは反復読み取り分離レベルでのみ有効であるため、この記事ではデフォルトで反復読み取りを使用します。

ロック ルール

• 原則 1
  ロックの基本単位はネクスト キー ロックで、これは開いた間隔と閉じた間隔です。
• 原則 2
  検索プロセス中にアクセスされたオブジェクトのみがロックされます。
• 最適化 1
  インデックスに対する同等のクエリの場合、一意のインデックスをロックすると、ネクストキー ロックは行ロックに縮退します。
• 最適化 2
  インデックスに対する等価クエリの場合、右にトラバースして最後の値が等価条件を満たさない場合、ネクスト キー ロックはギャップ ロックに縮退します。
• バグ
  一意のインデックスに対する範囲クエリは、条件を満たさない最初の値にアクセスします。

データ準備

テーブル名: t
新しいデータ: (0,0,0),(5,5,5), ( 10,10,10),(15,15,15),(20,20,20),(25,25,25)
以下の例は基本的に図で示されているので、それと照らし合わせて読むことをお勧めします例によっては「3 つの考え方を破壊する」可能性もありますので、記事を読んだ後に自分で実践することをお勧めします。

#ケース

等値クエリ ギャップ ロック

• 等値クエリ ギャップ ロック

  

• テーブル t には id=7 がないため、原則 1 に従って、ロック単位はネクストキーロックであるため、セッション A のロック範囲は (5,10] になります。

• 同時に、最適化 2 による同等のクエリ (id=7) では、id=10 が満たされないため、ネクストキー ロックはギャップ ロックに縮退します。最後のロック範囲 (5,10)

したがって、セッション B が id=8 のレコードをこのギャップに挿入するとロックされますが、セッション C は次の行を変更できます。 id=10.

一意でないインデックスと同等のロック

• 一意でないインデックスにのみ追加されたロック

  
  

• セッション A は、インデックス c

  の行 c=5 に読み取りロックを追加したいと考えています。原則 1 によれば、ロック ユニットはネクスト キー ロックであるため、ネクスト キー ロックを次のキー ロックに追加します。 (0,5]
  c は
  通常のインデックス であるため、レコード c=5 のみがアクセスされます。 はすぐに停止できません 。 右にトラバースする必要があります そして c=10 が見つかった場合のみあきらめます。原則 2 に従って、アクセスはロックされる必要があるため、(5,10] にはネクストキー ロック を追加する必要があり、最適化 2: 等価性にも適合します。判定、右にトラバース、最後の値は c=5 の等価条件を満たさないため、劣化 ギャップロック (5,10)
  原則 2 に従って、アクセスされたオブジェクトのみがロックされます。カバリング インデックスを使用し、主キー インデックスにアクセスする必要がないため、主キー インデックスにロックが追加されないため、セッション B の更新ステートメントを実行できます。
  ただし、セッション C が挿入 (7,7 ,7)、セッション A のギャップ ロック (5,10) によってロックされます。
  

この例では、共有モードのロックはカバー インデックスのみをロックしますが、更新用の場合は異なります。更新用に実行すると、システムは次にデータを更新すると考え、主キーのインデックスに条件を満たす行を与えます。行ロックを追加します。

この例は、ロックがインデックスに追加されることを示しています。同時に、共有モードでロックを使用して行に読み取りロックを追加し、データが更新されないようにしたい場合のガイダンスも提供します。カバーするインデックスの最適化をバイパスし、インデックスに存在しないフィールドをクエリ フィールドに追加する必要があります。たとえば、セッション A のクエリ ステートメントを変更して、c=5 が共有モードでロックされる t から d を選択します。これが効果です。

3 主キー インデックス範囲ロック

範囲クエリ。

テーブル t の場合、次の 2 つのクエリ ステートメントは同じロック範囲を持っていますか?

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

mysql> select * from t where id>=10 and id こう思うかもしれませんが、id 定義はint 型の場合、これら 2 つのステートメントは同等ですよね?実際、それらは完全に同等ではありません。

論理的には、これら 2 つのクエリ ステートメントは間違いなく同等ですが、ロック ルールが異なります。ここで、セッション A で 2 番目のクエリ ステートメントを実行して、ロックの効果を確認してみましょう。

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

次に、上記のロック ルールを使用して、セッション A にどのようなロックが追加されるかを分析します。

実行を開始するとき、id=10 の最初の行を見つける必要があるため、next-key lock(5,10] にする必要があります。最適化 1 によると、主キー ID の等価条件は縮退します。行ロックに追加すると、id=10 の行に対する行ロックのみが追加されます。

範囲検索はその後も検索を続け、id=15 の行が見つかった時点で停止するため、ネクスト キー ロックが適用されます。 (10,15] を追加する必要があります。

したがって、この時点のセッション A のロック範囲は、主キー インデックス、行ロック ID=10、ネクスト キー ロック(10,15]) になります。 .

ここで注意が必要なのは、セッション A が id=10 の行を初めて見つけたときに判定されることです。同等のクエリとして、id=15 まで右にスキャンする場合、その範囲が使用されます。クエリと判定。

範囲クエリのロックをもう一度見てみましょう。ケース 3 と比較できます。

非一意のインデックス範囲ロック

##t 値に挿入(8,8 ,8);(ブロックされました)

#session_1	session_2	session_3
#begin; select * from t where c>=10 and c<更新の場合は 11;
	##更新 t セット d = d 1 where c=15;(blocked)

#session1 は

c=10
• を使用して初めてレコードを見つけます。インデックスc (5,10) next-key lockc は
非一意インデックス
• を追加しました。最適化ルールはありません。つまり、縮退しません。行ロック したがって、セッション 1 は最終的に
(5,10]
• および (10,15] c のネクストキー ロックとしてロックされます。
結果から判断すると、sesson2 は (8,8,8) の挿入ステートメントを挿入しようとしたときにブロックされました。

一意でないインデックスは c=15 までスキャンする必要があります。

一意のインデックス範囲ロックのバグ

最初の 4 つのケースでは、2 つの原則と 2 つの最適化が使用されます。ロック ルールのバグ ケースを見てください。

session_1#begin; select * from t 更新用の id>10 および id<=15;#update t set d=d 1原則 1 に従って、インデックス ID に

session_2	session_3
where id=20;(ブロック)
#t 値 (16,16,16) に挿入;(ブロック)		session1 は範囲クエリです

(10,15] next-key lock

のみを追加します。キーのみの場合、ループは

id=15

行までと判断されます。トラバースを停止します。
• しかし実装では、InnoDB は条件を満たさない最初の行、つまり id=20 までスキャンを続けます。これは範囲スキャンであるため、 ID ##(15,20] next-key lock
の # もロックされます。

したがって、セッション 2 が id=20 を更新したい場合、この行はブロックされます。 If session3 は id=16 を挿入しようとしていますが、これもブロックされます。 論理的に言えば、一意のインデックスが id=15 までスキャンして必要がないと判断するため、行 id=20 をロックする必要はありません。ただし、実装ではこれが行われているため、バグである可能性があります。

非一意インデックスでの「等しい値」の例

To 「ギャップ」の概念をよりわかりやすく説明します。

レコード 7 の挿入

新しく挿入された行 c=10、つまり、テーブルには c=10 が 2 つあります。 、現時点でのインデックス c のギャップのステータスは何ですか?

一意でないインデックスには主キーの値が含まれているため、「同一の」行が 2 つ存在することはできません。 2 つの c=10 は主キー値 ID が異なるため、2 つの c=10 レコードの間にギャップがあります。

次のケースを見てください。

6

delete ロック ロジックは

select ... for update

に似ており、初期ルールにも準拠します。 #session_2

session_3

begin;

delete * from t where c=10##insert into ##原則 1 に従って、(c=5,id=5 を追加します) ) to (c=10,id=10) next-key lock##session_1

values(13,13,13);(blocking)
update t set d=d 1 where c=15;		session1 を移動するときは、まず最初の c=10:
次に、session1 は行 (c=15, id=15) が見つかるまで右に検索し、ループは終了します。最適化2、同等のクエリ、右側に条件を満たさない行が見つかったため、(c=10, id=10)から(c=15,id=15)のギャップロックに縮退します(オープンインターバル、( c=5,id=5) と (c=15,id=15) の 2 つの行にはロックがありません)	7 制限ステートメント ロック

session_2

begin;

delete * from

where c=10制限 2

values(13,13,13);(blocking)# ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

セッション 1 の削除ステートメントにより、制限 2 が追加されます。実際には、テーブル t には c=10 のレコードが 2 つしかないことがわかります。したがって、制限 2 を追加するかどうかに関係なく、削除の効果は同じですが、ロックの効果は異なります。ケース 6 の結果とは異なり、セッション B の挿入ステートメントが成功したことがわかります。

これは、ケース 7 の delete ステートメントが制限 2 を明示的に追加しているためです。そのため、行 (c=10、id=30) を通過した後、条件を満たすステートメントがすでに 2 つあり、ループ終わりました。

したがって、インデックス c のロック範囲は、図に示すように、(c=5, id=5) から (c=10, id=30) までのフロントオープンおよびバッククローズの範囲になります。以下:

制限 2 によるロック効果

(c=10,id=30) の後のギャップがロック範囲内にないことがわかります。 c=12 を挿入する insert ステートメントは正常に実行できます。

この例の実践における重要な指針は、データを削除するときに制限を追加しようとすることです。これにより、削除されるデータの数が制御され、操作が安全になるだけでなく、ロックの範囲も縮小されます。

デッドロックの例

前の例では、分析するときに、利便性が高いため、ネクスト キー ロックのロジックに従って分析しました。最後に、別のケースを見て説明します。ネクストキー ロックは、実際にはギャップ ロックと行ロックの合計の結果です。

あなたは疑問に思っているでしょうが、この概念は最初に言及されていませんでしたか?心配しないで、まず次の例を見てみましょう。

ケース 8

セッション A の操作シーケンス トランザクションを開始した後、クエリ ステートメントを実行し、共有モードでロックを追加し、共有モードでロックをインデックスに追加しますネクストキー ロック(5,10]とギャップ ロック(10,15)が c に追加されます。

セッション B の更新ステートメントも追加されますnext-key lock(5,10] をインデックス c に追加する必要があります。待機中のロックを入力してください;

その後、セッション A は、ギャップ ロックによってロックされている行 (8,8,8) を再度挿入しようとしています。

セッション B のネクスト キー ロックのアプリケーションはまだ成功していないのでは?

In実際、セッション B の「add next-key lock(5,10]」操作は、実際には 2 つのステップに分かれています。最初に (5,10) のギャップ ロックを追加すると、ロックが成功します。 c=10 の行ロックを追加すると、ロックされます。

つまり、ロック ルールを分析するときは、ネクスト キー ロックを使用して分析できます。ただし、特定の実行時にそれを知っておく必要があります。 , ギャップ ロックと行ロックの 2 つの段階に分割する必要があります。

最後のケースでは、ネクストキー ロックが実際にはギャップ ロックと行ロックによって実装されていることを明確に知ることができます。 read-committed 分離レベル (read-committed) と考えると分かりやすいと思いますが、途中でギャップ ロック部分が削除され、行ロック部分だけが残ります。読み取りコミット分離レベル、つまりステートメントの実行中に行ロックが追加された場合、ステートメントの実行が完了した後、「条件を満たさない行」の行ロックを待機せずに直接解放する必要があります。 読み取りコミット分離レベルでは、ロック範囲が小さく、ロック時間が短いため、多くのビジネスでは読み取りコミットもデフォルトで使用されます。リピータブルリードを使用すると、ファントムリードの問題が解決され、トランザクションを並列処理するシステムの能力が最大化されます。

ギャップロックと行ロック、ロック待機が発生するかどうかの判断を誤りやすいです。

ギャップ ロックは反復読み取り分離レベルでのみ有効であるため、この記事のデフォルトは反復読み取りです。

詳細については、
SQL

無料列~~を参照してください。

	# #insert into t

以上がショック！ 1 つの SQL ステートメントに非常に多くのロックがあります...の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。