PostgreSQLロック確認SQL｜ブロッキング特定から解除までコピペ集

PostgreSQLでロック待ちを確認するには、pg_blocking_pids(pid) で「待たせている側のPID」を求め、pg_stat_activity と結合して両者のSQLと状態を並べるのが最短です。 解除は、待っている側の文だけ止めるなら pg_cancel_backend、待たせている側の接続ごと終わらせるなら pg_terminate_backend を使います。

この記事では、2〜4つのセッションで行ロックと「ALTER TABLE の待ち行列で後続のSELECTまで止まる」状態を実際に再現し、確認SQLの出力をそのまま載せました。ロックモード8種の競合表と、よく使うSQLが取るモードを pg_locks で実測した一覧、lock_timeout・statement_timeout・idle_in_transaction_session_timeout・log_lock_waits の効果も、PostgreSQL 16.15での実行結果で確認できます。

ロック確認・解除の早見表

検証環境とテーブル

以降の出力は、PostgreSQL 16.15に Python 3.14 + psycopg 3.3.6 から複数セッションを張って再現したものです。 セッションには application_name に sess_A などの名前を付け、どれがどれかわかるようにしています。結果はPythonから取得した値を、読みやすいよう psql と同じ表の形に整えて載せています（値はそのままです）。

CREATE TABLE stock (id int PRIMARY KEY, name text, qty int);
INSERT INTO stock VALUES (1,'ボールペン',100),(2,'ノート',50),(3,'付箋',80);

psql で手元で再現する場合は、ターミナルを2〜4つ開いて SET application_name = 'sess_A'; のように名前を付けてから、各見出しのSQLを順に実行すると同じ状態を作れます。

確認SQL1：誰が誰を止めているか（pg_blocking_pids × pg_stat_activity）

最初に流すのはこのSQLです。 待っているセッションと、待たせているセッションを1行ずつ組にして表示します。

-- 誰が誰を止めているか（PostgreSQL 9.6+）
SELECT w.pid                                       AS waiting_pid,
       w.application_name                          AS waiting_app,
       date_trunc('second', now() - w.query_start) AS waiting_for,
       w.wait_event_type || ':' || w.wait_event    AS wait_event,
       left(w.query, 45)                           AS waiting_query,
       b.pid                                       AS blocking_pid,
       b.application_name                          AS blocking_app,
       b.state                                     AS blocking_state,
       left(b.query, 45)                           AS blocking_query
FROM pg_stat_activity w
CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid)
JOIN pg_stat_activity b ON b.pid = bp.pid
ORDER BY waiting_for DESC;

行ロックの例で実行する

セッションAが BEGIN して在庫を更新したまま、コミットせずに止まっている状態です。セッションBが同じ行を更新しようとして待ちます。

-- sess_A
BEGIN;
UPDATE stock SET qty = qty - 1 WHERE id = 1;
-- （ここで止まる＝コミットしていない）

-- sess_B
UPDATE stock SET qty = qty + 5 WHERE id = 1;   -- 待ちになる

確認SQLの実行結果（3秒後）：

 waiting_pid | waiting_app | waiting_for |     wait_event     |                waiting_query                | blocking_pid | blocking_app |   blocking_state    |                blocking_query
-------------+-------------+-------------+--------------------+---------------------------------------------+--------------+--------------+---------------------+---------------------------------------------
        5383 | sess_B      | 00:00:03    | Lock:transactionid | UPDATE stock SET qty = qty + 5 WHERE id = 1 |         5382 | sess_A       | idle in transaction | UPDATE stock SET qty = qty - 1 WHERE id = 1

読み方のポイントは3つです。

blocking_query は「今ロックを取っているSQL」ではなく「そのセッションが最後に投げたSQL」です。ロックはトランザクションの中でそれより前に取られていることもあるので、blocking_state と xact_start もあわせて見ると判断しやすくなります。

確認SQL2：pg_locks でロックの中身を見る

pg_locks を見ると、どのテーブル・どのモードのロックを、誰が持っていて誰が待っているか（granted）がわかります。

-- ロックの詳細（待ち時間つき。waitstart は PostgreSQL 14+）
SELECT l.pid,
       a.application_name                        AS app,
       l.locktype,
       l.relation::regclass                      AS rel,
       l.mode,
       l.granted,
       l.page, l.tuple,
       l.transactionid                           AS xid,
       date_trunc('second', now() - l.waitstart) AS waited
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype IN ('relation', 'tuple', 'transactionid')
  AND a.pid <> pg_backend_pid()
