beautifulsoups.dev
Published on

PostgreSQL RDS deadlock_detected(40P01) 원인·해결

Authors

서버가 멀쩡해 보이는데도 애플리케이션에서 갑자기 deadlock_detected (40P01) 예외가 터지면 대부분의 팀은 “DB가 느려졌다”거나 “RDS가 불안정하다”로 오해합니다. 하지만 40P01은 PostgreSQL이 교착상태(deadlock)를 감지했기 때문에 한 트랜잭션을 강제로 중단시킨 결과입니다. 즉, 성능 이슈라기보다 동시성 제어(락) 설계 문제에 가깝습니다.

이 글에서는 RDS(PostgreSQL) 환경에서 40P01이 왜 발생하는지, 어떤 로그/메트릭을 보면 되는지, 그리고 코드/쿼리/스키마 차원에서 어떻게 줄이는지까지 실무 관점으로 정리합니다.

1) deadlock_detected(40P01)란 무엇인가

PostgreSQL은 트랜잭션 격리와 MVCC를 제공하지만, 다음과 같은 상황에서는 여전히 락(lock) 이 필요합니다.

  • UPDATE/DELETE로 같은 행을 수정하려고 할 때의 row lock
  • SELECT ... FOR UPDATE/SHARE 같은 명시적 row lock
  • FK 체크, 인덱스 갱신, DDL 수행 등에서의 다양한 lock

교착상태는 보통 아래처럼 발생합니다.

  • 트랜잭션 A가 자원 1을 잡고 자원 2를 기다림
  • 트랜잭션 B가 자원 2를 잡고 자원 1을 기다림

서로가 서로를 기다리며 영원히 진행하지 못하므로, PostgreSQL은 deadlock_timeout(기본 1초) 이후 교착 여부를 판단하고, 한쪽을 희생(victim)으로 롤백시켜 시스템을 살립니다. 그때 애플리케이션에는 SQLSTATE 40P01이 전달됩니다.

핵심은 “DB가 죽었다”가 아니라 “동시에 실행되는 트랜잭션들이 서로 다른 순서로 락을 잡고 있다”입니다.

2) RDS에서 deadlock을 더 자주 체감하는 이유

RDS 자체가 교착을 만들지는 않지만, 운영환경에서 다음 조건이 겹치면 40P01이 더 자주 드러납니다.

  • 트래픽 증가로 동시 UPDATE/DELETE가 늘어남
  • 배치/워커/웹이 같은 테이블을 동시에 갱신
  • ORM이 의도치 않게 여러 테이블을 한 트랜잭션에서 만짐
  • 인덱스/외래키 부족으로 락 범위가 커지거나 오래 잡힘
  • 커넥션 풀에서 긴 트랜잭션이 섞여 들어옴(사용자 요청 + 백그라운드 작업)

인프라 레벨에서 장애처럼 보이는 패턴은 대개 애플리케이션의 재시도 폭주, 타임아웃, ALB 502/504로 이어집니다. 이런 연쇄를 다루는 체크리스트가 필요하다면 AWS ALB 502·504 난사 - 원인별 해결 체크리스트도 함께 참고하면 좋습니다.

3) 가장 흔한 원인 패턴 6가지

3.1 서로 다른 순서로 테이블/행을 업데이트

가장 전형적입니다.

  • 트랜잭션 A: users 업데이트 → orders 업데이트
  • 트랜잭션 B: orders 업데이트 → users 업데이트

락 획득 순서가 뒤집히면 교착 확률이 급격히 올라갑니다.

3.2 SELECT ... FOR UPDATE 남발

큐 처리, 재고 차감, 작업 할당에서 흔합니다.

  • 워커들이 같은 후보 행을 FOR UPDATE로 잡으려다 경합
  • 트랜잭션이 길면 락 유지 시간이 길어져 교착 가능성 상승

3.3 외래키(FK) 인덱스 누락

