출처 : http://inhim.blog.me/100101665843

채번 테이블을 사용하여 키값을 가져오는 방식은 추천할만한 방법은 아니다.

특히 키값이 건너뜀 없이 순차적으로 발행되어야 하는 업무요건이라면 채번 트랜잭션과 메인 트랜잭션이 분리될 수 없기 때문에 심한 Lock 경합을 피할 수 없을 것이다.

이런 업무요건은 heavy 한 동시트랜잭션 환경에서는 받아들일 수 없는 것이다.

키값이 건너뛰어 발행되도 괜찮다면 채번 트랜잭션은 메인 트랜잭션으로부터 분리하여 Lock 경합을 최소화하여야 한다.

 

아래의 방법은 간단한 자율트랜잭션 채번펑션을 이용해 이를 구현한 사례로서 "오라클 성능 고도화 원리와 해법 1(조시형 저)" 에서 발췌하였다.

 

이 사례에서 중요한 것은 채번펑션이 자율트랜잭션(autonomous_transaction) 이어야 한다는 것이다. 그렇지 않다면 메인트랜잭션의 일관성이 깨지게 될 것이기 때문이다.

예를 들어, 메인트랜잭션에서 8번 라인에서 에러를 만나 rollback 될 때에도 2번 라인의 update 문은 commit 될 것이기 때문이다.

하지만 채번펑션이 자율트랜잭션이라면 2번 라인의 update 문은 commit 되지 않을 것이므로 메인트랜잭션은 일관성을 지킬 수 있다.

 
채번 테이블 생성

CREATE TABLE SEQ_TAB (GUBUN  VARCHAR2 (3)
          , SEQ   NUMBER
          , CONSTRAINT PK_SEQ_TAB PRIMARY KEY (GUBUN, SEQ)
           )
ORGANIZATION INDEX; 

ORGANIZATION INDEX 에 대한 설명 : http://blog.naver.com/tyboss/70151522617

 

채번 Function

 CREATE OR REPLACE FUNCTION SEQ_NEXTVAL (L_GUBUN VARCHAR2)
  RETURN NUMBER
IS
  PRAGMA AUTONOMOUS_TRANSACTION;                  --자율트랜잭션 선언
  L_NEW_SEQ  SEQ_TAB.SEQ%TYPE;
BEGIN
  SELECT SEQ + 1
   INTO L_NEW_SEQ
   FROM SEQ_TAB
  WHERE GUBUN = L_GUBUN
       FOR UPDATE;

  UPDATE SEQ_TAB
   SET SEQ = L_NEW_SEQ
  WHERE GUBUN = L_GUBUN;

  COMMIT;
  RETURN L_NEW_SEQ;
END;
/

 

채번 Function 사용

 BEGIN
  UPDATE TAB1
   SET COL1 = :X
  WHERE COL2 = :Y;

  INSERT INTO TAB2
    VALUES (SEQ_NEXTVAL ('100')
       , :X
       , :Y
       , :Z);

  LOOP
   -- do anything ...
   END LOOP;
   COMMIT;
 EXCEPTION
   WHEN OTHERS THEN ROLLBACK;
 END;
/

 

======================================================================================================

 

자율트랜잭션(autonomous transaction)이란 부모트랜잭션으로부터 독립적인 트랜잭션이라는 뜻으로서, 자율트랜잭션에 대해 더 알기 원한다면 아래 실습 결과를 보세요.

채번할 때 사용하는 COMMIT 때문에 PRAGMA AUTONOMOUS_TRANSACTION; 옵션을 사용하지 않은 경우에는 비지니스 로직의 ROLLBACK이 실행되지 않음

 CREATE TABLE TAB_NONAUTO (MSG CLOB);

CREATE TABLE TAB_AUTO (MSG CLOB);

-- 1번 프로시저 생성
CREATE OR REPLACE PROCEDURE NONAUTONOMOUS_INSERT
AS
BEGIN
  INSERT INTO TAB_NONAUTO
    VALUES ('일반자식트랜잭션 입력');

  COMMIT;
END;
/

-- 2번 프로시저 생성
CREATE OR REPLACE PROCEDURE AUTONOMOUS_INSERT
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO TAB_AUTO
    VALUES ('자율자식트랜잭션 입력');

  COMMIT;
END;
/

-- 테스트
BEGIN
    INSERT INTO TAB_NONAUTO VALUES('부모트랜잭션 입력');
     NONAUTONOMOUS_INSERT;
     ROLLBACK;
END;
/

SELECT * FROM TAB_NONAUTO;

MSG

---------------------------

부모트랜잭션 입력

일반자식트랜잭션 입력

 

 

 

BEGIN
      INSERT INTO TAB_AUTO VALUES('부모트랜잭션 입력');
     AUTONOMOUS_INSERT;
     ROLLBACK;
END;
/

 

SELECT * FROM TAB_AUTO;

 

 

MSG

---------------------------

자율자식트랜잭션 입력

 

 

 

 

 

+ Recent posts

티스토리툴바