MySQLのエラー『Deadlock Found』の解決方法

MySQLの『Deadlock Found』エラーは、複数のトランザクションが互いにロックを奪い合い、デッドロック状態に陥った場合に発生します。このエラーが発生すると、MySQLは一方のトランザクションを強制的にロールバックし、もう一方を続行させます。この記事では、デッドロックの発生条件とその解決策について詳しく解説します。

1. エラーの発生条件

デッドロックは、主に以下の条件で発生します。

• 複数のトランザクションが同じテーブルの異なる行をロックした後、相互に別の行のロックを要求する。
• トランザクションの実行順序が競合し、リソースの待ち状態が発生する。
• 長時間ロックが解放されずに保持されることで、別のトランザクションの進行を妨げる。
• インデックスが適切に設定されていないために、予期しない範囲ロックが発生する。

2. デッドロックの発生例

以下のコードでは、2つのトランザクションが相互にロックを奪い合い、デッドロックが発生する可能性があります。

-- トランザクション A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- ここでBのロックを待つ
COMMIT;

-- トランザクション B
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- ここでAのロックを待つ
COMMIT;

この場合、トランザクションAがID=1のレコードをロックし、トランザクションBがID=2のレコードをロックした後、互いに相手のロックを解除するのを待つ状態になり、デッドロックが発生します。

3. 解決策1: トランザクションの順序を統一する

デッドロックを防ぐために、すべてのトランザクションでテーブルやレコードの更新順序を統一します。

-- トランザクション A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- トランザクション B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

このように、両方のトランザクションが同じ順序で更新を行うことで、デッドロックの可能性を減らせます。

4. 解決策2: トランザクションの粒度を小さくする

トランザクションの実行時間が長いほど、デッドロックが発生しやすくなります。可能な限りトランザクションを短縮し、影響範囲を小さくします。

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

このように、1つのトランザクションで複数の処理を行うのではなく、複数の小さなトランザクションに分割することで、デッドロックの発生を抑えられます。

5. 解決策3: ロックの範囲を限定する

必要な行だけをロックすることで、不要なロック競合を避けることができます。たとえば、SELECT ... FOR UPDATE を使用すると、更新するレコードのみをロックできます。

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

6. 解決策4: 適切なインデックスを設定する

インデックスが適切に設定されていないと、予期しない範囲ロックが発生し、デッドロックの原因になります。必要なカラムにインデックスを追加することで、ロック範囲を最小限に抑えます。

ALTER TABLE accounts ADD INDEX (id);

7. 解決策5: 自動リトライ機能を実装する

デッドロックが発生した場合、トランザクションを再試行することで問題を回避できることがあります。

DECLARE done INT DEFAULT 0;
REPEAT
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET done = 1;
END;
    START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;

    SET done = 0;
END;
UNTIL done = 0 END REPEAT;

8. 解決策6: トランザクション分離レベルを調整する

MySQLのデフォルトのトランザクション分離レベルは REPEATABLE READ ですが、READ COMMITTED に変更するとデッドロックのリスクを軽減できます。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

9. 解決策7: InnoDBのデッドロックログを確認する

デッドロックが発生した場合、MySQLのエラーログに記録されます。

SHOW ENGINE INNODB STATUS;

このコマンドでデッドロックの詳細を確認し、適切な対応を行います。

10. 解決策8: 長時間実行されるクエリを特定する

長時間実行されるクエリがデッドロックの原因となることがあります。以下のSQLで、実行中のクエリを確認できます。

SHOW PROCESSLIST;

時間のかかるクエリがあれば、最適化を検討します。