PostgreSQL 데드락 40P01 재현·탐지·해결

서론

운영 중인 PostgreSQL에서 갑자기 ERROR: deadlock detected와 함께 SQLSTATE 40P01이 발생하면, 대개 애플리케이션은 “가끔 실패하는 요청” 정도로만 인지합니다. 하지만 데드락은 재현이 어렵고, 관측(Observability)이 부족하면 원인 규명이 오래 걸리며, 임시로 재시도만 붙이면 장애가 잠복하는 전형적인 문제입니다.

이 글은 40P01을 의도적으로 재현하고, PostgreSQL이 어떤 방식으로 **데드락을 탐지/해결(희생자 선택)**하는지 확인한 다음, 실무에서 통하는 **근본 해결 패턴(락 순서 통일, 트랜잭션 축소, 인덱스/쿼리 재설계, SKIP LOCKED 등)**을 정리합니다.

40P01(Deadlock) 기본 메커니즘

데드락은 간단히 말해 “서로가 서로의 락을 기다리는 순환(cycle)”입니다.

• 세션 A: 리소스 1을 잡고 리소스 2를 기다림
• 세션 B: 리소스 2를 잡고 리소스 1을 기다림

PostgreSQL은 무한정 기다리지 않습니다. 내부적으로 deadlock_timeout 이후 대기 그래프를 검사해 순환을 발견하면, 한 트랜잭션을 **희생자(victim)**로 골라 롤백시키고 40P01을 반환합니다.

자주 등장하는 락 종류

• Row-level lock: SELECT ... FOR UPDATE, UPDATE, DELETE 등이 잡는 행 락
• Table-level lock: DDL, 일부 DML 패턴, FK 체크 등에서 강화될 수 있음
• Advisory lock: 애플리케이션이 임의로 사용하는 락(잘못 쓰면 데드락 유발)

데드락 재현: 가장 단순한 2행 교차 업데이트

아래는 “두 트랜잭션이 서로 다른 행을 먼저 잡고, 다음 행을 잡으려다 교착”되는 전형적인 재현입니다.

1) 테스트 테이블 준비

DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  id   int PRIMARY KEY,
  bal  int NOT NULL
);
INSERT INTO accounts(id, bal) VALUES (1, 100), (2, 100);

2) 세션 A

BEGIN;
UPDATE accounts SET bal = bal - 10 WHERE id = 1;
-- 여기서 잠깐 대기 (세션 B가 id=2를 잡게)
UPDATE accounts SET bal = bal + 10 WHERE id = 2;
COMMIT;

3) 세션 B

BEGIN;
UPDATE accounts SET bal = bal - 10 WHERE id = 2;
-- 여기서 잠깐 대기 (세션 A가 id=1을 잡게)
UPDATE accounts SET bal = bal + 10 WHERE id = 1;
COMMIT;

두 번째 UPDATE에서 서로가 서로의 행 락을 기다리며 데드락이 형성되고, PostgreSQL이 한쪽을 ERROR: deadlock detected로 중단합니다.

탐지 1: 로그로 데드락 원인(대기 관계) 잡기

운영에서 가장 먼저 해야 할 일은 “데드락이 발생했을 때 어떤 쿼리들이 어떤 락을 기다렸는지”를 로그로 남기는 것입니다.

추천 설정(가능하면 세션/DB 단위로 점진 적용)

-- postgresql.conf 또는 ALTER SYSTEM
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '200ms';

-- 필요 시(환경에 따라 비용 큼)
-- ALTER SYSTEM SET log_min_duration_statement = 200;

SELECT pg_reload_conf();

• deadlock_timeout은 “대기 후 데드락 검사까지의 시간”입니다. 기본값(1s)이 너무 크면 문제 상황에서 대기 시간이 길어지고, 너무 작으면 검사 오버헤드가 늘 수 있습니다. 100~500ms 사이에서 튜닝하는 경우가 많습니다.
• log_lock_waits=on이면 락 대기 자체도 로그로 남아, 데드락 직전 상황을 복원하기가 쉬워집니다.

로그에는 보통 다음이 포함됩니다.

• 어떤 PID가 어떤 락을 기다렸는지
• 어떤 PID가 그 락을 잡고 있었는지
• 데드락을 일으킨 SQL 텍스트(설정에 따라)

탐지 2: pg_stat_activity로 “지금” 막힌 세션 보기

데드락은 짧게 발생했다가 한쪽이 죽으면서 해소되므로, 실시간으로 보려면 막힌 순간을 포착해야 합니다.

SELECT
  now() AS ts,
  pid,
  usename,
  state,
  wait_event_type,
  wait_event,
  left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY state, pid;

• wait_event_type='Lock' 인 세션들이 핵심 후보입니다.
• state='active'인데 wait_event가 Lock이면, 실행 중이지만 락을 기다리는 상황일 가능성이 큽니다.

누가 누구를 막는지(블로킹 체인)

SELECT
  a.pid AS waiting_pid,
  pg_blocking_pids(a.pid) AS blocking_pids,
  left(a.query, 120) AS waiting_query
FROM pg_stat_activity a
WHERE array_length(pg_blocking_pids(a.pid), 1) > 0;

pg_blocking_pids()는 교착의 “고리”까지 직접 보여주진 않지만, 대기/점유 관계를 빠르게 파악하는 데 유용합니다.

탐지 3: pg_locks로 락 객체/모드까지 확인

좀 더 깊게 보려면 pg_locks로 어떤 락 모드가 충돌 중인지 확인합니다.

SELECT
  l.pid,
  l.locktype,
  l.mode,
  l.granted,
  l.relation::regclass AS relation,
  l.page,
  l.tuple
FROM pg_locks l
WHERE l.pid IN (
  SELECT pid FROM pg_stat_activity
  WHERE array_length(pg_blocking_pids(pid), 1) > 0
)
ORDER BY l.pid, l.granted;

• Row lock은 tuple 정보가 의미 있게 나오기도 하지만, 상황에 따라 NULL일 수 있습니다.
• 테이블/인덱스 레벨 충돌은 relation과 mode가 단서가 됩니다.

해결 1: 락 획득 순서를 “항상 동일하게” 만들기

위 재현 예시는 본질적으로 “id=1을 먼저 잡는 트랜잭션”과 “id=2를 먼저 잡는 트랜잭션”이 섞여 생깁니다. 해결의 정석은 락 순서를 통일하는 것입니다.

나쁜 패턴(입력 순서대로 업데이트)

-- (1) 먼저 from_id 차감, (2) to_id 증가
UPDATE accounts SET bal = bal - 10 WHERE id = :from_id;
UPDATE accounts SET bal = bal + 10 WHERE id = :to_id;

좋은 패턴(정렬된 순서로 락 획득)

-- 두 행을 미리 같은 순서로 잠그기
BEGIN;

-- 작은 id부터 잠금
SELECT id FROM accounts
WHERE id IN (:from_id, :to_id)
ORDER BY id
FOR UPDATE;

-- 잠금 확보 후 업데이트
UPDATE accounts SET bal = bal - 10 WHERE id = :from_id;
UPDATE accounts SET bal = bal + 10 WHERE id = :to_id;

COMMIT;

핵심은 FOR UPDATE로 필요한 행들을 먼저, 그리고 항상 같은 정렬 기준으로 잠그는 것입니다. 이렇게 하면 동시에 들어와도 “대기”는 생길 수 있어도 “교착”은 크게 줄어듭니다.

해결 2: 트랜잭션을 짧게(락 보유 시간 최소화)

데드락은 보통 “락을 오래 쥐고 있는 트랜잭션”이 있을 때 확률이 급격히 올라갑니다.

• 트랜잭션 내부에서 네트워크 호출(HTTP), 파일 I/O, 외부 API 호출, 긴 계산을 하지 않기
• 사용자 입력 대기 같은 인터랙션을 트랜잭션 밖으로 빼기
• 배치 업데이트는 적절히 청크로 쪼개기

예를 들어 아래는 위험합니다.

BEGIN;
UPDATE orders SET status='PAID' WHERE id = :id;
-- 여기서 결제사 API 호출을 기다린다? (절대 비추)
COMMIT;

결제 승인/검증은 트랜잭션 밖에서 완료하고, DB 트랜잭션은 상태 반영만 짧게 수행하도록 구조를 바꾸는 편이 안전합니다.

해결 3: 한 번에 “같은 테이블을 여러 방식으로” 건드리지 않기

데드락은 서로 다른 테이블 간에도 흔하지만, 같은 테이블에서도 다음 조합에서 잘 터집니다.

• 인덱스가 부족한 UPDATE ... WHERE 조건이 많은 행을 스캔하며 락을 넓게 잡는 경우
• FK/트리거로 인해 예상치 못한 순서로 다른 테이블 락이 잡히는 경우

인덱스로 스캔 범위를 줄여 락 충돌면적 축소

예: 상태 컬럼으로 배치를 업데이트한다면

-- 조건 컬럼 인덱스가 없으면 많은 튜플을 훑으며 락 경합이 커짐
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON orders(status);