PostgreSQL은 FK 무결성 검증 시 참조/피참조 테이블을 확인합니다. 이때 FK 컬럼에 인덱스가 없으면 더 많은 행을 훑거나 더 오래 락을 잡아 교착을 유발하기 쉽습니다.

  • 특히 DELETE(부모 삭제) + UPDATE/INSERT(자식 변경)가 동시에 일어날 때 위험

3.4 동일 행을 여러 번 갱신하는 “읽고-계산하고-쓰기”

예: 포인트/잔고 차감

  • 읽기 → 계산 → 업데이트 사이 간격이 길면 다른 트랜잭션이 끼어듦
  • 여러 행을 묶어 업데이트하면 락 순서가 불안정해짐

3.5 트랜잭션이 과도하게 큼(긴 트랜잭션)

  • 네트워크 호출, 외부 API 호출, 파일 I/O를 트랜잭션 안에서 수행
  • ORM에서 lazy loading으로 예상치 못한 쿼리가 트랜잭션 안에서 실행

3.6 DDL/마이그레이션과 DML 동시 실행

ALTER TABLE, CREATE INDEX, VACUUM FULL 등은 강한 락을 요구할 수 있습니다. 운영 중 마이그레이션이 DML과 겹치면 교착/장기 블로킹이 발생합니다.

4) 먼저 해야 할 진단: “어떤 쿼리끼리” 교착인가

4.1 RDS 파라미터/로그 설정

교착 분석의 핵심은 deadlock 로그입니다.

  • log_lock_waits = on
  • deadlock_timeout = '1s' (기본값이지만 명시 권장)
  • log_line_prefix에 %m [%p] %u@%d 등 포함(세션 추적 용이)

RDS에서는 Parameter Group에서 설정하고 재시작/적용 여부를 확인합니다.

4.2 CloudWatch / PostgreSQL 로그에서 deadlock 메시지 확인

deadlock 발생 시 보통 아래 정보가 포함됩니다.

  • 어떤 PID들이 서로를 기다렸는지
  • 어떤 relation(테이블/인덱스)에서 어떤 lock 모드가 충돌했는지
  • 각 프로세스가 실행 중이던 SQL

이 SQL 두 개(또는 그 이상)가 “락 획득 순서가 뒤집힌 지점”을 찾는 출발점입니다.

4.3 실시간으로 막힌 세션 보기: pg_stat_activity

SELECT
  pid,
  usename,
  datname,
  state,
  wait_event_type,
  wait_event,
  now() - xact_start AS xact_age,
  now() - query_start AS query_age,
  left(query, 200) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;
  • wait_event_type = 'Lock' 인 세션을 우선 확인
  • xact_age가 긴 세션은 교착/블로킹의 “원인 제공자”일 가능성이 큼

4.4 락 그래프 확인: pg_locks 조인

SELECT
  a.pid AS waiting_pid,
  a.query AS waiting_query,
  b.pid AS blocking_pid,
  b.query AS blocking_query,
  l1.locktype,
  l1.mode AS waiting_mode,
  l2.mode AS blocking_mode,
  l1.relation::regclass AS relation
FROM pg_locks l1
JOIN pg_stat_activity a ON a.pid = l1.pid
JOIN pg_locks l2
  ON l1.locktype = l2.locktype
  AND l1.database IS NOT DISTINCT FROM l2.database
  AND l1.relation IS NOT DISTINCT FROM l2.relation
  AND l1.page IS NOT DISTINCT FROM l2.page
  AND l1.tuple IS NOT DISTINCT FROM l2.tuple
  AND l1.virtualxid IS NOT DISTINCT FROM l2.virtualxid
  AND l1.transactionid IS NOT DISTINCT FROM l2.transactionid
  AND l1.classid IS NOT DISTINCT FROM l2.classid
  AND l1.objid IS NOT DISTINCT FROM l2.objid
  AND l1.objsubid IS NOT DISTINCT FROM l2.objsubid
  AND l1.pid <> l2.pid
