이야기 하게될 것이고, (다음 #3번에 트랜잭션 상태에 대해 알아보자)
● SET XACT_ABORT
SET XACT_ABORT OFF : 오류를 일으킨 Transact-SQL 문만 롤백 , 되고 다음 라인 작업처리를 계속함
SET XACT_ABORT ON : T-SQL 문에서 런타임 오류가 발생할 경우 전체 트랜잭션이 종료된 후 해당범위 롤백
SET XACT_ABORT ON : T-SQL 문에서 런타임 오류가 발생할 경우 전체 트랜잭션이 종료된 후 해당범위 롤백
일반적을 SSMS 에서 쿼리창을 열면 기본 설정은 XACT_ABort 는 OFF 상태이다. 바꿔말하면 , 오류를 읽으킨 쿼리만 롤백 시키고 나머지는 모두 실행한다는 뜻이다. 백문이불여일견, 예제를 보자
● SET XACT_ABORT OFF 예제 (기본 : OFF)
( SET XACT_ABORT OFF - 오류를 일으킨 Transact-SQL 문만, 롤백 되고 다음 라인 처리작업을 이어서 계속함
SET XACT_ABORT ON / OFF 전체 쿼리 샘플
먼저 샘플을 위해 Table 을 하나 작성하자.
----------------------------------------------------------------------------------------------------------------------------------------------
Create table t_XACTTest
(
idx int primary key,
name varchar(50)
)
위 에서도 언급했지만 기본 XACT_ABORT 는 OFF 이다.
( SET XACT_ABORT OFF - 오류를 일으킨 Transact-SQL 문만, 롤백 되고 다음 라인 처리작업을 계속함 )
⑤ 의 경우 idx 가 primary key로 설정되어있기때문에 중복된 값을 허락하지 않아 오류가 발생한다.
예상대로 ⑤ 라인에서 오류가 발생했다. 오류이유도 예상대로이다. 하지만 그 뒤로 남은 ⑥, ⑦ 쿼리들은 이어서 실행됨
결과적으로 오류가 발생한 ⑤ 번만 제외하고, 모두 정상적으로 실행되었다.
먼저 샘플을 위해 Table 을 하나 작성하자.
----------------------------------------------------------------------------------------------------------------------------------------------
Create table t_XACTTest
(
idx int primary key,
name varchar(50)
)
위 에서도 언급했지만 기본 XACT_ABORT 는 OFF 이다.
( SET XACT_ABORT OFF - 오류를 일으킨 Transact-SQL 문만, 롤백 되고 다음 라인 처리작업을 계속함 )
⑤ 의 경우 idx 가 primary key로 설정되어있기때문에 중복된 값을 허락하지 않아 오류가 발생한다.
예상대로 ⑤ 라인에서 오류가 발생했다. 오류이유도 예상대로이다. 하지만 그 뒤로 남은 ⑥, ⑦ 쿼리들은 이어서 실행됨
결과적으로 오류가 발생한 ⑤ 번만 제외하고, 모두 정상적으로 실행되었다.
그럼 이제 트랜잭션으로 묶어보자
현재 상태는 여전히 SET XACT_ABOART OFF (기본) 상태이다
오류가능성이 있는 쿼리부분을 트랜잭션으로 묶어보아도 같은 현상이다, ⑤번 쿼리만 오류발생 후 아래 쿼리 까지 계속실행하여 commit 시킨다.
Begin Tran ~ Commit Tran 의 영향이 없는것 처럼 보인다.
현재 상태는 여전히 SET XACT_ABOART OFF (기본) 상태이다
오류가능성이 있는 쿼리부분을 트랜잭션으로 묶어보아도 같은 현상이다, ⑤번 쿼리만 오류발생 후 아래 쿼리 까지 계속실행하여 commit 시킨다.
Begin Tran ~ Commit Tran 의 영향이 없는것 처럼 보인다.
그럼 Begin Tran ~ Rollback Tran 으로 해보자 ?
정상적으로 ④ ⑤ ⑥ 쿼리에 대해 롤백 됬다.
결론은, 트랜잭션의 영향을 받는다 오류난 쿼리만 , 진행하지 안을뿐
● SET XACT_ABORT ON 예제
(T-SQL 문에서 런타임 오류가 발생할 경우 전체 트랜잭션이 종료된 후 롤백 됩니다. )
SET XACT_ABORT ON : T-SQL 문에서 런타임 오류가 발생할 경우 전체 트랜잭션이 종료된 후 롤백 됩니다.
위의 예제를 그대로 활용하여 ON 을 설정하여 실행해보자.
----------------------------------------------------------------------------------------------------------------------------------------------
Delete from t_XACTTest -- 일단 테이블을 비운다.
SET XACT_ABORT ON -- 설정변경
(T-SQL 문에서 런타임 오류가 발생할 경우 전체 트랜잭션이 종료된 후 롤백 됩니다. )
예상대로, ⑤ 의 경우 idx 가 primary key로 설정되어있기때문에 중복된 값을 허락하지 않아 오류가 발생한다.
그런데 ON 설정의 경우, 오류가 발생한 ⑤ 시점 후 부터는 모두 처리하지 않았다.
결국 오류직전 쿼리 까지는 모두 실행이 된다.
①,②,③,④,⑤ 까지 실행은 했지만 ⑤는 오류로 인해 반영이 안된 상태로 ⑥,⑦ 도 XACT_ABORT ON 의 영향을받아 실행되지 않았다. 위의 이미지는 중간값을 찍어서 2개의 ROW 만 출력되었지만,
만약 마지막에 쿼리한다면, 값은 3개가 되겟다. 증거는 ④번의 (3.'CCC') 가 1row Affected 되었기 때문이다.
그럼 이제 트랜잭션으로 묶어보자
현재 상태는 여전히 SET XACT_ABOART ON 상태이다
⑤번의 오류로 인해 Begin Tran ~ Commit Tran 구간이 모두 롤백이 되었다.
정리하자면,
SET XACT_ABORT ON / OFF는 현재 세션의 업무처리 중 오류가 발생하면 어떻게 해야될까? 에 대한 설정 방법중 하나 이다. 하지만 개인적인 생각에는, 설정을 단독으로는 잘 사용하지 않을꺼같다. 왜냐하면, 말그대로 환경에 대한 설정이지 제어에 대한 방법을 제공하지 않기 때문이다.
하지만, 상단에서 처리된 어떤 업무가, 하단에 영향을 미친다고 생각해보자.
상단에서 오류가 발생하면 , 하단에서는 처리하면 안되는, OFF 상태면 상관없이 뭔가 처리를 하게 될테니 위험하다, 데이터의 연관성이나 끊김 데이터가 발생할 수 도 있다. 그럴경우 SET XACT_ABORT ON 설정 후 , 1차 안정장치를 해두고,Try ~ Catch 나, XACT_STATE() 등 을 활용하면, 2중으로 안전하게 구현하는데 도움이 되지 않을까 싶다.
