@Transactional 안에서 다른 DB를 호출했더니 — idle-in-transaction 7.54분

TL;DR
- 증상: Grafana DB Connection Hold Time Max가 1시간 안에 8번 튐. 최대 7.54분.
        반면 avg는 5~20ms 정상.
- 처음 본 단서: avg는 정상이고 max만 튀는 패턴 — 전체가 느려진 게 아니라
        '1개 트랜잭션이 커넥션을 독점'하는 형태.
- 진짜 원인: @Transactional이 메인 DB(HikariPool-1) 커넥션을 잡은 상태에서,
        메서드 안에서 *다른 DataSource*(서브 DB, HikariPool-2)를
        10스레드 × 14배치로 병렬 조회.
        그동안 메인 DB 커넥션은 쿼리 없이 트랜잭션만 열어두고 대기
        → PostgreSQL 입장에서 'idle in transaction'.
- 해결: @Transactional 범위를 *쓰기 구간*으로 좁히고
        read와 외부 I/O는 트랜잭션 밖으로 이동.

이런 분께: Spring Multi-DataSource 환경에서 HikariPool hold time이 비정상으로
       튀거나, 매분 도는 @Scheduled가 누적되는 게 의심되는 분

상황 (Context)

• 환경: K8s + Spring Boot + PostgreSQL × 2
• DataSource 구성: HikariPool-1 = 메인 DB, HikariPool-2 = 서브 DB
• 워크로드: 매분 도는 @Scheduled가 여러 카탈로그(Catalog A/B/C)의 항목 데이터를 갱신. 서브 DB에서 항목별 상세를 모아 메인 DB에 upsert.
• 시점: 이 코드는 4.5개월간 운영 중이었음. Grafana에 DB Connection Hold Time 대시보드를 새로 깔면서 처음으로 가시화.

마주한 문제

Grafana DB Connection Hold Time 패널에서 해당 배치 서비스가 단일 1위로 튀어 올라옴.

항목 값

전체 서비스 중 순위	1위 (2위 대비 압도적 격차)
HikariPool-1 max 피크	7.54분 (08:11), 6.40분 (08:50), 6.30분 (08:30)
HikariPool-1 avg	5~20ms (정상)
HikariPool-2 max	항상 0ms (사실상 미사용처럼 보임)
관측 1시간 스파이크	8회, 평균 8.6분 간격

이상한 세 가지:

1. avg는 정상인데 max만 튄다 — 전체가 느려진 게 아니라 어떤 한 작업이 커넥션을 길게 잡고 있음.
2. HikariPool-2 max는 항상 0ms — 서브 DB를 그렇게 많이 조회하는데 측정값이 없다.
3. 4.5개월 운영 중이었는데 왜 이제 보이지? — 실은 처음부터 있던 문제. 대시보드가 늦게 생긴 것뿐이었다.

가설과 시도 — what didn't work

가설 1. 쿼리 자체가 느림

이 시간에 도는 어떤 쿼리가 그냥 오래 걸리는 거 아닐까?

→ 탈락. 그러면 avg도 같이 올라야 한다. avg 5~20ms는 일반 쿼리들은 멀쩡하다는 뜻. avg/max 괴리 자체가 핵심 단서였다.

가설 2. 트래픽 burst — 일시 spike

매분 정기 실행이라 burst와는 거리가 있지만 혹시 특정 시간대에 부하가 겹치나?

→ 탈락. 외부 API 호출도 거의 없는 배치성 작업. 더구나 1시간에 8번이라는 일정한 간격이 burst 패턴이 아님.

가설 3. HikariCP 풀 크기 부족

풀이 작아서 커넥션을 못 받고 대기하는 거?

→ 탈락. 풀 사이즈와 무관하게 한 번 잡은 커넥션을 7분씩 들고 있는 게 문제. 풀을 늘려도 같은 패턴이 다른 자리에서 또 터짐. 해결책이 아니라 임시방편.

남은 가능성: 누군가 커넥션을 잡고 쿼리도 안 보내면서 안 놓는 것. → 다음 단서는 코드를 직접 보는 것이었다.

원인 분석

단서 — 코드 추적

