Hibernate LockAcquisitionException인데 lock 경합이 아니었다 — PostgreSQL 40001과 슬로우 쿼리의 합작

TL;DR
- 증상: 뉴스 목록 API가 평소 2~3초 슬로우, 가끔 SQLSTATE 40001로 실패
- 처음 의심: Hibernate가 LockAcquisitionException으로 매핑 → 트랜잭션 lock 경합
- 진짜 원인: lock 경합 아님. PostgreSQL Replica의 recovery conflict
          → 조건 1: Replica에서 슬로우 쿼리 실행 중 (수 초 이상)
          → 조건 2: Main DB에서 동일 테이블 대량 INSERT/UPDATE + VACUUM
          → 두 조건이 겹치면 max_standby_streaming_delay(30s) 초과 → replica가 쿼리 강제 종료 → 40001
- 해결: (1) 파티션 pruning 활성화로 1.4s 절감
       (2) 페이징을 LIMIT 전에 적용하도록 쿼리 구조 변경 → 5.7s → 40ms (99% 단축)
       (3) max_standby_streaming_delay 30s → 120s, hot_standby_feedback on (인프라 측)

이런 분께: Spring/Hibernate에서 SQLState 40001이 가끔 보이는데 lock 경합으로 안 잡히는 분 / PostgreSQL 슬로우 쿼리 + 파티션 + 페이징 최적화 패턴 찾는 분

상황 (Context)

• 환경: PostgreSQL primary + read replica, Spring Boot + Hibernate 백엔드
• API: 뉴스 목록 조회 (페이징, 다국어, 태그·심볼 부가 정보 포함)
• 시점: 평소 응답이 느리던 API가 어느 순간부터 간헐적으로 에러까지 발생

마주한 문제

평소엔 그냥 느린 API였다.

• 응답 시간 2~3초
• 사용자 측 체감은 나쁘지만 동작은 함

그런데 어느 시점부터는 간헐적으로 에러가 떨어지기 시작:

WARN  [SqlExceptionHelper] SQL Error: 0, SQLState: 40001
ERROR [SqlExceptionHelper] ERROR: canceling statement due to conflict with recovery
  Detail: User query might have needed to see row versions that must be removed.

Hibernate stack trace는 친절하게도 LockAcquisitionException 으로 매핑해서 던져줌. 첫 직감: "트랜잭션 lock 경합 났네."

가설과 시도 — what didn't work

가설 1. 트랜잭션 lock 경합

SQLSTATE 40001은 PostgreSQL에서 serialization_failure로 분류되는 코드. Serializable isolation 같은 트랜잭션 격리 환경에서 흔히 나는 코드라 처음엔 자연스럽게 의심.

→ 탈락. 두 가지 모순:

• 이 API는 READ ONLY SELECT. 다른 트랜잭션과 write 경합할 일이 없음
• 에러 메시지가 ERROR: canceling statement due to conflict with recovery — recovery 키워드. 일반 lock 경합과 다른 무엇.

가설 2. Hibernate의 connection 재사용 문제

HikariCP가 broken 커넥션을 반환했나?

→ 탈락. 같은 쿼리가 동일 커넥션에서 다른 시점엔 성공. 커넥션 자체 문제 아님.

가설 3. 단순 슬로우 쿼리 timeout

쿼리가 너무 느려서 어딘가에서 timeout?

→ 부분 단서. 쿼리는 분명 느림 (5초 이상). 그런데 statement_timeout 설정값과 일치하지 않는 시점에 에러. 다른 메커니즘이 있음.

에러 메시지의 conflict with recovery 가 가리키는 방향 — Replica recovery conflict 으로 좁혀짐.

원인 분석

진짜 원인 — Replica Recovery Conflict

PostgreSQL primary/replica 환경에서 read replica는 primary의 WAL을 받아 적용한다. 여기서 vacuum WAL을 적용하려는데 replica의 read 쿼리가 같은 테이블을 잡고 있으면 충돌이 발생.

조건 1: Replica에서 슬로우 쿼리 실행 중 (수 초 이상)
조건 2: Main DB에서 동일 테이블에 대량 INSERT/UPDATE + VACUUM 발생
────────────────────────────────────────────────────────────
두 조건이 겹치면 → max_standby_streaming_delay(기본 30s) 초과 → 40001 에러

