Tech Learning Daily — 正しい UPDATE を書いただけなのに、なぜ DB はトランザクションを片方だけ殺すのか？

DB 🌿 基礎 ⏱ 約 7 分

正しい UPDATE を書いただけなのに、なぜ DB はトランザクションを片方だけ殺すのか？

Sentry に PG::TRDeadlockDetected や ActiveRecord::Deadlocked が一件だけ上がり、再実行したら何事もなく通った——そんな経験はないだろうか。SQL は間違っていないし、ローカルでは一度も再現しない。DB はなぜ、正しいはずのトランザクションを途中で打ち切ったのか。

🎯 3 行まとめ

デッドロックは、2 つ以上のトランザクションが互いに相手の持つロックを待ち合い、誰も先に進めなくなる循環待ちの状態である。
DB はデッドロックを未然に防ぐのではなく、起きた後に「待ちの循環」を見つけて片方をエラーで終わらせ、残りを先に進める。
対策の基本は「複数行をロックする順序をアプリ全体で揃える」ことと「デッドロックエラーならトランザクション全体を再実行する」ことの 2 つだ。

前提: UPDATE は行ロックを取り、COMMIT まで離さない

PostgreSQL や MySQL（InnoDB）で UPDATE を実行すると、対象の行に行ロックがかかる。行ロックとは「この行は今自分が書き換え中なので、他のトランザクションは書き換えるな」という印で、同じ行を更新しようとした別トランザクションは、ロックが外れるまで待たされる。

ロックが外れるのは文が終わった時ではなく、トランザクションが COMMIT か ROLLBACK で終わった時だ。途中で離すと、まだ確定していない値を別トランザクションが上書きでき、ロールバック時に整合性を保てなくなるからである。つまり 1 つのトランザクションの中で複数行を更新すると、取ったロックは最後まで手元に溜まっていく。

循環待ち: 2 人が互いの行を握って待つ

PostgreSQL の公式ドキュメントにある例をそのまま借りる。口座 11111 から 22222 へ送金するトランザクション 1 と、22222 から 11111 へ送金するトランザクション 2 が同時に走ったとする。

  Tx1                    Tx2
   │                      │
   │ UPDATE id=11111      │
   │ (row A lock)         │
   │                      │ UPDATE id=22222
   │                      │ (row B lock)
   │                      │
   │                      │ UPDATE id=11111
   │                      │ → wait row A
   │ UPDATE id=22222      │
   │ → wait row B         │
   ▼                      ▼

     ┌─────┐  wait B   ┌─────┐
     │ Tx1 │──────────▶│ Tx2 │
     │     │◀──────────│     │
     └─────┘  wait A   └─────┘

Tx2 は Tx1 が行 A を離すのを待ち、Tx1 は Tx2 が行 B を離すのを待つ。ロックは COMMIT まで離されないので、どちらも自力では永遠に終われない。これがデッドロックだ。どちらの SQL も単体では正しく、問題は「同じ 2 行を逆の順序でロックした」というタイミングの組み合わせにしかない。ローカルで再現しないのは、2 つのトランザクションがほぼ同時に走る必要があるからである。

🍱 たとえるなら

箸立てに箸が 2 本だけある食堂で、A さんは左の 1 本、B さんは右の 1 本を先に取ってしまった。2 人とも「もう 1 本が空いたら食べ始める」と待っており、手元の 1 本は食べ終わるまで返さない。放っておけば閉店まで誰も食べられない。そこで店員が見回りに来て、片方に「一度箸を戻して並び直してください」と頼む。DB のデッドロック検出はこの店員にあたり、全員が「左の箸から取る」と決めておけば、そもそも店員の出番はなくなる。

DB は防がない。見つけてから片方を殺す

DB ほどのソフトウェアなら、危ない順序のロックを最初から拒否してくれそうに思える。しかし実際は逆で、PostgreSQL も InnoDB も、デッドロックを事前には防がない。待ちが発生した後に、「誰が誰を待っているか」を矢印でつないだ待ちグラフ（wait-for graph）を調べ、矢印が輪になっていたら、関わったトランザクションの 1 つを中止して輪を切る。中止された側はエラーを受け取り、取っていたロックを全部解放するので、残った側が先に進める。

どちらが中止されるかは選べない。PostgreSQL のドキュメントは「どのトランザクションが中止されるかは予測しにくく、それに頼るべきではない」と書いている。InnoDB は挿入・更新・削除した行数が少ない「小さい」トランザクションを優先して選ぼうとする。エラーは PostgreSQL では SQLSTATE 40P01（deadlock_detected）、MySQL ではエラー 1213 として返る。

PostgreSQL の検出はロック待ちの瞬間ではなく、少し待ってから走る。この待ち時間が deadlock_timeout で、デフォルトは 1s だ。グラフの探索は安価ではないため、「本番でデッドロックはまれ」と楽観的に仮定し、しばらく待っても解けないロック待ちだけを調べる設計になっている。

# postgresql.conf
deadlock_timeout = 1s   # 検出を走らせるまでの待ち
log_lock_waits = on     # 同じ時間を超えたロック待ちをログに出す

deadlock_timeout を小さくするとデッドロックの解消は早まるが、普通のロック待ちにも探索が頻繁に走って CPU を使う。大きくすると、デッドロックに巻き込まれたリクエストがその分だけ長く固まる。log_lock_waits を on にすると、この時間を超えたロック待ちがログに残るので、デッドロック未満の「長いロック待ち」を見つける手がかりになる。

デッドロックと「ただの長いロック待ち」は別物

