😓

バッチ処理で起きたMySQLデッドロックを潰した話: SQLと運用の両面から対処した

に公開
MySQL
SQL
パフォーマンス
tech

状況

定期実行しているバッチが、ある日 MySQL の deadlock によって異常終了しました。
リリース前に、バッチと並行しているプロセスが対象のDBのロックを握るようなことがそもそも無いことを確認したうえで本番運用しましたが、deadlockを起こしてしまいました。
調べていくと、SQL のインデックス設計とバッチ運用の両方に改善余地がありました。
この記事では、deadlock の調査過程、原因の切り分け、そして SQL 改善と運用改善の両面からどう対処したかをまとめます。

試したこと

まずエラーログの確認。
error.log には以下の deadlock が記録されていた。

(2026-03-21 01:00:01) error:
[1213: Deadlock found when trying to get lock; try restarting transaction]
in EXECUTE("
UPDATE serial_pool
SET assigned_order_id = 999999999, fuga_id = 111111, bar_id = 2222222
WHERE id IN ('55555')
")

下記でdeadlockの詳細を確認。

SHOW ENGINE INNODB STATUS\G

のはずが、当初間違えてレプリカに打っていたので結果が取得できませんでした。すみません。
おかげで、performance_schemaやRDSのgeneral logを全て掘るという遠回りをしました。

general logには、

16:00:01.473883Z 32744221 BEGIN
16:00:01.539  32744221 UPDATE serial_pool ... id IN ('55555')
16:00:01.585  32744221 ROLLBACK
16:00:01.585  32744221 SET AUTOCOMMIT=1
16:00:01.596  32744221 Quit

強制ロールバックした履歴が残っていました。

その後、何とかレプリカではなくライターのインスタンスにSHOWコマンドを打たないといけないことに気づき、deadlockの詳細を得ることに成功しました。

deadlockの詳細

trx (1) = 32744221 serial_pool 本バッチ側

本バッチ側では、hoge_tableに対してループでSELECT...FOR UPDATE → UPDATEを実行していました。
FOR UPDATEはXロック（排他ロック）を握るので、競合ロックの可能性は当初から想定していたのですが、並列するプロセスが存在しないので問題ないだろうということでリリースしていました。
今回の問題の本質は、そのXロックの範囲でした。

SELECT id
FROM serial_pool
WHERE group_id = ?
  AND assigned_order_id IS NULL
ORDER BY id ASC
LIMIT 1
FOR UPDATE;

こんなクエリを打っていたのですが、なんと対象インデックスに対して22368 row lock(s)とXロックを大量保持してしまっていたのです。
更に、後段でUPDATEを打つときにも数件ですがXロックを保持します。

trx (2) = 32744210　別バッチ

別バッチで油断していたのが、INSERT ... SELECT hoge_tableを行っていたのですが
この時、例えSELECTであっても

  • トランザクション分離レベルがデフォルトのREPEATABLE READ
  • STRAIGHT_JOIN
  • NOT EXISTS
    などの条件で一貫性保持が必要だと判断された場合には、indexなどに対してSロック（共有ロック）をとることがあるのです。
    SELECTだからと言って油断してはならないということです。
    共有ロックと排他ロックはお互いにロック待ちを起こすので、今回のdeadlockに繋がったということでした。

改善対応

対応としてはSQL改善と運用改善の二軸にしました。

インデックス貼り直してXロック範囲を狭める

原因は「FOR UPDATE 付きの取得クエリに対して、条件と並び順に合ったインデックスがなかったこと」でした。

SELECT id
FROM serial_pool
WHERE group_id = ?
  AND assigned_order_id IS NULL
ORDER BY id ASC
LIMIT 1
FOR UPDATE;

これに対して、順番違いのインデックスしかなかったので
EXPLAIN ANALYZE では、1 件取りたいだけなのに数万行規模を読んでいました。
しかも FOR UPDATE 付きなので、単に遅いだけではなく、ロック競合しやすい状態になっていました。

  1. 条件に合うインデックスがない
  2. ORDER BY id LIMIT 1 のために広い範囲を走査する
  3. FOR UPDATE により走査中のロック影響が広がる
  4. 別トランザクションの更新とロック順序がぶつかる

なので

ALTER TABLE serial_pool
  ADD INDEX idx_assign_pick (group_id, assigned_order_id, id);

これで期待したのは、

  • group_id = ? で絞る
  • assigned_order_id IS NULL で絞る
  • そのまま id ASC で先頭を取る

ただし、それだけでは終わらなかった

インデックス追加後に EXPLAIN ANALYZE を取り直すと、FORCE INDEX を付けた場合は期待通り高速になりました。
一方で、素の SQL では optimizer がまだ主キーを選ぶケースがありました。

deadlockが起きてもリトライさせる

deadlockそのものをできるだけ防ぐ対応と同時に、仮にdeadlockが起きてしまってもバッチを長時間止めなければ、再実行されて救済できます。
もちろん、再実行する際に結果が冪等になる設計は必須です。

今回、バッチ内でdeadlockが起きたときに、DB抽象化レイヤがexceptionをthrowせずに静かにexit()する挙動でした。これは何が問題かというと、ユースケース層で例外をcatchする座組にしても、exitで落ちてしまえば例外処理に移行できないので

  • バッチが途中で止まる。
  • 重複実行防止ファイルが残存したまま止まる。
  • バッチが長時間実行されない。
    という事態を招いていました。
register_shutdown_function(array($this, 'cleanupRunFileOnShutdown'));

// ...中略

    private function cleanupRunFile()
    {
        if (empty($this->runFilePath) || !file_exists($this->runFilePath)) {
            return;
        }

        $contents = @file_get_contents($this->runFilePath);
        if ($contents === false) {
            return;
        }

        if (strpos($contents, 'pid=' . $this->runFilePid) !== 0) {
            return;
        }

        @unlink($this->runFilePath);
    }

register_shutdown_functionを使って、途中で落ちた場合もロックファイルを削除できるようにします。

学び

検証環境

RDS MySQL 8.0.32

一次情報へのリンク

MySQL / InnoDB ロック・デッドロック

InnoDB における行ロック・ギャップロック・next-key lock の挙動について説明されています。

デッドロックがどのように検出され、どのトランザクションがロールバックされるかが説明されています。

デッドロックの詳細情報を確認するためのコマンドについて説明されています。

SELECT / FOR UPDATE / ロック

SELECT ... FOR UPDATE や LOCK IN SHARE MODE がどのようにロックを取得するかについて説明されています。

インデックスと実行計画

実行計画の見方と各項目の意味が説明されています。

実際の実行時間や走査行数を含めた詳細な実行計画の確認方法について説明されています。

トランザクション分離レベル

REPEATABLE READ における一貫性読み取りやロックの挙動について説明されています。

未検証の範囲

別バッチのINSERT...SELECTの見直し

更新履歴

  • 2026-04-09: 初版

Discussion