MS-SQL Server

SQL Server 트랜잭션 관련 테스트 (@@trancount를 입력하면 "2"가 저장되는 이유)

임현수 2011. 6. 19. 16:48

팀 내에서 트랜잭션 관련 테스트를 하다고 호기심이 생겨서 서로 이야기를 나누었던 내용 정리해서 올립니다. 
메일을 주고 받았던 느낌 그대로 정리해 보았습니다. 일부 내용은 제가 임의로 편집하였습니다. ^^

[호기심 발동 – A님 문의]

트랜잭션을 걸지 않은 상태에서 insert 문을 실행할 경우, 내부적으로는 자동 트랜잭션 처리가 되는데요,

이 상태라면 내부적으로는 @@trancount = 1 이 입력되어야 한다고 생각됩니다.

예를 들어 아래처럼 실행하면 결과가 2가 나옵니다. (1이 나와야 할 것 같은데.. 자동 트랙잭션 카운트가 있으니까..)

혹시 이유를 아시는 분이 계실까요? 결과만 보자면 insert 시에 내부적으로 중첩 트랜잭션을 사용하는 것 같습니다.

테스트 내용은 아래와 같습니다.
/* 임시 테이블 생성 */
create
table tab01 (

           title varchar(100)

,          col01 int

,          col02 int

)


/* @@trancount
입력 */

insert into tab01 (title, col01, col02) values ('@@Trancount 입력', @@TRANCOUNT, 0)

 

/* 조회 */

select *

from tab01 with (readuncommitted)

where title = '@@Trancount 입력'




[
회신 1 B]
블로그를 찾아보니 내부적인 트랜잭션도 포함하여 계산하기 때문인 것으로 보입니다.
트랜잭션 로그 기록을 가지고 보면 좀 더 정확하게 볼 수 있네요. 다들 궁금해 하는 것이 비슷한 것 같습니다.

Ø 
https://blogs.msdn.com/b/jenss/archive/2010/07/10/usage-of-trancount-in-dml-statements.aspx

[회신 2 C] (@@trancount 외에 함수 활용 사례 공유)
Insert/update 문은 내부적으로 트랜잭션을 또 한번 사용하는 것 같습니다. 

추가적으로 @@trancount는 명시적인 트랜잭션을 사용하지 않는 경우에 그리 적절하지 않다고 합니다.
중첩된 트랜잭션도 롤백 때는 한방이므로 트랜잭션을 중첩시켜서 얻는 이득도 없고 하니 그냥 @@trancount > 0 인 경우를 체크하거나,
말씀하신 것과 같은 오동작(?)을 막기 위해서는 xact_state() 함수를 사용하시는 것도 좋을 듯 합니다.

@@trancount xact_state() 비교한 테스트 내용은 아래와 같습니다.
/* 테이블 생성
create table tab02 (
           title varchar(100)
,          col01 int
,          col02 int
)

/* INSERT:
암시적 트랜잭션 */
insert into tab02 values ('INSERT:암시적트랜잭션', @@TRANCOUNT, XACT_STATE())

/* INSERT: 명시적 트랜잭션 */
begin tran
           insert into tab02 values ('INSERT:명시적트랜잭션', @@TRANCOUNT, XACT_STATE())
commit

/* UPDATE 테스트를 위한 임시 데이터 생성 */
insert into tab02 values ('UPDATE:암시적트랜잭션', 0, 0)
insert into tab02 values ('UPDATE:명시적트랜잭션', 0, 0) 

/* UPDATE: 암시적 트랜잭션 */

update tab02

set col01 = @@TRANCOUNT, col02 = xact_state()

where title = 'UPDATE:암시적트랜잭션'

 

/* UPDATE: 명시적 트랜잭션 */

begin tran

           update tab02

           set col01 = @@TRANCOUNT, col02 = xact_state()

           where title = 'UPDATE:명시적트랜잭션'

commit

 

/* 결과 조회 */

select

           title

,          col01 as [@@trancount ]

,          col02 as [xact_state() ]

from tab02 with (readuncommitted)





[회신 3  D] (추가 호기심 발동)
내부적으로 트랜잭션이 발생한다면 아래의 경우는 어떻게 동작하는지 테스트 부탁드려요~ ^^
           1. Delete 구분도 동일하게 동작할까요
           2. update 중에 replace 형태가 아닌 delete /insert 형태가 존재하는데 이런 경우도 동일할까요?


[회신 3 E]
1. Delete 구문도 동일하게 동작할까요?

/* 테이블 생성 */

create table tab03 (

       title varchar(100)

,      col01 int

,      col02 int

)

 

/* @@trancount 입력 */

insert into tab03 (title, col01, col02) values ('DELETE 테스트1인경우', 1, 0)

insert into tab03 (title, col01, col02) values ('DELETE 테스트2인경우', 2, 0)

 

/* 삭제 */

delete from tab03

where col01 = @@TRANCOUNT

 

/* 어떤 값이 남았는지 조회 */

select *
from tab03 with (readuncommitted)

 



2. update
중에 replace 형태가 아닌 delete /insert 형태가 존재하는데 이런 경우도 동일할까요? 
/* 테이블 생성 */
create table tab04 (
       title varchar(100)
,      col01 int
,      col02 int
)

/* 
클러스터 인덱스 추가 */

create clustered index idx_tab04 on tab04 (col01)

 

/* 업데이트 테스트를 위한 기초 데이터 입력 */

insert into tab04 values ('UPDATE:암시적트랜잭션', 0, 0)

insert into tab04 values ('UPDATE:명시적트랜잭션', 0, 0)

 

/* UPDATE: 암시적 트랜잭션 */

update tab04

set col01 = @@TRANCOUNT, col02 = xact_state()

where title = 'UPDATE:암시적트랜잭션'

 

/* UPDATE: 명시적 트랜잭션*/

begin tran

       update tab04

       set col01 = @@TRANCOUNT, col02 = xact_state()

       where title = 'UPDATE:명시적트랜잭션'

commit

/* 어떤 값이 남았는지 조회 */

select *

from tab04 with (readuncommitted)
 




[
정리 – B]

여러가지 테스트를 해보았으니 이제 내용을 정리해보도록 하겠습니다.

결론적으로 SQL Server Locking two-phase locking입니다. 정확하게 이야기하면 Strong Strict two-phase locking(SS2PL)를 사용합니다.

따라서 암시적 트랜잭션 처리시 데이터 가공이 발생하는 시점에 @@trancount 2로 보이는 것이 맞습니다.

2PL을 이용하는 경우 부산물로 데드락이 발생할 수 있는 구조입니다.

 

추가적으로 2PL SS2PL에 대해서 초간단 요약해보았습니다. 추가적으로 궁금하신 분은 참고 문헌을 봐주세요

1.     Two-Phase Locking (2PL)

Ø  Issuing lcok : 필요한 잠금을 획득

Ø  Releasing lock : 사용한 잠금을 해제

2.     Strong strict two-phase locking (SS2PL)

Ø  2PL의 요건을 만족

Ø  Write(exclusive) lock commit, rollback 후 해제

 

[참고 문헌]

1.     https://blogs.msdn.com/b/jenss/archive/2010/07/10/usage-of-trancount-in-dml-statements.aspx

2.     http://en.wikipedia.org/wiki/Two-phase_locking

3.     http://blog.naver.com/PostView.nhn?blogId=asradad1&logNo=80052014598&redirect=Dlog&widgetTypeCall=true