ORDER BY l.pid, l.granted DESC, l.locktype;

行ロックの例での実行結果（stock 以外の行は省略）：

 pid  |  app   |   locktype    |  rel  |       mode       | granted | page | tuple |  xid  |  waited
------+--------+---------------+-------+------------------+---------+------+-------+-------+----------
 5382 | sess_A | relation      | stock | RowExclusiveLock | t       |      |       |       |
 5382 | sess_A | transactionid |       | ExclusiveLock    | t       |      |       | 35952 |
 5383 | sess_B | relation      | stock | RowExclusiveLock | t       |      |       |       |
 5383 | sess_B | transactionid |       | ExclusiveLock    | t       |      |       | 35953 |
 5383 | sess_B | tuple         | stock | ExclusiveLock    | t       |    0 |     1 |       |
 5383 | sess_B | transactionid |       | ShareLock        | f       |      |       | 35952 | 00:00:03

行ロックの待ちは、pg_locks では「相手のトランザクションID（xid）への ShareLock 待ち」として表れます。 最後の行が「B（5383）が A のトランザクション 35952 の終了を待っている」ことを示しています。

PostgreSQLは更新した行の情報を pg_locks に1行ずつは載せず、行ヘッダーに記録します。そのため「どの行か」は tuple の行や、後述の log_lock_waits のログ（while updating tuple (0,1) in relation "stock"）で確認します。

確認SQL3：idle in transaction（放置トランザクション）を見つける

ロック待ちの多くは、待たせている側が idle in transaction（BEGIN したまま何もしていない）状態です。

-- 1秒以上放置されているトランザクション（本番では '1 minute' などに変える）
SELECT pid,
       application_name                           AS app,
       usename,
       state,
       date_trunc('second', now() - xact_start)   AS xact_age,
       date_trunc('second', now() - state_change) AS idle_for,
       left(query, 45)                            AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '1 second'
ORDER BY state_change;

 pid  |  app   | usename |        state        | xact_age | idle_for |                 last_query
------+--------+---------+---------------------+----------+----------+---------------------------------------------
 5382 | sess_A | t98     | idle in transaction | 00:00:03 | 00:00:03 | UPDATE stock SET qty = qty - 1 WHERE id = 1

ALTER TABLE の待ち行列で後続まで止まる例

ALTER TABLE が待ちに入ると、その後ろに来た普通の SELECT や UPDATE まで待たされます。 ロック確認が必要になる典型的な場面なので、実際に再現しました。

-- sess_A：読み取りだけのトランザクションを開いたまま
BEGIN;
SELECT count(*) FROM stock;          -- AccessShareLock を持ち続ける

-- sess_B：列追加（AccessExclusiveLock が必要なので A を待つ）
ALTER TABLE stock ADD COLUMN note text;

-- sess_C：普通の読み取り
SELECT * FROM stock WHERE id = 2;    -- A とは競合しないのに待つ

-- sess_D：普通の更新
UPDATE stock SET qty = 10 WHERE id = 3;  -- これも待つ

pg_blocking_pids で待ちの連鎖を見る

SELECT pid, application_name AS app, state,
       pg_blocking_pids(pid) AS blocked_by,
       date_trunc('second', now() - xact_start) AS xact_age,
       left(query, 45) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid()
  AND datname = current_database()
ORDER BY xact_start;

 pid  |  app   |        state        | blocked_by | xact_age |                 query
------+--------+---------------------+------------+----------+----------------------------------------
 5392 | sess_A | idle in transaction | {}         | 00:00:04 | SELECT count(*) FROM stock
 5393 | sess_B | active              | {5392}     | 00:00:04 | ALTER TABLE stock ADD COLUMN note text
 5394 | sess_C | active              | {5393}     | 00:00:03 | SELECT * FROM stock WHERE id = 2
 5395 | sess_D | active              | {5393}     | 00:00:03 | UPDATE stock SET qty = 10 WHERE id = 3

C と D を止めているのは A ではなく B（ALTER TABLE）と表示されます。 これは、PostgreSQLのロック待ちが順番待ちの列になっていて、先に並んだ B の AccessExclusiveLock 要求と、C の AccessShareLock が競合するためです。pg_blocking_pids は「すでに持っている人」だけでなく「列の前に並んでいて競合する人」も返します。

flowchart LR
    A["sess_A<br>idle in transaction<br>AccessShareLock 取得済み"]
    B["sess_B<br>ALTER TABLE<br>AccessExclusiveLock 待ち"]
    C["sess_C<br>SELECT<br>AccessShareLock 待ち"]
    D["sess_D<br>UPDATE<br>RowExclusiveLock 待ち"]
    B -- 待つ --> A
    C -- 待つ --> B
    D -- 待つ --> B

同じ時点の pg_locks：

 pid  |  app   |   locktype    |  rel  |        mode         | granted | xid   |  waited
------+--------+---------------+-------+---------------------+---------+-------+----------
 5392 | sess_A | relation      | stock | AccessShareLock     | t       |       |
 5393 | sess_B | transactionid |       | ExclusiveLock       | t       | 35954 |
 5393 | sess_B | relation      | stock | AccessExclusiveLock | f       |       | 00:00:04
 5394 | sess_C | relation      | stock | AccessShareLock     | f       |       | 00:00:03
 5395 | sess_D | relation      | stock | RowExclusiveLock    | f       |       | 00:00:03

A を COMMIT すると、B・C・D がまとめて進みました。

[sess_B] done in 4.0s
[sess_C] done in 3.0s
[sess_D] done in 3.0s

根本の原因（列の先頭）を探すには、blocked_by が {} で、かつ他のセッションの blocked_by に出てくるPIDを見ます。 次のSQLで先頭だけを取り出せます。

-- 待ちの連鎖の先頭（自分は誰にも止められておらず、誰かを止めているセッション）
SELECT a.pid, a.application_name, a.state,
       date_trunc('second', now() - a.xact_start) AS xact_age,
       left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) = 0
  AND a.pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity);

同じ状態を作り直して実行すると、列の先頭にいる sess_A だけが返りました（PIDは再現のたびに変わります）。

  pid  | application_name |        state        | xact_age |           query
-------+------------------+---------------------+----------+----------------------------
 10799 | sess_A           | idle in transaction | 00:00:01 | SELECT count(*) FROM stock

ロックモード8種の競合表

テーブルレベルのロックは8種類あり、名前に「Row」が付いていてもすべてテーブル単位のロックです。 ×が付いた組み合わせは同時に持てず、後から来た側が待ちます（公式ドキュメント「明示的ロック」の表と同じ内容です）。

覚え方：ACCESS EXCLUSIVE はすべてと競合し、ACCESS SHARE（普通のSELECT）は ACCESS EXCLUSIVE とだけ競合します。 普通のSELECTが止まるときは、ほぼ必ず ACCESS EXCLUSIVE を取る操作（ALTER TABLE など）が関係しています。

よく使うSQLが取るロックモード一覧

次の表は、各SQLをトランザクション内で実行した直後に pg_locks を見て確認したものです（PostgreSQL 16.15。「公式」はトランザクション内で実行できないため公式ドキュメントで確認したもの）。

確認に使ったコードの要点は次のとおりです。各SQLを実行し、同じトランザクション内で自分のPIDのロックを読み、最後にロールバックしています。

# Python 3.14 / psycopg 3.3.6
import psycopg
c = psycopg.connect("host=127.0.0.1 dbname=t98 user=t98 password=...", autocommit=True)
for s in ["SELECT * FROM stock2", "CREATE INDEX ON stock2 (qty)", "ALTER TABLE stock2 ADD COLUMN note text"]:
    with c.transaction(force_rollback=True):   # 最後に必ずロールバック
        c.execute(s)
        rows = c.execute("""SELECT relation::regclass::text, mode FROM pg_locks
                            WHERE pid = pg_backend_pid() AND locktype = 'relation'
                              AND relation = 'stock2'::regclass""").fetchall()
        print(f"{s:45s} -> {rows}")

SELECT * FROM stock2                          -> [('stock2', 'AccessShareLock')]
CREATE INDEX ON stock2 (qty)                  -> [('stock2', 'ShareLock')]
ALTER TABLE stock2 ADD COLUMN note text       -> [('stock2', 'AccessExclusiveLock')]

行レベルのロック（FOR UPDATE・FOR NO KEY UPDATE・FOR SHARE・FOR KEY SHARE）の違いや使い分けは、楽観ロック・悲観ロックの記事で扱っているため、ここでは確認と解除に絞ります。

ロックを解除する：pg_cancel_backend と pg_terminate_backend

まず pg_cancel_backend で待っている文や重い文を取り消し、それで解消しないとき（待たせている側が idle in transaction など）に pg_terminate_backend で接続を終了します。

実際に実行する

行ロックの例（A が idle in transaction、B が待ち）で、まず B を取り消します。

SELECT pg_cancel_backend(5383);   -- B

 pg_cancel_backend
-------------------
 t

B 側に返ったエラー：

ERROR:  canceling statement due to user request
CONTEXT:  while updating tuple (0,1) in relation "stock"

B の待ちは消えますが、原因の A は idle in transaction のまま残ります。

 pid  |  app   |        state        | blocked_by | xact_age |                    query
------+--------+---------------------+------------+----------+---------------------------------------------
 5382 | sess_A | idle in transaction | {}         | 00:00:03 | UPDATE stock SET qty = qty - 1 WHERE id = 1
 5383 | sess_B | idle                | {}         |          | UPDATE stock SET qty = qty + 5 WHERE id = 1

続いて A の接続を終了します。

SELECT pg_terminate_backend(5382);   -- A

 pg_terminate_backend
----------------------
 t

A 側で次にSQLを送ると、接続が切れていることがわかります。

FATAL:  terminating connection due to administrator command

A の更新（qty - 1）はコミットされていないため取り消され、在庫は元の100のままでした。

 id |    name    | qty
----+------------+-----
  1 | ボールペン | 100

判断フロー

flowchart TD
    S["確認SQL1で待ちを確認"] --> Q1{"待たせている側の state は？"}
    Q1 -- "active（重いクエリ実行中）" --> C1["待たせている側に pg_cancel_backend"]
    Q1 -- "idle in transaction" --> Q2{"持ち主に連絡して<br>COMMIT / ROLLBACK できる？"}
    Q2 -- できる --> R["持ち主に終了してもらう"]
    Q2 -- できない --> T["pg_terminate_backend<br>（未コミットの変更は取り消し）"]
    C1 --> Q3{"解消した？"}
    Q3 -- しない --> T
    Q3 -- した --> E["timeout 設定で再発を防ぐ"]
    R --> E
    T --> E

解除前のチェックリスト：

再発を防ぐ設定：4つのタイムアウトとログ

ロック待ちを長引かせないための設定は、lock_timeout・statement_timeout・idle_in_transaction_session_timeout・log_lock_waits の4つが基本です。 いずれも PostgreSQL 16 での実行結果です。

公式ドキュメントでは、statement_timeout などを postgresql.conf で全体に設定することは推奨されていません。ALTER ROLE ... SET や ALTER DATABASE ... SET、アプリの接続設定で対象を絞って設定するのがおすすめです。

lock_timeout：ALTER TABLE を先にあきらめさせる

前の「待ち行列」の例で、ALTER TABLE の前に lock_timeout を付けました。

-- sess_A：BEGIN; SELECT count(*) FROM stock;（開いたまま）

-- sess_B
SET lock_timeout = '2s';
ALTER TABLE stock ADD COLUMN note text;

-- sess_C（B の0.5秒後）
SELECT * FROM stock WHERE id = 2;

[sess_C] done in 1.5s
[sess_B] LockNotAvailable: canceling statement due to lock timeout

B は2秒であきらめてエラーになり、後ろに並んでいた C は B が抜けた時点（1.5秒待ち）で結果を得ました。 lock_timeout がないときは、C は A が終わるまで待ち続けていました。DDLを流すときは SET lock_timeout を付け、エラーになったら時間をおいて再実行する手順にしておくと、他の処理への影響を小さくできます。

-- マイグレーションの定型
SET lock_timeout = '3s';
ALTER TABLE stock ADD COLUMN note text;
-- エラーなら数秒待って再実行

statement_timeout：長い文を打ち切る

SET statement_timeout = '1s';
SELECT pg_sleep(3);

ERROR:  canceling statement due to statement timeout

1.0秒で打ち切られました。lock_timeout と違い、ロック待ちに限らず実行時間全体に効きます。両方使う場合は、lock_timeout を statement_timeout より短くしないと lock_timeout が働く前に statement_timeout が先に切れます。

idle_in_transaction_session_timeout：放置トランザクションを自動で終わらせる

SET idle_in_transaction_session_timeout = '2s';
BEGIN;
UPDATE stock SET qty = 0 WHERE id = 2;
-- 3秒何もしない
COMMIT;

