Oracle JDBC 채번 테이블 병목을 PRAGMA AUTONOMOUS_TRANSACTION으로 끊은 이야기

최근 회사에서 long running lock 때문에 장애를 겪었고, 그 과정에서 Oracle + JDBC 애플리케이션의 테이블 기반 채번 문제를 다시 정리하게 됐습니다. 채번 로직 자체는 복잡하지 않았습니다. 실제로 문제가 된 건 트랜잭션 초반에 잡은 row lock이 긴 업무 트랜잭션 끝까지 살아남는 구조였습니다.

처음엔 애플리케이션 쪽에서 트랜잭션 경계를 더 잘게 쪼개면 해결될 줄 알았습니다. 그런데 제가 회사에서 쓰는 자체 프레임워크와 그 위에서 돌리던 WAS의 JTA transaction manager는 nested transaction을 지원하지 않았습니다. Jakarta Transactions API도 현재 스레드가 이미 트랜잭션에 묶여 있고 transaction manager가 nested transaction을 지원하지 않으면 begin()에서 NotSupportedException이 날 수 있다고 설명합니다.¹ 그래서 채번은 DB 레벨 autonomous routine으로 내려야 했습니다.

이 글에서는 long running lock 때문에 채번이 실패하던 구조와, 제가 이번에 선택한 해법을 정리해보겠습니다.

왜 채번이 실패하는 구조가 되었나

시퀀스 객체 대신 채번 테이블을 써야 할 때가 있습니다. 여러 로직이나 여러 테이블이 같은 번호 체계를 공유해야 하면 보통은 seq_name='ORDER' 같은 row 하나를 두고 그 값을 계속 증가시키는 방식입니다. 채번 로직 자체는 단순하지만, 그 row에 대한 lock이 필요해집니다.

보통은 중복 번호를 막기 위해 아래처럼 select ... for update를 사용합니다.

SELECT next_val
  FROM app_sequence
 WHERE seq_name = 'ORDER'
 FOR UPDATE;

Oracle 문서에서도 SELECT FOR UPDATE는 update/delete 전에 특정 row를 명시적으로 잠그기 위한 문장으로 설명합니다. active set에 포함된 row는 open 시점에 잠기고, 다른 세션은 그 row에 대한 lock을 기다리게 됩니다.²

채번 직후 바로 commit하는 짧은 트랜잭션이면 그럭저럭 버틸 수 있습니다. 그런데 제가 본 시스템은 그렇지 않았습니다. 복잡한 JOIN 으로 이루어진 SQL 로 검증하고 여러 테이블에 상태를 update하거나 insert 했습니다. 모두 같은 JDBC 트랜잭션 안에 붙어 있었고, 그동안 채번 row lock도 같이 살아 있었습니다.

결국 채번 row lock의 점유 시간은 테이블에서 채번하는 로직 경계가 아니라 업무 전체 트랜잭션 길이에 끌려다니게 됩니다.

long running lock이 어떻게 채번 실패로 이어졌나

제가 문제를 이해한 방식은 아래와 비슷했습니다.

세션 A
  BEGIN UserTransaction
  SELECT next_val FROM app_sequence WHERE seq_name = 'ORDER' FOR UPDATE
  orders INSERT
  order_items INSERT
  inventory UPDATE
  검증 쿼리
  외부 시스템 연동
  COMMIT

세션 B
  BEGIN UserTransaction
  SELECT next_val FROM app_sequence WHERE seq_name = 'ORDER' FOR UPDATE  -- 세션 A가 lock 해제할 때까지 대기

세션 C
  BEGIN UserTransaction
  SELECT next_val FROM app_sequence WHERE seq_name = 'ORDER' FOR UPDATE  -- 세션 B 뒤에서 또 대기

세션 D...

이 구조에서는 사용자가 늘어날수록 같은 채번 row를 기다리는 세션이 줄줄이 쌓입니다. 운영에서는 세션들이 서로 멈춘 것처럼 보였고, 요청이 몰리면 채번 단계에서 오래 대기하다가 결국 실패로 처리되기도 했습니다.

문제를 재현하는 건 어렵지 않았습니다. 세션 초반에 채번 row를 FOR UPDATE로 잠근 뒤 같은 트랜잭션 안에 DML과 검증 쿼리를 누적하고, 여러 세션이 동시에 같은 seq_name='ORDER'를 요청하게 만들면 됐습니다. 여기서 봐야 할 건 TPS보다 lock hold time과 wait queue 길이였습니다.

제가 택한 해결책: 채번만 autonomous transaction으로 분리

애플리케이션 전체 트랜잭션은 그대로 두고, 채번 로직만 저장 프로시저의 autonomous transaction으로 분리했습니다.

