MySQLのデッドロックを再現して直す｜ログの読み方とPHPの再試行

Deadlock found when trying to get lock; try restarting transactionが出たら、まずエラー番号が1213かを確かめます。1213なら、やり直す単位は失敗したSQLだけではなく、そのトランザクション全体です。

ただし、同じ処理を繰り返すだけでは競合の原因が残ります。ここでは2つの接続でデッドロックを起こし、ログから更新順序の食い違いを読み取り、順序を揃えてからPHPの再試行処理を入れます。サンプルはMySQL 8.4.11／PHP 8.3.30で実行確認しています。

まず、2つの接続から同じ検証テーブルを開く

本番とは別の検証用データベースを用意してください。以下では名前をdeadlock_labとし、deadlock_demoという未使用のテーブル名を使います。初期データは2行だけです。

CREATE TABLE deadlock_demo (
  id INT PRIMARY KEY,
  quantity INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO deadlock_demo VALUES (1, 10), (2, 10);

次に、同じDBへ接続するSQLクライアントを2つ開きます。接続Aと接続Bでそれぞれ以下を実行し、接続IDが違い、データベース名が同じであることを確認しましょう。エディタのタブが別でも、同じ接続を共有する設定では再現できません。

SELECT CONNECTION_ID(), DATABASE();
SELECT @@innodb_deadlock_detect;

innodb_deadlock_detectが1（ON）の環境を使います。0の場合、この手順では検出による1213ではなくロック待ちのタイムアウトになることがあります。検証のために本番の設定を変更する必要はありません。

SQLを交互に実行して1213を起こす

各接続でまとめて流さず、次の表の上から順に1行ずつ実行します。AとBが別々の行を先に確保することが再現のポイントです。

順番	接続	実行するSQL	起こること
1	A	`START TRANSACTION;`	Aの処理を開始
2	A	`UPDATE deadlock_demo SET quantity = quantity + 1 WHERE id = 1;`	Aがid=1を保持
3	B	`START TRANSACTION;`	Bの処理を開始
4	B	`UPDATE deadlock_demo SET quantity = quantity + 1 WHERE id = 2;`	Bがid=2を保持
5	A	`UPDATE deadlock_demo SET quantity = quantity + 1 WHERE id = 2;`	Bを待って、実行中になる
6	B	`UPDATE deadlock_demo SET quantity = quantity + 1 WHERE id = 1;`	Aを待とうとしてデッドロックが成立

手順5でAの画面が実行中になったら、完了を待たずにBで手順6を実行します。先にロック待ちがタイムアウトした場合は、両方でROLLBACK;を実行して最初からやり直してください。

デッドロックを検出すると、どちらか一方に次のエラーが返ります。最後にSQLを送ったBが必ず選ばれるわけではありません。

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

エラーが出なかった接続でCOMMIT;を実行してから、SELECT * FROM deadlock_demo ORDER BY id;で結果を見ます。初期値10から、2行とも11になれば、片方のトランザクションだけがコミットされています。失敗した側の最初のUPDATEも取り消されるため、片方だけ12にはなりません。

ロック待ち自体を調べたい場合は、MySQLのロック・デッドロックと待機中の接続を確認する方法も参照してください。

LATEST DETECTED DEADLOCKを「保持→待機」で読む

再現直後に、確認権限のある接続から以下を実行します。\Gはmysqlコマンドラインの縦表示用です。GUIで受け付けない場合はSHOW ENGINE INNODB STATUS;を実行し、Status列を開いてください。

SHOW ENGINE INNODB STATUS\G

検証では次のように出ました。読みたい箇所を残して省略しています。トランザクション番号は接続A・Bという名前ではなく、ログ内で2つの処理を区別する番号です。

LATEST DETECTED DEADLOCK
...
*** (1) TRANSACTION:
UPDATE deadlock_demo SET quantity = quantity + 1 WHERE id = '1'
*** (1) HOLDS THE LOCK(S):
... index PRIMARY of table `deadlock_lab`.`deadlock_demo`
... lock_mode X locks rec but not gap
Record lock, heap no 3 ...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
... index PRIMARY ... lock_mode X locks rec but not gap waiting
Record lock, heap no 2 ...

*** (2) TRANSACTION:
UPDATE deadlock_demo SET quantity = quantity + 1 WHERE id = '2'
*** (2) HOLDS THE LOCK(S):
... index PRIMARY ... lock_mode X locks rec but not gap
Record lock, heap no 2 ...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
... index PRIMARY ... lock_mode X locks rec but not gap waiting
Record lock, heap no 3 ...
*** WE ROLL BACK TRANSACTION (2)

ログの箇所	この再現例で読むこと
`TRANSACTION`直下のSQL	検出時に実行していたSQL。先に実行したUPDATEまで全部並ぶわけではない
`HOLDS THE LOCK(S)`	すでに持っているロック。(1)がheap no 3、(2)がheap no 2を保持
`WAITING FOR THIS LOCK`	次に欲しいロック。相手が保持するレコードを互いに待っている
`index PRIMARY`	今回競合したのは主キー索引上のレコード
`lock_mode X`	排他ロック。`rec but not gap`はこの箇所がレコードのロックであることを示す
`WE ROLL BACK TRANSACTION (2)`	今回は(2)が取り消された。次の実行で同じ側になるとは限らない

同じtable・index・space・pageのレコードを見比べると、(1)は3を持って2を待ち、(2)は2を持って3を待つという輪が見えます。heap noはそのページ内のレコード番号であり、テーブルのidそのものではありません。今回は実行したSQLと照合し、id=1とid=2の取り合いだと確認できます。

実際の障害調査でも、表示中のSQLだけを修正する前に、そのトランザクションが先に実行したSQLをアプリケーション側の記録でたどります。デッドロック出力の形式や詳細は、MySQL公式のデッドロック例でも確認できます。

更新順序をid=1→2へ揃える

原因がこの2行の取得順序なら、AとBの両方を同じ順序にします。修正後は、両接続で次のSQLを使います。

START TRANSACTION;
UPDATE deadlock_demo SET quantity = quantity + 1 WHERE id = 1;
UPDATE deadlock_demo SET quantity = quantity + 1 WHERE id = 2;
COMMIT;

Aがid=1を保持している間、Bは最初のUPDATEで待ちます。この時点でBはid=2を取っていないので、Aはid=2の更新を終えてコミットできます。その後Bが進み、先ほどの輪ができなくなります。

動的に受け取った複数IDを更新する場合も、処理ごとに違う順番へ並べず、例えば数値昇順に揃えてから1件ずつ更新します。WHERE id IN (2, 1)の値の並びだけを変えても、SQLエンジンがその順序でロックを取る約束にはなりません。

この修正で解消するのは、再現した更新順序の問題です。別テーブルの更新、二次索引、外部キーの検査なども関わる実際の処理では、ログに登場するロックをもう一度たどる必要があります。MySQL公式も、処理順序の統一や短いトランザクションとともに、アプリケーション側での再試行を案内しています。

PHPでは1213だけ、トランザクション全体を再試行する

次はPDO用の関数です。PDOは例外モードで使い、$workへ「ひとまとまりとしてコミットしたいDB操作」を渡します。初回を含めて最大3回、再試行の間に50〜100ms、次は100〜200msの待ち時間を入れています。回数と待ち時間はこのサンプルの設定であり、万能な推奨値ではありません。

declare(strict_types=1);

function retryDeadlock(PDO $pdo, callable $work, int $maxAttempts = 3): mixed
{
    if ($maxAttempts < 1 || $maxAttempts > 5) {
        throw new InvalidArgumentException('maxAttempts must be between 1 and 5.');
    }
    if ($pdo->inTransaction()) {
        throw new LogicException('Call outside an existing transaction.');
    }

    for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
        try {
            $pdo->beginTransaction();
            $result = $work($pdo);
            $pdo->commit();
            return $result;
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            $isDeadlock = $e instanceof PDOException
                && (int) ($e->errorInfo[1] ?? 0) === 1213;

            if (!$isDeadlock || $attempt === $maxAttempts) {
                throw $e;
            }
            $baseUs = 50_000 * (2 ** ($attempt - 1));
            usleep($baseUs + random_int(0, $baseUs));
        }
    }
    throw new LogicException('Unreachable.');
}

