PostgreSQL 실전 강좌 #6 잠금과 동시성: 데드락, DDL 락, SKIP LOCKED

인프라 PostgreSQL 데이터베이스
4 분 소요

기초 6편의 결론은 “MVCC 덕에 읽기와 쓰기는 서로를 막지 않는다"였습니다. 남은 것이 쓰기와 쓰기입니다. 같은 행을 두 트랜잭션이 동시에 고치려 할 때는 잠금이 등장하고, 여기서 실전의 사건들(느닷없는 대기, 데드락, 마이그레이션이 서비스를 세우는 사고)이 나옵니다.

잠금의 두 층위 #

  • 행 잠금: UPDATE·DELETE는 대상 행을 잠급니다. 다른 트랜잭션이 같은 행을 고치려 하면 앞의 트랜잭션이 끝날 때까지 대기합니다. 다른 행이면 서로 무관합니다. 그래서 행 잠금의 문제는 대개 “핫 로우”, 즉 모두가 고치는 소수의 행(집계 카운터, 인기 상품 재고)에서 납니다.
  • 테이블 잠금: DDL이 잡는 층위입니다. 실전 1편에서 본 대로 ALTER TABLE의 ACCESS EXCLUSIVE는 SELECT까지 막고, 더 무서운 것은 대기 중인 DDL 뒤로 모든 쿼리가 줄을 서는 현상이었습니다. lock_timeout이 그 보험이었습니다.

기억할 원칙은 하나입니다. 잠금은 트랜잭션이 끝나야 풀립니다. 문장이 끝나도 트랜잭션이 열려 있으면 잠금은 유지됩니다. 실전 3편의 idle in transaction이 위험한 세 번째 이유입니다(잠금 보유, VACUUM 방해에 이어).

누가 누구를 막고 있나 #

장애 중에 “쿼리가 안 끝나요"가 들어오면, 대기의 사슬을 추적합니다.

잠금 대기 조회 
-- 잠금 대기 중인 쿼리와, 그것을 막고 있는 pid
SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       state, now() - query_start AS waiting_for,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids()가 “이 pid를 막는 pid 목록"을 바로 돌려주므로, 원시 뷰인 pg_locks를 손으로 조인할 필요 없이 사슬의 뿌리를 찾을 수 있습니다. 뿌리가 idle in transaction이거나 폭주 쿼리라면 SELECT pg_terminate_backend(pid);로 끊는 것이 최후의 수단입니다.

데드락: 구조와 예방 #

데드락은 두 트랜잭션이 서로가 쥔 잠금을 기다리는 상태입니다. 구조는 언제나 같습니다. A가 1번 행을 잡고 2번을 기다리는데, B는 2번을 잡고 1번을 기다립니다. PostgreSQL은 이것을 감지해 한쪽을 에러로 끊어 주므로 시스템이 영원히 멈추지는 않지만, 끊긴 쪽 요청은 실패합니다.

예방 규칙도 구조에서 바로 나옵니다. 여러 행(또는 여러 테이블)을 고칠 때는 항상 같은 순서로 잡습니다. 이체 로직이라면 “보내는 쪽 먼저"가 아니라 “id가 작은 계좌 먼저"처럼 전역 순서를 정하는 것입니다. 한 문장으로 여러 행을 갱신할 때도 WHERE id IN (...) 의 잠금 순서는 보장되지 않으므로, 순서가 중요한 로직은 정렬해서 잡습니다.

잠금 순서 고정 
-- 잠금 순서를 명시적으로 고정
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

SELECT FOR UPDATE: 읽고-판단-쓰기의 안전판 #

기초 6편에서 “읽고 판단해서 쓰는 로직은 원자적 한 문장이 먼저"라고 했습니다. 한 문장으로 안 되는 경우(읽은 값으로 복잡한 계산을 해야 할 때)의 도구가 SELECT ... FOR UPDATE입니다. 읽는 시점에 그 행을 UPDATE와 같은 강도로 잠가서, 내 판단과 쓰기 사이에 남이 끼어들 수 없게 만듭니다. 잠그는 범위는 최소로, 트랜잭션은 짧게가 사용 규칙입니다.

SKIP LOCKED: DB로 만드는 작업 큐 #

FOR UPDATE의 파생 옵션 하나가 패턴 하나를 통째로 만들었습니다. 잠긴 행은 건너뛰고 다음 행을 달라는 SKIP LOCKED입니다.

작업 큐 쿼리 
-- 워커 여러 개가 동시에 실행해도 서로 다른 작업을 집어 갑니다
UPDATE jobs SET status = 'running', started_at = now()
WHERE id = (
    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
RETURNING *;

워커 10개가 같은 쿼리를 동시에 돌려도, 각자 잠기지 않은 첫 행을 하나씩 집어 가므로 중복 처리도 대기도 없습니다. 별도 메시지 큐를 세우기 전에, 트랜잭션과 함께 원자적으로 굴러가는 작업 큐가 필요하다면 이 패턴이 PostgreSQL의 표준 답입니다.

정리 #

  • MVCC가 남긴 충돌은 쓰기 대 쓰기입니다. 행 잠금은 핫 로우에서, 테이블 잠금은 DDL에서 사건이 납니다.
  • 잠금은 트랜잭션이 끝나야 풀립니다. 짧은 트랜잭션이 모든 잠금 문제의 절반을 예방합니다.
  • 대기 추적은 pg_blocking_pids로 사슬의 뿌리를 찾고, 최후에는 pg_terminate_backend입니다.
  • 데드락 예방은 전역 잠금 순서 하나로 요약됩니다. 항상 같은 순서로 잡습니다.
  • 읽고-판단-쓰기는 FOR UPDATE, 작업 큐는 FOR UPDATE SKIP LOCKED가 표준 패턴입니다. 다음 편은 파티셔닝입니다.
X