循環していないロック待ち（相手が長いトランザクションを開いたまま放置している等）は、デッドロック検出の対象にならない。PostgreSQL では、デッドロックが検出されない限りロック待ちは無期限に続く。待ちの上限を付けたい場合は lock_timeout を使う。デフォルトは 0（無効）で、値を入れるとロック取得の 1 回ごとにその時間で文がエラーになる。全セッションに効いてしまうため、ドキュメントは postgresql.conf での設定を推奨しておらず、マイグレーションなど特定のセッションで SET lock_timeout = '3s'; のように使うのが一般的だ。

MySQL では innodb_lock_wait_timeout（デフォルト 50 秒）が同じ役割を持つ。ここに落とし穴がある。innodb_rollback_on_timeout がデフォルトの OFF のままだと、タイムアウト時にロールバックされるのは直前の 1 文だけで、トランザクションはまだ開いている。アプリがエラーを握りつぶして COMMIT すると、前半の更新だけが確定してしまう。デッドロック（1213）ではトランザクション全体が巻き戻るのと対照的だ。

💼 実務でどう出会うか

典型は「複数行を順不同で更新する処理」が並行で走る場面だ。カートの商品の在庫をカート内の並び順で減らす、バッチと Web リクエストが同じユーザー群を別の順序で更新する、などである。対策の第一は、ロックを取る順序を揃えることで、Rails なら Product.where(id: ids).order(:id).lock.to_a のように主キー順で先に SELECT ... FOR UPDATE してから更新すると、全員が同じ順で行を取りに行くので輪ができない。

第二は再実行だ。順序を揃えきれない処理は必ず残るので、ActiveRecord::Deadlocked を捕まえて transaction ブロック全体を数回までやり直す（失敗した文だけをやり直しても、ロックとそれまでの更新は既に巻き戻っている）。トランザクションを短く保ち、外部 API 呼び出しをトランザクションの外に出すことも、ロックを握る時間を縮めて衝突確率を下げる。

⌨️ 手を動かす（5 分）

Docker の PostgreSQL で、記事の送金デッドロックを再現する。まず準備する。

docker run --rm -d --name pg -e POSTGRES_PASSWORD=pw postgres:17
sleep 3
docker exec pg psql -U postgres -c "CREATE TABLE accounts(id int PRIMARY KEY, balance int); INSERT INTO accounts VALUES (1,100),(2,100);"

ターミナルを 2 つ開き、両方で docker exec -it pg psql -U postgres を実行する。[A] / [B] はどちらのターミナルかを示す。行頭の -- [A] などを除いた SQL を、上から順に 1 行ずつ打つ。

-- [A] BEGIN; UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- [B] BEGIN; UPDATE accounts SET balance = balance - 10 WHERE id = 2;
-- [B] UPDATE accounts SET balance = balance + 10 WHERE id = 1;   -- ここで止まる
-- [A] UPDATE accounts SET balance = balance + 10 WHERE id = 2;   -- 約 1 秒後に片方がエラー

約 1 秒（deadlock_timeout）待ったあと、どちらかに ERROR: deadlock detected と、どのプロセスがどのトランザクションを待っていたかを示す DETAIL が出て、もう片方の UPDATE が通るはずだ。両方で id を 1 → 2 の順に更新し直すと、後から来た側はただ待つだけでエラーにならない。終わったら docker stop pg で片付ける。

🙅 よくある誤解

デッドロックが出たら SQL がバグっている — 個々の SQL は正しくても、ロック順序が逆の処理が同時に走るだけで起きる。並行処理する DB ではゼロにしきれない前提で、再実行を組み込んでおくのが正攻法である。
デッドロックとロック待ちタイムアウトは同じエラー — デッドロックは循環待ちを DB が検出して即座に片方を中止するもので、タイムアウトは循環がなくても待ちが上限を超えたら打ち切るものだ。MySQL では巻き戻る範囲も違う（デッドロックはトランザクション全体、タイムアウトはデフォルトで直前の文だけ）。
1 文の UPDATE しかしないから無関係 — 1 文でも複数行を更新するなら、行をロックしていく順序は実行計画次第になり、別の文と逆順になれば輪ができうる。WHERE id IN (...) の一括更新が並行で走る処理は対象になりうる。

📖 用語ミニ辞典

行ロック: 更新中の行に他トランザクションの書き込みを待たせる印。COMMIT / ROLLBACK まで保持される。
デッドロック: 複数のトランザクションが互いの持つロックを待ち合い、どれも進めなくなった循環待ち状態。
待ちグラフ（wait-for graph）: 「誰が誰のロックを待っているか」を矢印で表したグラフ。輪があればデッドロック。
deadlock_timeout: PostgreSQL がロック待ち開始からデッドロック検出を走らせるまでの待ち時間。デフォルト 1 秒。
lock_timeout / innodb_lock_wait_timeout: 循環の有無に関係なく、ロック待ちが一定時間を超えたら文をエラーにする上限。

🔗 もっと深く

PostgreSQL: Explicit Locking — 行ロック・テーブルロックの種類と、記事で使った送金デッドロックの例・ロック順序の助言が載っている一次資料。
PostgreSQL: Lock Management — deadlock_timeout がなぜ遅延検出なのかの説明と、log_lock_waits との関係。
MySQL 8.4: Deadlock Detection — InnoDB の犠牲者選択と、innodb_deadlock_detect を切ってタイムアウトに頼る選択肢。
MySQL 8.4: How to Minimize and Handle Deadlocks — 再実行・順序の固定・インデックスなど実務的な対策の一覧。