07. Consistent vs. Current 모드 읽기

(1) Consistent 모드 읽기와 Current 모드 읽기의 차이점

1) Consistent 모드 읽기(gets in consistent mode)

• SCN 확인 과정을 거치며 쿼리가 시작된 시점을 기준으로 일관성 있는 상태로 블록을 액세스하는 것. (쿼리 실행 시간에 관계없이 항상 쿼리가 시작된 시점의 데이터를 가져온다.)
• SQL trace의 'query', Autotrace에서의 'consistent gets' : Consistent 모드에서 읽은 블록 수
• CR copy를 생성할 필요가 없어 Current 블록을 읽더라도 Consistent 모드에서 읽었다면 'query'항목에 집계.
• select문에서 읽은 블록은 대부분 여기에 해당. CR블록을 생성하려고 Undo 세그먼트로부터 읽어들이는 블록 수까지 더해진다.

[SQL Trace 결과]
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.010        0.004          0          0          0          0
Execute      1    0.050        0.042          0          0          0          0
Fetch        3    0.620        0.638          5       6683          0         20
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        5    0.680        0.684          5       6683          0         20

[AutoTrace 결과]
Statistics
----------------------------------------------------------
          5  recursive calls
         30  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        680  bytes sent via SQL*Net to client
        625  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

2) Current 모드 읽기(gets in current mode)

• SQL문이 시작된 시점이 아니라 데이터를 찾아간 바로 그 시점의 최종 값을 읽으려고 블록을 액세스하는 것을 말한다.
• 블록 SCN이 쿼리 SCN보다 높고 낮음을 따지지 않으며, 그 시점에 이미 커밋된 값이라면 그대로 받아들이고 읽는다.(???)
• SQL trace의 'current', Autotrace에서의 'db block gets' : Current 모드에서 읽은 블록 수
• DML문을 수행할 때 주로 나타난다.
• select for update 문을 수행할 때도 Current 모드 읽기를 발견할 수 있다.
• 8i 이전 버전에서는 Full 스캔을 포함하는 select문에서도 Current 모드 읽기가 나타났다.
  (세그먼트 헤더의 익스텐트에 대한 현재 시점 정보 필요. Locally Managed 테이블스페이스에서는 발생하지 않는다.)
• 디스크 소트가 필요할 정도로 대량의 데이터를 정렬할 때도 Current 모드 읽기가 나타난다.

(2) Consistent 모드로 갱신할 때 생기는 현상

<상황1>

• TX2 update는 t2 시점에 시작하지만 TX1에 의해 걸린 Lock을 대기하다가 t3시점에 TX1이 커밋된 후에 진행을 계속한다.
• 두 개의 update문이 Consistent 모드로 값을 읽고 갱신했다면 t1, t2 시점에 SAL 값은 1,000 이었으므로 둘다 1,000을 읽고 각각 100, 200을 갱신한다.
• 최종값은 1,200이 될것이며, Lost Update가 발생하는 결과를 초래한다.
• Lost Update 문제를 회피하려면 갱신 작업만큼은 Current모드를 사용해야 한다.
• TX2 update는 Exclusive Lock 때문에 대기했다가 TX1 트랜잭션이 커밋한 후 Current 모드로 그 값을 읽어 진행을 계속한다.

(3) Current 모드로 갱신할 때 생기는 현상

<상황2>

• Current 모드로 처리 했다면, TX2 트랜잭션은 TX1 트랜잭션이 커밋되기를 기다렸다가 SAL 값이 2,000으로 갱신되는 것을 확인하고 정상적으로 update 수행.
• 항상 Current 모드로만 작동하는 Sybase, SQL Server 같은 DBMNS에서 수행해 보면 실제 위와 같은 결과(3,000)가 나온다.
• 오라클에서는 TX2의 갱신이 실패하므로 최종 값은 2,000이 된다.

<상황3>

• TX1이 1~100,000까지의 Unique한 번호를 가진 테이블에서 no>50000 조건에 해당하는 50,000개 레코드에 대해 인덱스를 경유해 순차적으로 갱신 작업을
  진행하고 있다고 하자. 그런데 도중에 TX2 트랜젝션에서 no값이 100,001인 레코드를 새로 추가하면 update 되는 최종 결과건수는? => 50,001건(MSSQL)