errorInfo[1]のドライバ固有エラー番号で1213を判定しています。getCode()のSQLSTATEだけで判定すると、扱いたいエラーの範囲が曖昧になります。配列の構成はPDOの公式リファレンスで確認できます。

呼び出す側は、接続を作ってから関数へ処理を渡します。接続情報は環境変数から取得する例です。DB_DSNには、例えばmysql:host=127.0.0.1;dbname=deadlock_lab;charset=utf8mb4を設定します。

$pdo = new PDO(
    getenv('DB_DSN'),
    getenv('DB_USER'),
    getenv('DB_PASSWORD'),
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

$ids = [2, 1]; // 検証用。実アプリでは入力を検証してから渡す
sort($ids, SORT_NUMERIC);

retryDeadlock($pdo, function (PDO $pdo) use ($ids): void {
    $stmt = $pdo->prepare(
        'UPDATE deadlock_demo SET quantity = quantity + 1 WHERE id = ?'
    );
    foreach ($ids as $id) {
        $stmt->execute([$id]);
    }
});

トランザクションの開始と終了はretryDeadlock()が担当します。呼び出す前や$workの中で、別途beginTransaction()、commit()、rollBack()を実行しないでください。読み取った値に基づいて更新する処理なら、必要な読み取りも$workの中へ入れ、再試行ごとに取り直します。

メール送信や外部API呼び出しは繰り返される点に注意

$workの途中でメールを送り、その後1213が出ると、DBは戻っても送信済みメールは戻りません。もう一度実行すれば二重送信になります。この関数へ渡すのは、トランザクションで取り消せるDB操作に揃えてください。

外部への通知は、DB更新のコミット後に処理するか、送信予定を同じDBトランザクションで記録して別の処理が配信する設計にします。どちらの場合も、通知の重複や失敗を扱う仕組みは別途必要です。接続断など、コミットできたか不明なエラーまでこの関数で自動再試行することはしていません。

また、MySQLのCREATE TABLEなどは暗黙のコミットを起こすことがあるため、このラッパーへ入れません。テーブルの準備とデータ更新は分けます。詳しくはPDOのトランザクションとDDLの注意事項を参照してください。

修正できたかは、成功回数だけでなく最終データで確かめる

検証では、各行を10へ戻したうえで、2つのPHPプロセスがそれぞれ両方の行を1ずつ増やすテストを行いました。逆順のケースでは最初の行を互いに取得してから次へ進ませ、実際に競合させています。

検証	結果	確かめたこと
2処理が逆順に更新	実行回数は2回と1回。両行とも12	片方が1213から復旧し、取り消された途中の更新を二重計上しない
両処理をid=1→2へ統一	どちらも1回。両行とも12	この競合では再試行が不要になった
UPDATE後に重複キーエラー1062	1回で例外が返り、先のUPDATEも取り消し	1213以外を無条件に繰り返さない
開始済みトランザクションから呼ぶ	開始前に例外	呼び出し元のトランザクションを勝手に終了しない

Lock wait timeout exceededの1205は、この関数では再試行しません。1205では既定で文単位のロールバックになるため、同じcatchで残ったトランザクションも明示的に戻してから例外を渡します。1213とのロールバック範囲の違いはInnoDBのエラー処理に記載されています。

実アプリへ入れた後は、上限まで失敗した件数に加え、何回目で成功したかも記録しましょう。「成功するが毎回リトライしている」なら、SQLの順序やトランザクションの長さを見直す余地があります。待ち時間や回数を増やす前に、ログから保持と待機の組み合わせを確認するところへ戻ってください。

まず、2つの接続から同じ検証テーブルを開く

SQLを交互に実行して1213を起こす

LATEST DETECTED DEADLOCKを「保持→待機」で読む

更新順序をid=1→2へ揃える

PHPでは1213だけ、トランザクション全体を再試行する

メール送信や外部API呼び出しは繰り返される点に注意

修正できたかは、成功回数だけでなく最終データで確かめる

あわせて読みたい記事

【MySQL】ロックとデッドロックについて

mysqldumpのバックアップと復元テスト｜失敗検知・隔離環境での照合

MySQLのONLY_FULL_GROUP_BYエラーを直す｜集計単位・ANY_VALUE・最新行の選び方

MySQL「Unknown collation: utf8mb4_0900_ai_ci」の直し方｜DDL修正と移行後の確認