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