SET XACT_ABORT 에 관해..

SET XACT_ABORT 옵션은 Transact-SQL 에서 사용이 되며
사용법은 SET XACT_ABORT ON|OFF 로 사용됩니다.

SET XACT_ABORT ON 으로 했다는 것은 이 다음부터 사용되는 쿼리문을 실행할때 런타임 오류(컴파일 오류는 해당되지 않음)가 발생하였을 경우 전체를 롤백시킨다는 뜻입니다.

 

SET XACT_ABORT OFF 로 했다는 것은 그 반대인 전체 롤백안하고 오류가 발생한 부분만 롤백한다는 뜻입니다.

예를 들면, 10 개의 데이터를 insert 하는 query 가 있다고 할 때, insert 하기 전에 SET XACT_ABORT ON 을 하게 될 경우, 4개까지 insert 하다가 5번째에서 도메인 오류, 제약 조건 오류(참조키,주키기타 등등..) 등.. 런타임 오류가 발생할 경우 즉, 10개중 하나라도 insert 가 되지 않는다면 전체를 롤백시킨다는 뜻이고,
SET XACT_ABORT OFF 를 했다면 5번째에서 오류가 나서 insert 가 안되면, 요것만 롤백하고 그 다음 6번째,7번째로 10번째 까지 insert 를 진행합니다. 즉, 10개 중에서 insert 되지 않는것만 롤백하고 나머지는 진커밋해버린다는 얘기죠.

** 2008/12/17 추가.

이 설정은 주로 한 테이블에 대량으로 insert, update, delete 를 할 때 유용할 것 같네요.
여러 질의문을 한 번에 실행할 경우 예를 들어, insert 와 delete 를 하나로 묶어 실행할

경우에는 transaction 처리 없이 SET XACT_ABORT ON 만 가지고는 전체 rollback 이 되지 않습니다.
그러므로 여러 질의문을 하나의 transaction 으로 사용할 때는 begin transaction 과 commit transaction 으로 묶어야 합니다.

예) 아래와 같이 임시 테이블을 하나만들고 Test column 을 주키로 잡은 뒤 data 를 넣습니다. 

 

IF OBJECT_ID(N'Temp',N'U') IS NOT NULL     DROP TABLE Temp;  GO    CREATE TABLE Temp(     Test INT NOT NULL PRIMARY KEY  );  GO    INSERT INTO Temp VALUES(1);  INSERT INTO Temp VALUES(2);  INSERT INTO Temp VALUES(3);  GO

 

 

여기서 만약 주키가 중복되는 data 를 넣으면 주키는 중복될 수 없다는 ERROR 가 뜰 것으로 예상할 수 있을 겁니다.

 

SET XACT_ABORT OFF  BEGIN TRAN     INSERT INTO Temp VALUES(1);     INSERT INTO Temp VALUES(3);     INSERT INTO Temp VALUES(4);  COMMIT TRAN

 

위를 실행해보면 1,3 은 주키가 중복되기 때문에 실행이 안되지만 SET XACT_ABORT 가 OFF 여서 에러가 발생한 문장은 roll back 되고 에러가 발생하지 않는 문장은 실행이 됩니다.

 

SET XACT_ABORT ON  BEGIN TRAN     INSERT INTO Temp VALUES(1);     INSERT INTO Temp VALUES(3);     INSERT INTO Temp VALUES(4);  COMMIT TRAN

 

하지만 위의 문장은 transaction 으로 묶여있는 문장에서 하나라도 error 가 발생되면 전체를 rollback 시킵니다.

만약 transaction 으로 묶지 않고 실행했을 시에는 XACT_ABORT 가 OFF 일 경우에는 transaction 으로 묶었을 때처럼 결과가 나오지만 ON 으로 했을 때는 error 가 발생되면 그 다음 문장들은 실행되지 않습니다.

따라서, transaction 을 실행하면서 error 발생 시 전체를 rollback 시켜야 할 때 ON 과 transaction 묶음을 사용해야 할 때 유용할 듯 하네요.