메뉴 건너뛰기

bysql.net

7._Consistent_vs._Current_모드_읽기

2012.03.18 09:17

시와처 조회 수:5708

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



Consistent Mode Read

Current Mode Read

 정의

SCN 확인후 Query 시작 시점을 기준으로 일관성

있는 Data Read

즉 Query 시작 시점의 Data를 읽어 오는것

Data를 찾아간 시점의 최종값을 읽어 온다

항상 Current Block 를 읽는다.

SqlTrace

통계항목

 Query 항목

※ Select 문에 의한 Block 수 +

  CR Block 생성을 위한 Undo Segment Block

Current 항목

 AutoTrace

통계항목

 Consistent gets

db block gets

 발생 Query

 Select 문

DML 문 사용시 주로 발생

select for update

8i에서의 full table scan

disk sort가 필요한 위한 대량 sort


※ Consistent Mode로 읽기 시작한 경우 CR Block 를 생성할 필요가 없어 Current Block를 읽더라도 통계항목은 Consistent 의 항목으로 수집 된다. 


[SQL_TRACE]

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      149      0.61       0.75          0       7372          0        2211
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      151      0.62       0.80          0       7372          0        2211



[AUTO_TRACE]
Statistics
----------------------------------------------------------
      56839  recursive calls
          2  db block gets
      45992  consistent gets
      19750  physical reads
          0  redo size
       2837  bytes sent via SQL*Net to client
        708  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
        101  sorts (memory)
          0  sorts (disk)
         60  rows processed
 

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


    - <상황1> 현재 emp 테이블 empno 7788의 sal = 1000

 TX1

 

 TX2

update emp set sal = sal + 100

 where empno = 7788;

T1

 

 

T2

update emp set sal=sal+200

 where empno=7788;

 commit;

T3

 

 

T4

Commit;


  • 위의 두개의 Transaction 수행 후 empno=7788의 sal 값은 1200 or 1300 ?
  • Consistent Mode로 생각한다면 답은 1200 : Consistent Mode로 읽는 다면 읽기 시작한 시점의 TX1 과 TX2의 sal 값은 모두 1000이다 그러므노 마지막에 수행된 tx2에 의해 1200 이 된다.
  • Consisten Mode로 읽어 Transaction을 처리한경우 TX1의 Transaction은 실종되는 현상이 발생 한다.


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

  

    - <상황2> 현재 emp 테이블 empno 7788의 sal = 1000

 TX1

 

 TX2

update emp set sal = 2000

 where empno = 7788

   and sal=1000;

T1

 

 

T2

update emp set sal=3000

 where empno=7788

   and sal=2000;

Commit;

T3

 

 

T4

Commit;


  •   Current Mode로 읽어 처리 된경우 TX2 가 읽는 시점에는 TX1에 의해 Current Block sal=2000 이 되어 있으므로 결과는 sal=3000 이 된다.
  •  Sybase, SQL server 과 같이 항상 current 모드로 적용 되는 DBMS에서는 항상 3000 이 된다.


   - <상황3> 테이블 t는 1~100,000 까지의 Unique한 no를 가진 테이블

 TX1

 

 TX2

update t set no = no + 1 where no > 50000;

T1

 

 

T2

insert into t values(100001, 100001);

 

T3

 commit;

 commit;

T4

 


  • TX1에 의해 갱신되어야 할 대상에 TX2의 data 포함 ???
  • 실제로 MSSQL에서는 index를 통해 순차적으로 읽지 않는 다면 txt2의 데이터를 포함한 50001개의 데이터가 갱신 되는현상을 볼수 있다.
  • DATA의 삽입 위치에 따라 갱신 대상이 변경 된다는 의미이다.


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


  • 오라클 에서 Update문을 수행하면 대상 레코드를 Consistent 모드로 읽고 실제 값을 변경할 때는 Current 모드로 읽어 처리
  • SQL Trace 에서 대상 레코드를 읽을 때는 query 항목으로 변경 블록의 액세스는 current 항목으로 계산된다.



    - <상황4>  현재 emp 테이블 empno 7788의 sal = 1000

 TX1

 

 TX2

update emp set sal = sal + 100

 where empno = 7788

   and sal=1000;

T1

 

 

T2

update emp set sal= sal + 200

 where empno=7788

   and sal=1000;

Commit;

T3

 

 

T4

