2. 트랜잭션 수준 읽기 일관성

조회 수 6588 추천 수 0 2011.03.26 11:23:30
휘휘 *.168.123.1

(1) 트랜잭션 수준 읽기 일관성이란?


  • 문장수준 읽기 일관성 (Statement-Level Read Consistency)
    • 쿼리가 시작된 시점을 기준으로 데이터를 일관성 있게 읽어 들이는것
  • 트랜잭션 수준 읽기 일관성 (Transaction-Level Read Consistency)
    • 트랜잭션이 시작된 시점을 기준으로 일관성 있게 데이터를 읽어들이는 것



(2) 트랜잭션 고립화 수준


ANSI /ISO SQL standard (SQL 92) 정의

Isolation Level
Dirty Read
Nonrepeatable Read
Phantom Read
READ UNCOMMITTED
Permitted
Permitted
Permitted
READ COMMITTED
--
Permitted
Permitted
REPEATABLE READ
--
--
Permitted
SERIALIZABLE
--
--
--



  • 레벨 0 (= Read Uncommitted)
  • 커밋되지 않은 데이터를 다른 트랜잭션에서 읽는 것을 허용
  • Dirty Read, Non-Repeatable Read, Phantom Read 현상 발생
  • Oracle 지원하지 않음

  • 레벨 1 (= Read Committed)
  • Dirty Read 방식 : 트랜잭션이 커밋되어 확정된 데이터만 읽는 것을 허용
  • 대부분의 DBMS의 기본모드
  • Non-Repeatable Read, Phantom Read 현상은 여전히 발생
  • DB2,SQL SERVER,SYBASE
    • 읽기 공유 LOCK을 통해 구현  (하나의 레코드를 읽을 때 LOCK을 설정하고 해당레코드를 빠져나가는 순간 해제)
  • ORACLE
    • 쿼리시작 시점의 UNDO 데이터를 제공하는 방식으로 구션

  • 레벨2 (= Repeatable Read)
  • 선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 후행 트랜잭션이 갱신하거나 삭제하는 것을 불허
  • Phantom Read 현상은 여전히 발생
  • DB2, SQL Server : 읽은 데이터에 걸린 공유 Lock을 커밋할때 까지 유지하는 방식으로 구현
  • Oracle
    • 명시적지원없음, 단 for update 절을 이용해 구현가능

  • 레벨 3 (= Serializable Read)
    • 선행 트랜잭션이 읽은 자료를 갱신하거나 삭제하지 못할뿐아니라 새로운 레코드 삽입도 금지
    • 완벽한 릭기 일관성 모드 제공


(3) Dirty Read (= Uncommitted Dependency)

  • 수정중인 데이터를 다른 트랜잭션에서도 읽을수 있도록 허용하는 것을 말하며 이때



select sum(잔고) from 계좌;


  • 같은 쿼리를 수행시킬때  동시작업이 이루어 지고 있다면 쿼리 결과값이 비일관성상태에 놓일수도 있게된다.
  • 오 라클은 지원하지 않으며 다른 dbms의 경우 지원하고 있지만 기본값으로 level 1을 사용한다.


(4) Non-Repeatable Read(= Inconsistent Analysis)

  • 한트랜잭션 내에서 같은 쿼리를 두번이상 사용할때 그 사이 다른 트랜잭션이 값을 수정또는 삭제하여
    두쿼리의 결과가 다른 비일관성이 나타나는 현상


tx1
t
tx2
select 당월주문금액 into : amt
from 고객
where 고객번호=123;
t1


t2
update 고객
set 당월주문금액=60000, 등급 ='A'
where 고객번호=123;

t3
commit;
if :amt>=50000 then
 update 고객
 set 등급='A'
 where 고개번호=123;
else
 update 고객
 set 등급='B'
 where 고개번호=123;
end if;
t4

commit;
t5


  • t1 시점에 123번 고객 주문금액은 40000 이었을경우
  • t2에의해 60000에 A로 변경되었드라도 t4에 의해서 등급이 B로  수정되어버린다.