Oracle의 autonomous transaction은 메인 트랜잭션과 완전히 독립적으로 시작됩니다. Oracle 문서 표현을 그대로 쓰면, autonomous transaction은 시작 후 메인 트랜잭션과 lock, resource, commit dependency를 공유하지 않습니다.³

상위 JDBC/JTA 트랜잭션이 길게 살아 있어도, 채번 프로시저 안에서 row를 잠그고 값을 증가시킨 뒤 바로 commit하면 채번 row lock은 거기서 끝납니다. 이후 상위 트랜잭션이 주문 insert를 계속하든, 마지막에 rollback되든, 채번 row lock 점유 시간과는 분리됩니다.

기존 제약 때문에 테이블 기반 채번을 유지해야 한다면, 이 방식은 long running lock 구간을 끊는 현실적인 개선책은 됐습니다. 대신 메인 트랜잭션이 검증에 실패해서 rollback되면 번호의 gap은 생길 수 있습니다.

왜 DB 레벨로 내렸나

제 애플리케이션은 Spring이 아니었습니다. 회사에서 쓰는 자체 프레임워크와 WAS의 JTA transaction manager가 nested transaction을 지원하지 않았기 때문에, 채번만 따로 떼어 애플리케이션 레이어에서 새 트랜잭션으로 감싸는 방식은 쓸 수 없었습니다. 그래서 채번은 DB 레벨 autonomous routine으로 내려야 했습니다.

Spring의 @Transactional(propagation = Propagation.REQUIRES_NEW)도 같은 문제를 푸는 방법이 될 수 있습니다. 다만 Spring 문서 기준으로 REQUIRES_NEW는 항상 독립된 physical transaction을 사용합니다.⁴ 호출 흐름은 대략 아래와 같습니다.

1. 기존 트랜잭션 suspend
2. 새 physical transaction 시작
3. 채번용 JDBC/JPA 호출
4. commit
5. 원래 트랜잭션 resume

이 방식도 문제를 풀 수는 있습니다. 다만 채번 한 번마다 애플리케이션 레이어에서 transaction context suspend/resume, 별도 physical transaction 시작/종료, 추가 resource acquisition이 한 번 더 들어갑니다. stored procedure 쪽도 autonomous transaction을 새로 시작하긴 합니다. 대신 그 새 트랜잭션이 DB 안에서 lock, update, commit, 결과 반환까지 끝나기 때문에 추가 network hop이나 DB/client 사이 정합성 검증이 필요 없습니다.

Oracle 문서 기준으로 보면, autonomous function도 SQL에서 호출되면서 자체 transaction을 commit할 수 있습니다.⁵ Oracle 문서에도 SQL 문장에서 호출된 서브프로그램은 PRAGMA AUTONOMOUS_TRANSACTION이 없는 한 transaction control statement를 실행할 수 없다고 정리돼 있습니다.⁶

저는 그래도 function 대신 procedure를 골랐습니다. 채번은 번호 소비와 commit이 같이 일어나는 쓰기 작업이어서, select SOME_FN() from dual 같은 SQL expression보다 부수효과를 명시적으로 드러내는 procedure call 쪽이 더 잘 맞았습니다.

Oracle 예시: 채번 테이블

예시 테이블은 단순하게 두겠습니다.

CREATE TABLE app_sequence (
    seq_name    VARCHAR2(50) PRIMARY KEY,
    next_val    NUMBER(19)   NOT NULL,
    updated_at  TIMESTAMP    DEFAULT SYSTIMESTAMP NOT NULL
);

INSERT INTO app_sequence (seq_name, next_val, updated_at)
VALUES ('ORDER', 1000001, SYSTIMESTAMP);

이 테이블은 Oracle sequence object와 달리 애플리케이션이 직접 row를 관리합니다. 그래서 번호 발급 정책을 커스터마이즈하기는 쉽지만, 반대로 lock 경합도 직접 책임져야 합니다.

Oracle 예시: autonomous transaction procedure

1. 프로시저 안에서 PRAGMA AUTONOMOUS_TRANSACTION 선언
2. 대상 row를 FOR UPDATE로 잠금
3. 현재 번호를 읽고 다음 값으로 증가
4. 즉시 COMMIT
5. 발급 번호를 OUT 파라미터로 반환

