※この記事にはアフィリエイト広告（プロモーション）が含まれます。
この記事の出力は PostgreSQL 18.6（Ubuntu 26.04）で採りました。
まず見る（結論）
SELECT w.pid AS waiting_pid, w.query AS waiting_query,
b.pid AS blocking_pid, b.state AS blocking_state, b.query AS blocking_query,
now() - b.xact_start AS blocking_xact_age
FROM pg_stat_activity w
JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid) ON true
JOIN pg_stat_activity b ON b.pid = bp.pid;- 止めている側の
stateがidle in transactionなら、pg_cancel_backendでは外れない。pg_terminate_backendで切る blocking_queryは止めている側が最後に実行した文で、ロックを取った文とは限らない
待っている接続を見つける
セッション A が id=1 を UPDATE したままコミットせず、セッション B が同じ行を UPDATE したときの pg_stat_activity です。
SELECT pid, state, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity WHERE datname = 'fsv' AND pid <> pg_backend_pid();
pid | state | wait_event_type | wait_event | blocked_by | query
------+--------+-----------------+---------------+------------+---------------------------------------------
1796 | active | Timeout | PgSleep | {} | ... BEGIN; UPDATE acct SET bal=bal-10 WHERE ...
1801 | active | Lock | transactionid | {1796} | ... UPDATE acct SET bal=bal+10 WHERE id=1;wait_event_type が Lock の行が待っている接続で、pg_blocking_pids が止めている側の pid を配列で返します。行ロックの待ちは wait_event が transactionid、表ロックの待ちは relation になりました。
pg_locks を見ると、待っている側は locktype が transactionid・mode が ShareLock・granted が f の行を持っていました。待つ対象は行ではなく、相手のトランザクション ID です。
idle in transaction が握ったまま
アプリやツールが BEGIN → UPDATE のあとコミットせずに止まると、その接続は idle in transaction になり、ロックを持ち続けます。
pid | state | wait_event_type | wait_event | blocked_by | query
------+---------------------+-----------------+---------------+------------+-----------------------------------
1837 | idle in transaction | Client | ClientRead | {} | UPDATE acct SET bal=0 WHERE id=3;
1842 | active | Lock | transactionid | {1837} | ... UPDATE acct SET bal=1 WHERE id=3;| 止めている側に実行 | 戻り値 | ロック |
|---|---|---|
pg_cancel_backend(1837) |
t |
外れない（1842 は待ったまま） |
pg_terminate_backend(1837) |
t |
外れる。1837 はロールバックされ、1842 の UPDATE が通った |
pg_cancel_backend は実行中の文を止める関数です。t が返るので効いたように見えます。切られた側のクライアントには FATAL: terminating connection due to administrator command が出ます。2つの関数の違いは 実行中のクエリを止める記事に書きました。
どれだけ放置されているかは now() - state_change で見ます。繰り返し起きるなら idle_in_transaction_session_timeout（既定 0＝無効）で自動的に切れるようにできます。
ALTER TABLE の後ろに SELECT が並ぶ
開いたままのトランザクション R が表を SELECT しただけの状態で ALTER TABLE を実行し、さらに別の接続 S から SELECT しました。
application_name | state | wait_event_type | wait_event | blocked_by
------------------+---------------------+-----------------+------------+------------
R | idle in transaction | Client | ClientRead | {}
DDL | active | Lock | relation | {R の pid}
S | active | Lock | relation | {DDL の pid}ALTER TABLE は AccessExclusiveLock を待ち、その後ろで S の AccessShareLock も待たされました。pg_blocking_pids を1段だけ見ると ALTER TABLE を止めればよいように見えますが、元は R です。blocked_by を空の行までたどります。
ALTER TABLE に SET lock_timeout = '2s' を付けて実行すると、2秒で ERROR: canceling statement due to lock timeout になりました。待ち続けて後ろに SELECT を溜めることはなくなるので、失敗したら時間をおいてやり直します。ALTER TABLE の書き方は ALTER TABLE の記事にあります。
待つ時間に上限を付ける
| 設定 | 既定 | ロック待ちで返るエラー |
|---|---|---|
lock_timeout |
0（無期限） |
canceling statement due to lock timeout |
statement_timeout |
0（無期限） |
canceling statement due to statement timeout |
statement_timeout は文の実行時間全体の上限なので、ロック待ちでなくても長い文は止まります。ロック待ちだけを切りたいときは lock_timeout を使います。
あとから調べるために log_lock_waits を有効にする
log_lock_waits は既定で off です。on にすると、deadlock_timeout（既定 1s）を超えて待った文がサーバーログに残ります。
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();LOG: process 1934 still waiting for ShareLock on transaction 1225 after 1000.303 ms
DETAIL: Process holding the lock: 1930. Wait queue: 1934.
CONTEXT: while updating tuple (0,4) in relation "acct"
STATEMENT: UPDATE acct SET bal=bal+10 WHERE id=4;
LOG: process 1934 acquired ShareLock on transaction 1225 after 2996.255 msDETAIL に止めていた側の pid が出るので、同じ時刻の pg_stat_activity の記録があれば突き合わせられます。再起動は要りません。デッドロックになった場合は deadlock detected の記事を見てください。
関連: PostgreSQLで実行中のクエリを止める ／ PostgreSQLのALTER TABLE ／ PostgreSQLの「deadlock detected」 ／ MySQLのERROR 1205
