(PRAGMA) AUTONOMOUS_TRANSACTION 이 무엇인가요? 자율 트랜잭션 이해하기.

PRAGMA AUTONOMOUS_TRANSACTION

PRAGMA 키워드는 지시문(directive)또는 전처리기(precompiler) 정도로 해석할 수 있습니다. 프라그마는 컴파일러에 특정 지시를 선언하여, PL/SQL 실행 과정에서 입력된 해당 지시문 정보를 처리하는 방법을 기술합니다. 프라그마에 대해 짧게 요약해 보려고 했는데 뭔가 더 복잡해진 것 같기는 하네요. 하지만, 프라그마 키워드는 1줄로 적힌 간단한 지시문이기에 크게 어렵지 않게 이해하실 수 있을 것입니다.

프라그마 키워드
PRAGMA AUTONOMOUS_TRANSACTION (프라그마 자율적인 트랜잭션)

프라그마에서 전처리를 지시하는 문장은 PL/SQL에서 다음과 같습니다.

AUTONOMOUS_TRANSACTION => 자율 트랜잭션 프라그마입니다. 현재 블록 내의 트랜잭션에 대한 커밋, 롤백 여부를 지시할 수 있습니다. 해당 사항은 메인(외부) 트랜잭션에 영향을 주지 않습니다. 오늘 주요하게 다룰 프라그마 주제와 관련이 있기에 추후 더 자세히 안내하겠습니다.
EXCEPTION_INIT => 사용자 정의 예외를 초기화 하겠다는 지시문입니다.
RESTRICT_REFERENCES => 펑션, 프로시저 등에서 참조를 제한하여 순도 수준(purity level)을 보장하겠다는 지시입니다. 보통 이 프라그마 지시와 함께 하는 함수를 순함수라고도 지칭합니다. 해당 프로그마는 RNDS(reads no database state), WNDS(writes no database state), WNPS(writes no package state) 등과 같은 지시와 함께 참조를 설정합니다.
SERIALLY_REUSABLE => 순차적 재사용 또는 직렬적 재사용 프라그마는 패키지 변수(패키지 레벨 데이터)가 다시 참조되기 전에 값이 초기화 하도록 합니다. 패키지 상에 선언된 변수가 다음 호출 시 값이 지속적으로 유지되지 않도록 지시하는 문장입니다.

프라그마 자율적인 트랜잭션

다양한 프라그마 키워드 중에 AUTONOMOUS_TRANSACTION에 대해 알아보도록 합시다. 해당 프라그마는 상기에서 안내해드렸듯이, 프라그마가 선언된 서브프로그램 블록 내의 트랜잭션에 관한 커밋과 롤백을 설정합니다. 해당 결과는 메인 트랜잭션에 영향을 주지 않습니다. 다음은 자율 트랜잭션 예제 자료입니다.

사번 7839의 급여는 5,000이며 다음과 같은 상황을 가정한 예시입니다.

메인 트랜잭션에서 해당 사원에게 보너스를 1250을 추가하며 급여를 그만큼 올려보도록 하겠습니다.
해당 업데이트 내용은 외부 트랜잭션 상에서 롤백을 하도록 가정합니다.

SET SERVEROUTPUT ON;

DECLARE
   L_EMPLOYEE_ID   NUMBER := 7839;
   L_BONUS         NUMBER := 1250;
   L_SAL           EMPLOYEES.SAL%TYPE;

   PROCEDURE PR_AUTONOMOUSTRANSACTION (P_EMPNO    EMP_BONUS.EMPNO%TYPE,
                                       P_BONUS    EMP_BONUS.BONUS%TYPE)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO EMP_BONUS (EMPNO, BONUS)
           VALUES (P_EMPNO, P_BONUS);

      COMMIT;
   END PR_AUTONOMOUSTRANSACTION;
BEGIN
   BEGIN
      SELECT SAL
        INTO L_SAL
        FROM EMPLOYEES
       WHERE EMPNO = L_EMPLOYEE_ID;

      DBMS_OUTPUT.PUT_LINE ('Salary before update : ' || L_SAL);

      UPDATE EMPLOYEES
         SET SAL = SAL + L_BONUS
       WHERE EMPNO = L_EMPLOYEE_ID;

      SELECT SAL
        INTO L_SAL
        FROM EMPLOYEES
       WHERE EMPNO = L_EMPLOYEE_ID;

      DBMS_OUTPUT.PUT_LINE ('Salary after update : ' || L_SAL);

      PR_AUTONOMOUSTRANSACTION (L_EMPLOYEE_ID, L_BONUS);

      ROLLBACK;

      SELECT SAL
        INTO L_SAL
        FROM EMPLOYEES
       WHERE EMPNO = L_EMPLOYEE_ID;

      DBMS_OUTPUT.PUT_LINE (
         'Salary after main transaction(ROLLBACK) : ' || L_SAL);
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         RAISE_APPLICATION_ERROR (-20000, 'Error');
   END;
END;

익명 블록의 실행 결과는 다음과 같습니다. 6,250으로 업데이트 되었으나 결국 롤백 하여 5,000으로 되돌아갑니다.

Salary before update : 5000
Salary after update : 6250
Salary after main transaction(ROLLBACK) : 5000
PL/SQL procedure successfully completed.

주목할 부분은 PR_AUTONOMOUSTRANSACTION (L_EMPLOYEE_ID, L_BONUS); 이 부분입니다. 해당 부분은 롤백 이전에 위치해 있으나, PRAGMA AUTONOMOUS_TRANSACTION; 지시문으로 프로시저 내부에서 커밋이 이루어집니다. 그리하여 해당 테이블에는 커밋된 데이터가 남아 있습니다.

SQL> SELECT * FROM EMP_BONUS;

     EMPNO      BONUS
---------- ----------
      7839       1250

메인 트랜잭션은 그대로 5,000입니다.

SQL> SELECT SAL
       FROM EMPLOYEES
      WHERE EMPNO = 7839;

       SAL
----------
      5000

예시로 프라그마를 지시한 프로시저입니다.

PROCEDURE PR_AUTONOMOUSTRANSACTION (P_EMPNO    EMP_BONUS.EMPNO%TYPE,
                                   P_BONUS    EMP_BONUS.BONUS%TYPE)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO EMP_BONUS (EMPNO, BONUS)
       VALUES (P_EMPNO, P_BONUS);

  COMMIT;
END PR_AUTONOMOUSTRANSACTION;