mysqlデッドロックの原因と解決策は何ですか?

複数のトランザクションが同じリソースに対して同時にロックを保持および要求し、循環依存関係が発生すると、デッドロックが発生します。解決策は次のとおりです: 1. より低い分離レベルを使用する; 2. 固定アクセスを使用するテーブルと行を順番に追加する; 3. 慎重に選択したインデックスをテーブルに追加する; 4. 使用するロックを減らす。

このチュートリアルの動作環境: Windows7 システム、mysql8 バージョン、Dell G3 コンピューター。

デッドロックは、複数のトランザクションが同じリソースに対して同時にロックを保持および要求し、循環依存関係が発生した場合に発生します。デッドロックは、トランザクションが異なる順序でリソースをロックしようとすると発生します。 StockPrice テーブルの 2 つのトランザクションを例に挙げます。

トランザクション 1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

トランザクション#2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET;
COMMIT;

運が悪い場合は、最初のトランザクションの後で各トランザクションが実行される可能性があります。ステートメントとプロセス中のリソースのロック。次に、各トランザクションがステートメントの 2 行目を実行しようとしましたが、ロックされていることがわかりました。デッドロックを打開する他の理由がない限り、2 つのトランザクションは他方が完了するまで永久に待機します。

この問題を解決するために、データベースにはさまざまなデッドロック検出およびタイムアウト メカニズムが実装されています。 InnoDB のような複雑なストレージ エンジンは、循環依存関係を示し、すぐにエラーを返します。そうしないと、デッドロックが発生してクエリが非常に遅くなります。その他の悪い習慣として、タイムアウトを待ってから諦めるというものがあります。 InnoDB がデッドロックを処理する現在の方法は、最も排他的でない行レベルのロックを保持しているトランザクションをロールバックすることです。 (ロールバックのほぼ最も単純な参照インジケータ)

ロック動作の順序はストレージ エンジンによって決定されます。したがって、一部のストレージ エンジンは特定の操作シーケンスでデッドロックする可能性があり、その他のストレージ エンジンはデッドロックしない可能性があります。デッドロックには 2 つのタイプがあります。実際のデータの競合により避けられないものと、ストレージ エンジンの動作方法に起因するものです。

デッドロックを解消できるのは、いずれかのトランザクションの部分的または完全なロールバックのみです。デッドロックはトランザクション システムにおける客観的な事実であるため、設計ではデッドロックの処理を考慮する必要があります。ビジネス システムによっては、トランザクションを最初から再試行できるものもあります。

デッドロックに対処する方法
デッドロックはトランザクション データベースの典型的な問題ですが、トランザクションをまったく実行できないほど頻繁に発生する場合を除き、通常は危険ではありません。の。通常、デッドロックによりトランザクションがロールバックされた場合に、常にトランザクションを再発行できるようにアプリケーションを作成する必要があります。

InnoDB は行レベルの自動ロックを使用します。単一行を挿入または削除するだけのトランザクションの場合でも、デッドロックが発生する可能性があります。これは、これらの操作が実際には「小規模」ではなく、挿入または削除された行の (おそらく複数の) インデックス レコードにロックを自動的に設定するためです。

次の手法を使用してデッドロックに対処し、その発生の可能性を減らすことができます。

SHOW INNODB STATUS を使用して、最後のデッドロックの原因を特定します。これは、デッドロックを回避するためにアプリケーションを調整するのに役立ちます。

デッドロックによりトランザクションが失敗した場合は、トランザクションを再発行できるように常に準備してください。デッドロックは危険ではありません。もう一度試してください。

トランザクションを頻繁にコミットします。小さな問題では衝突が起こりにくくなります。

ロックされた読み取り (SELECT ... FOR UPDATE または ... LOCK IN SHARE MODE) を使用している場合は、READ COMMITTED など、より低い分離レベルを使用してみてください。

固定順序でテーブルと行にアクセスします。そうすれば、トランザクションは明確に定義されたクエリを形成し、デッドロックは発生しません。

慎重に選択したインデックスをテーブルに追加します。そうすれば、クエリでスキャンする必要があるインデックス レコードの数が減り、設定するロックの数も減ります。 EXPLAIN SELECT を使用して、MySQL がクエリに最も適切と考えるインデックスを決定します。

ロックの使用を減らします。 SELECT が古いスナップショットからデータを返すことを許可できる場合は、それに FOR UPDATE 句や LOCK IN SHARE MODE 句を追加しないでください。同じトランザクション内の持続読み取りはそれぞれ独自の新しいスナップショットから読み取るため、ここでは READ COMMITTED 分離レベルを使用することをお勧めします。

他に解決策がない場合は、テーブルレベルのロックを使用してトランザクションをシリアル化します。トランザクション テーブル (InnoDB など) で LOCK TABLES を使用する正しい方法は、AUTOCOMMIT = 0 を設定し、トランザクションを明示的にコミットするまで UNLOCK TABLES を呼び出さないことです。たとえば、テーブル t1 に書き込み、テーブル t から読み取る必要がある場合、次のように実行できます。

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

[do something with tables t1 and t2 here];

COMMIT;

UNLOCK TABLES;

テーブルレベルのロックにより、トランザクションが適切にキューに入れられ、デッドロックが回避されます。

シリアル化されたトランザクションを取得する方法は、単一行のみを含む補助「セマフォ」テーブルを作成することです。他のテーブルにアクセスする前に、各トランザクションでその行を更新します。このようにして、すべてのトランザクションが順番に発生します。シリアル化されたロックは行レベルのロックであるため、InnoDB のオンザフライ デッドロック検出アルゴリズムもこの場合に機能することに注意してください。デッドロックを解決するには、MySQL テーブル レベルのロックを備えたタイムアウト メソッドを使用する必要があります。

アプリケーションで LOCK TABLES コマンドを使用します。AUTOCOMMIT=1 の場合、MySQL は InnoDB テーブル ロックを設定しません。

関連する推奨事項: 「mysql チュートリアル 」

以上がmysqlデッドロックの原因と解決策は何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。