잠금과 대기 세션

#핵심 요약

실무에서 가장 흔한 잠금 장애는 누군가 BEGIN 후 UPDATE하고 커밋하지 않아 다른 세션이 같은 행을 기다리는 경우임

psql은 기본 자동 커밋이라 잠금을 붙잡아 두려면 BEGIN으로 트랜잭션을 먼저 열어야 함(Oracle과 다른 점)

기다리는 세션은 active이고 wait_event_type = Lock으로 보이지만, 원인 세션은 idle in transaction이라 state = 'active' 조회에 나오지 않음

pg_blocking_pids(pid)가 그 세션을 막는 PID 배열을 돌려주므로 이것으로 홀더를 찾고, 홀더의 query에 남은 마지막 SQL을 봄

#개념과 동작 원리

#필수 명령어 또는 SQL

#잠금 재현 (창 A, B)

create table lock_demo (id int primary key, cnt int);
insert into lock_demo values (1, 0);
begin;
update lock_demo set cnt = cnt + 1 where id = 1;
-- COMMIT 하지 않고 둠

update lock_demo set cnt = cnt + 1 where id = 1;
-- 창 A의 잠금을 기다리며 멈춤

결과 해석: 개별 실행 출력은 기재 없음

#지금 실행 중인 세션과 대기 이벤트 (창 C)

select pid, usename, state,
       wait_event_type as wtype, wait_event,
       extract(epoch from now() - query_start)::int as sec,
       left(query, 40) as query
  from pg_stat_activity
 where backend_type = 'client backend'
   and state = 'active'
   and pid <> pg_backend_pid()
 order by sec desc;

결과 해석:

뷰에는 시작 시각만 있어 몇 초째인지는 now() - query_start로 직접 계산함

left(query, 40)은 SQL을 잘라 한 줄에 맞추고, pg_backend_pid() 조건은 이 조회를 돌리는 자기 세션을 뺌

실습 기록: 창 B(PID 32587) 한 행이 active | Lock | transactionid | 8로 나오고 원인인 창 A는 보이지 않음

psql의 \watch 3으로 3초마다 다시 돌릴 수 있지만 화면이 계속 밀려 비교가 어려움

#누가 누구를 막는지, 홀더의 마지막 SQL (창 C)

select w.pid as waiter,
       w.wait_event_type || ': ' || w.wait_event as event,
       extract(epoch from now() - w.query_start)::int as sec,
       pg_blocking_pids(w.pid) as blocked_by
  from pg_stat_activity w
 where cardinality(pg_blocking_pids(w.pid)) > 0;

select h.pid, h.state,
       extract(epoch from now() - h.state_change)::int as idle_sec,
       left(h.query, 45) as last_query
  from pg_stat_activity h
 where h.pid in (select unnest(pg_blocking_pids(pid)) from pg_stat_activity);

결과 해석:

실습 기록: 웨이터 32587 | Lock: transactionid | 8 | {32578}, 홀더 32578 | idle in transaction | 11 | update lock_demo set cnt = cnt + 1 where id =

홀더 PID는 배열 안에 있어 unnest()로 풀어야 찾을 수 있음

idle_sec는 상태가 idle in transaction으로 바뀐 뒤 흐른 시간이며 트랜잭션 전체 시간은 xact_start로 봄

웨이터가 여럿이면 홀더 ← 웨이터 1 ← 웨이터 2처럼 줄을 서므로 맨 앞 홀더를 찾으려면 배열을 따라 여러 번 올라감

세 쿼리를 거쳐서야 "창 A가 BEGIN 후 UPDATE하고 커밋하지 않아 창 B가 기다린다"는 그림이 나오며, 세션을 끊는 방법은 세션 종료와 영향에서 다룸

#누적 통계로 초당 수치 구하기 (창 C)

select sum(xact_commit) as commits,
       sum(blks_read)   as disk_reads,
       sum(blks_hit)    as cache_hits,
       sum(tup_returned + tup_fetched) as rows_read
  from pg_stat_database;
select pg_sleep(10);
select sum(xact_commit) as commits,
       sum(blks_read)   as disk_reads,
       sum(blks_hit)    as cache_hits,
       sum(tup_returned + tup_fetched) as rows_read
  from pg_stat_database;

결과 해석:

pg_stat_database의 값은 통계가 초기화된 뒤 쌓인 누적값이라, 두 번 재서 빼고 간격(10초)으로 나눠야 초당 커밋, 디스크 읽기, 캐시 적중, 읽은 행 수가 됨

이 값은 DB 전체의 부하 흐름이며 앞의 잠금이 풀렸는지와는 관계없음

#사용 조건과 주의사항

마지막 SQL은 잘린 문자열이고 같은 트랜잭션 안의 앞선 SQL은 보이지 않으므로, 홀더가 무엇을 바꿨는지는 애플리케이션과 함께 확인함

idle in transaction이 오래 남지 않게 하려면 idle_in_transaction_session_timeout 같은 설정을 검토함

실습이 끝나면 창 A에서 rollback;(또는 commit;)으로 잠금을 풀고 lock_demo를 지움