MySQL のデッドロック問題を解決する方法 (詳細な例)

この記事では、mysql に関する関連知識を提供し、主に一般的なデッドロックのケースの分析と議論、およびデッドロックを可能な限り回避する方法を紹介します。

推奨学習: mysql チュートリアル

1. デッドロックとは

デッドロックは並行システム一般的な問題データベース MySQL の同時読み取りおよび書き込みリクエストのシナリオにも表示されます。 「デッドロック」は、2 つ以上のトランザクションが、すでに保持しているロックを解放するのをお互いに待っている場合、またはロック シーケンスが矛盾しているためにループ内でロック リソースを待っている場合に発生します。一般的なエラー メッセージは、ロックを取得しようとしたときにデッドロックが見つかりました... です。

たとえば、トランザクション A は X1 ロックを保持し、X2 ロックを適用し、トランザクション B は X2 ロックを保持し、X1 ロックを適用します。トランザクション A と B はロックを保持し、相手が保持しているロックを申請して待機するループに入り、デッドロックが発生します。

#上の図に示すように、右側の 4 台の車両のリソース要求によりループ現象、つまり無限ループが発生し、デッドロックが発生しました。

#デッドロックの定義から、MySQL におけるデッドロックの要因は次のとおりです。

#2 つ以上のトランザクション
各トランザクションすでにロックを保持しており、新しいロックを申請しています
ロック リソースは同時に同じトランザクションによってのみ保持できるか、互換性がありません
ロックの保持とロックの申請により、トランザクションはループ内で相互に待機します

4. ##2. InnoDB ロック タイプ
##デッドロックを分析するには、次のようにします。 InnoDB のロック タイプを理解する必要があります。

#MySQL InnoDB エンジンは、標準の

行レベル ロック: 共有ロック (S ロック) と排他的ロック (X ロック)

#異なるトランザクションは、レコードの同じ行に同時に S ロックを追加できます。

トランザクションがレコードの特定の行に X ロックを追加すると、他のトランザクションは S ロックまたは X ロックを追加できず、ロック待機が発生します。
2. トランザクション T1 が行 r の S ロックを保持している場合、別のトランザクション T2 が r のロックを要求すると、次の処理が実行されます。

T2 が S ロックを要求し、すぐに許可されました。その結果、T1 と T2 は両方とも行 r

T2 が X を要求した行の S ロックを保持します。

1. T1 が r の X ロックを保持している場合、r の X ロックと S ロックに対する T2 の要求はすぐに許可できません。T2 は T1 を待つ必要があります。ロックを解除するには互換性がありません。共有ロックと排他ロックの互換性は以下の通りです:
2.1. ギャップロック(隙間ロック)

ギャップロックは隙間をロックして挿入を防ぎます。インデックス列に 2、4、8 の 3 つの値があるとします。4 がロックされると、2 つのギャップ (2,4) と (4,8) も同時にロックされます。他のトランザクションは、これら 2 つのギャップの間にインデックス値を持つレコードを挿入できません。ただし、ギャップ ロックには例外があります。

インデックス列が一意のインデックスの場合、このレコードのみがロックされます (行ロックのみが追加されます)。ロックの隙間ではありません。

ジョイント インデックスの場合、それが一意のインデックスである場合、where 条件にジョイント インデックスの一部のみが含まれている場合でも、ギャップ ロックは追加されます。

1. 2.2、ネクスト キー ロック
2. ネクスト キー ロックは、実際には行ロック レコードの前にあるギャップ ロックの組み合わせです。インデックス値 10、11、13、および 20 があると仮定すると、考えられる次のキー ロックには次が含まれます:

(negative infinity, 10], (10, 11], (11, 13], ( 13,20], (20, 正の無限大)

RR 分離レベルでは、InnoDB は主に

ファントム読み取り

問題を防ぐためにネクスト キー ロックを使用します。

##2.3, Intention lock(Intention lock)

複数粒度のロックをサポートするために、InnoDB では行ロックとテーブル ロックを同時に存在させることができます。 InnoDB は、インテンション ロックと呼ばれる追加のロック メソッドをサポートしています。インテンション ロックは、ロックされたオブジェクトを複数のレベルに分割します。インテンション ロックは、トランザクションがより細かい粒度でロックすることを意味します。インテンション ロックは 2 つのタイプに分けられます:

意図的共有ロック (IS): トランザクションはテーブル内の特定の行に共有ロックを追加することを目的としています

意図的排他的ロック (IX): トランザクションテーブル内の特定の行に排他的ロックを追加する予定です

1. #InnoDB ストレージ エンジンは行レベルのロックをサポートしているため、意図的なロックはテーブル全体のスキャン以外のリクエストを実際にはブロックしませんテーブルレベルのインテントロックと行レベルのロックの互換性は次のとおりです:

2. 2.4. 挿入意図ロック (Insert Intention lock)

   挿入意図ロックは、レコードの行を挿入する前に設定されるギャップ ロックであり、このロックは挿入方法のシグナル、つまり複数の When を解放します。トランザクションは同じインデックス ギャップに挿入されるため、ギャップ内の同じ位置に挿入されない限り、相互に待機する必要はありません。列のインデックス値が 2 と 6 であるとします。2 つのトランザクションの挿入位置が異なる限り (たとえば、トランザクション A は 3 を挿入し、トランザクション B は 4 を挿入)、それらを同時に挿入できます。

   2.5. ロック モード互換性マトリックス

   水平方向は保持されているロック、垂直方向は要求されているロックです:

   

   3. デッドロック ログの読み取り

   具体的なケース分析を行う前に、まずデッドロック ログの読み取り方法を理解し、デッドロック ログの情報をデッドロック問題の解決にできる限り活用しましょう。 。

   #次のテスト ケースのデータベース シナリオは次のとおりです。

   MySQL 5.7 トランザクション分離レベルは RRです。

   テーブル構造とデータは次のとおりです。

   

   テスト例は次のとおりです。

   

   show Engine innodb status を実行すると、最新のデッドロックのログを表示できます。
   3.1. ログ分析は次のとおりです:
   1.***** (1) トランザクション: トランザクション 2322、アクティブ 6 秒開始インデックス read
   トランザクション番号は 2322、6 秒間アクティブ、インデックス読み取りの開始は、トランザクション ステータスがインデックスに従ってデータを読み取り中であることを示します。その他の一般的なステータスは次のとおりです。

   

   mysql tables in use 1 は、現在のトランザクションがテーブルを使用していることを示します。

   locked 1 は、テーブルにテーブル ロックがあることを意味します。DML ステートメントの場合、LOCK_IX

   LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

   LOCK WAIT は待機中であることを意味します。ロック、 2 lock struct(s) は trx->trx_locks を意味します。 ロック リストの長さは 2 です。各リンク リスト ノードは、テーブル ロック、レコード ロック、レコード ロックなど、トランザクションによって保持されるロック構造を表します。自動インクリメントロック。この使用例では、2locks は IX ロックを表し、lock_mode X (ネクストキー ロック)

   1 行ロック (s) は、現在の行レコード ロック/ギャップ ロックの数を表します。取引。

   MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

   MySQL スレッド ID 37 は、トランザクションを実行するスレッド ID が 37 (つまり、show processlist;) で表示される ID であることを意味します。

   delete from Student where stuno= 5 は、トランザクション 1 によって実行されている SQL を示します。不快なことは、show Engine innodb status が完全な SQL を表示できないことです。通常、現在ロックを待機している SQL が表示されます。

    ***** (1) WAITING FOR THIS LOCK TO BE GRANTED:
   RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

   RECORD LOCKS はレコード ロックを表します。この内容は、トランザクション 1 がテーブル Student 上の idx_stuno の X ロックを待っていることを示します。この場合、実際には Next-Key Lock です。
   トランザクション 2 のログは、上記の分析と似ています。

   2.***** (2) HOLDS THE LOCK(S):
   RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

   トランザクション 2 の Student(stuno,score) 値 (2,10) への挿入が a=5 ロックを保持していることを示します。モード X

   LOCK_gap ですが、トランザクション 2 によって実行された stuno=5 の学生からの削除がログから確認できません;

   これにより、DBA は次のことを行うことも困難になります。ログのみに基づいてロック問題の根本原因を分析します。
   3.***** (2) このロックが許可されるのを待っています:
   レコード ロック スペース ID 11 ページ番号 5 n ビット 72 テーブル cw**** のインデックス idx_stuno .****student trx id 2321 lock_mode

   #4. 古典的なケース分析

   4.1. トランザクションの同時挿入の一意キーの競合

   テーブルの構造とデータは次のとおりです:

   

   テスト例は次のとおりです。

   

   ログ分析は次のとおりです。 :

   

   トランザクション T2 t7(id,a) 値への挿入 (26,10) ステートメントの挿入は成功し、a=10 を保持します。

   排他的行ロック (Xlocks は記録しますが、ギャップなし)

   1. トランザクション T1 は t7(id,a) 値 (30,10) に挿入します。これは、T2 の最初の挿入ですでにレコード a= が挿入されているためです。 10、トランザクション T1 挿入 a=10 は一意のキーの競合を引き起こすため、その競合に対して一意のキーを申請する必要があります。 インデックス プラス S 次キー ロック (つまり、ロック モード S 待機) これは

      ギャップ ロック
   は、(,10]、(10,20] の間のギャップ領域をロックするために適用されます。

   3. トランザクション T2 t7(id,a) 値 (40,9) に挿入 このステートメントによって挿入される a=9 の値は、によって適用される ギャップ ロック 4 ～ 10 の間にあります。トランザクション T1 であるため、トランザクション T2 の 2 番目の挿入ステートメントは、トランザクション T1 の S-Next-key Lock ロック が解放されるまで待機する必要があり、lock_mode X ロックのギャップが確保されてから、rec insert 意図が待機します。とログに表示されます。

   4.2. 最初に更新してから挿入する場合の同時実行デッドロックの問題

   テーブル構造は次のとおりで、データはありません:

   

   テスト例は次のとおりです:

   

   デッドロック分析:
   存在しない 2 つのトランザクション更新レコードが ギャップ ロックを取得していることがわかります。 (ギャップ ロック) 次々と 、ギャップ ロックは互換性があるため、更新プロセス中にブロックされません。両方ともギャップ ロックを保持し、インテンション ロックを挿入するために競合します。ギャップ ロックを保持している他のセッションがある場合、現在のセッションは挿入意図ロックを適用できず、デッドロックが発生します。

   5. デッドロックを可能な限り回避する方法

   1. インデックスを合理的に設計し、区別性の高い列を複合インデックスの前に配置して、ビジネス SQL が可能な限りインデックスを通過できますより少ない行を見つけてロック競合を減らします。

   2. ビジネス ロジック SQL の実行順序を調整して、トランザクションの前で長時間ロックを保持する更新/削除 SQL を回避します。

   3. 大規模なトランザクションを避け、大規模なトランザクションを複数の小さなトランザクションに分割して処理するようにしてください。小規模なトランザクションではロックの競合が発生する可能性も低くなります。

   4. 固定順序でテーブルと行にアクセスします。たとえば、データを更新する 2 つのトランザクションの場合、トランザクション A は 1、2 の順序でデータを更新し、トランザクション B は 2、1 の順序でデータを更新します。これにより、デッドロックが発生する可能性が高くなります。

   5. 同時実行性が比較的高いシステムでは、特にトランザクションでは明示的にロックしないでください。たとえば、select ... for update ステートメントがトランザクション (開始トランザクションが実行されるか、自動コミットが 0 に設定される) 内にある場合、見つかったレコードはロックされます。

   6. 主キー/インデックスでレコードを検索してみてください。範囲検索では、ロックの競合が発生する可能性が高くなります。追加のクォータ計算にはデータベースを使用しないでください。たとえば、プログラムによっては、「select ... where ... order by rand();」などのステートメントが使用されますが、このようなステートメントではインデックスが使用されないため、テーブル全体のデータがロックされます。

   7. SQL とテーブルの設計を最適化して、同時に多すぎるリソースを占有する状況を軽減します。たとえば、接続テーブルの数を減らしますと、複雑な SQL を複数の単純な SQL に分解します。

   推奨学習:

   mysql 学習チュートリアル

以上がMySQL のデッドロック問題を解決する方法 (詳細な例)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。