태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Read Consistency In Function

오라클 2008.07.14 15:39
Select Query 내에서 Fucnction을 사용할 경우 Read Consistency가 문제가 되는 경우가 있다. 예를 들면 다음과 같다.

우선 테스트에 필요한 테이블과 함수를 만든다.

drop table t1 purge;
drop table t2 purge;
create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);

create or replace function f1(v1 int)
return number
is
  v_max int;
begin
  select max(c2) into v_max from t2;
 
  return v_max;
end f1;
/

create or replace function fsleep(v1 int)
return number
is
begin
  dbms_lock.sleep(0.1);
  return 1;
end fsleep;
/


insert into t1
select level, level
from dual
connect by level <= 1000
;

insert into t2 values(1, 1)
;

@gather t1
@gather t2


다음과 같이 test2 스크립트를 수행시킨다.

ho start sqlplus ukja/ukja@ukja102 @test2
@sleep 0.2


test2 스크립트는 다음과 같이 t2 테이블의 로우 값을 계속 증가시킨다.(반드시 commit 수행되어야 한다)

begin
  for idx in 1 .. 100000 loop
    update t2 set c2 = c2 + 1
    where fsleep(c1) = 1;
    commit;
  end loop;
end;
/


test2 스크립트가 t2 테이블의 로우 값을 계속 증가시키는 상황에서 다음과 같이 Function을 이용해 t2 테이블의 Max 값을 가져온다.

select c1, f1(c1) as max_val
from t1
where fsleep(c1) = 1
    and rownum <= 20
;

        C1    MAX_VAL
---------- ----------
         1          2
         2          3
         3          4
         4          5
         5          6
         6          7
         7          8
         8          9
         9         10
        10         11
        11         12
        12         13
        13         14
        14         15
        15         16
        16         17
        17         18
        18         19
        19         20
        20         21

결과는 놀랍게도 Max 값은 Function이 호출될 때마다 증가한다. 즉 Main Query의 Read Consistency가 Function에서 동작하지 않는다. 다시 말하면 한번의 Function 호출 단위로 Read Consistency가 이루어진다는 것이다.

이것이 문제인가? 경우에 따라 다르다.

만일 Function의 Read Consistency를 Main Query와 맞추어 주고 싶다면? 다음과 같은 해결책들이 알려져 있다.

1. Read Only Transaction 사용

set transaction read only;

select c1, f1(c1) as max_val
from t1
where fsleep(c1) = 1
    and rownum <= 20
;

        C1    MAX_VAL
---------- ----------
         1         21
         2         21
         3         21
         4         21
         5         21
         6         21
         7         21
         8         21
         9         21
        10         21
        11         21
        12         21
        13         21
        14         21
        15         21
        16         21
        17         21
        18         21
        19         21
        20         21

2. Serializable Isolation Level의 사용

set transaction isolation level serializable;

select c1, f1(c1) as max_val
from t1
where fsleep(c1) = 1
    and rownum <= 20
;

        C1    MAX_VAL
---------- ----------
         1         41
         2         41
         3         41
         4         41
         5         41
         6         41
         7         41
         8         41
         9         41
        10         41
        11         41
        12         41
        13         41
        14         41
        15         41
        16         41
        17         41
        18         41
        19         41
        20         41

3. Scalar Subquery의 사용

select c1,
  (select max(c2) from t2) as max_val
from t1
where fsleep(c1) = 1
    and rownum <= 20
;

        C1    MAX_VAL
---------- ----------
         1         61
         2         61
         3         61
         4         61
         5         61
         6         61
         7         61
         8         61
         9         61
        10         61
        11         61
        12         61
        13         61
        14         61
        15         61
        16         61
        17         61
        18         61
        19         61
        20         61

Function Read Consistency에 대한 이해 외에도 테스크 스크립트를 작성하는 방법도 눈여겨 볼 필요가 있겠다.

1. 2008/07/04 - [Advanced Oracle] - Automize your test case 에서 언급한 바와 같이 간단한 트릭을 이용해 테스트를 자동화하는 기법과
2. fsleep 함수에서 dbms_lock.sleep을 이용해서 일부러 Query의 수행을 천천히 만드는 기법 등은 테스트 시에 매우 유용한 테크닉이다.


'오라클' 카테고리의 다른 글

Web에서 Code Style 사용하기  (1) 2008.07.23
Dynamic Sampling and Concatenation  (0) 2008.07.17
Read Consistency In Function  (2) 2008.07.14
Why Creativity Matters?  (0) 2008.07.07
Automize your test case  (0) 2008.07.04
Trackback 0 : Comments 2
  1. 박광서 2008.07.16 13:10 신고 Modify/Delete Reply

    참 유익한 정보 담아갑니다. 감사합니다. fsleep 펑션은 참 기발하네요^^

  2. 김용범 2008.07.22 16:50 신고 Modify/Delete Reply

    놀라운 사실이네요... 감사합니다...

Write a comment

티스토리 툴바