(5) Phantom Read

  • 한 트랜잭션 안에서 일정범위의 레코드들을 두번 이상 읽을 때 , 첫번재 쿼리에서 없던 레코드가 두번재 쿼리에서 나타나는 현상

tx1
t
tx2
insert into 지역별고객
select 지역, count(*)
from 고객
group by 지역;
t1


t2
insert into 고객
(고객번호, 이름, 지역, 연령대)
values (:a,:b,:c,:d);

t3
commit;
insert into 연령대별고객
select 연령대, count(*)
from 고객
group by 연령대;
t4

commit;
t5



  • tx1 트랜젝션 기준으로 보면 t1에서 집계한 지역별 총고객수와 t2의 수행이 종료된후 수행된
  • t4의 결과의 총고객수가 불일치 이문제를 해결하려면



set transaction isolation level serializable;


  • 명령으로  고립화 수준을 레벨3 로 변경 하면 되나
  • sql server의 경우 이때 t2 트랜젝션에 락이 걸려 동시성에 문제가 발생
  • oracle 의 경우 발생하지 않음


tx1
t
tx2
insert into 로그백업
select * from 로그;
t1


t2
insert into 로그 (일시,id,..)
values (sysdate,'0001',..);

t3
commit;
delete from 로그;
t4

commit;
t5


  • t4 의 delete로 t2에서 삽입된 로그가 사라짐
  • sql server의경우 고립화 수준 향상을통해 tx1동안 락발생
  • oracle은 락에의해 insert가 안되는 문제는 발생하지 않음


oracle XE


실험자료 생성


create table cust as select rownum as cno, 'A'||rownum as name, 'DDD' as area, mod(rownum,3) as age from dual connect by level<20
SQL> /

Table created.

SQL> create table acu ( area varchar2(10), cnt number);

Table created.

SQL> create table bcu (age varchar2(10),cnt number);

Table created.

SQL> commit;

Commit complete.



TX1


SQL> set transaction isolation level serializable;

Transaction set.

SQL> insert into acu select area,count(*) from cust group by area;

1 row created.


TX2


SQL> insert into cust values ('22','adsf','kkk','0');

1 row created.

SQL> commit;

Commit complete.




SQL> insert into bcu select age,count(*) from cust group by age;

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from acu;

AREA              CNT
---------- ----------
DDD                19

SQL> select * from bcu;

AGE               CNT
---------- ----------
1                   7
2                   6
0                   6

SQL>







02gr.jpg

  • 일반적으로 일관성이 높아지면 동시성이 저하되지만
  • 오라클의 경우 LOCK사용하지 않아 동시성이 저하되지 않는다




SERIALIZABLE_ABORTS

오라클이 트랜잭션 레벨에서 일관성있는 데이터를 제공하지 못할때 발생




SQL> create table acc as select '5' as no, 1000 as amt from dual;

Table created.

SQL> commit;



tx1
t
tx2
SQL> set transaction isolation level serializable;

Transaction set.


t1


t2
SQL> update acc set amt=amt+1000 where no=5;

1 row updated.



t3

SQL> commit;

Commit complete.



SQL> select amt from acc where no=5;

       AMT
----------
      1000


t4


SQL> update acc set amt=amt+500 where no=5;
update acc set amt=amt+500 where no=5
       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction


t5



serializable level 로 설정되어 tx1의 경우 모든 값을t1시점으로 읽어오게되며로 t4 의경우도 t2에 의한 갱신된결과가
보여지지 않고 t5에서 update를 사도하려고 할경우 08177 에러가 발생


SQL> select sql_text from v$sql where SERIALIZABLE_ABORTS>0;

SQL_TEXT
--------------------------------------------------------------------------------
update acc set amt=amt+500 where no=5

SQL>

로 조회가능







참 고: On Transaction Isolation Levels By Tom Kyte

http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html