Posted inデータベース

デッドロック・サバイバルガイド：PostgreSQLとMySQLにおける検出と解決の比較

Database tutorial - IT technology blog
Database tutorial - IT technology blog

午前2時の悪夢：トランザクションの衝突

午前2時15分。枕元でスマートフォンが鳴り響いています。PagerDutyのアラートが押し寄せ、アプリケーションのレイテンシは垂直上昇し、データベースのコネクションプールは完全に応答を停止しています。ログを確認すると、犯人が見つかりました：ERROR: deadlock detected。MySQL、PostgreSQL、あるいはMongoDBであっても、デッドロックはあらゆるシステムにおいて避けては通れない問題です。デッドロックは稼働時間など気にしません。ただ循環参照だけを気にします。

デッドロックは、2つのトランザクションが互いに必要とするロックを保持し合っているときに発生します。どちらも前に進むことができず、永久的な停止状態に陥ります。現代のエンジンはこれらのサイクルを自動的に解消しますが、その影響は甚大です。強制終了されたトランザクションはリクエストの失敗を意味し、ユーザーを失望させ、エラー率の急増によってサーキットブレーカーをトリップさせる可能性があります。

データベースエンジンの対抗策：異なる戦略

どちらのデータベースもデッドロックを処理しますが、その内部ロジックは驚くほど異なります。これらの仕組みを理解することは、単なる「生存」のためではなく、パフォーマンスを最適化するために役立ちます。

PostgreSQL：ウェイトフォー・グラフ

Postgresは慎重派です。正常なトランザクションに対して貴重なCPUサイクルを浪費しないよう、デッドロックを常にスキャンすることはありません。代わりに、deadlock_timeout（通常は1秒）で定義されたタイマーを待ちます。トランザクションが丸1秒間アイドル状態になると、Postgresは探索を開始します。誰が誰をブロックしているかをマッピングするための「ウェイトフォー・グラフ（wait-for graph）」を構築します。ループを発見すると、通常は検出器を起動させたばかりのトランザクションを強制終了し、残りのキューを解消させます。

MySQL (InnoDB)：積極的なハンター

InnoDBはより積極的な姿勢をとります。ロックリクエストをリアルタイムで追跡し、デッドロックサイクルが形成された瞬間にそれを解消することがよくあります。デフォルトで innodb_deadlock_detect が有効になっており、エンジンは容赦なく犠牲者を選びます。ロールバックのコストを最小限に抑えるため、通常は「仕事量」が最も少ないトランザクション（Undoログのサイズで測定）をロールバックします。

自動処理に潜む隠れたコスト

データベースにデッドロックを「修正」させるのはリスクの伴う戦略です。エンジンに頼り切るのではなく、デッドロックを最小限に抑えるべき理由は以下の通りです。

メリット

  • システムの生存： 検出機能がなければ、たった1つのデッドロックでコネクションがいつまでも保留され続ける可能性があります。これは最終的に500以上のコネクションプールを使い果たし、サービス全体をクラッシュさせます。
  • 自己修復： データベースは、衝突の一方が生き残って処理を完了できるように保証します。

デメリット

  • CPUへの負荷： 秒間5,000件以上のトランザクションを処理している場合、絶え間ないサイクルチェックは総CPUオーバーヘッドの10〜15%を消費する可能性があります。
  • ユーザーへの影響： どちらか一方のトランザクションは「死ぬ」必要があります。バックエンドがその特定のエラーコードをキャッチしない場合、ユーザーには汎用的な500エラーが表示され、チェックアウトフローが中断されてしまいます。
  • ドミノ倒し効果： PostgreSQLにおけるわずか1秒の遅延であっても、リクエストが積み重なり、マイクロサービス全体でタイムアウトの連鎖を引き起こす可能性があります。

本番環境の要塞化

可視性は最大の武器です。ログに記録されていないものは最適化できません。

1. PostgreSQLの戦略

deadlock_timeout は1秒に設定したままにしましょう。100msまで下げると、多くのロックは数ミリ秒で自然に解消されるため、シグナルよりもノイズが増えることがよくあります。本当の成果は、詳細なログを有効にすることから得られます。

# postgresql.conf
deadlock_timeout = 1s
log_lock_waits = on
log_recovery_conflict_waits = on

2. MySQLの戦略

検出機能がオンであることを確認しつつ、innodb_lock_wait_timeout で厳格なセーフティネットを設定します。デフォルトは50秒に設定されていることが多いですが、現代のウェブアプリにとっては長すぎます。デッドロックではないものの単に処理が遅い「ゾンビ」トランザクションを排除するため、5〜10秒を目指しましょう。

# my.cnf
innodb_deadlock_detect = ON
innodb_lock_wait_timeout = 10
log_error_verbosity = 3

実行：予防と回復力

優れたデータベースエンジニアは、ロックを回避するコードと、ロックが発生した際に生き残るコードを書きます。

戦略A：一貫したロック順序の強制

ほとんどのデッドロックは、異なる順序で行を更新することから発生します。ユーザーAがアカウント1、次に2を更新し、ユーザーBがアカウント2、次に1を更新すると、最終的に衝突します。解決策はシンプルです。ロックをかける前に、必ずIDをソートしてください。

# すべてのスレッドが同じ順序でロックするようにIDをソートする
ids = sorted([account_src_id, account_dst_id])
with db.transaction():
    # 一貫してアカウント1、次にアカウント2の順でロックをかける
    db.execute("SELECT * FROM accounts WHERE id = %s FOR UPDATE", ids[0])
    db.execute("SELECT * FROM accounts WHERE id = %s FOR UPDATE", ids[1])

戦略B：スマートなリトライループ

高トラフィックのシステムでは、デッドロックは避けられない統計的な現実となることがあります。アプリケーションは冪等である必要があります。私は、複数のリトライがさらなるデッドロックを引き起こす「群衆（thundering herd）」効果を防ぐために、エクスポネンシャル・バックオフを使用しています。

import time
import psycopg2
from psycopg2 import errorcodes

def execute_with_retry(query, params, max_retries=3):
    for attempt in range(max_retries):
        try:
            with connection.cursor() as cursor:
                cursor.execute(query, params)
                connection.commit()
                return
        except psycopg2.extensions.TransactionRollbackError as e:
            if e.pgcode == errorcodes.DEADLOCK_DETECTED:
                connection.rollback()
                # 100ms、200ms、次に400ms待機
                time.sleep(0.1 * (2 ** attempt)) 
                continue
            raise
    raise Exception("3回のリトライ後もデッドロックが解消されませんでした")

戦略C：診断のための深掘り

実際にアラート対応をしている最中は、推測に頼ってはいけません。以下のクエリを使用して、摩擦の原因となっている正確なステートメントを特定しましょう。

PostgreSQL ブロッキングクエリ：

SELECT 
    blocked_locks.pid     AS blocked_pid,
    blocking_locks.pid    AS blocking_pid,
    blocked_activity.query  AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM  pg_catalog.pg_locks         blocked_locks
JOIN  pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
JOIN  pg_catalog.pg_locks         blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN  pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

MySQL ステータスチェック：

-- 最新のデッドロックの完全な履歴を表示
SHOW ENGINE INNODB STATUS;

-- 現在停止しているトランザクションを特定
SELECT * FROM information_schema.innodb_trx;
SELECT * FROM sys.innodb_lock_waits;

デッドロックは失敗の兆候ではなく、スケールと並行処理の副作用です。厳格なロック順序を強制し、回復力のあるリトライロジックを構築することで、午前2時の危機を、コーヒーを飲み終える前に解決する些細なログエントリに変えることができます。

Share:
Tags: