Isolation level

Isolation level

수많은 트랜잭션에 의해 데이터는 끊임없이 변경 및 ACCESS되고 있으며 이러한 과정 속에 검색되는 데이터 값의 어디까지를 내 트랜잭션에서 처리할 것인가를 결정하기 위해 ASE에서는 isolation level를 지정할 수 있다 isolation은 다른 트랜잭션에 의해 변경되고 있는 dirty page를 읽을 수 있는가? 하나의 트랜잭션에서 검색 되는 데이터의 일관성을 유지할 것인가? 하나의 트랜잭션에서 발생되는 phantom 현상을 허용할 것인가에 따라 isolation의 level을 1,2,3으로 정의한다 o dirty read? o nonrepeatable read? o phatom read? o isolation level? o isolation 설정 방법? o nonrepeatable 발생을 해결하려면? o phantom read 발생을 해결하려면? Dirty Read? 트랜잭션1이 데이터를 변경후 트랜잭션을 종료(commit또는 rollback)하지 않은 상태에서 트랜잭션2가 변경된 데이터로 접근할 수 있다 트랜잭션1이 변경한 데이터에 대해서 commit할 지 rollback할지 트랜잭션2는 알 수 없으므로 rollback과 함께 트랜잭션1이 종료한다면 트랜잭션2는 잘못된 데이터로부터 잘못된 처리를 할 수 있는 위험이 있다 이와 같이 업무 성격에 따라 Dirty Page를 다른 트랜잭션이 접근하는 것을 허용할 것인지, 안할 것인지에 따라 ioslation 0(허용)와 isolation 1(허용 안함)로 정의 할 수 있다 Nonrepeatable Read? 트랜잭션1이 select하고 있는 테이블에는 Shared lock이 걸려 있으므로 트랜잭션2는 트랜잭션1이 검색했던 페이지의 데이터를 Modify한 후 트랜잭션2를 종료(commit)할 수 있다. 이때 아직 트랜잭션을 종료하지 않은 트랜잭션1이 먼저 select한 데이터를 다시 검색했을때 같은 트랜잭션에서 같은 질의가 발생했음에도 불구하고 다른 데이터 값을 가져올 수 있다 이와 같이 동일한 트랜잭션에서 동일한 질의에 대해 검색되는 데이터 값에 대한 일관성을 유지할 것인가(isolation level 2), 아니면 유지하지 않을 것인가 (isolation level 1)에 따라 isolation level을 지정할 수 있다 Phantom Read? 트랜잭션1이 질의를 발생하여 조건에 맞는 row의 sets을 검색중인 상황에서 트랜잭션2가 같은 테이블의 다른 row의 sets(트랜잭션1이 검색하고 있지 않는 row의 sets)의 데이터 값을 변경한 후 트랜잭션을 종료 했다 트랜잭션2가 변경한 데이터 값이 우연히도 트랜잭션1의 조건절에 영향을 주는 데이터 값이라 아직 트랜잭션을 종료하지 않은 트랜잭션1이 동일한 질의를 실행시켰을 경우 처음 데이터보다 많은 rows의 set을 검색하게 된다 이와 같이 같은 트랜잭션에서 동일한 질의를 발생시켰을 때 더 많은 rows의 sets을 보게 되는 현상을 phantom 현상이라 한다 이와 같이 같은 트랜잭션에서 phantom현상을 허용할 것인지(isolation level 2) 허용하지 않을 것인지(isolation level 3)에 따라 isolation level을 정의할 수 있다 Isolation Level? 위의 세가지 현상을 허용할 것인지 허용하지 않을 것인지에 따라 isolation level을 4가지로 나눌 수 聆만?level이 높아질수록 더 많은 제약을 부여한다   Level 1 (Read Committed)가 SQL Server의 기본 격리 수준(Isolation Level)이다. Dirty Read Nonrepeatable Read Phantom Read Level 0 READ UNCOMMITTED Allowed Allowed Allowed Level 1 READ COMMITTED Prevented Allowed Allowed Level 2 REPEATABLE READ Prevented Prevented Allowed Level 3 SERIALIZABLE Prevented Prevented Prevented Level 0 변경되고 있는 데이터에 대한 정보를 실시간 보기 위한 노력으로 set transaction isolation level 0 명령에 의해 설정되며 select명령 실행 시 share page lock을 필요로 하지 않는다 Page read 시에 해당 page에 대한 어떠한 lock도 장해가 되지 않으며 select 문장의 holdlock option 또한 아무런 효과가 없다 현재 다른 트랜잭션에 의해 변경중인(exclusive page lock)page에 대해서도 read가 가능하다 Level 1 isql에서 default mode로 사용되며 select시 읽혀지는 page에 대해 순간적으로 Shared Page Lock이 걸리며 읽힌 후 바로 Lock이 해제된다 select문장에서 holdlock option을 주게 되면 page를 읽을 때 부여되는 Shared Page Lock이 바로 해제가 되지 않으며 commit/rollback 명령에 의해서만 해제된다 Level 2 같은 트랜잭션에서의 동일한 질의에 대해 동일한 결과 값을 보장해주기 위한 노력으로 select시 발생한 Shared Lock은 트랜잭션이 종료(commit or rollback)될 때까지 해제되지 않는다 Datarows lock scheme에서만 지원되며 같은 트랜잭션에서 동일한 질의에 대해 Phantom 현상을 허용한다 Level 3 실행되는 모든 select문에 대해 holdlock option을 준 효과를 가진다 따라서 트랜잭션이 종료(commit/rollback)되기 전까지는 read된 모든 page에 대해서 shared page lock이 해제 되지 않으므로 수많은 lock을 발생시킬 수 있으므로 주의해야 한다 보다 자세한 내용은 more를!!!

 

출처 : http://www.jiic.com

..more

>접기

Isolation의 설정 방법?

Syntax for Session-Level Isolation:
set transaction isolation level {
0 | read uncommitted |
1 | read committed |
2 | repeatable read |
3 | serializable }

Syntax for Statement-Level Isolation:
select ...
at isolation {
0 | read uncommitted |
1 | read committed |
2 | read repeatable |
3 | serializable }

a @@isolation returns the isolation level for the session

nonrepeatable 발생 해결하려면?

isolation level을 2,3으로 올려야 한다
예)
현재의 isolation level은 1이고, 여러 page를 차지하는 table을 만든다
1> use sybsystemprocs
2> go
1> create table testTABLE (
2> a char(255),
3> b char(255),
4> c char(255),
5> d char(255))
6> go
1> insert into testTABLE values ('1','1','1','1')
2> insert into testTABLE values ('2','2','2','2')
3> insert into testTABLE values ('3','3','3','3')
4> insert into testTABLE values ('4','4','4','4')
5> insert into testTABLE values ('5','5','5','5')
6> insert into testTABLE values ('6','6','6','6')
7> insert into testTABLE values ('7','7','7','7')
8> insert into testTABLE values ('8','8','8','8')
9> insert into testTABLE values ('9','9','9','9')
10> insert into testTABLE values ('10','10','10','10')
11> insert into testTABLE values ('11','11','11','11')
12> insert into testTABLE values ('12','12','12','12')
13> insert into testTABLE values ('13','13','13','13')
14> insert into testTABLE values ('14','14','14','14')
15> insert into testTABLE values ('15','15','15','15')
16> insert into testTABLE values ('16','16','16','16')
17> insert into testTABLE values ('17','17','17','17')
18> insert into testTABLE values ('18','18','18','18')
19> insert into testTABLE values ('19','19','19','19')
20> insert into testTABLE values ('20','20','20','20')
21> go
1> select @@isolation
2> go
-----------
1

지금부터는 2개의 session A,B를 함께 사용해서 하십시요
*************
session A
*************
1> select @@isolation
2> go
-----------
1

1> -- (1) transaction 을 시작한다
2> begin tran
3> go
1> ---(3) a='1' 인 내용을 select한다. lock은 읽는 순간에만 걸린다
2> select * from testTABLE where a = '1'
3> go

a -- b ---c ---d
--- ---- --- ----
1 ---1 ---1 ---1

1> ---(6) 위에서 select한 내용을 똑같이 시도한다.
-- 그러나, 한 transaction안에서도 값은 위의 select와 똑같지 않다
-- 이것이 바로 nonrepeatable read 이다
2> select * from testTABLE where a = '1'
3> go
a --- b ---c ---d
---- ---- ---- -----
1 -- -1 ---1 ---123
1> ---(7)
2> commit tran
3> go

