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

ショック！ 1 つの 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 (5,10) next-key lockc は
を追加しました。最適化ルールはありません。つまり、縮退しません。行ロックしたがって、セッション 1 は最終的に
および (10,15] c のネクストキーロックとしてロックされます。

一意でないインデックスは 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 レコードの間にギャップがあります。

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

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

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

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