Posted inDatabase

PostgreSQLのデッドロック特定と解消：Lock Treeによる原因究明とタイムアウト最適化

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

1. 背景とデッドロック対策が不可欠な理由

以前、私たちのチームで100GB規模のデータベースをMySQLからPostgreSQLへ移行したときのことです。3日かけて計画を練り、1日で移行作業（cutover）を完了させました。すべてが順調に見えたのも束の間、夜20時のフラッシュセール突入と同時に、何千人ものユーザーが一斉にウォレットへのチャージと決済を開始しました。バックエンドのコネクションプール（200接続）はわずか30秒で枯渇。APIのタイムアウトが多発し、PostgreSQLのログにはあの悪名高いエラーがずらりと並びました。ERROR: deadlock detected。

根本的な原因は、ACID特性を保証するためのPostgreSQLのロッキング機構にあります。デッドロック（死の膠着状態）は、トランザクションAがレコード1をロックした状態でレコード2のロックを要求し、同時にトランザクションBがレコード2をロックした状態でレコード1のロックを要求した際に発生します。互いにロックを解放しないため、双方が無限の待ちループに陥ってしまうのです。

PostgreSQLにはこの循環待ちを検知し、いずれかのトランザクションを自動的にabort（中断）させてシステムを救済するバックグラウンドプロセスが備わっています。しかし、この自動検知・強制終了メカニズムを恒久的な解決策と考えてはいけません。デッドロックが頻発すると、データベースはロックグラフのスキャンを絶え間なく実行するためCPU使用率が90〜100%まで跳ね上がり、ユーザーの決済エラーが多発して、アプリケーションの接続がドミノ倒しのように崩壊してしまいます。

2. ローカル環境でデッドロックを再現する

問題を根本から治療するには、まず発生のメカニズムを正確に理解する必要があります。最も直感的な方法は、2つのターミナルを開いて意図的にロック競合を再現してみることです。

まず、アカウントテーブルを作成し、AliceとBobという2つのテストレコードを挿入します。

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    owner_name VARCHAR(50) NOT NULL,
    balance NUMERIC(12, 2) NOT NULL DEFAULT 0
);

INSERT INTO accounts (owner_name, balance) VALUES 
('Alice', 1000.00),
('Bob', 2000.00);

次に、2つのターミナルタブ（Session 1とSession 2）を開いてPostgreSQLに接続し、以下のタイムラインに沿って順番に実行します。

ステップ1： Session 1でトランザクションを開始し、Aliceの残高から100ドルを差し引きます。

-- セッション1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

ステップ2： Session 2に切り替え、別のトランザクションを開始してBobの残高から200ドルを差し引きます。

-- セッション2
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;

ステップ3： Session 1に戻り、Bobの口座に100ドルを加算してみます。

-- セッション1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

この時点でターミナル1の応答が止まります。id = 2の行がSession 2によってRowExclusiveLockを保持されているため、クエリが待機状態（ハング）になります。

ステップ4： Session 2で、逆にAliceへ送金するクエリを実行します。

-- セッション2
UPDATE accounts SET balance = balance + 200 WHERE id = 1;

その瞬間、わずか1秒で循環待ちのサイクルが完成します。PostgreSQLがデッドロックを検知し、Session 2を即座に強制終了（abort）します。

ERROR:  deadlock detected
DETAIL:  Process 14210 waits for ShareLock on transaction 85412; blocked by process 14205.
Process 14205 waits for ShareLock on transaction 85413; blocked by process 14210.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "accounts"

3. システムを救う3つの重要設定：deadlock_timeout、lock_timeout、ログ設定

PostgreSQLは、クエリがブロックされた瞬間に即座にデッドロック検査を行うわけではありません。理由は単純で、ロックグラフの探索アルゴリズムが多くのCPUリソースを消費するためです。PostgreSQLは一定時間待機した後に初めて検査処理を実行します。

3.1. deadlock_timeout：検知までの時間を短縮する

deadlock_timeoutは、プロセスがロックを待機してからPostgreSQLがデッドロック検査ワーカーを起動するまでの時間を定義します。エンジンのデフォルト値は1s（1秒）です。

高負荷なオンライントランザクション処理（数千TPS規模のOLTP）システムにおいて、1秒間の待機は長すぎます。後続の数百件のリクエストが連鎖的に滞留してしまうため、この値は200ms - 300ms程度に引き下げるのが推奨されます。

-- サーバーの再起動なしでクラスター全体に即時適用
ALTER SYSTEM SET deadlock_timeout = '300ms';
SELECT pg_reload_conf();

3.2. log_lock_waits：早期警戒レーダーを有効化する

デフォルトのPostgreSQLは、致命的な事態（デッドロックエラー）に至った場合にのみログを出力します。他者を長時間ブロックしている低速クエリを早期に捕捉したい場合は、今すぐlog_lock_waitsを有効化してください。プロセスがdeadlock_timeoutを超えてロック待ち状態になると、サーバーはログファイルに詳細を記録します。

ALTER SYSTEM SET log_lock_waits = 'on';
SELECT pg_reload_conf();

3.3. lock_timeout：導火線を断ち切る安全装置

これはデータベースを保護するための最も重要な防御策です。デフォルト設定はlock_timeout = 0（無期限に待機）となっています。ロック競合が発生した場合、クエリはコネクションプールが枯渇するまで延々と停止し続けます。数秒以内にロックが取得できない場合はクエリを自動的にタイムアウトさせる制限を設けましょう。

-- 現在のセッションに対して3秒を設定
SET lock_timeout = '3s';

-- 本番環境でDDLマイグレーションを実行する際に極めて重要
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN status VARCHAR(20) DEFAULT 'active';
COMMIT;

accountsテーブルに対して未コミットの読み取り／書き込みトランザクションが存在する場合でも、上記のALTER TABLEはテーブル全体を無期限にロックし続けることなく、2秒で自動的にタイムアウトして終了します。

4. 監視体制とアプリケーション層における根本的対策

4.1. Lock Treeクエリでroot-blockerを特定する

データベースのメトリクスが危険水域に達しレイテンシが急上昇した際、真っ先に把握すべきは「誰がロックを独占している元凶（root blocker）で、誰が待たされている被害者なのか」です。以下のクエリを実行することで、プロセスID（PID）間の依存・待機関係を正確にあぶり出すことができます。

SELECT
    blocked_locks.pid         AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocking_locks.pid        AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query    AS blocked_statement,
    blocking_activity.query   AS blocking_statement,
    now() - blocked_activity.query_start AS waiting_duration
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.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    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;

長時間ロックを保持しシステム全体を停滞させているPIDを特定できた場合、以下の2段階の緊急対応を行います。

-- レベル1：対象PIDで実行中のクエリをグレースフルにキャンセル
SELECT pg_cancel_backend(<blocking_pid>);

-- レベル2：上記で応答がない場合、接続を強制切断
SELECT pg_terminate_backend(<blocking_pid>);

4.2. バックエンドコードにおける根本的改善

データベース側のチューニングは被害を最小限に抑える対症療法に過ぎません。デッドロックを根絶するには、ビジネスロジックの実装設計から見直す必要があります。

  • リソースの更新順序を常に統一する： これは最も基本的かつ重要な鉄則です。複数のアカウントを操作する場合、トランザクションAとトランザクションBは常に同じ順序（例えばIDの昇順）でリソースをロックしなければなりません。注意点として、PostgreSQLではUPDATE ... ORDER BY構文はサポートされていません。実行前にバックエンドコード側でID配列をソートするか、事前にSELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATEのように明示的なロックを取得してください。
  • トランザクション内に外部I/Oを持ち込まない： BEGIN ... COMMITブロックの途中で外部サードパーティのHTTP APIを呼び出したり、メール送信やPDF生成を行ったりすることは絶対に避けてください。必要なデータはあらかじめメモリ上で準備しておき、トランザクションを開いたら迅速にDBへ書き込み、即座にコミットします（目標実行時間：50ms未満）。
  • キュー処理にはSKIP LOCKEDを活用する： バックグラウンドワーカーがジョブを取得して処理する際は、通常のSELECT FOR UPDATEではなく、SELECT ... FOR UPDATE SKIP LOCKEDを使用します。これにより、他のプロセスによってロックされている行が自動的にスキップされ、ワーカー間のロック競合リスクを完全に排除できます。
  • Exponential Backoffによるリトライ処理を実装する： デッドロックは一時的なエラー（transient error）です。アプリケーション層にはPostgreSQLのエラーコード40P01を捕捉するミドルウェアを導入すべきです。このエラーを検知した際は、ランダムなジッター（ゆらぎ）を加えつつ、50ms、100ms、200msと段階的に待機時間を延ばして自動リトライを行うことで、ユーザー体験を損なわずに処理を完結させられます。
Share:
Tags: