DATOR


오라클 autonomous(자율적, 자주적) 트랜잭션 SQL


아래의 내용은 오로지 테스트를 위하여 가상으로 설정한 내용입니다.


우리 회사는 ERP 패키지 시스템을 만드는 회사입니다.
데이터베이스는 오라클을 사용합니다.
우리 회사의 시스템에서 생성한 데이터는 업무의 원천 데이터로 여러 다른 시스템과 많은 인터페이스를 가질 수 있으며,

개발 프로그램 모듈의 재할용을 높이기 위하여 테이블의 모든 PK를 "테이블명_NO" 하나로 결정하였습니다.

또한 본질 식별자는 우리 회사만 알고 있으며, 우리의 제품을 구매한 회사는 쉽게 알 수 없습니다.


번호를 저장하는 테이블은 채번 테이블이라는 이름으로 별도로 존재합니다.
채번 테이블은 키값, 구분, 번호의 3개의 항목을 가지고 있으며 식별자는 구분입니다.

그런데 문제가 발생하였습니다.
업무별 채번을 하는 테이블과 업무 테이블의 세션이 동일하여 원하지 않는 커밋이 발생하고 있습니다.
마스터 테이블을 Insert 하기 위해 마스터 테이블을 채번하고 마스터 테이블에 Inset 합니다.
다시 상세 테이블을 Insert하기 위하여 상세 테이프블을 채번하고 상세 테이블을 Insert 도중 오류가 있어 Rollback 하였습니다.
그런데 마스터 테이블에는 이미 자료가 저장되었습니다.

알고 보니 상세 테이블 채번 시 채번 테이블이 커밋되면서 마스터 테이블이 함께 커밋된 것 같습니다.

우리는 채번 테이블이 시퀀스처럼 동작하기를 원합니다.
그렇다고 채번만을 위해서 세션을 추가로 연결하기에는 부담이 됩니다.
현재의 구조를 그대로 유지하면서 문제를 해결할 수 있는 방법일 있을까요?

 

오라클에는 autonomous 트랜잭션이 있습니다.
이 트랜잭션은 부모 트랜잭션과는 독립적으로 트랜잭션을 통제합니다.
현재 실행 중인 트랜잭션에는 영향을 미치지 않으면서 별도의 처리를 하고 커밋 또는 롤백을 독립적으로 수행할 있습니다.

단지 프로시저 등의 변수 선언부분에 pragma autonomous_transaction 이라고 추가하는 것만으로 잘 동작합니다.


-- 채번 함수 작성
create or replace function fu_cre_keyno(v_keyno_type in varchar2) return varchar2
as
    pragma autonomous_transaction;
    v_keyno varchar2(11);
    v_keyno_num number;
begin
   
    select v_keyno_type||lpad(nvl(max(keyno_num), 0) + 1, 9, '0'), nvl(max(keyno_num), 0) + 1
    into v_keyno, v_keyno_num
    from cre_keyno
    where keyno_type = v_keyno_type;
   
    if (v_keyno_num  = 1) then
        insert into cre_keyno (keyno, keyno_type, keyno_num)
        values (v_keyno, v_keyno_type, v_keyno_num);
        commit;
    else
        update cre_keyno
        set keyno = v_keyno,
            keyno_num = v_keyno_num
        where keyno_type = v_keyno_type;
        commit;
    end if;
   
    return v_keyno;
end;
/

 

SQL> select * from cre_keyno;
선택된 레코드가 없습니다.

 

SQL> select * from master;
선택된 레코드가 없습니다.

 

SQL> select * from detail;
선택된 레코드가 없습니다.

 

-- 입력 후 롤백
declare
    v_1 varchar2(11);
    v_2 varchar2(11);
begin
    select fu_cre_keyno('M1') into v_1 from dual;
    insert into master values (v_1, '마스터');
    select fu_cre_keyno('D1') into v_2 from dual;
    insert into detail values (v_2, v_1, '디테일');
    rollback;
end;
/

 

PL/SQL 처리가 정상적으로 완료되었습니다.

 

SQL> select * from cre_keyno;
KEYNO       KE  KEYNO_NUM
----------- -- ----------
M1000000001 M1          1
D1000000001 D1          1

 

SQL> select * from master;
선택된 레코드가 없습니다.

 

SQL> select * from detail;
선택된 레코드가 없습니다.


처리가 실패하더라도 채번은 계속하는 것을 채번 테이블을 조회하여 확인할 수 있습니다.

 

-- 입력 후 커밋
declare
    v_1 varchar2(11);
    v_2 varchar2(11);
begin
    select fu_cre_keyno('M1') into v_1 from dual;
    insert into master values (v_1, '마스터');
    select fu_cre_keyno('D1') into v_2 from dual;
    insert into detail values (v_2, v_1, '디테일');
    commit;
end;
/

 

PL/SQL 처리가 정상적으로 완료되었습니다.

 

SQL> select * from cre_keyno;
KEYNO       KE  KEYNO_NUM
----------- -- ----------
M1000000002 M1          2
D1000000002 D1          2

 

SQL> select * from master;
MASTER_NO
-----------
RMK
--------------------------------------------------------------------------------
M1000000002
마스터


SQL> select * from detail;
DETAIL_NO   MASTER_NO
----------- -----------
RMK
--------------------------------------------------------------------------------
D1000000002 M1000000002
디테일


autonomous 트랜잭션은 시스템 사용에 대한 감사의 목적으로 사용되어 질 수 있습니다.
시스템의 중요한 어떤 테이블을 누군가 수정한 경우 뿐만 아니라 수정하려고 하다가 실패한 경우까지 기록으로 남길 수 있습니다.
기타 다른 목적으로도 사용되어질 수 있을 것 같습니다.

각자의 용도에 맞게 활용해 보시고 사례를 남겨 주시면 많이 사람에게 도움을 될 수 있을 것 같습니다.

 

감사합니다.

TAG

Leave Comments


profile데이터의 요람에서 무덤까지~ 한참걸립니다. 

Category