꿈꾸는 개발자, DBA 커뮤니티 구루비

pl/sql 다중 dml 처리 시 expcetion rollback 처리에 대한 질문

by classwizar [PL/SQL] [2012.12.31 12:05:27]

안녕하세요 . 한가지 질문이 있어 게시글을 남깁니다.
pl/sql 처리 시

해당 프로시저에
begin
insert table a

inset table b

insert table c
....

  EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK TO es_test;
v_result := SQLERRM;
end

으로 테이블에 insert 문을 날리는 프로시저를 생성하였습니다.

exception 발생 시 전체 Rollback 처리를 하였고 ,
test겸 insert table c에 예외를 발생시켜 exception 구분으로 떨어지게 하였습니다.

제 생각으로 하나의 트렌잭션에 묶여
전체 rollback이 될거라 예상했으나, 테이블 a, b에는 자료가 insert 되어 rollback처리가 되지 않았습니다.

show autocommit;
으로 확인했는데 셋팅값은 autocommit OFF
로 되어 있었구,

savepoint를 생성하여, 롤백처리 해본 결과..

ORA-01086 : es_test 저장점이 이 세션에 설정되지 않았거나 부적합합니다.
라는 오류메세지를 뿌려 주었습니다.

제가 알기론
SAVEPOINT A ;
UPDATE TABLE SET A ='B'
ROLLBACK TO A;
로 할 경우 제대로 실행

SAVEPOINT A ;
UPDATE TABLE SET A ='B'
COMIIT;
ROLLBACK TO A;
위와 같이 날리면
ORA-01086 이 에러가 떨어지는 것을 확인하였으며,
프로시저 실행 시 각각 INSERT 구문에서 AUTOCOMMIT;이 되는 것으로 추정해봤습니다.

왜 그렇게 처리가 되는지 제가 하고 싶은 처리를 할려면 어떻게 해야 하는지 잘 모르겠습니다.

답변 부탁드립니다.

감사합니다.

by Oracler [2012.12.31 21:23:02]

PL/SQL에서 EXCEPTION 섹션에서 처리하는 예외처리는 예외상황(런타임 에러)가 발생했을때 프로그램이 에러가 나기 전에 제어권을 가져와서 에러 상황을 잘 처리하게 하기 위함입니다.

다시 말해 예외처리를 하겠다는 것은 현재의 트랜잭션을 판단하여 커밋할 것인가 아니면 롤백할 것인가를 프로그래머가 판단하겠다는 뜻이기도 합니다.

EXCEPTION 섹션에서 처리되는 내용은 자동으로 커밋되거나 롤백되는 것이 아닙니다. 즉, 트랜잭션이 종료가 되는 것이 아니라 계속 이어지는 것입니다. 다음의 코드를 실행해 보면 이해가 되실 것 같네요.

-- 테스트 테이블 생성
CREATE TABLE a (no NUMBER(2));
CREATE TABLE b (no NUMBER(2));
-- PL/SQL 블록에서 테이블 a에는 정상값 입력, 테이블 b에는 예외상황 발생을 위한 값 입력
BEGIN
  INSERT INTO a VALUES (10);
  INSERT INTO b VALUES (1000);
EXCEPTION
  WHEN OTHERS THEN
  NULL;  -- 아무것도 하지 않고 그냥 종료하게 하기 위함
END;
/
-- 테이블 a의 내용 확인
SELECT * FROM a;

위 쿼리문의 결과는 당연 10이라는 값이 출력됩니다. 그 이유는 PL/SQL에서 시작된 트랜잭션이 아직 끝나지 않고 계속되고 있기 때문입니다. 정말 그런지 확인해 보려면 ROLLBACK 해 보면 됩니다.

ROLLBACK;
-- 롤백 후 테이블 a의 내용 확인
SELECT * FROM a;

롤백한 후 테이블 a의 내용을 확인하면 삽입된 데이터가 롤백되어 보이지 않게 됩니다. 그리고 이 롤백에 의해 트랜잭션은 종료가 되지요.

by classwizar [2014.05.26 21:45:20]

감사드린다는 인사를 빼먹은거 같습니다. 그때 당시 정신이 없없나 봅니다. ㅠㅠ

by Oracler [2012.12.31 21:27:56]

결론적으로, 질문자께서 아마도 다음의 내용과 혼동을 하신 것 같습니다.

"PL/SQL 블록에서 예외가 발생했는데 예외 상황을 처리(Exception Handling)하지 않으면 롤백된다."

예외 상황을 처리하지 않으면 트랜잭션 전체가 자동으로 롤백됩니다만 질문자께서는 EXCEPTION 섹션을 사용하여 예외 처리를 하였기 때문에 트랜잭션이 종료가 되지 않고 계속되고 있는 상태가 된 것입니다.

따라서 질문자의 의도대로 예외가 발생했을 때 롤백되게 하고 싶으면 명시적으로 ROLLBACK을 지정해 주어야 합니다. 아래와 같이 하셔야 합니다.

BEGIN
    INSERT INTO a VALUES (10);
    INSERT INTO b VALUES (1000);
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;    -- 명시적으로 ROLLBACK
END;
/

그럼, 도움이 되셨길...

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변