7장. 트랜잭션과 격리 수준

"동시에 두 사람이 같은 계좌를 만지면?" — 이 질문의 모든 답이 격리 수준입니다. Dirty Read부터 Serialization Anomaly까지, 그리고 Lock과 Deadlock의 실전 대응까지 다룹니다.

7.1 ACID 복습 — 이 장의 주인공은 I

7.2 이상현상 실험실 — 같은 시나리오, 다른 격리 수준

SQL 표준이 정의한 이상현상은 Dirty Read(미커밋 값 읽음), Non-repeatable Read(같은 행이 변함), Phantom Read(행 개수가 변함), 그리고 PostgreSQL이 중요하게 다루는 Serialization Anomaly입니다. 두 트랜잭션을 나란히 세워두고, 격리 수준에 따라 무엇이 달라지는지 직접 봅시다.

-- Repeatable Read의 대가: 쓰기 충돌 시 에러 → 재시도 로직 필수
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 1000 (스냅샷 고정)
-- 그 사이 T2가 같은 행을 UPDATE & COMMIT
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update (40001)
ROLLBACK;  -- 애플리케이션이 처음부터 재시도해야 한다

7.3 Serializable — 스냅샷도 못 막는 Write Skew

Repeatable Read조차 못 막는 미묘한 버그가 있습니다. 각자 "읽고 → 판단하고 → 다른 행을 쓰는" 두 트랜잭션이 교차하면, 개별로는 멀쩡한데 합치면 제약이 깨지는 결과가 나옵니다. 병원 당직 시스템으로 봅시다 — 규칙: 최소 한 명은 당직.

-- 세션 기본 격리 수준
SET default_transaction_isolation = 'read committed';  -- 기본 유지 권장

-- 필요한 트랜잭션만 올린다
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 읽기 전용 리포트라면: 재시도 없는 안전 스냅샷을 기다림
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

7.4 Lock — MVCC가 못 없애는 최후의 줄서기

MVCC 덕에 읽기·쓰기는 서로 안 막지만, 쓰기끼리·DDL은 Lock이 필요합니다. 테이블 레벨 Lock은 8단계나 되지만, 운영에서 기억할 것은 두 개입니다: INSERT/UPDATE/DELETE가 잡는 ROW EXCLUSIVE, 그리고 DROP·TRUNCATE·대부분의 ALTER가 잡는 ACCESS EXCLUSIVE — 후자는 SELECT까지 차단하므로 DDL 실수가 곧 서비스 장애가 됩니다.

행 레벨 Lock은 SELECT … FOR UPDATE 계열로 명시적으로 잡을 수 있습니다. 특히 SKIP LOCKED는 큐 워커 패턴의 표준입니다.

-- 큐 소비자 패턴: 여러 워커가 경합 없이 서로 다른 작업을 집는다
BEGIN;
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED   -- 이미 잠긴 행은 건너뛴다 (9.5+)
LIMIT 10;
-- ... 처리 ...
UPDATE jobs SET status = 'done' WHERE id = ANY(...);
COMMIT;

7.5 Deadlock — 서로를 기다리다 함께 멈춘다

A가 B의 Lock을 기다리고, B가 A의 Lock을 기다리면 영원히 못 풉니다. PostgreSQL은 deadlock_timeout(기본 1초) 후 대기 그래프에서 사이클을 찾아 한쪽을 강제 abort합니다.

• 접근 순서 고정 — 모든 트랜잭션이 같은 순서로 행·테이블에 접근.
• 트랜잭션은 짧게 — Lock 보유 시간 최소화.
• UPSERT 활용 — 같은 키 경합은 INSERT … ON CONFLICT DO UPDATE 한 문장으로.
• log_lock_waits = on — deadlock 직전의 긴 대기를 로그로 남겨 사전 감지.

7.6 실전 진단 — 누가 누구를 막고 있나

-- 블로킹 체인 추적 (9.6+)
SELECT blocked.pid  AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid  AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL (
    SELECT pid, query FROM pg_stat_activity
    WHERE pid = ANY(pg_blocking_pids(blocked.pid))
) blocking ON true
WHERE blocked.wait_event_type = 'Lock';

-- 원인 세션 정리: 부드럽게 → 강제로
SELECT pg_cancel_backend(12345);     -- 쿼리만 취소
SELECT pg_terminate_backend(12345);  -- 연결 종료

✍️ 이해도 체크