안전한 채번 설계: Sequence·Identity·채번 Table·MAX+1

핵심 요약

안전한 채번 설계는 먼저 유일성, 증가 방향, 요청 순서, Commit 순서, 번호 연속성, 동시 처리량을 서로 다른 요구로 분리하는 것에서 시작합니다.

고동시성 내부 식별자
→ Sequence 또는 Identity 우선 검토
→ Gap과 Commit 순서 불일치 허용

업무 범위별 표시 번호
→ 범위·형식·확정 시점·취소 정책을 별도로 정의
→ 필요하면 범위별 채번 Row를 직렬화

강한 Gapless 요구
→ 모든 요청을 직렬화하고 실패·취소·재처리까지 통제
→ 처리량과 운영 복잡도 증가를 감수

Oracle Sequence는 Transaction Commit·Rollback과 독립적으로 NEXTVAL을 소비하므로 고동시성에 적합하지만 Gapless와 Commit 순서를 보장하지 않습니다. Identity Column도 Sequence Generator를 사용하므로 같은 성격을 가집니다. 반면 MAX(id)+1은 여러 Session이 같은 Snapshot에서 같은 다음 값을 계산할 수 있어 일반적인 동시 환경의 채번 방식으로 안전하지 않습니다.

학습 목표

• Sequence·Identity·채번 Table·MAX+1의 동시성 구조를 비교한다.
• 유일성·증가성·요청 순서·Commit 순서·Gapless를 구분한다.
• CACHE, ORDER, CYCLE, SCALE의 의미와 Trade-off를 설명한다.
• Identity Column의 입력 모드와 제약을 설명한다.
• 채번 Row의 Lock 범위와 Autonomous Transaction의 독립 Commit 위험을 진단한다.
• Surrogate Key와 업무 표시 번호를 분리해 설계한다.

1. 채번 요구사항을 분해한다

다음 요구는 서로 같지 않습니다.

1. 중복 값이 저장되지 않아야 한다.
2. 다음 값이 이전 값보다 커야 한다.
3. NEXTVAL 요청 순서대로 번호가 생성되어야 한다.
4. Commit된 업무 순서와 번호 순서가 같아야 한다.
5. Rollback·취소·장애가 있어도 번호가 비면 안 된다.

Primary Key 목적이라면 Sequence만 믿기보다 Table에 PRIMARY KEY 또는 UNIQUE Constraint를 유지해야 합니다. CYCLE, 수동 입력, 다른 Generator 사용까지 포함한 최종 데이터 무결성은 Constraint가 담당합니다.

2. Oracle Sequence와 Transaction 독립성

Sequence는 Table에서 MAX(id)+1을 찾지 않고 독립된 Sequence Generator에서 값을 생성합니다.

CREATE SEQUENCE order_id_seq
  START WITH 1
  INCREMENT BY 1
  CACHE 1000
  NOORDER
  NOCYCLE;

INSERT INTO orders(order_id, customer_id, order_date)
VALUES (order_id_seq.NEXTVAL, :customer_id, SYSTIMESTAMP);

Sequence 값은 Transaction의 Commit이나 Rollback과 독립적으로 증가합니다.

Session A: NEXTVAL 100 획득
→ INSERT 실패 또는 ROLLBACK
→ 100은 Sequence에 반환되지 않음
→ 이후 NEXTVAL은 101 이후 값

이 때문에 업무 Transaction의 Row Lock으로 채번을 직렬화하지 않고 여러 Session이 서로 다른 값을 빠르게 얻을 수 있습니다. 대신 번호의 빈칸은 오류가 아니라 설계 특성일 수 있습니다.

NOCYCLE은 최대·최소값에 도달한 뒤 더 이상 값을 만들지 않는 Primary Key용 기본 선택입니다. CYCLE은 한계에 도달하면 반대편 값부터 다시 생성하므로 장기간 유일성이 필요한 Key에서는 Constraint 충돌 가능성을 반드시 고려해야 합니다.

3. CACHE·NOCACHE와 Gap

CACHE n은 여러 Sequence 값을 Memory에 미리 확보해 빠르게 제공하는 설정입니다.

NOCACHE 또는 매우 작은 CACHE
→ Sequence 상태를 더 자주 확보
→ 접근 비용 증가 가능

