Posted inMySQL

MySQL Deadlock: 午前2時の「悪夢」から根本的な解決策まで

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

午前2時の「悪夢」

スマホが鳴り止まない。Slackからは500エラーの通知が飛び交う。Sentryはログで埋め尽くされている：Deadlock found when trying to get lock; try restarting transaction。バックエンドシステムを運用しているなら、これはデータベースが「窒息」しているという警告信号です。

私は以前、1分間に2,000件以上の注文を処理する約50GBのMySQL 8.0システムを運用していました。単純に見えるUpdate文が、処理フロー全体をフリーズさせてしまうことがありました。デッドロックは構文エラーではありません。トランザクション同士がリソースを奪い合っている競合状態です。慌ててサービスを再起動するのではなく、ログを読み解くことで根本原因を解決できます。

簡単に言うと：デッドロックとは？

「お互いがお互いを待ち続ける」状況を想像してみてください。2つのトランザクションが並行して実行され、次のような行き詰まりに陥ります：

  • トランザクションA：行1のロック（lock）を保持しており、次に行2を更新したい。
  • トランザクションB：行2のロックを保持しており、逆に行1を更新したい。

双方がロックを解放せず、永遠に待ち続けます。ここで「審判」であるInnoDBが登場します。リソースを解放するために、一方のトランザクション（通常、変更量が少ない方）を強制的にロールバック（中止）させます。これが、アプリケーションログにrestarting transactionというエラーが表示される理由です。

InnoDB Statusで「診断」する方法

エラーが発生した際、私がターミナルで最初に叩くコマンドはこれです：

SHOW ENGINE INNODB STATUS;

このコマンドは非常に長いテキストを返します。戸惑わずに、LATEST DETECTED DEADLOCKというセクションまでスクロールしてください。ここが、手がかりが詰まった「事件現場」です。

実際のログを分析する

以下は、私が実際に遭遇したバグのログの一部です：

*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec
UPDATE orders SET status = 'processing' WHERE id = 10;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 index PRIMARY of table `shop`.`orders` trx id 12345 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 3 sec
UPDATE orders SET status = 'shipped' WHERE id = 5;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 50 index PRIMARY of table `shop`.`orders` trx id 12346 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 index PRIMARY of table `shop`.`orders` trx id 12346 lock_mode X locks rec but not gap waiting

ここから何が読み取れるでしょうか？

  1. トランザクション(1)はid = 10の行のロックを待っています。
  2. トランザクション(2)は、トランザクション(1)が必要としているそのロックを保持（HOLDS）しています。
  3. 逆に、(2)も(1)が持っている別のロックを待っています。

原因の多くは、データの更新順序が不一致であることです。ある処理フローは10から5の順で更新し、別のフローは5から10の順で更新しようとしたためです。

デッドロックを阻止するための5つの「鉄則」

連晩バグ修正に追われた経験から、高負荷時でもデータベースをスムーズに動かすための5つのルールをまとめました。

1. 常に固定の順序で更新する

これが黄金律です。複数のレコードを更新する必要がある場合（例：3つの商品の在庫を減らす）、SQLを実行する前に必ずIDを昇順などでソートしてください。

# 安全なコード：更新前に必ずIDをソートする
ids = sorted([10, 5, 8])
for id in ids:
    cursor.execute(f"UPDATE products SET stock = stock - 1 WHERE id = {id}")

両方のトランザクションがID 5と10を更新しようとする際、まずID 5のロックを奪い合います。勝敗がはっきりするため、互いに待ち合うデッドロックは発生しなくなります。

2. トランザクションの実行時間を短縮する

重いロジックや外部APIの呼び出し、メール送信などをデータベーストランザクション内に含めないでください。トランザクションが長くなるほど、ロックを保持する時間も長くなり、他のスレッドとの競合確率が飛躍的に高まります。

3. インデックスを最適化してフルテーブルスキャンを避ける

UPDATE文にインデックスがない場合、MySQLはテーブル全体をスキャン（Table Scan）する必要があります。すると、特定の行だけでなく、データの範囲全体をロック（Gap Lock）してしまいます。データ更新を行うすべてのクエリが、Primary KeyまたはUnique Indexに基づいていることを確認してください。

4. 可能であれば分離レベル（Isolation Level）を下げる

MySQLのデフォルトはREPEATABLE READで、ギャップロック（Gap Locking）が非常に厳格です。完全な一貫性がそれほど厳しく求められないアプリケーションであれば、READ COMMITTEDへの変更を検討してください：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

実際、ECサイトのシステムでこの設定変更を行ったところ、ギャップロックに起因するデッドロックが80%削減されました。

5. アプリケーション層でリトライ機構を実装する

秒間数千リクエストに達するシステムでは、デッドロックを100%回避するのは困難です。ユーザーにエラーを返す代わりに、トランザクションを自動的に再試行するロジックを記述しましょう。

max_retries = 3
for i in range(max_retries):
    try:
        # ここにDBロジックを記述
        db.commit()
        break
    except Exception as e:
        if "Deadlock found" in str(e):
            time.sleep(0.1) # 100ms待機してから再試行
            continue
        raise e

おわりに

デッドロックへの対処は、単にSQLを修正するだけではありません。データがどのようにアクセスされ、競合するかを理解する必要があります。SHOW ENGINE INNODB STATUSでログを読み、更新順序を標準化し、常にリトライプランを用意しておくことを忘れないでください。この経験が、予期せぬ「システム停止」に直面した際の自信に繋がれば幸いです。

Share:
Tags: