DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

PostgreSQLのデッドロックとロック競合の特定と解決方法

PostgreSQLのロック競合とデッドロックをマスターする。`pg_locks`を使用してブロックしているセッションを特定し、一般的なデッドロックシナリオを分析し、一貫したトランザクション順序や最適化されたクエリなどの実践的なテクニックを学び、これらの重要なデータベース問題を防止・解決する。よりスムーズで効率的なPostgreSQL運用を実現する。

PostgreSQLのデッドロックとロック競合の特定と解決方法

PostgreSQLのデッドロックやロック競合は、通常、アプリケーションが突然動かなくなったように感じられることで現れます。リクエストが積み重なり、ワーカーがactiveまたはidle in transaction状態になり、データベース自体にはまだ十分なCPUリソースが残っている場合があります。問題は単純な容量不足ではありません。あるセッションが別のセッションによるロック解放を待っており、時にはその背後に長い列ができることもあります。

これを最速で処理するには、2つのケースを区別することです。ロック競合はセッションが待機しているが、最終的には続行できる可能性があることを意味します。デッドロックは、2つ以上のセッションが互いに待機し合う循環が発生し、PostgreSQLが一方のトランザクションをキャンセルしなければならないことを意味します。両方のデバッグには同じ基本的なツールを使用しますが、修正方法は異なることがよくあります。

PostgreSQLのロックの基本

PostgreSQLは、多くのセッションが同時に動作する際に、テーブル、行、トランザクション、およびその他の内部オブジェクトを保護するためにロックを使用します。また、MVCCを使用しているため、通常の読み取りと書き込みは互いにブロックしないことがよくあります。これがPostgreSQLが高い同時実行性をうまく処理できる理由ですが、ロックの問題が混乱を招く原因でもあります。問題は通常、「ユーザーが多すぎる」という一般的なものではなく、特定のステートメントパターンです。

ロックの種類

PostgreSQLはさまざまなロックレベルを利用し、それぞれ異なる保護レベルを提供します。これらを理解することは、問題の診断に不可欠です。

  • AccessShareLock: 通常のSELECTで使用されます。主にACCESS EXCLUSIVEと競合します。そのため、書き込みが行われている間も多くの読み取りを実行できます。
  • RowExclusiveLock: テーブルに対するINSERT、UPDATE、DELETEで一般的です。名前は誤解されやすいですが、テーブル内のすべての行が排他的にロックされるわけではありません。
  • ShareUpdateExclusiveLock: FULLなしのVACUUM、ANALYZE、および一部のインデックス操作で使用されます。通常の読み取りと書き込みを許可しますが、いくつかのメンテナンス操作と競合します。
  • ShareLock / ShareRowExclusiveLock / ExclusiveLock: 特定のDDLや制約関連の操作で使用される、より強力なテーブルレベルのモードです。
  • AccessExclusiveLock: 最も制限の強いテーブルロックです。ALTER TABLE、DROP TABLE、TRUNCATE、VACUUM FULLはこの種類のロックを取得できます。通常の読み取りだけでなく書き込みもブロックします。

行レベルのロックはテーブルレベルのロックモードとは別です。UPDATEはテーブルレベルのRowExclusiveLockと、変更する行に対する行ロックを取得します。「この行はロックされている」と言う場合、通常は別のトランザクションがその行を変更したか、FOR UPDATEで選択したがまだコミットしていないことを意味します。

ロックモード

ロックモードは、トランザクションが必要とするアクセスの種類を示します。これらはRowExclusiveLock、ShareLock、ExclusiveLockなどの名前で表されることがよくあります。

ロック競合とブロックしているセッションの特定

ロック競合は、複数のトランザクションが他のトランザクションによって保持されているロックを待機しているときに発生します。これにより、アプリケーションの速度が大幅に低下する可能性があります。pg_locksシステムビューは、これらの問題を診断するための主要なツールです。

pg_locksの使用

pg_locksビューは、データベースシステム内のすべてのアクティブなロックに関する情報を提供します。どのセッションがロックを保持し、どのセッションが待機しているかを理解するのに非常に役立ちます。

以下は、ブロックしているセッションを特定するための一般的なクエリです。

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_locks.locktype AS blocked_locktype,
    blocked_locks.virtualtransaction AS blocked_vtx,
    blocked_locks.mode AS blocked_mode,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_locks.locktype AS blocking_locktype,
    blocking_locks.virtualtransaction AS blocking_vtx,
    blocking_locks.mode AS blocking_mode,
    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.offset IS NOT DISTINCT FROM blocked_locks.offset AND
    blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
    blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN
    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND blocking_locks.pid != blocked_locks.pid;

クエリの説明:

  • pg_locksをpg_stat_activityと2回結合しています。1回目はブロックされたプロセス用、2回目はブロックしているプロセス用です。
  • WHERE NOT blocked_locks.granted句は、現在待機中のロックをフィルタリングします。
  • blocking_locks.pid != blocked_locks.pidは、セッションが自分自身をブロックしていると報告しないようにします。
  • pg_locksの結合条件は、同じリソース上のロックを一致させます。

出力の解釈

  • blocked_pid / blocking_pid: 関係するセッションのプロセスID（PID）。
  • blocked_user / blocking_user: これらのPIDに関連付けられたユーザー。
  • blocked_statement / blocking_statement: 現在実行中または待機中のSQLクエリ。
  • blocked_mode / blocking_mode: 要求および保持されているロックモード。

このクエリが行を返す場合、ロック競合が発生しています。blocking_pidは、blocked_pidが待機しているロックを保持しています。

デッドロックの理解と解決

デッドロックは、2つ以上のトランザクションがそれぞれサイクル内の別のトランザクションによって保持されているロックを待機し、どちらも自分自身では解決できない循環依存関係が生じたときに発生します。PostgreSQLはデッドロックを検出し、トランザクションの1つ（通常はデッドロックを引き起こし、最も作業が少ないもの）を中止することで自動的に解決します。

一般的なデッドロックシナリオ

  1. 2つのトランザクションが異なるテーブルの異なる行を逆順で更新する場合:

    • トランザクションA: テーブル1の行Xを更新し、次にテーブル2の行Yを更新しようとします。
    • トランザクションB: テーブル2の行Yを更新し、次にテーブル1の行Xを更新しようとします。 トランザクションAが行Xをロックし、トランザクションBが行Yをロックすると、互いが保持するロックを取得しようとしたときにデッドロックが発生します。

  2. UPDATEの後にSELECT ... FOR UPDATEが続く場合:

    • トランザクションA: 行を更新します。
    • トランザクションB: 同じ行に対してSELECT ... FOR UPDATEを実行します。 UPDATEがまだ行排他ロックを保持しているときにSELECT FOR UPDATEが共有ロックを取得しようとし、他の依存関係が存在する場合、デッドロックが発生する可能性があります。

デッドロックの検出

PostgreSQLはデッドロック情報をサーバーログに記録します。通常、次のようなメッセージが表示されます。

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5679.
Process 5679 waits for ExclusiveLock on tuple (0,1) of relation 12345; blocked by process 1234.
HINT:  See server log for detail.

PostgreSQLは自動的に1つのトランザクションを選択して中止し、他のトランザクションが続行できるようにします。事後的にpg_stat_activityに頼って全体像を把握しないでください。確認する頃には、キャンセルされたステートメントが消えている可能性があります。サーバーログが通常、サイクルの最良の記録です。

デッドロックの解決

デッドロックが検出され、PostgreSQLがトランザクションを中止して解決した場合:

  1. 被害者を特定する: PostgreSQLのログでdeadlock detectedメッセージを確認します。どのプロセスが中止されたかが指定されています。
  2. 中止されたトランザクションを再試行する: SQLSTATE 40P01（deadlock_detected）を受け取ったアプリケーションは、操作が安全に再試行できる場合、トランザクション全体を再試行する必要があります。最後のステートメントのみを再試行すると、アプリケーションの状態が不整合になる可能性があります。
  3. 原因を分析する: 解決の鍵は、将来のデッドロックを防ぐことです。これには、デッドロックが発生した理由を理解し（一般的なシナリオで説明したように）、アプリケーションロジックまたはデータベース設計を調整することが含まれます。

ロック競合とデッドロックを防ぐテクニック

予防は治療に勝ります。ロック競合を最小限に抑え、デッドロック状況を回避する戦略を実装することは、高性能なPostgreSQLデータベースにとって重要です。

1. 一貫したトランザクション順序

  • ルール: すべてのトランザクションで、リソース（テーブル、行）に常に同じ順序でアクセスおよび変更します。複数のトランザクションがTableAとTableBを更新する必要がある場合、常にTableAをTableBの前に更新するか、その逆を一貫した方法で行うようにします。
  • 例: トランザクションがusersとordersのレコードを更新する必要がある場合、常に最初にusersに対して操作を実行し、次にordersに対して実行します。あるトランザクションがusersを更新してからordersを更新し、別のトランザクションがordersを更新してからusersを更新するシナリオを避けます。

2. トランザクション期間を最小化する

  • ルール: トランザクションは可能な限り短く保ちます。トランザクションが開いている時間が長いほど、保持するロックが多くなり、競合の可能性が高まります。
  • アクション: トランザクション内では必要なデータベース操作のみを実行します。データベース以外の作業（例：外部API呼び出し、トランザクション状態に依存しない複雑な計算）はトランザクション境界の外に移動します。