JOIN pg_stat_activity b ON b.pid = l2.pid
WHERE NOT l1.granted AND l2.granted;

이 결과로 “누가 누구를 막고 있는지” 체인을 확인할 수 있습니다. 교착은 순간적으로 발생하고 바로 한쪽이 죽어 사라지므로, 평소에 블로킹을 줄이는 것이 교착 감소의 핵심입니다.

5) 해결 전략: 재시도보다 먼저 구조를 바꿔라

교착은 완전히 0으로 만들기 어렵습니다. PostgreSQL도 이를 전제로 40P01을 제공합니다. 하지만 발생 빈도를 낮추고, 발생하더라도 영향을 최소화할 수 있습니다.

5.1 락 획득 순서를 “항상 동일하게” 강제

가장 효과가 큽니다.

  • 여러 테이블을 업데이트한다면 항상 같은 순서로
  • 동일 테이블의 여러 행을 업데이트한다면 항상 같은 정렬 기준으로

예: 여러 사용자 행을 갱신해야 한다면 id 오름차순으로 잠그기

-- 먼저 잠글 대상 행을 정렬된 순서로 고정
SELECT id
FROM users
WHERE id = ANY($1)
ORDER BY id
FOR UPDATE;

-- 이후 업데이트
UPDATE users
SET last_seen_at = now()
WHERE id = ANY($1);

5.2 트랜잭션을 짧게: “DB 안에서만” 끝내기

  • 트랜잭션 안에서 외부 API 호출 금지
  • 사용자 요청 처리 중 불필요한 쿼리(특히 N+1) 제거
  • ORM 사용 시 flush/commit 타이밍 점검

5.3 SKIP LOCKED로 작업 큐/워커 경합 줄이기

작업 테이블에서 “선점” 패턴을 구현할 때 교착을 크게 줄일 수 있습니다.

WITH picked AS (
  SELECT id
  FROM jobs
  WHERE status = 'ready'
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs
SET status = 'processing', started_at = now()
WHERE id IN (SELECT id FROM picked)
RETURNING *;
  • 여러 워커가 동시에 실행돼도 서로 잠긴 행을 건너뜀
  • FOR UPDATE 경합이 교착으로 번지기 전에 분산됨

5.4 FK 컬럼 인덱스 추가(특히 자식 테이블)

FK 자체는 인덱스를 자동 생성하지 않습니다.

-- 예: orders(user_id) 가 users(id)를 참조한다면
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_id
ON orders(user_id);
  • CONCURRENTLY는 운영 중 락 영향을 줄이지만 시간이 더 걸릴 수 있음
  • 인덱스 추가 후 EXPLAIN (ANALYZE, BUFFERS)로 효과 확인

5.5 UPDATE 범위를 줄이고, 불필요한 갱신을 피하기

같은 값으로 업데이트하면 락만 잡고 실익이 없습니다.

UPDATE accounts
SET balance = $2
WHERE id = $1
  AND balance IS DISTINCT FROM $2;

또는 “증분 업데이트”로 단일 문장화하면 읽기-계산-쓰기 간격을 줄일 수 있습니다.

UPDATE accounts
SET balance = balance - $1
WHERE id = $2
  AND balance >= $1
RETURNING balance;

5.6 격리 수준/락 강도 재검토

  • 기본 READ COMMITTED에서 충분한 경우가 많습니다.
  • 불필요하게 SERIALIZABLE을 쓰면 재시도와 충돌이 늘 수 있습니다.
  • LOCK TABLE 같은 강한 락은 정말 필요한 구간에만 제한적으로.

6) 애플리케이션 레벨 대응: 40P01은 “안전한 재시도” 대상

구조를 개선하더라도, 고부하 환경에서는 교착이 드물게 남을 수 있습니다. 따라서 애플리케이션은 40P01을 명시적으로 캐치하고 짧은 지수 백오프로 재시도하는 것이 정석입니다.

