[PostgreSQL] 쿼리가 멈춰 있을 때 누가 누구를 막고 있나 — pg_locks와 pg_blocking_pids

설치·접속: PostgreSQL 설치와 접속

부제: 같은 행을 두 세션이 UPDATE하면 뒤에 온 쪽이 대기에 걸린다 — 막은 놈과 막힌 놈을 한 줄로 잇는다

-- 세션 A: BEGIN 후 id=1 행을 UPDATE하고 커밋 안 함 (락 보유)
-- 세션 B: 같은 id=1 행을 UPDATE 시도 → 대기 상태로 멈춤
-- 감시 세션에서: 막힌 세션과 막은 세션을 잇는다
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       left(blocked.query,35) AS blocked_query, blocked.wait_event_type AS wait
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

 blocked_pid | blocking_pid |            blocked_query            | wait
-------------+--------------+-------------------------------------+------
       10382 |        10377 | UPDATE users SET name=name WHERE id  | Lock

pg_blocking_pids(pid)는 "이 세션을 막고 있는 다른 세션 목록"을 배열로 돌려주는 함수라, 이걸 pg_stat_activity와 조인하면 막힌 pid(10382) ← 막은 pid(10377) 관계가 한 줄로 나온다. 락 경합 조사의 정석이다. 실제로 각 세션이 어떤 락을 잡고 무엇을 기다리는지는 pg_locks에서 granted 컬럼으로 드러난다.

SELECT pid, locktype, mode, granted FROM pg_locks
WHERE relation='users'::regclass OR locktype='transactionid'
ORDER BY pid, granted DESC;

  pid  |   locktype    |       mode       | granted
-------+---------------+------------------+---------
 10599 | relation      | RowExclusiveLock | t         -- A: 획득 완료
 10599 | transactionid | ExclusiveLock    | t
 10604 | relation      | RowExclusiveLock | t         -- B: 테이블 락은 얻었지만
 10604 | tuple         | ExclusiveLock    | t
 10604 | transactionid | ExclusiveLock    | t
 10604 | transactionid | ShareLock        | f         -- ★ 이 한 줄이 대기의 정체

granted=f인 줄이 곧 "이 락을 아직 못 받아 멈춰 있다"는 신호다. 세션 B(10604)는 A의 트랜잭션이 끝나기를 기다리며 그 트랜잭션 ID에 ShareLock을 걸려다 막혔다. PostgreSQL의 행 락은 테이블이 아니라 이렇게 상대 트랜잭션이 끝날 때까지 기다리는 형태로 구현된다. 즉 B를 풀려면 A가 커밋/롤백하거나, A를 종료시켜야 한다.

이렇게도 쓴다

막힌 세션을 유발한 락의 종류까지 한 번에 본다. (조합: pg_locks 셀프조인)

SELECT w.pid AS waiting, l.pid AS holding, w.mode AS want, l.mode AS held
FROM pg_locks w JOIN pg_locks l
  ON w.transactionid = l.transactionid AND w.pid <> l.pid
WHERE NOT w.granted;

 

락을 오래 붙든 원흉 세션만 종료한다. (조합: pg_terminate_backend)

SELECT pg_terminate_backend(10377);   -- 막은 쪽을 끊으면 막힌 쪽이 즉시 진행

 

대기하지 말고 "잠겨 있으면 즉시 에러"로 실패시킨다. (조합: NOWAIT)

SELECT * FROM users WHERE id=1 FOR UPDATE NOWAIT;

 

무한정 기다리지 않게 락 대기 시간 상한을 건다. (조합: lock_timeout)

SET lock_timeout = '3s';   -- 3초 안에 락 못 얻으면 쿼리 취소

 

테이블 단위 락(ALTER 등)이 걸린 상황을 본다. (조합: relation 필터)

SELECT pid, mode, granted FROM pg_locks
WHERE relation='orders'::regclass AND locktype='relation';

 

교착(deadlock)이 언제 몇 번 났는지 로그에서 확인한다. (조합: 로그)

grep -i deadlock /var/log/postgresql/postgresql-16-main.log