세션 1 에서 아래 구문 실행 후에 세션 2 에서 select 후 세션 1 에서 rollback이 아닌 쿼리Abort (쿼리 실행 취소)를 하면 아래와 같은 현상이 발생됨
세션 1
use test
drop table if exists tb_test
create table tb_test (
index_no int identity(1,1) primary key
, num int
, contents nvarchar(100)
)
--15만건
insert into tb_test ( num, contents)
select top 150000 m1.object_id, N'test'
from master.sys.all_sql_modules as m1
cross join master.sys.all_sql_modules as m2
begin tran
delete
from dbo.tb_test
waitfor delay '0:0:10'
--세션 2 실행 후 바로 쿼리 실행 취소
세션 2
use test
select *
from dbo.tb_test
where index_no = 1
실행 결과 왼쪽 세션은 쿼리 실행중 / 오른쪽 세션은 취소 된 상태
Blocked Process 조회
53번 세션 트랜잭션 카운트, opentran 정보 확인
쿼리 실행 취소 시, 실질적으로 rollback이 되지 않고 정상적으로 트랜잭션이 종료 되지 않음을 확인할 수 있음.
ROLLBACK 혹은 COMMIT 실행 시 정상적으로 트랜잭션이 종료 됨을 확인할 수 있다.
SET XACT_ABORT ON옵션을 사용한다면, 쿼리 실행 취소를 할 경우 Abort와 동시에 해당 트랜잭션이 ROLLBACK 처리 되는 것을 확인 할 수 있다.
SET XACT_ABORT ON 옵션에 대해 알아보자
SET XACT_ABOART OFF
세션 1
use test
drop table if exists tb_test1
create table tb_test1 (
index_no int identity(1,1) primary key
, num int
, contents nvarchar(100)
)
drop table if exists tb_test2
create table tb_test2 (
index_no int identity(1,1) primary key
, num int
, contents nvarchar(100)
)
drop table if exists tb_test3
create table tb_test3 (
index_no int identity(1,1) primary key
, num int
, contents nvarchar(100)
)
insert into tb_test1 ( num, contents)
select top 150000 m1.object_id, N'test'
from master.sys.all_sql_modules as m1
cross join master.sys.all_sql_modules as m2
insert into tb_test2 ( num, contents)
select top 150000 m1.object_id, N'test'
from master.sys.all_sql_modules as m1
cross join master.sys.all_sql_modules as m2
insert into tb_test3 ( num, contents)
select top 150000 m1.object_id, N'test'
from master.sys.all_sql_modules as m1
cross join master.sys.all_sql_modules as m2
SET XACT_ABORT OFF
begin tran
delete
from dbo.tb_test1
delete
from dbo.tb_test2
where index_no = 150001/0 --에러발생
delete
from dbo.tb_test3
COMMIT
세션 2
-- set transaction isolation level read uncommitted
-- set transaction isolation level read committed
-- 커밋 후에 확인할 것이기 때문에 UNCOMMITED, COMMITTED 트랜잭션 격리 수준 선언 영향은 없습니다.
use test
select *
from dbo.tb_test1
where index_no = 1
select *
from dbo.tb_test2
where index_no = 1
select *
from dbo.tb_test3
where index_no = 1
결과
오류가 난 부분만 처리가 되지 않았고 나머지 부분은 처리 완료되어 commit되었음을 알 수 있다.
SET XACT_ABORT ON 위 스크립트에서 XACT_ABORT OFF 부분만 ON 으로 바꿔주고 실행하면
오류 발생시, 해당 트랜잭션에서의 작업은 모두 ROLLBACK 처리 됨을 알 수 있다.
결론1 : 명시적 트랜잭션을 사용할 경우 작업 후에 COMMIT 혹은 ROLLBACK 을 꼭 실행해줘야 한다.
결론2 : DBCC OPENTRAN으로 확인하거나 SELECT @@trancount 로 열린 트랜잭션이 있는지 확인하자.