6.1 Python(psycopg) 예시

import time
import random
import psycopg
from psycopg.errors import DeadlockDetected

MAX_RETRIES = 5

def run_tx(conn, fn):
    for attempt in range(1, MAX_RETRIES + 1):
        try:
            with conn.transaction():
                return fn(conn)
        except DeadlockDetected:
            if attempt == MAX_RETRIES:
                raise
            # 지수 백오프 + 지터
            sleep = (2 ** (attempt - 1)) * 0.05 + random.random() * 0.05
            time.sleep(sleep)

# 사용 예
# run_tx(conn, lambda c: c.execute("UPDATE ..."))
  • 재시도는 “멱등성(idempotency)”이 보장되는 작업에만 적용
  • 재시도 횟수는 작게(보통 3~5) + 지터 필수

6.2 Java(Spring)에서는

  • SQLState 40P01을 기준으로 재시도 정책 적용
  • @Transactional 내부에서 외부 호출 금지

재시도를 잘못 설계하면 트래픽이 더 몰려 장애가 커질 수 있습니다. 오토스케일링/워커 증설이 오히려 문제를 악화시키는 사례도 많으니, 동시성 폭주를 다루는 관점에서는 EKS HPA 폭주를 KEDA 큐기반 오토스케일링으로 안정화 같은 글의 “부하를 더 밀어 넣기 전에 큐로 완충”이라는 아이디어가 DB 교착 대응에도 그대로 적용됩니다.

7) 운영 체크리스트(빠르게 효과 보는 순서)

7.1 즉시(오늘)

  • PostgreSQL 로그에서 deadlock 쿼리 페어 확인
  • 긴 트랜잭션(xact_age) 상위 세션 제거/원인 코드 추적
  • 40P01 재시도(지수 백오프+지터) 적용

7.2 단기(이번 스프린트)

  • 동일 리소스 업데이트 순서 통일(테이블/행 정렬)
  • 작업 큐는 SKIP LOCKED로 전환
  • FK 인덱스 누락 점검 및 추가

7.3 중장기(이번 분기)

  • 트랜잭션 경계 재설계(도메인 서비스/리포지토리 레벨)
  • 핫스팟 행(단일 카운터/단일 집계 테이블) 분산 설계
  • 배치/워커의 동시성 제한(큐 기반, rate limit)

8) 자주 묻는 질문

8.1 deadlock_timeout을 늘리면 해결되나?

아닙니다. deadlock_timeout은 “교착을 감지하기까지 기다리는 시간”입니다. 늘리면 교착이 더 오래 지속되어 피해가 커질 수 있습니다. 근본은 락 경쟁을 줄이는 것입니다.

8.2 statement_timeout으로 막을 수 있나?

부분적으로만 가능합니다. 타임아웃은 증상을 줄일 뿐, 교착 원인(락 순서/긴 트랜잭션)을 제거하지 못합니다. 다만 사용자 요청 보호를 위해 합리적인 statement_timeout을 두는 것은 유효합니다.

8.3 RDS 파라미터 튜닝으로 해결 가능?

로그/가시성을 높이는 데는 도움이 되지만, 40P01의 근본 해결은 애플리케이션과 스키마/쿼리 설계에서 나옵니다.

9) 결론

RDS PostgreSQL의 deadlock_detected(40P01)은 “DB가 불안정해서”가 아니라 동시 트랜잭션이 서로 다른 순서로 락을 잡는 구조에서 발생합니다.

가장 효과적인 해결은 다음 3가지입니다.

  1. 락 획득 순서 통일(정렬/규칙화)
  2. 트랜잭션을 짧게(외부 작업 분리)
  3. 작업 큐는 SKIP LOCKED, FK 인덱스 보강

그리고 남는 소수의 교착은 애플리케이션에서 40P01 안전 재시도로 흡수하면 운영 안정성이 크게 올라갑니다.

Discuss on Twitter • View on GitHub