[PROCEDURE] 독립적인 트랜잭션 설정

서론

  Oracle에서 프로시저를 사용하다가 자꾸 예상하지 못한 부분에서 COMMIT/ROLLBACK 이 처리되었다. 확인해보니까 내부 로직에서 데이터를 적재한 후 로그 테이블에 로그를 쌓기위한 다른 프로시저를 호출하였는데, 그 프로시저에서 COMMIT/ROLLBACK을 실행하고 있었다.

 

  이처럼 프로시저가 하나의 트랜잭션에서 여러 프로시저를 호출하여 사용하는 경우 문제가 발생할 수 있다. 따라서, 각 프로시저마다 독립적인  트랜잭션을 구성할 수 있도록 'PRAGMA AUTONOMOUS_TRANSACTION' 옵션을 적용해야한다.

 

  간단한 예제를 통해 어떻게 적용되는지 알아보도록 하자.

 

 

 

1. 예제

1-1. 요약

    1. 데이터를 적재하는 프로시저를 호출.
    2. 프로시저가 테이블에 데이터를 INSERT하고 COMMIT/ROLLBACK을 처리하기전에 다른 프로시저를 호출.

    3. 다른 프로시저에서 데이터를 적재하고 COMMIT/ROLLBACK을 처리한 후 종료.

    4. 다른 프로시저가 종료된 후 COMMIT/ROLLBACK을 처리.

    5. 데이터 적재 결과 확인

 

 

1-2. 테이블 생성

-- 테이블 생성
CREATE TABLE T_COMMIT_TEST(
  NUM         NUMBER,
  CALLER      VARCHAR2(30),
  LOG         VARCHAR2(200),
  LOAD_DTTM   VARCHAR2(30)
);
 
-- 테이블 삭제
-- DROP TABLE T_COMMIT_TEST PURGE;

 

 

1-3. 로그 프로시저 생성

CREATE OR REPLACE PROCEDURE PR_INSERT_LOG(
  I_CALLER   IN    VARCHAR2,
  I_LOG      IN    VARCHAR2
)
IS
 
  -- 현재 프로시저가 독립적인 트랜잭션 가지도록 설정.
  PRAGMA AUTONOMOUS_TRANSACTION;
  
  V_NUM            NUMBER;
  V_CALLER         VARCHAR2(30);
  V_LOG            VARCHAR2(200);
  V_LOAD_DTTM      VARCHAR2(30);
 
BEGIN
  
  V_NUM    := -1;
  V_CALLER := I_CALLER;
  V_LOG    := I_LOG;
 
  BEGIN
  
    -- 다음 순번 조회
    SELECT NVL(MAX(T1.NUM), 0)+1 AS NEXT_NUM
      INTO V_NUM
      FROM T_COMMIT_TEST T1
    ;
  
    V_LOAD_DTTM   := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
 
    -- INSERT
    INSERT INTO T_COMMIT_TEST (
      NUM,
      CALLER,
      LOG,
      LOAD_DTTM
    ) VALUES (
      V_NUM,
      V_CALLER,
      V_LOG,
      V_LOAD_DTTM
    );
 
    COMMIT;
  
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE( SQLERRM );
      
      ROLLBACK;      
      
      V_LOG         := SQLERRM;
      V_LOAD_DTTM   := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
    
      -- INSERT
      INSERT INTO T_COMMIT_TEST (
        NUM,
        CALLER,
        LOG,
        LOAD_DTTM
      ) VALUES (
        V_NUM,
        V_CALLER,
        V_LOG,
        V_LOAD_DTTM
      );
      
      COMMIT;
      
  END;
END
;

 

 

1-4. 데이터 적재 프로시저 생성

CREATE OR REPLACE PROCEDURE PR_COMMIT_TESTER(
  I_CALLER   IN    VARCHAR2
)
IS
 
  V_NUM            NUMBER;
  V_CALLER         VARCHAR2(10);
  V_DATA           VARCHAR2(200);
 
BEGIN
  
  V_NUM    := -1;
  V_CALLER := I_CALLER;
  
  -- BLOCK 1
  BEGIN
  
    FOR V_IDX IN 1..3
    LOOP
 
      -- 데이터 적재 시작 로그
      PR_INSERT_LOG('PROCEDURE', '[BLOCK-'||V_IDX||'][START]' );
      
      -- 다음 순번 조회
      SELECT NVL(MAX(T1.NUM), 0)+1 AS NEXT_NUM
        INTO V_NUM
        FROM T_COMMIT_TEST T1
      ;
      
      -- 테스트 데이터
      V_DATA        := 'TEST DATA1'; 
      V_LOAD_DTTM   := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
      
      -- INSERT
      INSERT INTO T_COMMIT_TEST (
        NUM,
        CALLER,
        LOG,
        LOAD_DTTM    
      ) VALUES (
        V_NUM,
        V_CALLER,
        V_DATA,
        TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
      );
      
      -- TOGGLE COMMIT AND ROLLBACK
      IF MOD(V_IDX, 3) != 1 THEN
        -- 데이터 적재 완료 로그
        PR_INSERT_LOG('PROCEDURE', '[BLOCK-'||V_IDX||'][COMMIT]' );
        COMMIT;
      ELSE
        PR_INSERT_LOG('PROCEDURE', '[BLOCK-'||V_IDX||'][ROLLBACK]' );
        ROLLBACK;
      END IF
      ;
      
      -- 데이터 적재 완료 로그
      PR_INSERT_LOG('PROCEDURE', '[BLOCK-'||V_IDX||'][END]' );
      
    END LOOP
    ;
    
  EXCEPTION
    WHEN OTHERS THEN
      V_LOAD_DTTM   := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
      DBMS_OUTPUT.PUT_LINE( SQLERRM );
      ROLLBACK;
  
  END
  ;
 
END
;

 

 

1-5. 프로시저 실행

-- OUTPUT 설정 ON
SET SERVEROUTPUT ON
 
 
-- PROCEDURE 실행
BEGIN
 
  -- 기존 데이터 삭제
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_COMMIT_TEST';
 
  -- 시작 로그
  PR_INSERT_LOG('DOCHI', 'START MAIN');
 
  -- 데이터 적재
  PR_COMMIT_TESTER('DOCHI');
 
  -- 완료 로그
  PR_INSERT_LOG('DOCHI', 'END MAIN');
  
END
;

 

 

1-6. 결과 확인

-- 적재 결과 조회
SELECT T1.NUM
     , T1.CALLER
     , T1.LOG
     , T1.LOAD_DTTM
  FROM T_COMMIT_TEST T1
 WHERE 1=1
 ORDER BY T1.NUM ASC

 

 

 

마치며

  'PRAGMA AUTONOMOUS_TRANSACTION' 옵션을 적용하여 간단하게 독립적인 트랜잭션 환경을 만들어 보았다. 프로시저 내부에서 다른 프로시저를 호출할 때, 그 내부에서 COMMIT/ROLLBACK 처리된다면 이 옵션을 적용해주도록 하자.