충분한 CACHE
→ NEXTVAL 접근 비용 감소
→ 장애·Cache 소실 시 사용하지 않은 값이 건너뛰어질 수 있음

Oracle AI Database 26ai에서 CACHE의 최소 지정값은 2이며, CACHE와 NOCACHE를 모두 생략하면 기본 20개를 Cache합니다. Oracle은 RAC에서 Sequence 성능을 위해 CACHE 사용을 권장합니다.

System Failure가 발생하면 아직 사용되지 않은 Cached Value가 손실될 수 있고, 잠재적 손실 수는 설정한 CACHE 값까지일 수 있습니다. Sequence가 Shared Pool에서 Aging Out되어도 Cache의 미사용 번호가 손실될 수 있습니다. 따라서 큰 Gap 하나만으로 중복이나 데이터 오염을 단정하지 않습니다.

SELECT sequence_name,
       increment_by,
       cache_size,
       order_flag,
       cycle_flag,
       last_number,
       scale_flag
FROM   user_sequences
WHERE  sequence_name = 'ORDER_ID_SEQ';

USER_SEQUENCES.LAST_NUMBER는 Cache를 사용하면 마지막으로 사용한 값이 아니라 Disk에 기록된 값이며, Cache에 마지막으로 배치된 값일 수 있습니다. 따라서 정확한 최근 발급 번호나 다음 실제 업무 번호로 해석하지 않습니다.

4. ORDER·NOORDER와 Commit 순서

ORDER는 Sequence 번호가 요청된 순서대로 생성되도록 보장합니다. NOORDER는 그 보장을 요구하지 않으며 기본값입니다.

NEXTVAL 요청 순서
≠
Transaction Commit 순서

Session A: NEXTVAL 100 → 긴 업무 처리 ─────────→ COMMIT
Session B: NEXTVAL 101 → 짧은 처리 → COMMIT

B가 먼저 Commit하면 Commit 관점의 순서는 101, 100이 될 수 있습니다. ORDER를 사용해도 Transaction 처리시간을 동일하게 만들지 않으므로 Commit 순서를 보장하지 않습니다.

Primary Key 생성은 일반적으로 NOORDER와 충분한 CACHE를 우선 검토합니다. 요청 순서가 실제 업무 요구인지 증명되지 않은 상태에서 ORDER를 사용하면 특히 RAC에서 불필요한 조정 비용과 enq: SV - contention 같은 대기를 늘릴 수 있습니다.

5. Identity Column

Identity Column은 Column 정의에 Sequence Generator를 연결합니다.