*************
session B
*************
1> -- (2) transaction 을 시작한다
2> begin tran
3> go
1> -- (4) session A 에서 읽었던 a='1' 인 내용을 update한다.
-- lock은 execluseve로 commit tran을 만날때 까지 풀리지 않는다
2> update testTABLE set d = '123' where a = '1'
3> go
1> ---(5) commit 을 하면, 위의 update로 인한 lock이 풀리고
-- 다른 session에서 update의 변경값을 읽을 수 있다
2> commit tran
3> go

위의 것은 Isolation level 1인 경우입니다. Isolation level 2로 바꾸고 하면 (3)번이 lock을 걸어 (4)번 수행이 되지 않습니다. 그래서 (6)번,(7)번 수행하고 lock이 풀려야 (4)번 수행됩니다
이처럼 Isolation level 2에서는 한 transaction이 1 row를 읽은 것이 transaction이 끝날 때 까지 lock을 걸어 수행이 같은 transaction에서 그 1 row를 다시 읽어도 같은 내용이 읽힙니다

phantom read 발생 해결하려면?

isolation level 3으로 해결할 수 있습니다
예)
현재의 isolation level은 1이고, 여러 page를 차지하는 table을 만든다

1> use sybsystemprocs
2> go
1> create table testTABLE (
2> a char(255),
3> b char(255),
4> c char(255),
5> d char(255)) lock datarows
6> go

1> set transaction isolation level 3
2> go

1> insert into testTABLE values ('1','1','1','1')
2> insert into testTABLE values ('2','2','2','2')
3> insert into testTABLE values ('3','3','3','3')
4> insert into testTABLE values ('4','4','4','4')
5> insert into testTABLE values ('5','5','5','5')
6> insert into testTABLE values ('6','6','6','6')
7> insert into testTABLE values ('7','7','7','7')
8> insert into testTABLE values ('8','8','8','8')
9> insert into testTABLE values ('9','9','9','9')
10> insert into testTABLE values ('10','10','10','10')
11> insert into testTABLE values ('11','11','11','11')
12> insert into testTABLE values ('12','12','12','12')
13> insert into testTABLE values ('13','13','13','13')
14> insert into testTABLE values ('14','14','14','14')
15> insert into testTABLE values ('15','15','15','15')
16> insert into testTABLE values ('16','16','16','16')
17> insert into testTABLE values ('17','17','17','17')
18> insert into testTABLE values ('18','18','18','18')
19> insert into testTABLE values ('19','19','19','19')
20> insert into testTABLE values ('20','20','20','20')
21> go

지금부터는 2개의 session A, B를 함께 사용해서 하십시요

*************
session A
*************
1> -- (1) 현재 isolation level은 2 입니다
1> select @@isolation
2> go
-----------
--- 2

2> begin tran -- transaction 을 시작 합니다
3> go
1> ---(3) 조건 < 9 인 내용을 보았습니다
2> select * from testTABLE where convert(int,a) < 9
3> go
a
-----------------------------------------------------------------------
1
2
3
4
5
6
7
8

1> ---(6) 조건 < 9 인 내용을 보았습니다
-- session B의 영향으로
-- 위의 select결과로 보이지 않던 0가 보인는 phantom현상이 발생했습니다
2> select * from testTABLE where convert(int,a) < 9
3> go
a
-----------------------------------------------------------------------
1
2
3
4
5
6
7
8
0
1> ---(7)
2> commit tran
3> go

*************
session B
*************
1> -- (2) 번호순 대로 수행하세요
2> begin tran
3> go
1> -- (4)
2> insert into testTABLE values ('0','0','0','0')
3> go
1> ---(5)
2> commit tran
3> go

위의 것은 Isolation level 2인 경우이다
Isolation level 2인 경우, 번호순서대로 수행이 가능하나
Isolation level 3로 바꾸고 하시면 (3)번이 lock을 걸어 (4)번 수행이 되지 않는다
그래서 (6)번,(7)번 수행하고 lock이 풀려야 (4)번 수행된다