Replica는 max_standby_streaming_delay 시간 동안 vacuum 적용을 대기한다. 시간이 초과되면 충돌하는 쿼리(read)를 강제 취소 — 클라이언트엔 SQLSTATE 40001 + ERROR: canceling statement due to conflict with recovery로 보고됨.

Hibernate가 40001을 잘못 매핑

flowchart LR
    A[PostgreSQL: SQLSTATE 40001<br/>conflict with recovery] --> B[Hibernate SQLStateConversionDelegate]
    B -->|class '40' = transaction rollback| C[LockAcquisitionException]
    
    style A fill:#fee
    style C fill:#fcc

Hibernate의 SQLStateConversionDelegate는 SQL state class '40'(transaction rollback)을 일률적으로 LockAcquisitionException으로 매핑. 그래서 40001(serialization_failure) + recovery conflict 모두 lock 경합처럼 보임. 함정 1번.

평소 슬로우의 정체 — 쿼리 병목 3가지

EXPLAIN ANALYZE 결과 쿼리 총 실행 5,705ms. 큰 병목 3개:

병목 1: 파티션 pruning 미작동 (1.4초)

news_i18n 테이블은 published_dt 기준 RANGE 파티션이었음. 그런데 CTE에서 published_dt 조건 없이 news_id + lang으로만 조인 → 파티션 pruning 안 일어나서 8개 파티션 전체 Seq Scan (197만건).

병목 2: symbol 전체 테이블 스캔 (0.7초)

83,555건의 뉴스 전체에 대해 news_symbol_rel을 Merge Join → 358만건 스캔.

병목 3: tag 전체 테이블 스캔 (1.4초)

83,555건 전체에 대해 news_tag를 Merge Join → 697만건 스캔.

변경 전 쿼리 실행 순서:
filtered_news (83,555건)
  → i18n 전체 파티션 Seq Scan (197만건)     ← 파티션 pruning 미작동
  → symbol 전체 조인 (358만건)               ← LIMIT 전에 전체 조인
  → tag 전체 조인 (697만건)                  ← LIMIT 전에 전체 조인
  → ORDER BY created_dt DESC
  → LIMIT 20

최종 결과는 20건인데 중간 단계에서 수백만 건 처리. 함정 2번: 페이징의 위치.

두 함정이 만나는 지점

flowchart TD
    A[느린 쿼리 5.7초<br/>Replica에서 실행 중] --> B{Main DB 상황}
    B -->|평시| C[느리지만 정상 응답<br/>2~3초]
    B -->|대량 INSERT/UPDATE 배치 시점| D[Main에서 VACUUM 발생]
    D --> E[Replica로 vacuum WAL 전송]
    E --> F[max_standby_streaming_delay 30s 대기]
    F -->|초과| G[Replica가 read 쿼리 강제 종료<br/>SQLSTATE 40001]
    G --> H[Hibernate: LockAcquisitionException 매핑]
    
    style D fill:#fee
    style G fill:#fee
    style H fill:#fcc

평시엔 그냥 느린 쿼리였지만, Main DB에서 외부 뉴스 피드의 대량 적재(INSERT/UPDATE) 배치가 도는 시점엔 충돌이 발생 — 그게 간헐적 에러의 정체.

해결

쿼리 측 + 인프라 측 두 갈래로 접근.

1차 — 파티션 pruning 활성화

i18n CTE에 published_dt 조건 추가:

-- 변경 후
i18n_origin AS (
    SELECT i18n.news_id, i18n.title, i18n.body
    FROM news.news_i18n i18n
    JOIN filtered_news a
      ON i18n.news_id = a.news_id
     AND i18n.lang = a.lang
    WHERE i18n.published_dt BETWEEN :startDate AND :endDate
)

결과: 8파티션 197만건 → 1파티션 17만건, 1.4초 → 0.09초. 총 5,705ms → 4,370ms (24% 개선).

2차 — 페이징을 LIMIT 전에 적용

핵심은 수백만 건을 조인한 뒤 20건만 남기는 구조를, 20건을 먼저 뽑고 거기에만 조인하는 구조로 변경.