CREATE OR REPLACE PROCEDURE allocate_id (
    p_seq_name IN  VARCHAR2,
    p_allocated OUT NUMBER
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_next_val NUMBER;
BEGIN
    SELECT next_val
      INTO v_next_val
      FROM app_sequence
     WHERE seq_name = p_seq_name
       FOR UPDATE;

    UPDATE app_sequence
       SET next_val = v_next_val + 1,
           updated_at = SYSTIMESTAMP
     WHERE seq_name = p_seq_name;

    p_allocated := v_next_val;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Oracle 문서에서도 autonomous transaction은 메인 트랜잭션과 독립적이며, 종료 전 COMMIT 또는 ROLLBACK으로 끝내야 한다고 설명합니다.⁷

이렇게 하면 채번 row lock은 이 프로시저 안에서만 짧게 유지됩니다. 상위 JDBC 트랜잭션이 이후 5초를 더 쓰든, 마지막에 rollback되든, 채번 row lock은 이미 해제된 상태입니다.

JDBC 호출 예시

JDBC에서는 아래처럼 호출했습니다.⁸

UserTransaction utx = ...;
DataSource dataSource = ...;

utx.begin();

try (Connection con = dataSource.getConnection();
     CallableStatement cs = con.prepareCall("{ call allocate_id(?, ?) }") ) {

    cs.setString(1, "ORDER");
    cs.registerOutParameter(2, Types.NUMERIC);
    cs.execute();

    long orderId = cs.getLong(2);

    // 여기서부터는 긴 업무 트랜잭션
    // orders insert
    // order_items insert
    // inventory update
    // 추가 검증/조회

    utx.commit();
} catch (Exception e) {
    utx.rollback();
    throw e;
}

이 흐름의 핵심은 allocate_id() 호출 시점의 row lock이 JDBC 업무 트랜잭션 전체 길이와 분리된다는 점입니다. 호출 직후 business DML이 길어져도, 채번 lock은 이미 autonomous transaction의 commit으로 끝났습니다.

바꾸기 전과 후를 나란히 보면

1) 기존 방식: long transaction 안에서 lock 유지

[세션 A]
BEGIN
SELECT next_val ... FOR UPDATE
orders INSERT
order_items INSERT
inventory UPDATE
검증 쿼리
추가 로직
COMMIT

[세션 B, C, D]
BEGIN
SELECT next_val ... FOR UPDATE  -- 대기

이 경우 lock hold time은 채번 코드 길이가 아니라 상위 업무 로직 길이에 따라 결정됩니다. long running lock이 길어질수록 뒤 요청은 채번 단계에서 실패 쪽으로 밀리기 쉽습니다.

2) 변경 방식: autonomous transaction으로 채번만 분리

[세션 A]
BEGIN UserTransaction
CALL allocate_id(...)  -- 내부에서 lock, update, commit 종료
orders INSERT
order_items INSERT
inventory UPDATE
추가 로직
COMMIT

[세션 B, C, D]
CALL allocate_id(...)  -- 앞선 채번 COMMIT 후 순차 진행

이 경우 대기열이 완전히 사라지는 것은 아닙니다. 같은 row를 갱신하는 이상 동시에 들어온 요청이 한 줄로 서는 구조는 남습니다. 다만 그 대기 구간이 번호 발급 로직 길이로 축소됩니다.

트레이드오프

native sequence를 쓸 수 있다면 대개 그쪽이 더 단순합니다. 이 패턴은 기존 제약 때문에 테이블 기반 채번을 유지해야 할 때의 대안에 가깝습니다.

1. 번호 gap은 생길 수 있습니다

autonomous transaction에서 번호를 먼저 commit했는데 상위 업무 트랜잭션이 rollback되면, 그 번호는 비게 됩니다. sequence를 써도 비슷한 성격의 gap은 생길 수 있기 때문에, 절대 연속 번호가 필요한 도메인이라면 이 문제를 별도로 다뤄야 합니다.

2. lock 경합은 줄어들지만 한 줄 대기는 남습니다

같은 seq_name='ORDER' row를 모두 갱신한다면, 번호 발급은 여전히 lock 대기열에 한 줄로 섭니다. 다만 그 줄이 긴 업무 트랜잭션 전체 길이가 아니라, 채번 프로시저 실행 시간만큼으로 짧아지는 것입니다.

3. sequence를 쓸 수 있다면 그게 더 단순합니다

기존 정책 때문에 꼭 테이블 기반 채번을 유지해야 하는 게 아니라면, 저는 먼저 native sequence나 identity로 바꿀 수 있는지를 검토하는 편이 더 낫다고 봅니다.

정리

채번 실패는 상위 JDBC 트랜잭션에 묶인 row lock의 지속 시간에서 시작됐습니다. 제가 쓰던 환경에서는 애플리케이션 레벨 nested transaction이 안 됐기 때문에, 채번은 Oracle stored procedure 안에서 autonomous transaction으로 짧게 끝내는 쪽으로 정리됐습니다.

지금 기준은 아래와 같습니다.

• sequence로 바꿀 수 있으면 먼저 sequence 검토
• 테이블 채번을 유지해야 하면 autonomous transaction procedure 검토
• 함수 호출을 SQL expression처럼 숨기지 말고 procedure call로 드러내기