BatchController
@Scheduled(cron = "0 * * * * *")            ← 매분 실행
  └─ processAllCatalogs()

CatalogService
@Transactional                                ← HikariPool-1 (메인 DB) 커넥션 획득
processAllCatalogs()
  ├─ processCatalog(CATALOG_A)                ← @Transactional(REQUIRED) → 같은 커넥션 참여
  ├─ processCatalog(CATALOG_B)
  └─ processCatalog(CATALOG_C)                ← 여기서 7분 hold
       └─ fetchExternalData(~2000 IDs)
            ├─ 150개씩 배치 → ~14배치
            ├─ 10스레드 풀로 externalJdbcTemplate 병렬 조회
            └─ allOf.join()                    ← 블로킹 대기
                                                이 동안 HikariPool-1 커넥션은 유휴
  ← 3개 카탈로그 끝나야 commit → 커넥션 반납

메커니즘 — 한 트랜잭션, 두 DataSource

@Transactional이 잡은 커넥션은 메인 DB (HikariPool-1) 의 것.
그런데 그 메서드 안에서 실제로 가는 쿼리는 externalJdbcTemplate (HikariPool-2, 서브 DB).

[Spring TX Manager]
  HikariPool-1 커넥션 ← BEGIN
                       ↓
                  fetchExternalData() 호출
                       ↓
                  [HikariPool-2] 10 threads × 14 batches
                       ↓ (수 분 소요)
                  allOf.join() 대기
                       ↓
                  결과 받아서 메인 DB에 upsert
                       ↓
  HikariPool-1 커넥션 ← COMMIT

fetchExternalData()가 도는 동안 HikariPool-1 커넥션은 쿼리 한 줄도 안 보내고 트랜잭션만 열어둔 채 기다린다. PostgreSQL pg_stat_activity 시각에서는 state = 'idle in transaction'.

왜 마지막 카탈로그에서 특히 오래 걸리나

카탈로그 항목 수 배치 수 (150개) 라운드 (10스레드)

Catalog A	~500	~4	1
Catalog B	~100	~1	1
Catalog C	~2,000	~14	2

앞 두 개는 1라운드로 끝나는데 마지막 카탈로그만 2라운드. 그 자체가 7분대 스파이크의 정체.

왜 HikariPool-2 max는 0ms로 보였나

가장 헷갈렸던 부분. 서브 DB를 그렇게 많이 두드리는데 메트릭에 안 잡힐 리가 없는데…

→ HikariCP가 Hold Time을 측정하는 시점은 트랜잭션 매니저가 커넥션을 가져갈 때. externalJdbcTemplate은 @Transactional 없이 호출됐기 때문에 Spring TX 매니저를 거치지 않음. HikariCP는 쿼리 단위로 잠깐 빌려주고 바로 반납받는다 → Hold Time 메트릭에 의미 있는 값이 안 잡힘.

즉 서브 DB 쪽은 빠르게 동작하고 있었다. 그동안 놀고 있는 건 메인 DB 쪽 커넥션이었다는 것.

해결

핵심 원칙

@Transactional 안에서 외부 I/O(다른 DB 쿼리 포함)를 분리한다.
서브 DB 조회는 트랜잭션 밖에서 선행하고, 메인 DB 쓰기만 트랜잭션으로 감싼다.

Before

@Transactional                              // 메인 DB 커넥션 획득
fun processCatalog(type: CatalogType): String {
    val ids = catalogRepository.findItemIds(type)         // 메인 DB read
    val details = fetchExternalData(ids)                  // 서브 DB read (수 분 소요)
    val items = mergeWithDetails(ids, details)
    catalogRepository.batchUpsertItems(items)             // 메인 DB write
    catalogRepository.upsertSummary(type)                 // 메인 DB write
}

After

// 트랜잭션 없이 데이터 수집
fun processCatalog(type: CatalogType): String {
    val ids = catalogRepository.findItemIds(type)         // read-only, TX 불필요
    val details = fetchExternalData(ids)                  // 서브 DB read, TX 밖
    val items = mergeWithDetails(ids, details)

    persistCatalog(items, type)                           // 쓰기만 TX
}

