즐거운인생

보통 순번 채번이 필요한 경우 대부분 Sequence를 사용합니다.

하지만 아래와 같은 사유로 시스템을 운영하다 보면 MAX + 1로 처리된 순번도 눈에 많이 보입니다.

1. 오래 전에 작성했거나,

2. 컬럼 조합에 따른 순번이어야 하는 경우 (ex> 접수일자 + 접수순번)

위와 같은 경우이거나, 프로젝트를 한 시스템을 인계 받았을 때 개발 가이드를 준수하지 않은 코드 등에서는

아래와 같이 작성된 MAX + 1 Query도 종종 있습니다.

INSERT  INTO  TBL_A (
	REQ_DT
     ,  SEQ
     ,  ...
     )  VALUES (
     	'20220622'
     , (SELECT  MAX(SEQ) + 1
          FROM  TBL_A
         WHERE  REQ_DT = '20220622')
     ,  ...

대강 위와 같은 형태입니다.

위와 같은 형태도 사실 거래가 많지 않은 시스템은 크게 문제가 없습니다. 순서대로 채번도 잘되고 데이터 무결성에도 문제가 없습니다.

하지만 거래량이 어느 정도 이상이 되는 대용량 시스템의 경우에는 언제든지 해당 Query로 인해 이슈가 발생할 수 있습니다.

문제점

해당 Query를 사용하는 transaction이 단순히 저 Query 하나로만 동작을 한다면 그나마 문제가 좀 덜하겠지만 해당 transaction에서 타 시스템과의 인터페이스 등 시간이 소요되는 다른 코드들이 존재한다면 Unique constraints violation이 발생하게 됩니다.

현재 MAX(SEQ)의 값이 1이라고 하면

transaction A 00:01:01.111(start) 00:01:567(commit)
transaction B 00:01:01.234(start) 00:01:789(commit)

위 순서로 유입되는 transaction A, B가 있다면 A와 B는 모두 MAX(SEQ) + 1 값으로 2를 얻어갑니다. 하여 둘 중에 먼저 commit이 일어나는 A는 SEQ 값을 2로 하여 문제 없이 commit 되고 transaction이 종료되지만,

B는 동일한 REQ_DT에 동일한 SEQ 값이 이미 생성되었으므로 unique constraints violation이 발생합니다.

(물론 REQ_DT, SEQ가 Composit PK 혹은 Unique Index라는 가정입니다.)

Sequence로 변경

이런 내용을 가장 깔끔하게 처리하는 방법은 Sequence 입니다. Sequence의 경우는 nextval을 한 후 바로 DD 테이블의 값이 증가하고 rollback이 불가능하므로 데이터의 무결성이 유지됩니다.

하지만 위와 같이 REQ_DT 하위의 순번으로 데이터가 관리되어야 한다면 Sequence는 사용할 수가 없습니다.

또한 현재 운영 중인 시스템의 영향도를 모두 파악하여 재구성한 후 Sequence로 변경하는 것도 결코 쉬운 일은 아닙니다.

(해당 테이블을 타 시스템으로 전달하여 해당 시스템도 해당 순번을 Key 값으로 사용하고 있는 경우 등등)

채번 테이블 사용

이런 경우에는 좀 귀찮기는 해도 채번 테이블을 사용할 수 있습니다. 채번 테이블은 MAX 순번을 관리하는 별도의 테이블을 운영하고 해당 테이블에서 값을 읽을 때 SELECT ~ FOR UPDATE로 Lock 처리를 통해 무결성을 유지하는 방법입니다.

REQ_DT VARCHAR2(8)
MAX_SEQ Number

위와 같은 형태의 채번 테이블이 있다면 transaction을 시작할 때 채번 테이블에서 순번을 가져오면서 Row Lock을 획득합니다.

SELECT MAX_SEQ
  FROM TBL_SEQ
 WHERE REQ_DT = #{reqDt}
   FOR UPDATE

 가져온 순번으로 원장에 데이터를 INSERT 합니다.

INSERT INTO TBL_A (
       REQ_DT
     , SEQ
     , ...
     ) VALUES (
       #{reqDt}
     , #{maxSeq}
     , ...

채번 테이블의 MAX_SEQ 값에 +1 한 후에 transaction을 종료 처리합니다.

UPDATE TBL_SEQ
   SET MAX_SEQ = MAX_SEQ + 1
 WHERE REQ_DT = #{reqDt}

위와 같은 방법으로 동시성 문제를 해결하고 데이터 무결성을 유지할 수 있습니다.