2. 트랜잭션 수준 읽기 일관성
2010.06.07 02:12
(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>
- 일반적으로 일관성이 높아지면 동시성이 저하되지만
- 오라클의 경우 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
----------
1000t4
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 transactiont5
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 Kytehttp://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
67 | Front Page | 운영자 | 2010.05.17 | 154868 |
66 | 4. Prefetch | balto | 2010.07.10 | 28445 |
65 | 5. 오라클 Lock | 휘휘 | 2010.06.07 | 26368 |
64 | 2. DB 버퍼 캐시 | 휘휘 | 2010.05.24 | 21919 |
63 | 3. SQL 트레이스 | balto | 2010.06.06 | 21176 |
62 | 1. 기본 아키텍처 [1] | 휘휘 | 2010.05.23 | 19910 |
» | 2. 트랜잭션 수준 읽기 일관성 | 휘휘 | 2010.06.07 | 19569 |
60 | 5. Undo | 토시리 | 2010.05.31 | 18665 |
59 | 11. Shared Pool | 실천하자 | 2010.05.31 | 18513 |
58 | 9. Static vs. Dynamic SQL [1] | balto | 2010.07.04 | 18350 |
57 | 4. Array Processing 활용 | 휘휘 | 2010.07.05 | 18252 |
56 | 1 장. 오라클 아키텍처 | 운영자 | 2010.05.20 | 17850 |
55 | 5. Fetch Call 최소화 | 휘휘 | 2010.07.05 | 16852 |
54 | 2. SQL 처리과정 | 휘휘 | 2010.06.28 | 15743 |
53 | 9. ASH(Active Session History) | 실천하자 | 2010.06.14 | 15608 |
52 | 3. 버퍼 Lock [1] | 휘휘 | 2010.05.24 | 15235 |
51 | 6. 바인드 변수의 부작용과 해법 | 실천하자 | 2010.06.28 | 14680 |
50 | 1. Explain Plan | 실천하자 | 2010.06.06 | 14666 |
49 | 8. PL/SQL 함수 호출 부하 해소 방안 | 토시리 | 2010.07.11 | 14031 |
48 | 7. Result 캐시 | 휘휘 | 2010.07.19 | 12970 |