@Transactional                                            // 쓰기 범위만 감싸기
internal fun persistCatalog(items: List<Item>, type: CatalogType) {
    catalogRepository.batchUpsertItems(items)
    catalogRepository.upsertSummary(type)
}

같이 한 정리

1. processAllCatalogs()의 @Transactional 제거
   3개 카탈로그는 서로 독립적. 한 트랜잭션으로 묶을 이유가 없었음. (오히려 묶었기 때문에 마지막 카탈로그가 도는 7분 동안 앞 결과들도 같이 잡혀 있었다.)
2. @Scheduled 중복 실행 방어
   매분 cron이라 이전 실행이 안 끝나면 또 시작됨. @Scheduled(fixedDelay = 60_000) 또는 ShedLock으로 중복 실행 차단.

즉시 출혈 차단 — 코드 수정 전에도 깔 수 있는 것

코드를 고치기까지 시간이 걸린다면, 그 사이 풀이 고갈되지 않게 두 가지를 먼저 깔아둔다.

1. PostgreSQL — idle_in_transaction_session_timeout

idle in transaction 상태로 60초 넘어가면 커넥션을 강제 종료. 재시작 없이 적용 가능.

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

장점: 어떤 코드가 또 같은 패턴을 만들어도 풀이 통째로 마르지는 않음.
단점: 진행 중인 트랜잭션이 강제로 죽어서 해당 작업은 실패함 → 코드 수정의 대체재가 아니라 안전벨트.

2. HikariCP — leak-detection-threshold

30초 이상 잡혀있는 커넥션이 있으면 스택 트레이스 로깅. 다음에 같은 패턴이 또 보이면 어디서 잡고 있는지 바로 알 수 있다.

spring:
  datasource:
    hikari:
      leak-detection-threshold: 30000

배운 점

1. @Transactional은 단일 DataSource를 가정한다.
메서드 안에서 다른 DataSource로 쿼리하면, 첫 번째 DataSource 커넥션은 그 작업이 끝날 때까지 쿼리 없이 트랜잭션만 열어두고 대기한다. Multi-DataSource 환경에서는 @Transactional의 경계를 더 좁게 잡아야 한다.

2. HikariCP Hold Time 메트릭은 만능이 아니다.
@Transactional을 거치지 않는 JdbcTemplate 직접 호출은 Hold Time이 사실상 0으로 보일 수 있다. "이쪽 풀은 안 쓰는 것 같은데?"라고 안심하면 안 됨 — 메트릭이 측정 안 한 것이지 안 쓰는 것이 아니다.

3. avg와 max의 괴리는 강력한 단서.

• avg가 같이 올라간다 → 전반적 성능 저하 (트래픽, 인덱스, GC 등)
• avg는 그대로, max만 튄다 → 개별 작업 한두 개가 자원 독점

후자는 코드 한 군데를 찾아내는 문제로 좁혀진다. 풀 늘리기, 인스턴스 늘리기 같은 전반적 처방을 동원할 일이 아니다.

4. @Transactional 범위는 쓰기 작업만.
read는 일반적으로 트랜잭션이 필요 없고(REPEATABLE READ가 필요한 특수 케이스 제외), 외부 I/O — 다른 DB든, HTTP API든, Kafka 발행이든 — 는 트랜잭션 안에 들어가면 그 시간만큼 커넥션이 묶인다. 트랜잭션 안에는 본인 DB 쓰기만이 안전한 기본값.

5. idle_in_transaction_session_timeout은 PostgreSQL 운영의 두 번째 안전벨트.
첫 번째가 statement_timeout(쿼리 오래 도는 것 차단)이라면, 두 번째가 이것 — 쿼리도 안 보내면서 트랜잭션만 열어두는 것 차단. 두 개를 같이 거는 게 표준이다. 이번 케이스도 만약 timeout이 미리 걸려 있었다면 풀 고갈까지 가는 위험은 없었을 것.

참고

• PostgreSQL — idle_in_transaction_session_timeout
• Spring — @Transactional Propagation
• HikariCP — Configuration & Metrics
• ShedLock — Distributed lock for @Scheduled