3. 適切な分離レベルを使用する

  • ルール: 正しいトランザクション分離レベルを理解し、選択します。PostgreSQLは以下を提供します。
    • READ UNCOMMITTED（PostgreSQLではREAD COMMITTEDでシミュレート）
    • READ COMMITTED（デフォルト）
    • REPEATABLE READ
    • SERIALIZABLE
  • アクション: デフォルトのREAD COMMITTEDは、ダーティリードを防ぎながら優れたパフォーマンスを提供します。REPEATABLE READとSERIALIZABLEはより強力な一貫性を提供しますが、より多くのserialization_failureエラー（スナップショット分離における実質的なデッドロック）や、場合によってはより多くのロック競合を引き起こす可能性があります。絶対に必要な場合にのみ使用してください。

4. クエリとインデックスを最適化する

  • ルール: 低速なクエリはロックをより長く保持します。クエリが効率的で適切にインデックス化されていることを確認します。
  • アクション: EXPLAIN ANALYZEを使用して低速なクエリを特定します。特にWHERE句やJOIN条件に対して、データ検索を高速化するための適切なインデックスを追加します。

5. SELECT ... FOR UPDATEを控えめに使用する

  • ルール: SELECT ... FOR UPDATEは、トランザクションの期間中、行をロックします。これは競合状態を防ぐために強力ですが、競合の主要な原因にもなり得ます。
  • アクション: トランザクションが作業を完了する前に他のトランザクションによる行の変更を防ぐために本当にロックが必要な場合にのみ使用します。特定のシナリオでは、アドバイザリロックの方が適している場合があることを検討します。

6. アドバイザリロック

  • ルール: データベースオブジェクトのロックに直接マッピングされないアプリケーションレベルのロックやより複雑な同期ニーズには、PostgreSQLのアドバイザリロックが強力なツールになります。
  • アクション: pg_advisory_lock()、pg_advisory_lock_shared()、pg_advisory_unlock()などの関数を使用して、カスタムロックメカニズムを実装します。これらのロックはデッドロック検出メカニズムによって自動的に検出されないため、アプリケーションロジックで注意深く管理する必要があります。

7. 操作のバッチ処理

  • ルール: 多数の個別のUPDATEやDELETEステートメントを発行する代わりに、それらを1つのステートメントにバッチ処理するか、可能な場合は一括ロード/更新にCOPYを使用することを検討します。
  • アクション: 単一のUPDATEステートメントは、個別のUPDATEのループよりも効率的にロックを取得する場合があります。バッチ操作のロック動作を分析します。

実践的なトリアージフロー

インシデントが発生している場合、頭の中の最も古い理論ではなく、待機中のセッションから始めます。

SELECT
    now() - a.query_start AS waiting_for,
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

1つのブロックPIDが何度も表示される場合は、それを調査します。

SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid = 12345;

注目すべきフレーズはidle in transactionです。そのセッションはアクティブに有用なデータベース作業を行っていませんが、ロックを保持している可能性があります。これは多くの場合、トランザクションを開き、クエリを実行し、外部APIを呼び出し、APIが戻ってからコミットするアプリケーションコードから発生します。可能であれば、外部呼び出しをトランザクションの外に移動します。

注意してキャンセルします。SELECT pg_cancel_backend(pid)は現在のクエリを停止するよう要求します。SELECT pg_terminate_backend(pid)はセッションを強制終了し、そのオープントランザクションをロールバックします。本番インシデントでは、ブロッカーを終了するのが正しい判断かもしれませんが、後でコードパスを修正できるように、まずクエリとトランザクションの経過時間を取得します。

もう1つの習慣は、インシデント後に役立ちます。ブロッククエリ、ブロックされたクエリ、およびアプリケーションログからのトランザクション境界を保存します。SQLステートメントだけでは不十分なことがよくあります。無害なUPDATE users SET last_seen_at = now()も、支払いAPIを待機するトランザクション内にある場合、ブロッカーになる可能性があります。デッドロックの修正は通常、孤立した1つのステートメントではなく、トランザクションフローレベルで行われます。

永続的な修正は通常、明白です。トランザクションを短く保ち、共有リソースに一貫した順序でアクセスし、更新がスキャンしすぎないようにインデックスを追加し、アプリケーションの再試行が40P01とシリアル化失敗を適切に処理するようにします。PostgreSQLはデッドロックを検出できますが、トランザクションパターンを再設計することはできません。その部分は、インシデントが落ち着いた後のアプリケーションとスキーマのレビューに委ねられています。