MySQLデッドロックの原因と解決策

データベースは、オペレーティング システムと同様に、複数のユーザーが使用する共有リソースです。複数のユーザーが同時にデータにアクセスすると、データベース内で複数のトランザクションが生成され、同じデータに同時にアクセスすることになります。同時操作が制御されていない場合、誤ったデータが読み取られて保存され、データベースの一貫性が損なわれる可能性があります。ロックは、データベースの同時実行制御を実装するための非常に重要なテクノロジです。実際のアプリケーションでは、ロック関連の例外が頻繁に発生します。2 つのトランザクションが競合するロックのセットを必要とし、トランザクションを続行できない場合、デッドロックが発生し、アプリケーションの正常な実行に重大な影響を及ぼします。

データベースには、排他ロック (排他ロック、つまり X ロック) と共有ロック (共有ロック、つまり S ロック) という 2 つの基本的なロック タイプがあります。データ オブジェクトが排他的にロックされている場合、他のトランザクションはそれを読み取ったり変更したりすることはできません。共有ロックを持つデータ オブジェクトは、他のトランザクションによって読み取ることができますが、変更することはできません。データベースは、これら 2 つの基本的なロック タイプを使用して、データベース トランザクションの同時実行を制御します。

最初の行き詰まりの事例

ユーザー A がテーブル A にアクセスし (テーブル A をロック)、次にテーブル B にアクセスします。別のユーザー B がテーブル B にアクセスし (テーブル B をロック)、次にテーブル A にアクセスしようとします。この時点で、ユーザー B はテーブル B をロックしているため、ユーザー A は続行する前にユーザー B がテーブル B を解放するのを待つ必要があります。同様に、ユーザー B は続行する前にユーザー A がテーブル A を解放するのを待つ必要があります。これがデッドロックです。

解決：

このタイプのデッドロックは非常に一般的であり、プログラムのバグによって発生します。プログラム ロジックを調整する以外に修正方法はありません。プログラムのロジックを注意深く分析してください。データベース内の複数のテーブルを操作する場合は、同じ順序で処理し、2 つのリソースが同時にロックされないようにしてください。たとえば、テーブル A と B を操作する場合は、常に最初に A、次に B の順序で処理します。2 つのリソースを同時にロックする必要がある場合は、常に同じ順序でリソースがロックされるようにしてください。

2番目の行き詰まり状況

ユーザー A がレコードをクエリして変更し、次にユーザー B がそのレコードを変更します。ユーザー A のトランザクションのロックの性質は、クエリの共有ロック試行から排他ロックにアップグレードされます。ただし、ユーザー B は共有ロックを持っているため、ユーザー B の排他ロックは A が共有ロックを解放するまで待機する必要があります。A は B の排他ロックのために排他ロックをアップグレードできないため、共有ロックも解放できず、デッドロックが発生します。このデッドロックはより微妙ですが、大規模なプロジェクトでよく発生します。たとえば、あるプロジェクトでは、ページ上のボタンをクリックしても、ボタンがすぐに無効にならないため、ユーザーは同じボタンを何度もクリックすることになります。このように、コードの同じセクションがデータベース内の同じレコードに対して複数の操作を実行するため、デッドロックに陥りやすくなります。

解決：

1. ボタンなどのコントロールについては、ユーザーが繰り返しクリックしたり、同じレコードを同時に操作したりすることを防ぐために、クリック後すぐに無効にします。

2. 制御には楽観的ロックを使用します。楽観的ロックは、主にデータ バージョン記録メカニズムに基づいて実装されます。つまり、データにバージョン識別子を追加します。データベース テーブルに基づくバージョン ソリューションでは、通常、これはデータベース テーブルに「バージョン」フィールドを追加することによって実現されます。データの読み込み時にバージョン番号も一緒に読み出され、その後更新する際にバージョン番号が1つ増加します。このとき、送信されたデータのバージョンデータは、データベーステーブル内の対応するレコードの現在のバージョン情報と比較されます。送信されたデータのバージョン番号がデータベーステーブルの現在のバージョン番号より大きい場合は更新され、そうでない場合は期限切れのデータとみなされます。楽観的ロック メカニズムにより、長いトランザクションでのデータベース ロックのオーバーヘッドが回避され (ユーザー A とユーザー B は操作中にデータベース データをロックしません)、高い同時実行性の下でのシステム全体のパフォーマンスが大幅に向上します。 Hibernate には、データ アクセス エンジンに組み込まれた楽観的ロック実装があります。なお、当社のシステムには楽観的ロック機構が実装されているため、外部システムからのユーザー更新操作は当社のシステムによって制御されず、データベース内のダーティデータが更新される可能性があることに注意してください。

3. 制御には悲観的ロックを使用します。ほとんどの場合、悲観的ロックは、操作の最大限の排他性を保証するために、Oracle の Select ... for update ステートメントなどのデータベースのロック メカニズムによって実装されます。しかし、これにはデータベースのパフォーマンスに大きなオーバーヘッドが伴い、特に長いトランザクションの場合は耐えられないことがよくあります。たとえば、金融システムでは、オペレータがユーザー データを読み取り、読み取ったユーザー データに基づいて変更 (ユーザー アカウントの残高の変更など) を行う場合、悲観的ロック メカニズムを使用すると、操作全体 (オペレータがデータを読み取ってから、データの変更を開始し、変更結果を送信するまで、さらにオペレータが途中でコーヒーを入れに行く時間も含む) 中、データベース レコードは常にロックされた状態になります。数百または数千の同時実行に直面した場合、このような状況は壊滅的な結果につながると考えられます。したがって、制御に悲観的ロックを使用する場合は慎重に検討する必要があります。

3番目の行き詰まり状況

トランザクション内で条件を満たさない更新文が実行されると、フルテーブルスキャンが実行され、行レベルロックがテーブルレベルロックにアップグレードされます。このようなトランザクションが複数回実行されると、デッドロックやブロッキングが発生する可能性があります。テーブル内のデータ量が非常に多いのに、インデックスが少なすぎるか不適切である場合にも、同様の状況が発生します。その結果、テーブル全体のスキャンが頻繁に行われ、最終的にアプリケーション システムが遅くなり、最終的にはブロックまたはデッドロックが発生します。

解決：

SQL ステートメントで複数のテーブルを関連付ける過度に複雑なクエリを使用しないでください。SQL ステートメントを分析するには「実行プラン」を使用し、完全なテーブル スキャンを必要とする SQL ステートメントの場合は、最適化のために対応するインデックスを作成します。

まとめ

一般的に、メモリオーバーフローやテーブルロックはコードの書き方が不適切であることが原因で発生するため、コードの品質を向上させることが最も根本的な解決策となります。最初に機能を実装し、その後テストフェーズでバグを修正すべきだと考える人もいますが、この考え方は間違いです。製品の品質が品質検査ではなく製造プロセス中に決定されるのと同様に、ソフトウェアの品質は設計とコーディングの段階で決定されます。ソフトウェア内のすべてのバグを見つけることは不可能であるため、テストはソフトウェア品質の検証に過ぎません。

上記は、MySQL デッドロックの原因と解決方法の詳細な内容です。MySQL デッドロックの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。