Commit;


  • TX2 시점의 Sal 값은 current 모드로 읽는 경우 1100 이므로 TX2 는 실패하게 된다.
  • 오라클의 원리이 대상을 추출 할때 Consistent Mode로 읽는 다면 TX2의 실패를 이해하기 힘들다.


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


  • 4절의 내용을 정확하게 pseudo 코드로 설명하면 다음과 같다.

for c in

{  select rowid rid from emp  /* Consistent 모드 읽기 단계1) */

   where empno = 7788 and sal = 1000

}

loop

   update emp set sal = sal + 200    /* Current 모드 읽기 단계2) */

     where empno = 7788 and sal = 1000

        and rowid = c.rid

     ;

end loop


  • 단계1) rowid를 찾는 과정에서는 consistent mode로 찾는다.
  • 단계2) Consistent mode의 rowid 해당 하는 데이터에 lock를 설정한후 current 모드로 update/delete 대상을 읽어 DML 처리 한다.


  • select 는 Consistent 모드로 처리한다.
  • insert,update,delete,mergem는 current 모드로 일고 쓴다. 다만 갱신대상 레코드 추출 단계에서는 Consistent 모드로 이뤄 진다.




위의 요약에 따른 <상황2>, <상황3> 에 대한 처리 절차


    - <상황2> 현재 emp 테이블 empno 7788의 sal = 1000

 TX1

 

 TX2

update emp set sal = 2000

 where empno = 7788

   and sal=1000;

T1

 

 

T2

update emp set sal=3000

 where empno=7788

   and sal=2000;

 commit;

T3

 

 

T4

Commit;


  • SQL Server => empno = 7788 최종 sal = 3000
  • Oracle => TX2 처리 대상 추출 불가로 empno = 7788 최종 sal = 2000
  • T2 시점에 TX1의 transaction이 commit 되지 않은 상태 이므로 대상을 추출할때의 Consistent 모드에서 제외된다.


   - <상황3> 테이블 t는 1~100,000 까지의 Unique한 no를 가진 테이블

 TX1

 

 TX2

update t set no = no + 1 where no > 50000;

T1

 

 

T2

insert into t values(100001, 100001);

 

T3

 commit;

 commit;

T4

 


  • 오라클에서는 TX2의 삽입 위치와 상관없이 TX1의 갱신 건수는 항상 50000건
  • TX1의 대상 추출 당시 Consistent 모드에 의해 TX2의 대상이 제외된다.


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


Query 1

update 계좌2

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

 where 계좌번호 = 7788;


Query 2

update 계좌2

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

 where 계좌번호 = 7788;


  • 스칼라 서브 쿼리는 이유가 없는 한 항상 Consistent 모드 읽기 수행
  • Query1의 계좌2.잔고는 current 모드로 읽는 반면
  • Query2의 계좌2.잔고는 Consistent 모드로 읽는다.


 오후 7:44:50 SQL> create table 계좌1 (계좌번호 number,계좌명 varchar2(10), 잔고 number);
 
Table created
 
오후 7:44:56 SQL> create table 계좌2 (계좌번호 number,계좌명 varchar2(10), 잔고 number,총잔고 number);
 
Table created
 
오후 7:45:18 SQL> alter table 계좌1 add primary key (계좌번호);
 
Table altered
 
오후 7:45:30 SQL> alter table 계좌2 add primary key (계좌번호);
 
Table altered
 
오후 7:45:34 SQL> insert into 계좌1 select no,'계좌명' || no,1000 from (select rownum no  from dual connect by level < 100);
 
99 rows inserted
 
오후 7:46:14 SQL> insert into 계좌2 select no,'계좌명' || no,1000,2000 from (select rownum no  from dual connect by level < 100);
 
99 rows inserted
 
오후 7:46:22 SQL> commit;
 
Commit complete
 
오후 7:48:50 SQL> select 계좌1.잔고, 계좌2.잔고,계좌2.총잔고,계좌1.잔고 + 계좌2.잔고 총잔고2
             2      from 계좌1, 계좌2
             3     where 계좌1.계좌번호 = 10
             4       and 계좌2.계좌번호 = 계좌1.계좌번호
             5  ;
 
     잔고        잔고     총잔고    총잔고2
---------- ---------- ---------- ----------
      1000       1000       2000       2000


TX 1  

오후 7:48:53 SQL> update 계좌1 set 잔고 = 잔고 + 100 where 계좌번호 = 10;
 
1 row updated
 
오후 7:49:51 SQL> update 계좌2 set 잔고 = 잔고 + 200 where 계좌번호 = 10;
 
