● XACT_STATE() 와 @@TranCount
(2개의 명령어는 현재 세션에 존재하는 활성화된 사용자 트랜잭션을 확인하는데 사용하게된다.)
select @@TranCount : 현재 세션에 존재하는 활성화된 사용자 트랜잭션의 갯수
select XACT_STATE() : 현재 세션에 존재하는 활성화된 사용자 트랜잭션의 유무
트랜잭션은 Active User Transaction , Active Transaction (or Active Open Transaction) 으로 구분된다,해석하자면, 활성화된 사용자 트랜잭션 , 활성화된 트랜잭션일 뜻하는데, 조만간 트랜잭션을 다룰때 다뤄보기로 하고, 대신 마지막에 DBCC OPENTRAN 에 대해서 팁으로 알아보자.
아래는 XACT_STATE() 값 의미이다.(테트넷에서 퍼왔다.)
|
반환 값
|
의미
|
|
1
|
현재 요청에 활성 사용자 트랜잭션이 있습니다. 해당 요청에서는 데이터를 쓰고 트랜잭션을 커밋하는 등 모든 동작을 수행할 수 있습니다.
|
|
0
|
현재 요청에 대한 활성 사용자 트랜잭션이 없습니다.
|
|
-1
|
현재 요청에 활성 사용자 트랜잭션이 있지만 오류가 발생하여 트랜잭션이 커밋할 수 없는 트랜잭션으로 분류된 상태입니다. 해당 요청에서는 트랜잭션을 커밋하거나 저장점까지 롤백할 수 없고 트랜잭션의 전체 롤백만 요청할 수 있습니다. 요청에서 트랜잭션을 롤백할 때까지 모든 쓰기 작업을 수행할 수 없습니다. 트랜잭션을 롤백할 때까지 읽기 작업만 수행할 수 있습니다. 트랜잭션이 롤백된 후에는 요청에서 읽기/쓰기 작업을 모두 수행할 수 있으며 새 트랜잭션을 시작할 수 있습니다.
일괄 처리가 완료되면 데이터베이스 엔진에서 자동으로 커밋할 수 없는 활성 트랜잭션을 모두 롤백합니다. 트랜잭션이 커밋할 수 없는 상태가 되었을 때 오류 메시지가 전송되지 않은 경우 일괄 처리가 완료되면 오류 메시지가 클라이언트 응용 프로그램으로 전송됩니다. 이 메시지는 커밋할 수 없는 트랜잭션이 검색되어 롤백되었음을 보여 줍니다.
|
XACT_STATE와 @@TRANCOUNT 함수는 모두 현재 요청에 활성 사용자 트랜잭션이 있는지 여부를 검색하는 데 사용될 수 있습니다.@@TRANCOUNT를 사용하여 트랜잭션이 커밋되지 않은 트랜잭션으로 분류되었는지 여부를 확인할 수는 없습니다. 또한 XACT_STATE를 사용하여 중첩된 트랜잭션이 있는지 여부를 확인할 수 없습니다.(출처 : https://technet.microsoft.com/ko-kr/library/ms189797(v=sql.110).aspx)
자 이제 예제로 좀더 정확히 확인해보자
참고, 아래는 예제 전체 풀 쿼리이다.
● XACT_STATE() 와 @@TranCount 예제 #1
먼저 샘플을 위해 Table 을 하나 작성하자.
----------------------------------------------------------------------------------------------------------------------------------------------
Create table t_TranTest
(
idx int primary key,
name varchar(50)
)
구
분하기 쉽게 트랜잭션에 A,B 명칭을 주었다, 2개의 중첩 트랜잭션을 설정하고 Trancount 와 XACT_STATE() 리턴값을 각각 확인해보자.
| 트랜잭션 |
Trancount |
XACT_STATE |
| A |
1 |
1 |
| A,B(중첩) |
2 |
1 |
| B 커밋 |
1 |
1 |
| A 커밋 |
0 |
0 |
Trancount 는 현재 활성화된 트랜잭션의 갯수를 흐름대로 알려주고 , XACT_SATE 는 현재 활성화된 트랜잭션의 존재 유무에 대한 정보를 알려준다.
● XACT_STATE() 와 @@TranCount 예제 #2
이번엔 XACT_STATE 상태값이 -1 인상태 요약해서 말하자면 활성 사용자 트랜잭션이 commit 이 불가능한 rollback 만 가능한 상태의 트랜잭션이 발생했을겨우다. 정리하자면, 트랜잭션이 있긴 있는데, 프로세스에 오류가 발생해서rollback 말고는 여지가없을때의 상황을 말한다.
위의 t_TranTest 테이블을 드롭 & 재생하고 다시 실행해 보자
1. 예제를 위해 우선 SET XACT_ABORT ON 옵션을 활성화 시키자. (이유는 OFF(디폴트) 상태에서는 오류난 구분만 오류를 발생시키고, 나머지는 모두 Commit 시키기때문이다 OFF 상태의 마지막 트랜잭션은 Commit 으로 끝나기때문에 Rollback이 나올 상황 연출이 안된다. 자세한건 전편 포스팅 참고)
2. 오류를 catch 하기 위해 Try ~ Catch 활용 한다.
(XACT_ABORT ON 상태에서는, 오류발생시 바로 rollback 시켜버린다. 하지만 우리는 지금 눈으로 확인해야하기 때문에 오류제어가 가능해져야한다. Try ~ cath 구문에서을 활용하면 바로 Rollback 되는것이 아니라 Catch 구문으로
제어가 넘어 온다.)
3. Begin tran 전에 ① insert 1개를 한다.
4. begin 안에서 ② insert 를 실행해 , PRIMARY KEY 제약 조건 오류를 강제로 발생
5. ② 에서 발생한 오류로 제어가 Catch 문으로 넘어간다. 그리고 아래이 결과와 메시지 확인이 가능하다.
Messages 부분은 오류가나서 commit 할 수 없는 상황임에도 Commit Tran 을 적어줬기때문이다
위의 Commit Tran을 주석으로 처리하고, rollback 처리 해보자(오류가 나서 catch 왔으니 Rollback 이 당연하겠다.)
정상적으로 오류메세지없이 깔끔하게 처리된다.
팁으로, 현재 서버에 활성화된 트랜잭션이 있는지에대한 정보를 확인할 수 있는 DBCC OPENTRAN 에 대해 알아보자
참고로 DBCC 로 시작하는 명령어들은, 현재 세션에만 국한된 명령어가 아닌 시스템 전체에 대한 서버 정보를 출력한다. 예를들면 위의 @@Trancount 나 xact_state 를 비롯해서 SET 기타 설정 명령어문들은 모두 현재 세션에서만 유효하다.(단, 시스템 전체에서, 가장 오래된 트랜잭션 1건만 출력)
위의 쿼리를 다시한번 실행해보자
DBCC OPENTRAN() 으로 활성화되어 열려있는 트랜잭션에 대한 정보를 시스템 차원에서 접근한다.
아쉬울수 있겠지만, DBCC OPENTRAN() 은 트랜잭션 전체 대한 정보가 아닌 가장오래된 트랜잭션에 대한 정보만 1건만 출력한다. 여기서는 2회 출력 모두 트랜잭션 A 에 대한 정보만 출력한다.
Messages 탭에서 결과를 확인이 가능하다. 다시한번 말하지만 , 다른세션에서도 조회가 가능하므로, DB 서버 관리자에게 유용할 수 있다. SPID 부터 시작시간 그리고 LSN(로그시퀀스번호) 까지 알 수 있다. SPID 만 알아도 다른 시스템 테이블 , 명령어 참고하면, 왠만한 정보는 금방 알아낼수있으리라 생각이 든다.
UID는 사용자 정보인데, 테크넷에 의하면 곧 사라질 정보라고한다, 의미없으니 무시하길..
XACT_STATE , @@TranCount.sql
⑤ 의 경우 idx 가 primary key로 설정되어있기때문에 중복된 값을 허락하지 않아 오류가 발생한다.
