락과 동시성 — SELECT FOR UPDATE

트랜잭션 두 개가 같은 데이터를 동시에 만지면? PostgreSQL 은 락으로 정리합니다. 행 단위·테이블 단위·advisory 락까지 종류가 다양 — 15편은 매일 만나는 락 패턴과 데드락 진단, 그리고 pg_locks/pg_stat_activity 로 락 상태 보는 법까지.

락의 두 축 — 무엇을 / 얼마나 강하게

SELECT FOR UPDATE — 가장 자주 쓰는 행 락

BEGIN;
  SELECT stock FROM inventory WHERE id = 5 FOR UPDATE;
  -- 다른 TX 가 이 행을 UPDATE·DELETE·FOR UPDATE 시도하면 대기

  IF stock > 0 THEN
    UPDATE inventory SET stock = stock - 1 WHERE id = 5;
  END IF;
COMMIT;

FOR SHARE · FOR KEY SHARE — 가벼운 락

-- FOR SHARE: 읽기만 하지만 "이 행 바꾸지 마"
SELECT * FROM users WHERE id = 5 FOR SHARE;
-- 다른 TX 는 SELECT FOR SHARE 는 OK, UPDATE 는 대기

-- FOR KEY SHARE (가장 약함): FK 참조 시 자동 사용
-- 다른 TX 의 UPDATE 는 허용, DELETE 만 차단

NOWAIT · SKIP LOCKED — 락 대기 회피

-- 이미 락 걸려있으면 즉시 에러
SELECT * FROM jobs WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row

-- 잠긴 행은 건너뛰기 — 작업 큐 패턴!
BEGIN;
  SELECT * FROM jobs
  WHERE  status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED;
  -- 다른 워커가 잠근 행은 보이지 않음 → 자연스러운 분산 큐
  -- 작업 처리...
  UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

테이블 락 — DDL 의 함정

-- 컬럼 추가는 ACCESS EXCLUSIVE 락 (모든 SELECT 도 막힘)
ALTER TABLE big_table ADD COLUMN new_col INT;
-- 큰 테이블이면 수 분 동안 서비스 정지

-- 안전한 패턴
ALTER TABLE big_table ADD COLUMN new_col INT NULL;   -- NULL 허용 → 즉시
-- 백필은 별도 배치로
-- NOT NULL 은 나중에 (PG 12+ 는 DEFAULT 와 함께면 빠름)

-- LOCK 직접
BEGIN;
  LOCK TABLE big_table IN ACCESS EXCLUSIVE MODE;
  -- ...
COMMIT;

데드락 — A 가 B 를 기다리고 B 가 A 를 기다림

-- TX1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
-- (잠시 후 B 도 UPDATE 하려는데)
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';   -- TX2 가 B 락 보유 → 대기

-- TX2 (동시)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'B';
UPDATE accounts SET balance = balance + 100 WHERE id = 'A';   -- TX1 이 A 락 보유 → 대기

-- 1초 뒤 PostgreSQL 이 감지하고 한 쪽을 abort:
-- ERROR: deadlock detected

락 상태 보기 — pg_locks · pg_stat_activity

-- 현재 활성 락
SELECT pid, relation::regclass, mode, granted
FROM   pg_locks
WHERE  NOT granted;        -- 대기 중인 것만

-- 누가 무엇을 하는 중인지
SELECT pid, usename, state, query_start, query
FROM   pg_stat_activity
WHERE  state != 'idle'
ORDER BY query_start;

-- 락 대기 체인 (블로커 추적)
SELECT
  blocked.pid          AS blocked_pid,
  blocked_act.query    AS blocked_query,
  blocking.pid         AS blocking_pid,
  blocking_act.query   AS blocking_query
FROM   pg_locks blocked
JOIN   pg_stat_activity blocked_act ON blocked_act.pid = blocked.pid
JOIN   pg_locks blocking ON blocking.locktype = blocked.locktype
                        AND blocking.granted = true
                        AND blocking.pid != blocked.pid
JOIN   pg_stat_activity blocking_act ON blocking_act.pid = blocking.pid
WHERE  NOT blocked.granted;

-- 강제 종료 (긴급)
SELECT pg_cancel_backend(pid);   -- 부드럽게
SELECT pg_terminate_backend(pid);-- 강제 (마지막 수단)

advisory 락 — 애플리케이션이 만드는 락

-- 행이 아니라 임의의 숫자 키에 락
SELECT pg_advisory_lock(123);   -- 세션 종료까지
-- ... 임계 구역 ...
SELECT pg_advisory_unlock(123);

-- 트랜잭션 단위
SELECT pg_advisory_xact_lock(123);
-- 트랜잭션 끝나면 자동 해제

-- 시도 (블록 안 함)
SELECT pg_try_advisory_lock(123);   -- true 면 획득, false 면 못 함

-- 사용 예 — "이 사용자의 결제는 한 번에 하나만"
SELECT pg_advisory_xact_lock(hashtext('payment-user-' || user_id));

락 통계 — 락 통한 병목 찾기

-- 락 대기 시간 (확장 필요)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, total_exec_time,
       (blk_read_time + blk_write_time) AS io_time
FROM   pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- 락 대기 통계 (PG 14+)
SELECT wait_event_type, wait_event, count(*)
FROM   pg_stat_activity
WHERE  wait_event_type = 'Lock'
GROUP BY wait_event_type, wait_event;

한 줄 가이드

• "읽고 → 검사 → 쓰기" 패턴 → SELECT FOR UPDATE.
• 작업 큐 → SELECT FOR UPDATE SKIP LOCKED.
• 여러 행 락 → 일관된 순서로 ORDER BY (데드락 회피).
• DDL은 짧고 작게 — 큰 테이블에 ALTER 직격 금지.
• 락 상황 모니터링 → pg_stat_activity + pg_locks 쿼리 즐겨찾기.
• 임의 키 락 → pg_advisory_xact_lock.