FATAL:  terminating connection due to idle-in-transaction timeout

接続が切断され、UPDATE は取り消されました（id=2 の qty は 50 のまま）。pg_terminate_backend を自動で行うイメージです。アプリ側では接続切れとして扱われるので、接続プールの再接続設定とあわせて使います。

log_lock_waits：ロック待ちをログに残す

-- スーパーユーザーで（DB単位の例）
ALTER DATABASE t98 SET log_lock_waits = on;
SHOW deadlock_timeout;   -- 1s（この時間を超えた待ちが記録される）

行ロックの例と待ち行列の例で出力されたログ（PostgreSQL 16のサーバーログ）：

LOG:  process 5383 still waiting for ShareLock on transaction 35952 after 1000.094 ms
DETAIL:  Process holding the lock: 5382. Wait queue: 5383.
CONTEXT:  while updating tuple (0,1) in relation "stock"
STATEMENT:  UPDATE stock SET qty = qty + 5 WHERE id = 1

LOG:  process 5393 still waiting for AccessExclusiveLock on relation 122999 of database 122955 after 1000.161 ms
DETAIL:  Process holding the lock: 5392. Wait queue: 5393.
STATEMENT:  ALTER TABLE stock ADD COLUMN note text

LOG:  process 5393 acquired AccessExclusiveLock on relation 122999 of database 122955 after 4005.619 ms
STATEMENT:  ALTER TABLE stock ADD COLUMN note text

「誰が持っていて（Process holding the lock）、誰が並んでいるか（Wait queue）」と、どの行で待ったか（tuple (0,1)）がログに残ります。 待ちが解消したときも acquired ... after 4005.619 ms と待ち時間が記録されるので、あとから調べるときに役立ちます。relation 122999 は SELECT 122999::regclass; でテーブル名に変換できます。

なお、deadlock_timeout はデッドロック検出を始めるまでの時間でもあります。デッドロック自体の原因と対策は、デッドロックの記事で扱っています。

よくある質問

Q. pg_locks に行ロックが1行ずつ出てこないのはなぜですか？ A. PostgreSQLは行ロックを行のヘッダーに記録し、pg_locks には載せないためです。 行ロックの待ちは「相手のトランザクションID（transactionid）への ShareLock 待ち」として表れます。どの行かは tuple の行や log_lock_waits のログの CONTEXT で確認できます。

Q. pg_cancel_backend を実行したのにロック待ちが解消しません。 A. 待たせている側が idle in transaction だと、実行中の文がないため cancel は効きません。 その場合は持ち主に COMMIT / ROLLBACK してもらうか、pg_terminate_backend で接続を終了します。

Q. 普通の SELECT が止まっているのはなぜですか？ A. ACCESS EXCLUSIVE を要求している操作（ALTER TABLE、TRUNCATE、VACUUM FULL など）が待ちの列の前にいる可能性が高いです。 pg_blocking_pids でSELECTを止めている PID を見ると、その操作が表示されます。さらにその操作を止めている先頭（多くは idle in transaction）を解消します。

Q. 権限がなくて他のユーザーのクエリ内容が見えません。 A. pg_stat_activity の query 列は、同じロールか pg_read_all_stats ロールを持つユーザーにだけ表示されます。解除の関数を使うには、同じロール、pg_signal_backend ロール、またはスーパーユーザーである必要があります。

まとめ

• 最初に流すのは「pg_blocking_pids × pg_stat_activity」の確認SQL。waiting_pid と blocking_pid の組で原因がわかる
• 行ロックの待ちは pg_locks では transactionid への ShareLock 待ちとして表れ、waitstart（PG14+）で待ち時間が取れる
• ALTER TABLE が待つと、後ろの普通の SELECT・UPDATE も待ちの列に並ぶ。先頭は blocked_by が空で誰かを止めているセッション
• ACCESS SHARE（SELECT）は ACCESS EXCLUSIVE とだけ競合。ALTER TABLE・TRUNCATE・VACUUM FULL などが ACCESS EXCLUSIVE を取る
• 解除はまず pg_cancel_backend、idle in transaction の相手には pg_terminate_backend（未コミットの変更は取り消し）
• DDLの前には SET lock_timeout。後続を長く止めずに済む
• idle_in_transaction_session_timeout と log_lock_waits で放置トランザクションの自動終了と記録ができる

株式会社RJCでは、PostgreSQLのロックの確認と解除の手順を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。