• 인덱스 경우가 아닌 Full Table Scan 방식으로 진행되었다면 insert되는 위치에 따라 결과 건수가 달라진다.
• 다른예로 "delete from 로그" 문장이 수행되는 도중에 다른 트랜잭션에 의해 새로 추가된 로그 데이터까지 지워질 수도 있다.
• 오라클에서는 TX2가 레코드를 insert하는 위치에 상관없이 TX1은 항상 50,000건만 갱신된다.

(4) Consistent 모드로 읽고, Current 모드로 갱신할 때 생기는 현상

• 오라클은 Consistent 모드로 읽고, Current 모드로 갱신한다.
  {info}
  오라클에서 update문을 수행하면, 대상 레코드를 읽을 때는 Consistent 모드로 읽고 실제 값을 변경할 때는 Current 모드로 읽는다. 따라서
  대상 레코드를 읽기 위한 블록 엑세스는 SQL 트레이스에서 query 항목으로 계산되고, 값을 변경하기 위한 블록 액세스는 current 항목에 계산된다.
  {info}

<상황4>

• TX2는 TX1이 커밋되기를 기다렸다가 TX1이 끝나면 계속 진행한다. 하지만 이미 값이 바뀐 상태이므로 TX2의 update는 실패하게 된다.
• 오라클 외 다른 DBMS는 항상 Current 모드 읽기만 지원하기 때문에 헷갈릴 이유가 없다.
• 위의 설명대로라면 TX2가 실제 값을 갱신할 때는 이미 1,100으로 바뀐 값을 읽겠지만 갱신 대상 레코드를 찾아갈 때는
  Consistent 모드를 사용하기 때문에 update에 실패할 이유가 없다고 생각한다.
• 하지만 다른 DBMS와 마찬가지로 오라클에서도 TX2 갱신은 실패한다.

(5) Consistent 모드로 갱신대상을 식별하고, Current 모드로 갱신

• 이해를 돕기 위해 pseudo코드로 표현하면

for c in
 ( select rowid rid from emp where empno = 7788 and sal = 1000 ) <- Consistent	
loop
    update emp set sal = sal + 200 where empno = 7788 and sal = 1000 and rowid = c.rid; <- Current
end loop;

• Consistent 모드에서 수행한 조건 필터링을 Current 모드로 액세스 하는 시점에 한 번 더 수행한다.

단계1] where절에 기술된 조건에 따라 수정/삭제할 대상 레코드의 rowid를 Consistent 모드로 찾는다 (DML 문이 시작된 시점 기준)

단계2] 앞에서 읽은 rowid가 가리키는 레코드를 찾아가 로우 Lock을 설정한 후에 Current 모드로 실제 update/delete를 수행한다.(값이 변경되는 시점 기준)
이 단계에서 Current 모드로 다시 한번 조건을 필터링하고, 갱신할 값을 읽어 수정/삭제한다.

* 단계1을 수행해 대상건을 '모두' 추출하고 나서 단계2를 수행하는것이 아니라, 단계1에서 커서를 열어 Fetch하면서 단계2를 건건히 반복 수행한다.

• 단계1은 update/delete가 시작된 시점 기준으로 수정/삭제할 대상을 식별하려고 Consistent 모드 읽기를 사용할 뿐 단계2에서 갱신하는데 사용하지 않는다.
• 단계1이 필요한 이유는 갱신이 진행되는 동안 추가되거나 변경을 통해 범위 안에 새로 들어오는 레코드를 제외하고자 하는 것이다.
• 이미 범위 안에 포함돼 있던 레코드는, 단계2에서 변경이 이루어지는 바로 그 시점 기준으로 값을 읽고 갱신한다.
• 이때는 블록 SCN이 쿼리 SCN보다 높고 낮음을 따지지 않으며, 그 시점에 이미 커밋된 값이라면 그대로 받아들이고 읽는다.

(6) 오라클에서 일관성 없게 값을 갱신하는 사례

