/**********************************************************************************************
-- Title : [2k5] Xact_Abort On/Off의 SQL 2000/2005 버전별 차이점
-- Key word : xact_abort @@trancount xact_state()
**********************************************************************************************/
use tempdb;
go
drop table ttt,ttt2,ttt3;
gocreate table ttt (col1 int primary key);
create table ttt2 (col1 int primary key);
create table ttt3 (col1 int primary key);
go
set xact_abort on; --// 2k와 2k5에서 on/off시 결과가 틀리다.
--1)
begin tran;
insert into ttt (col1) values (1);
select * from tttttt;
commit tran;
select * from ttt;
select xact_state(), @@trancount
-- 2k5 off : 1 입력, xact 증가
-- 2k off : 1 입력, xact 증가
-- 2k5 on : 1 무입력, xact 무증가
-- 2k off : 1 입력, xact 증가
go
--2)
begin tran;
exec ('insert into ttt2 (col1) values (2);')
insert into ttt2 (col1) values (2);
commit tran;
select * from ttt2;
select xact_state(), @@trancount -- 0
-- 2k5 off : 2 입력, xact 무증가
-- 2k off : 2 입력, xact 무증가
-- 2k5 on : 2 무입력, xact 무증가
-- 2k off : 2 무입력, xact 무증가
go
--3)
begin tran;
exec ('insert into ttt22 (col1) values (22);')
insert into ttt2 (col1) values (222);
commit tran;
select * from ttt2;
select xact_state(), @@trancount -- 0
-- 2k5 off : 222 입력, xact 무증가
-- 2k off : 222 입력, xact 무증가
-- 2k5 on : 222 무입력, xact 무증가
-- 2k off : 222 입력, xact 무증가
go
--4)
begin tran;
insert into ttt3 values (3)
exec ('insert into ttt3 (col3) values (33);')
commit tran;
select * from ttt3;
select xact_state(), @@trancount -- 0
-- 2k5 off : 3 입력, xact 무증가
-- 2k off : 3 입력, xact 무증가
-- 2k5 on : 3 무입력, xact 무증가
-- 2k off : 3 입력, xact 무증가
go
[2k5] Xact_Abort On/Off의 SQL 2000/2005 버전별 차이점
2008. 9. 7. 01:40
반응형
반응형