변경 전: filtered_news → symbol 전체 조인 → tag 전체 조인 → ORDER BY → LIMIT
변경 후: filtered_news → i18n 조인 → ORDER BY → LIMIT (20건) → symbol 조인 → tag 조인

구체적으로:

• paged_news CTE 추가: filtered_news + i18n 조인 후 ORDER BY + LIMIT/OFFSET 먼저 적용
• 페이징된 20건에 대해 LEFT JOIN LATERAL로 symbol/tag 조인 (필요한 행만)
• ROW_NUMBER() WHERE rn=1 대신 DISTINCT ON으로 단순화

결과: 5,705ms → 40ms (99.3% 개선).

인프라 측 — Replica recovery 허용 시간 + feedback

max_standby_streaming_delay: 30s → 120s   # vacuum 대기 시간 확대
hot_standby_feedback:        off → on     # replica의 long-running query를 primary에 보고

hot_standby_feedback=on은 replica에서 실행 중인 쿼리의 XID를 primary에 보고해서, primary의 vacuum이 그 XID 이전 시점 데이터를 정리하지 않게 막음. 단점은 vacuum이 지연될 수 있어 dead tuple 누적 위험 — 슬로우 쿼리 자체를 줄이는 게 가장 안전한 보완책이라 1·2차 쿼리 개선과 같이 가야 함.

결과

항목 변경 전 1차 (파티션 pruning) 2차 (페이징 구조 변경)

i18n	197만 Seq Scan (1.4s)	17만 Index Scan (0.09s)	Index Scan loops=180
symbol	358만 전체 (0.7s)	358만 전체 (0.7s)	Index Scan loops=20
tag	697만 전체 (1.4s)	697만 전체 (1.4s)	Index Only Scan loops=20
총 실행 시간	5,705ms	4,370ms	40ms

API 응답: 2~3초 (에러 시 40001) → ~500ms (Main 대량 적재 시에도 정상). 슬로우 쿼리 자체가 사라지니 Replica recovery conflict도 동시에 해소.

배운 점

1. SQLSTATE 40001 + LockAcquisitionException이 보이면 recovery conflict도 의심하라.
Hibernate의 SQL state class '40' 일괄 매핑은 lock 경합처럼 보이는 잘못된 인상을 준다. PostgreSQL primary/replica 환경이라면, 에러 detail 메시지의 conflict with recovery 키워드 확인이 결정적 단서. 진짜 원인은 replica recovery에 의한 강제 쿼리 취소.

2. 페이징 쿼리는 LIMIT을 먼저 적용해야 한다.
수만~수십만 건을 모두 조인한 뒤 LIMIT 20을 거는 구조 → 중간에 수백만 건 처리. paged_news 같은 CTE로 페이징을 먼저 잘라내고, 부가 데이터는 페이징된 결과에 대해서만 LATERAL JOIN.

3. 파티션 테이블 조인은 파티션 키 조건 필수.
RANGE 파티션인데 파티션 키 조건 없이 조인 → pruning 미작동 → 모든 파티션 Seq Scan. published_dt BETWEEN ... AND ... 같은 명시적 조건이 들어가야 옵티마이저가 파티션을 좁힘.

4. DISTINCT ON은 ROW_NUMBER() WHERE rn=1보다 간결하고 빠르다.
같은 의도를 표현할 때 PostgreSQL의 DISTINCT ON (group_key) ... ORDER BY 문법이 더 명료. 옵티마이저도 더 잘 다룸.

5. 슬로우 쿼리는 언젠가 40001 에러로 진화한다.
평시엔 그냥 "느린 API"였지만, replica/main 동기화가 빡빡한 시점엔 40001로 터진다. 슬로우 쿼리는 단순 UX 문제가 아니라 시한폭탄. 평시 응답 시간이 수 초인 쿼리는 우선순위 높여서 개선.

참고

• PostgreSQL — Hot Standby Conflicts
• PostgreSQL — Error Codes (Class 40)
• PostgreSQL — Table Partitioning Pruning
• Hibernate — SQLStateConversionDelegate
• 관련 글: API는 504, replica DB는 EOF — PostgreSQL Hang 쿼리의 후폭풍