[SQL2k5] 트랜잭션 격리 수준(isolation level)별 테스트 스크립트

/**********************************************************************************************
-- Title : [SQL2k5] 트랜잭션 격리 수준(isolation level)별 테스트 스크립트
-- Reference : hanbitbook.co.kr, dbrang.com
-- Key word : read uncommitted committed repeatable read phantom snapshot
**********************************************************************************************/
/*
-- read uncommitted
*/
use tempdb;
go

if object_id('isotbl') is not null
   drop table isotbl;
go

create table isotbl
 (name nvarchar(10),
  money int  );
go

insert into isotbl values ('우재남', 1000);
insert into isotbl values ('당탕이', 0);
go

begin tran
 update isotbl set money = money - 500 where name = '우재남';
 update isotbl set money = money + 500 where name = '당탕이';
go

select * from isotbl; -- after image 보인다.
go

/* 다른 세션 시작 */
use tempdb;
go

set transaction isolation level read uncommitted;
go

select * from isotbl where name = '당탕이' -- after image가 보인다.
go

/* 다른 세션 끝 */
rollback tran;
go

/*
-- read committed
*/
use tempdb;
go

begin tran
 update isotbl set money = money - 500 where name = '우재남';
 update isotbl set money = money + 500 where name = '당탕이';
go

 /* 다른 세션 시작 */
 use tempdb;
 go

 set transaction isolation level read committed;
 go

 select * from isotbl where name = '당탕이'; -- 배타 잠금에 의해 읽히지 않고 대기한다.
 go

 /* 다른 세션 끝 */
rollback tran;
go
/*
-- repeatable read
*/
use tempdb;
go

-- unrepeatable 문제 발생건
begin tran
 select * from isotbl;
/*
name       money
---------- -----------
우재남        500
당탕이        500
*/
go

 /* 다른 세션 시작 */
 begin tran; -- 원세션에서 공유 잠금 상태임
  update isotbl set money = money - 500 where name = '우재남';
  update isotbl set money = money + 500 where name = '당탕이';
 commit tran;
 /* 다른 세션 끝 */
select * from isotbl;
/*
name       money
---------- -----------
우재남        0
당탕이        1000
*/
--  같은 트랜잭션 내에서 2번 읽었는데
--  다른 값이 나왔다.(unrepeatable read)
commit tran;
go

-- unrepeatable read 해결
set transaction isolation level repeatable read;
go

begin tran
 select * from isotbl;
go
/*
name       money
---------- -----------
우재남        0
당탕이        1000
*/

 /* 다른 세션 시작 */
 begin tran
  update isotbl set money = money - 500 where name = '우재남';
  update isotbl set money = money + 500 where name = '당탕이';
 commit tran
 /* 다른 세션 끝 */
select * from isotbl; -- 변환 없음
go
/*
name       money
---------- -----------
우재남        0
당탕이        1000
*/
commit tran; -- 다른 세션에서 대기중이던 commit이 실행됨.
go

-- 상기 commit 이해에 대기중이던 다른 세션의 트랜잭션이 실행되어 값이 변경된다.
select * from isotbl;
/*
name       money
---------- -----------
우재남        -500
당탕이        1500
*/
go

/*
-- phantom read
*/
use tempdb;
go

set transaction isolation level serializable;
go

begin tran;
select * from isotbl;
go
/*
name       money
---------- -----------
우재남        1000
당탕이        0
*/

 /* 다른 세션 시작 */
 use tempdb;
    go

 insert into isotbl values ('한주연',5000);   -- 삽입되지 않고 대기한다.
    go

 /* 다른 세션 끝 */
select * from isotbl; -- 다른 세션 삽입값이 안보인다.
go

/*
name       money
---------- -----------
우재남        1000
당탕이        0
*/
commit tran
go

select * from isotbl; -- 다른 세션 삽입값이 보인다.
go
/*
name       money
---------- -----------
우재남        1000
당탕이        0
한주연        5000
*/
/*
-- snapshot
*/
if exists(select * from master..sysdatabases where name = 'trandb')
   drop database trandb;
go

create database trandb;
go

use trandb;
go

alter database trandb set allow_snapshot_isolation on;
go

/*level snapshot을 사용할려면 allow_snapshot_isolation을 on 해야한다.*/
set transaction isolation level snapshot;
go

drop table ttt;
go

create table ttt
( a int, b int);
go
insert
into ttt values (1,1);
insert into ttt values (2,2);
insert into ttt values (3,3);
go

begin tran;
 select * from ttt; -- 3행
go

 /* 다른 세션 시작 */
 use trandb;
    go

 insert into ttt values (4,4);
 insert into ttt values (5,5);

 /* 다른 세션 끝 */
select * from ttt; -- 3행
go

commit tran;
go

select * from ttt;  -- 5행
go