CREATE TABLE orders (
    order_id NUMBER
      GENERATED ALWAYS AS IDENTITY
      (CACHE 1000),
    customer_id NUMBER NOT NULL,
    order_date  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

Identity의 identity_options는 대부분 CREATE SEQUENCE와 같은 설정을 사용합니다. 따라서 CACHE, Gap, Rollback 비반환, Commit 순서 미보장 특성을 공유합니다. Oracle은 Identity 생성 시 성능을 위해 기본 20보다 큰 CACHE를 지정할 것을 권장합니다.

주요 제약은 다음과 같습니다.

• Table당 Identity Column은 하나만 지정할 수 있습니다.
• 숫자 Data Type이어야 합니다.
• NOT NULL과 NOT DEFERRABLE이 암시적으로 적용됩니다.
• Identity Column에 별도 DEFAULT Clause를 함께 지정할 수 없습니다.
• CREATE TABLE AS SELECT는 원본 Column의 Identity 속성을 상속하지 않습니다.

Table 전용 Key를 간결하게 정의할 때 Identity가 편리합니다. 여러 Table이 하나의 번호 체계를 공유하거나 Sequence 자체를 명시적으로 운영해야 하면 독립 Sequence가 더 적합합니다.

6. MAX(id)+1의 Race Condition

다음 SQL은 단일 Session에서는 정상처럼 보입니다.

SELECT NVL(MAX(order_id), 0) + 1
FROM   orders;

하지만 여러 Session은 각자의 일관된 Snapshot에서 같은 MAX를 볼 수 있습니다.

Session A: MAX = 100 → 101 계산
Session B: MAX = 100 → 101 계산

A: 101 INSERT 성공
B: 101 INSERT → Unique Constraint 충돌 또는 중복 저장

Unique Constraint가 있으면 중복 저장은 차단하지만 다음 비용은 남습니다.

• 충돌 예외와 재시도
• MAX 조회 비용
• 조회와 INSERT 사이 경쟁 구간
• 재시도 폭증 시 응답시간 변동

Constraint가 없으면 실제 중복 데이터가 저장될 수 있습니다. MAX+1을 안전하게 만들려면 결국 단일 Writer를 강제하거나 별도 채번 Row를 잠가야 하므로 Sequence가 제거한 직렬화를 다시 도입하게 됩니다.

7. 채번 Table과 Row Lock 직렬화

업무 범위별 마지막 번호를 별도 Row에 저장할 수 있습니다.

CREATE TABLE document_sequence (
    document_type VARCHAR2(30) PRIMARY KEY,
    last_no       NUMBER NOT NULL
);

한 Statement로 증가와 반환을 함께 수행합니다.

UPDATE document_sequence
SET    last_no = last_no + 1
WHERE  document_type = :document_type
RETURNING last_no INTO :new_no;

같은 document_type을 갱신하는 Transaction은 동일 Row Lock에서 직렬화됩니다. 서로 다른 지점·연도·문서 유형을 별도 Row로 나누면 경합 범위를 줄일 수 있지만, 분할 기준이 실제 번호 유일성 범위와 같아야 합니다.

장점
→ 범위별 증가 규칙을 Transaction 안에서 통제
→ Main 업무와 같은 Transaction이면 함께 Rollback 가능

비용
→ 범위별 Hot Row
→ Transaction이 길수록 다음 채번 대기 확대
→ 강한 연속성 요구일수록 처리량 감소

반드시 DML 직후 SQL%ROWCOUNT를 확인합니다. 대상 범위 Row가 없으면 UPDATE ... RETURNING이 번호를 만들지 못하므로 초기 Row 생성 경쟁을 Unique Constraint와 예외 처리로 통제해야 합니다.

UPDATE document_sequence
SET    last_no = last_no + 1
WHERE  document_type = p_document_type
RETURNING last_no INTO l_new_no;

IF SQL%ROWCOUNT = 0 THEN
  -- 범위 Row 초기화 정책 또는 명시적 오류 처리
  RAISE_APPLICATION_ERROR(-20001, '채번 범위가 없습니다.');
END IF;

번호를 얻은 뒤 외부 API 호출이나 사용자 입력을 기다리면 채번 Row Lock이 오래 유지됩니다. 채번은 업무 확정 직전에 수행하고 Transaction을 짧게 끝냅니다.

8. Gapless 요구의 실제 범위

채번 Table을 Main 업무와 같은 Transaction에서 갱신하면 Main Transaction Rollback 시 last_no도 돌아갈 수 있습니다. 그러나 이것만으로 모든 상황의 Gapless가 자동 보장되지는 않습니다.

번호 획득
→ 업무 Row 저장
→ COMMIT 성공
→ 이후 업무 취소·문서 폐기

Commit 뒤의 취소나 외부 전송 실패까지 번호를 다시 사용하면 감사 추적과 중복 의미가 깨질 수 있습니다. 강한 연속성 요구는 다음을 함께 정의해야 합니다.

• 번호 부여 시점
• Commit 실패와 재시도
• 취소 번호를 결번으로 둘지, 취소 기록으로 남길지
• 장애 복구 시 중복 발급 방지
• 유일성 Constraint와 감사 이력

따라서 “번호에 빈칸이 없음”보다 “모든 번호의 발급·확정·취소 상태를 설명할 수 있음”이 더 안전한 운영 목표가 될 수 있습니다. 법적 번호는 해당 업무 규정과 감사 요구를 별도로 확인합니다.

9. Autonomous Transaction 채번

Autonomous Transaction은 Main Transaction과 Lock·Commit 의존성을 공유하지 않는 독립 Transaction입니다.

Autonomous 채번 COMMIT 성공
→ Main Transaction 재개
→ Main 업무 실패·ROLLBACK
→ 채번 번호와 기록은 이미 Commit됨

따라서 Main 업무 Rollback과 무관하게 번호가 소비되어 Gap이 발생할 수 있습니다. 번호 할당 기록을 반드시 독립적으로 남겨야 하고 Gap을 허용하는 경우에만 신중히 사용합니다.

또한 Autonomous Transaction이 Main Transaction이 보유한 Resource에 접근하면 Deadlock이 발생할 수 있습니다. Main Transaction이 채번 Row나 연관 Row를 이미 잠근 뒤 Autonomous Routine이 같은 Resource를 갱신하는 구조를 피해야 합니다.

Autonomous Transaction을 단순히 Row Lock 대기를 숨기거나 Gapless를 만드는 해법으로 사용하지 않습니다. 재시도 시 동일 업무에 여러 번호가 할당되지 않도록 Idempotency Key나 할당 상태를 함께 설계해야 합니다.

10. Surrogate Key와 업무 표시 번호 분리

내부 Join용 Key와 사용자에게 보이는 번호는 목적이 다를 수 있습니다.

order_id
→ 내부 PK·Join용
→ Sequence 또는 Identity
→ Gap 허용, 높은 동시성

display_order_no
→ 날짜·지점·문서 유형·일련번호
→ 업무 확정 시 별도 정책으로 발급
→ Unique Constraint와 취소·감사 상태 관리

이렇게 분리하면 내부 PK는 빠르고 단순하게 생성하고, 표시 번호의 형식·범위·재발급·취소 규칙을 독립적으로 운영할 수 있습니다. 표시 번호도 Application 검사만 믿지 말고 실제 유일성 범위에 맞는 UNIQUE Constraint를 둡니다.

11. Scalable Sequence와 Index Hotspot

단조 증가 Key는 B-Tree Index의 오른쪽 Leaf Block에 Insert가 집중될 수 있습니다. 실제로 Sequence나 Index Block 경합이 확인된 고동시성 적재 환경에서는 SCALE Sequence를 검토할 수 있습니다.

SCALE은 Instance와 Session에서 파생한 Offset을 Sequence 값 앞에 붙여 인접 Key 집중을 완화합니다. 대신 값의 외형과 자연 정렬 순서가 일반 단조 Sequence와 달라지므로 업무 표시 번호로 사용하기보다 Unordered Primary·Unique Key에 적합합니다.

Oracle은 SCALE과 ORDER를 동시에 사용하는 것을 강하게 권장하지 않습니다. 순서 보장과 분산된 Key 생성은 서로 반대되는 목표이므로 Wait Event와 Index 경합 증거 없이 무조건 적용하지 않습니다.

12. 운영 진단과 선택 절차

12.1 먼저 요구를 확정한다

유일성만 필요한가?
→ Gap이 허용되는가?
→ 요청 순서와 Commit 순서 중 무엇이 필요한가?
→ 번호의 유일성 범위는 전역·지점·연도·문서 유형 중 무엇인가?
→ 번호 발급 후 취소·재시도는 어떻게 기록하는가?

12.2 구현과 Constraint를 확인한다

• 내부 PK: NOCYCLE Sequence 또는 Identity와 PK Constraint
• 범위별 번호: 채번 Row의 PK·Unique 범위와 업무 범위 일치
• 표시 번호: 최종 조합 Column 또는 표현에 Unique Constraint
• MAX+1: 동시 환경의 기본 방식에서 제외
• Autonomous: 독립 Commit 필요성과 Deadlock 가능성 검토

12.3 운영 지표를 확인한다

• Sequence의 CACHE_SIZE, ORDER_FLAG, CYCLE_FLAG, SCALE_FLAG
• enq: SV - contention과 Index Block 경합
• 채번 Row의 Row Lock Wait, 대기 Session 수, Transaction 지속시간
• Unique Constraint 충돌과 Retry 횟수
• 번호 발급·확정·취소 상태의 감사 가능성

Gap이 크다는 사실만으로 Cache를 즉시 줄이거나 NOCACHE로 바꾸지 않습니다. 먼저 실제 오류인지, 단순 Rollback·Concurrent Use·Cache 손실인지 구분하고 처리량 저하와 맞바꾸는 변경인지 검증합니다.

혼동하기 쉬운 판단