mssql SET XACT_ABORT 의 오해

http://www.mkexdev.net/Article/Content.aspx?parentCategoryID=2&categoryID=24&ID=497

 

MS SQL의 SET XACT_ABORT 옵션에 대해 MSDN의 설명을 보면 다음과 같다

SET XACT_ABORT 옵션을 ON으로 설정하면 Transact-SQL 문에서 런타임 오류가 발생할 경우 전체 트랜잭션이 종료된 후 롤백 됩니다. SET XACT_ABORT 옵션을 OFF로 설정하면 일부 경우에 오류를 일으킨 Transact-SQL 문만 롤백 되고 처리 작업을 계속합니다

기본값은 OFF 이다

문득 ON으로 설정하기만 하면 트랜잭션이 보장될 것만 느낌이 물씬 드는 설명이 아닐 수 없다
아는 사람은 다 알고 있겠지만, 필자처럼 혼동하는 사람도 분명 있으리라 보인다

간단한 테스트를 해 보자.
테이블을 만들고 ID 컬럼을 주키(Primary Key)로 정의한다. 그리고 데이터를 입력한다

CREATE TABLE TEST
(
 ID  INT PRIMARY KEY,
 NAME VARCHAR(10)
)

INSERT INTO TEST VALUES(1,'1')
INSERT INTO TEST VALUES(2,'2')

이렇게 만들었으면 다음과 같이 조회된다

SELECT * FROM TABLE1

이제 본격적으로 트랜잭션 처리 유형에 따른 결과를 살펴보자


-- CASE 1
--  SET XACT_ABORT 기본값이 off 이므로 선언하지 않으면 off 임을 기억하자
INSERT INTO TEST VALUES(3,'3')
INSERT INTO TEST VALUES(2,'2')
INSERT INTO TEST VALUES(4,'4')

음.. 이건 예상한 대로다. 트랜잭션의 '트'자만 아는 사람도 충분히 예상한 결과일 것이다
명시적으로 트랜잭션 블럭(BEGIN TRAN/COMMIT or ROLLBACK)을 하지 않았으니 당연히 오류가 난 놈 빼고
입력된 것이다

다음 테스트를 위해 입력된 3,4 값을 지우자
DELETE TEST WHERE ID IN (3,4)


-- CASE 2
BEGIN TRAN
 INSERT INTO TEST VALUES(3,'3')
 INSERT INTO TEST VALUES(2,'2')
 INSERT INTO TEST VALUES(4,'4')
COMMIT TRAN

에잉??.. 명시적으로 트랜잭션 블럭을 써 줬는데도 값이 들어가넹...
이런, 놀랍다.. 아니 값이 안 들어가겠지.. 하는 나의 오해가 놀랍다 ㅎㅎ;

명시적으로 트랜잭션 블럭을 써 줬지만 SET XACT_ABORT 는 OFF 임을 유념하자
확인했으면 , 또 지우자
DELETE TEST WHERE ID IN (3,4)

-- CASE 3
SET XACT_ABORT ON
 INSERT INTO TEST VALUES(3,'3')
 INSERT INTO TEST VALUES(2,'2')
 INSERT INTO TEST VALUES(4,'4')

이벤엔 3만 들어갔네... 확인하고 또 지우자.
오류가 나는 구문 바로 위에껀 입력되었다. 즉 ON으로 설정하면 오류가 난 쿼리 이하는 트랜잭션이 종료되어
실행되지 않는 것이다
확인하고 또 지우자...
DELETE TEST WHERE ID IN (3,4)

-- CASE 4
SET XACT_ABORT ON
BEGIN TRAN
 INSERT INTO TABLE1 VALUES(3,'3')
 INSERT INTO TABLE1 VALUES(2,'2')
 INSERT INTO TABLE1 VALUES(4,'4')
COMMIT TRAN

CASE 4 에서야 비로서 완벽한 트랜잭션이 보장되었다
명시적 트랜잭션 블럭과 ON 설정으로 완벽한 트랜잭션이 보장되는 것이다

PS)
뭔가, 느낌이 오는가..
물론 MS SQL 의 2005 의 TRY/CATCH 로도 트랜잭션을 처리하는 방법이 있을 수 있다
그리고 @@ERROR 으로 오류를 간파해 명시적으로 ROLLBACK 할 수도 있다.
 
그러나 이 글에서 강조하고자 하는 것은 트랜잭션 처리를 위한 프로그래밍적인 스킬이 아니라,
XACT_ABORT 에 대한 오해가 있다면 푸는게 목적이다