1 row updated
 

 TX 2

오후 7:50:34 SQL> update 계좌2 set 총잔고 = 계좌2.잔고 + (select 잔고 from 계좌1 where 계좌번호 = 계좌2.계좌번호) where 계좌번호 = 10;
 
1 row updated

 TX 1

오후 7:50:07 SQL> commit;
 
Commit complete

 TX 2

 오후 7:51:36 SQL> commit;
 
Commit complete



오후 7:52:00 SQL> select 계좌1.잔고, 계좌2.잔고,계좌2.총잔고,계좌1.잔고 + 계좌2.잔고 총잔고2
             2      from 계좌1, 계좌2
             3     where 계좌1.계좌번호 = 10
             4       and 계좌2.계좌번호 = 계좌1.계좌번호
             5  ;
 
 잔고        잔고        총잔고      총잔고2
---------- ---------- ---------- ----------
      1100       1200       2200       2300
 


이러한 오류를 해결 하기 위하여 다음과 같이 주의 하여 Query를 작성해야 한다.

 오후 8:01:18 SQL> select 계좌1.잔고, 계좌2.잔고,계좌2.총잔고,계좌1.잔고 + 계좌2.잔고 총잔고2
             2    from 계좌1, 계좌2
             3   where 계좌1.계좌번호 = 20
             4     and 계좌2.계좌번호 = 계좌1.계좌번호
             5  ;
 
     잔고       잔고      총잔고     총잔고2
---------- ---------- ---------- ----------
      1000       1000       2000       2000



 TX 1

오후 8:01:18 SQL> update 계좌1 set 잔고 = 잔고 + 100 where 계좌번호 = 20;
 
1 row updated
오후 8:01:18 SQL> update 계좌2 set 잔고 = 잔고 + 200 where 계좌번호 = 20;
 
1 row updated

 TX 2

오후 8:01:28 SQL> update 계좌2

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

where 계좌번호 = 20;
 
1 row updated

 TX 3

오후 8:01:36 SQL> commit;
 
Commit complete

 TX 2

오후 8:01:40 SQL> commit;
 
Commit complete


  • TX2 의 계좌1.잔고와 계좌2.잔고를 모두 Current 모드로 읽었기 때문에 일관성 있게 갱신됨

 오후 8:01:49 SQL> select 계좌1.잔고, 계좌2.잔고,계좌2.총잔고,계좌1.잔고 + 계좌2.잔고 총잔고2
             2    from 계좌1, 계좌2
             3   where 계좌1.계좌번호 = 20
             4     and 계좌2.계좌번호 = 계좌1.계좌번호
             5  ;
 
     잔고       잔고      총잔고    총잔고2
---------- ---------- ---------- ----------
      1100       1200       2300       2300



이 게시물을
목록
목록
번호 제목 글쓴이 날짜 조회 수
26 3._SQL트레이스 sapius 2012.04.03 37300
25 8._Statspack_AWR 시와처 2012.04.01 11768
24 7._Response_Time_Analysis_방법론과_OWI file 시와처 2012.04.01 5916
23 6._V$SYSTEM_EVENT 시와처 2012.04.01 3507
22 4._동시성_구현_사례 [1] dasini 2012.03.26 11962
21 5._오라클_Lock file 시와처 2012.03.25 12875
20 3._비관적_vs._낙관적_동시성_제어 dasini 2012.03.25 6396
19 2._AutoTrace 남송휘 2012.03.25 3138
18 1._Explain_Plan 남송휘 2012.03.25 4543
17 3장._오라클_성능_관리 남송휘 2012.03.25 3117
16 2._트랜잭션_수준_읽기_일관성 file AskZZang 2012.03.20 6364
15 1._트랜잭션_동시성_제어 AskZZang 2012.03.19 4883
14 11._Shared_Pool 박영창 2012.03.19 3395
13 10._대기_이벤트 박영창 2012.03.18 10624
12 9._Snapshot_too_old 박영창 2012.03.18 9969
11 8._블록_클린아웃 시와처 2012.03.18 12305
» 7._Consistent_vs._Current_모드_읽기 file 시와처 2012.03.18 5708
9 2장._트랜잭션과_Lock AskZZang 2012.03.16 5462
8 6._문장수준_읽기_일관성 file 정찬호 2012.03.11 57563
7 4._Redo file 남송휘 2012.03.11 5606
쓰기
태그