-- 그리고 작은 단위로 처리
UPDATE orders
SET status = 'ARCHIVED'
WHERE status = 'DONE'
  AND id IN (
    SELECT id FROM orders
    WHERE status = 'DONE'
    ORDER BY id
    LIMIT 500
    FOR UPDATE SKIP LOCKED
  );

SKIP LOCKED는 “이미 다른 트랜잭션이 잡고 있는 행은 건너뛰고 다음 행을 처리”하게 해, 워커 여러 개를 돌릴 때 데드락/경합을 크게 줄입니다(대신 처리 순서/정확한 즉시성 요구가 있으면 주의).

해결 4: 격리수준/락 전략을 요구사항에 맞게 선택

• SERIALIZABLE은 동시성 오류(직렬화 실패)를 더 잘 잡지만, 트랜잭션 충돌이 증가할 수 있습니다.
• READ COMMITTED가 기본이며, 대부분의 OLTP는 여기서 설계합니다.

데드락을 피하려고 무작정 격리수준을 낮추기보다, 락 순서 통일 + 트랜잭션 축소가 우선입니다.

해결 5: 애플리케이션 레벨 재시도(필수 안전망)

근본 해결을 하더라도, 분산 시스템/복잡한 트랜잭션에서는 데드락을 0으로 만들기 어렵습니다. 따라서 40P01은 재시도 가능한 오류로 취급하는 것이 일반적입니다.

예: Python(psycopg)에서 40P01 재시도

import time
import random
import psycopg

MAX_RETRIES = 5

def run_tx(conn, fn):
    for attempt in range(1, MAX_RETRIES + 1):
        try:
            with conn.transaction():
                return fn(conn)
        except psycopg.Error as e:
            # SQLSTATE 40P01: deadlock_detected
            if getattr(e, "sqlstate", None) == "40P01" and attempt < MAX_RETRIES:
                backoff = (2 ** (attempt - 1)) * 0.05 + random.random() * 0.05
                time.sleep(backoff)
                continue
            raise

• 지수 백오프 + 지터는 “동시에 재시도해서 또 부딪히는” 상황을 완화합니다.
• 재시도는 어디까지나 안전망이며, 재시도율이 올라가면 DB 내부 경합이 이미 심각하다는 신호입니다.

재시도 설계 전반은 네트워크 타임아웃과도 결이 같으니, 재시도/백오프를 더 체계적으로 다루고 싶다면 Python httpx ReadTimeout·ConnectError 재시도 설계도 함께 참고하면 좋습니다.

운영 체크리스트: “재현-관측-개선” 루프

1) 재현 가능한 최소 케이스 만들기

• 실제 테이블/쿼리에서 핵심만 추출해 로컬/스테이징에서 2세션으로 재현
• 재현이 되면 해결도 빨라집니다(락 순서, 인덱스, 트랜잭션 범위 변경의 효과를 바로 검증 가능)

2) 관측 강화

• log_lock_waits, deadlock_timeout 조정
• 필요 시 pg_stat_statements로 상위 쿼리/평균 지연/호출 빈도 확인
• APM에서 40P01 발생률(%)과 재시도 횟수, P95/P99 지연을 함께 보기

3) 개선 적용 순서(추천)

1. 트랜잭션에서 “불필요한 작업” 제거(가장 효과 큼)
2. 락 획득 순서 통일(FOR UPDATE + 정렬)
3. 인덱스 보강으로 스캔/락 범위 축소
4. 배치/워커는 SKIP LOCKED 등으로 경합 회피
5. 애플리케이션 재시도는 항상 유지(다만 재시도율이 높으면 근본 원인 재점검)

자주 하는 실수

• 데드락을 “DB가 느려서” 생기는 것으로 오해하고 스펙만 올리기(경합 구조가 그대로면 재발)
• 재시도만 추가하고 로그/관측을 안 남겨 원인 규명이 영원히 안 되는 상태 만들기
• 트랜잭션 안에서 외부 API 호출/대기 수행
• 업데이트 대상 행을 미리 잠그지 않고, 입력 순서대로 여러 행을 갱신

마무리

40P01 데드락은 PostgreSQL의 결함이 아니라, 동시성 높은 환경에서 락을 잡는 순서와 범위가 설계되지 않았을 때 자연스럽게 발생하는 현상입니다.

• 재현: 2세션 교차 업데이트로 최소 케이스 확보
• 탐지: log_lock_waits/deadlock_timeout + pg_stat_activity/pg_locks로 증거 수집
• 해결: 락 순서 통일, 트랜잭션 축소, 인덱스/배치 전략 개선, 40P01 재시도

이 루프를 한 번 체계화해두면, 데드락은 “원인 불명의 간헐 오류”가 아니라 “설계로 줄이고 관측으로 관리하는 이벤트”가 됩니다.