update	계좌2
set	총잔고 = 계좌2.잔고 +
                (select 잔고 from 계좌1 where 계좌번호 = 계좌2.계좌번호)
where   계좌번호 = 7788;

update	계좌2
set	총잔고 = (select 계좌2.잔고 + 잔고 from 계좌1 where 계좌번호 = 계좌2.계좌번호)
where   계좌번호 = 7788;

• 스칼라 서브쿼리는 특벽한 이유가 없는 한 항상 Consistent 모드로 읽기를 수행한다.
• 첫번째 문장에서
  계좌2.잔고는 Current 모드로 읽는 반면 계좌1.잔고는 Consistent 모드로 읽는다.
  위 update 문장이 진행되는 도중에 계좌1에서 변경이 발생했더라고 update문이 시작되는 시점의 값을 찾아 읽고,
  delete가 발생했더라도 지워지기 이전 값을 찾아 읽는다.
• 두번째 문장은
  Current모드로 읽어야 할 계좌2의 잔고 값을 스칼라 서브쿼리내에서 참조하기 때문에 스칼라 서브쿼리까지도 Current모드로 작동하게 된다.
  위 update 문장이 진행되는 도중에 계좌1에서 변경이 발생했다면 그 새로운 값을 읽고, delete가 발생했다면 조인에 실패해 NULL값으로 update 될것이다.

create table 계좌1
nologging
as
select empno 계좌번호, ename 계좌명, 1000 잔고 from emp;

create table 계좌2
nologging
as
select empno 계좌번호, ename 계좌명, 1000 잔고, 2000 총잔고 from emp;

alter table 계좌1 add constraint 계좌1_pk primary key(계좌번호);

alter table 계좌2 add constraint 계좌2_pk primary key(계좌번호);

select  계좌1.잔고, 계좌2.잔고, 계좌2.총잔고
       ,계좌1.잔고+계좌2.잔고 총잔고2
from    계좌1, 계좌2
where   계좌1.계좌번호 = 7788
and     계좌2.계좌번호 = 계좌1.계좌번호;

      잔고       잔고     총잔고    총잔고2
---------- ---------- ---------- ----------
      1000       1000       2000       2000


---------------------- ex 1) ---------------------------------
TX1> 
update 계좌1 set 잔고 = 잔고 + 100 where 계좌번호 = 7788;

update 계좌2 set 잔고 = 잔고 + 200 where 계좌번호 = 7788;

TX2>
update	계좌2
set	총잔고 = 계좌2.잔고 +
                (select 잔고 from 계좌1 where 계좌번호 = 계좌2.계좌번호)
where   계좌번호 = 7788;

TX1>
commit;

TX2>
commit;

TX1>
select  계좌1.잔고, 계좌2.잔고, 계좌2.총잔고
       ,계좌1.잔고+계좌2.잔고 총잔고2
from    계좌1, 계좌2
where   계좌1.계좌번호 = 7788
and     계좌2.계좌번호 = 계좌1.계좌번호;

      잔고       잔고     총잔고    총잔고2
---------- ---------- ---------- ----------
      1100       1200       2200       2300

---------------------- ex 2) ---------------------------------
TX1> 
update 계좌1 set 잔고 = 잔고 + 100 where 계좌번호 = 7788;

update 계좌2 set 잔고 = 잔고 + 200 where 계좌번호 = 7788;

TX2>
update	계좌2
set	총잔고 = (select 계좌2.잔고 + 잔고 from 계좌1 where 계좌번호 = 계좌2.계좌번호)
where   계좌번호 = 7788;

TX1>
commit;

TX2>
commit;

TX1>
select  계좌1.잔고, 계좌2.잔고, 계좌2.총잔고
       ,계좌1.잔고+계좌2.잔고 총잔고2
from    계좌1, 계좌2
where   계좌1.계좌번호 = 7788
and     계좌2.계좌번호 = 계좌1.계좌번호;

      잔고       잔고     총잔고    총잔고2
---------- ---------- ---------- ----------
      1100       1200       2300       2300

문서에 대하여

• 최초작성자 : 김종원
• 최초작성일 : 2009년 10월 23일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*