제2의 비엔지니어 인생관을 꿈꾸며

Posted
Filed under MSSQL
이번에 포스팅할 내용은 Try ~ Catch 와 더불어 트랜잭션 관련 내용이 조금 포함된다. DB 에서 데이터 처리에 있어 트랜잭션은 이미 너무 많이 들었을것이다. 하지만, Try ~ Catch 에 종속된 문제는 아니다. 작업 처리의기본 범위의 문제이다. 자세한건 구글링을 통해 정독하자, 아래는 오류제어와 관련된 범주내에서 SET XACT_ABORT 개념과 함께 트랜잭션에
이야기 하게될 것이고, (다음 #3번에  트랜잭션 상태에 대해 알아보자)


● SET XACT_ABORT
SET XACT_ABORT OFF : 오류를 일으킨 Transact-SQL 문만  롤백 , 되고 다음 라인 작업처리를 계속함
SET XACT_ABORT
ON   : T-SQL 문에서 런타임 오류가 발생할 경우 전체 트랜잭션이 종료된 후 해당범위  롤백 

일반적을 SSMS 에서 쿼리창을 열면 기본 설정은 XACT_ABort 는   OFF  상태이다. 바꿔말하면 , 오류를 읽으킨 쿼리만 롤백 시키고 나머지는 모두 실행한다는 뜻이다. 백문이불여일견, 예제를 보자

SET XACT_ABORT ON / OFF 전체 쿼리 샘플 (다운로드)


● SET XACT_ABORT OFF 예제 (기본 : OFF)
( SET XACT_ABORT OFF  - 오류를 일으킨 Transact-SQL 문만, 롤백 되고 다음 라인 처리작업을 이어서 계속함

먼저 샘플을 위해 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 의 영향이 없는것 처럼 보인다.

그럼 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 의 영향을받아 실행되지 않았다. ( 결과 3Row 출력)

그럼 이제 트랜잭션으로 묶어보자
현재 상태는 여전히 SET XACT_ABOART ON  상태이다 

⑤번의 오류로 인해 Begin Tran 
~ Commit Tran 구간이 모두 롤백이 되었다.

정리하자면,
SET XACT_ABORT ON / OFF는 현재 세션의 업무처리 중 오류가 발생하면 어떻게 해야될까? 에 대한 설정 방법중 하나 이다. 하지만 개인적인 생각에는, 설정을 단독으로는 잘 사용하지 않을꺼같다. 왜냐하면, 말그대로 환경에 대한 설정이지 제어에 대한 방법을 제공하지 않기 때문이다. 

하지만, 상단에서 처리된 어떤 업무가, 하단에 영향을 미친다고 생각해보자.
상단에서 오류가 발생하면 , 하단에서는 처리하면  안되는, OFF 상태면 상관없이 뭔가 처리를 하게 될테니 위험하다, 데이터의 연관성이나 끊김 데이터가 발생할 수 도 있다.  그럴경우 SET XACT_ABORT ON 설정 후 , 1차 안정장치를 해두고,Try ~ Catch 나, XACT_STATE() 등 을 활용하면, 
2중으로 안전하게 구현하는데 도움이 되지 않을까 싶다.
2018/04/20 15:43 2018/04/20 15:43