[Oracle] Consistent vs Current 읽기

1. Consistent mode 읽기 

• Consistent Mode 읽기 ( gets in consistent mode)는 SCN 확인 과정을 거치며 쿼리가 시작된 시점을 기준으로 일관성 있는 상태로 블록을 엑세스하는 것을 말한다. (항상 쿼리가 시작된 시점의 데이터를 가져온다.) 
• SQL trace call 통계에서 볼 수 있는 'query' 항목과 Auto Trace에서의 'consistent gets' 항목이 지금 설명한 consistent mode에서 읽은 블록 수를 의미한다.
• CR copy를 생성할 필요가 없어 Current Block을 읽더라도 Consistent mode 에서 읽었다면 'query' 항목으로 집계된다. 
• select문에서 읽은 블록은 대부분 여기에 해당하며, 여기에는 CR Block을 생성하려고 Undo Segment로부터 읽어 들이는 블록수까지 더해진다. 

SQL Trace

 

Auto Trace

2. Current Mode 읽기 

• SQL문이 시작된 시점이 아니라 데이터를 찾아간 바로 그 시점의 최종 값을 읽으려고 블록을 엑세스하는것을 말한다.
• DML문을 수행할 때 주로 나타난다.
• select for update 문을 수행할 떄도 Current 모드 읽기를 발견할 수 있다.
• 블록 SCN이 쿼리 SCN보다 높고 낮음을 따지지 않으며 그 시점에 이미 커밋된 값이라면 그대로 받아들이고 읽는다.
• 9i 이상부터는 Full Table Scan을 하더라도 Current 모드 읽기가 발생하지 않는다.
• Index rowid를 이용한 테이블액세스 시에는 테이블 익스텐트 정보가 불필요하므로 버전에 상관없이 Current 모드 읽기가  발생하지 않는다. 
• Disk Sort가 필요할 정도로 대량의 데이터를 정렬할 때도 Current 모드 읽기가 나타난다.  

 

 

3. Consistent Mode로 갱신할 때 생기는 현상 

EMP 테이블 7788번 사원의 SAL 값이 현재 1000인 상황에서 아래 TX, TX2 두 개의 트랜잭션이 동시에 수행되었다.

양쪽 트랜잭션이 모두 완료된 시점에 7788번 사원의 SAL 값은 얼마여야 할까?

 

TX1에서 실행된 트랜잭션이 아직 끝나지 않았으므로, t2 시점에 TX2에서 Update를 날린 후 Lock 대기한다. 

Consistent Mode 이므로 쿼리가 시작된 시점의 emp 값을 가지고 각자 100, 200 을 더한다. 

최종값은 1200이 되고 TX1의 처리 결과는 사라지는 LOST UPDATE 가 발생한다. 

 

4. Current Mode 로 갱신할 때 생기는 현상 

 

데이터를 찾아간 그 시점의 데이터를 확인 하는 Current Mode로 처리한다면 최종값은 3000 이 된다. 

( 항상 Current 모드로만 작동하는 Sysbase, SQL Sever 는 3000이 나올 것이다.) 

 

 

TX1 : 1~100000까지의 Unique한 번호(no)를 가진 테이블에서 no > 50000 조건에 해당하는 50000개 레코드에 대해 인덱스를 경유해 순차적으로 갱신 작업을 진행중 

 

TX2 : 중간에 한건의 row insert 하고 commit 

 

실제 항상 Current 모드를 사용하는 SQL Server에서 테스트를 해보면, 50001건이 갱신이 된다. 즉 update가 진행되는 동안 새로 추가된 레코드까지도 값이 변경된 것이다. 

 

만약, 인덱스를 이용해 no 값을 순차적으로 읽지 않고 Full Table Scan 방식으로 Update를 진행한다면, Insert가 되는 100,001번째 레코드가 삽입되는 위치에 따라 update 건수가 100000 일수도 있고 100001일수도 있다. ( 한꺼번의 여러 블록을 읽어 오므로 ) 

 

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

Current Mode 의 갱신 문제를 피하려고 오라클은 Consistnet 모드로 읽고, Current 모드로 갱신한다. 

 

오라클에서 Update문을 수행하면, 대상 레코드를 읽을 때는 Consistent Mode로 읽고 실제 값을 변경할 떄는 Current모드로 읽는다. 따라서 대상 레코드를 읽기 위한 block access 는 SQL trace에서 query 항목으로 계산되고, 값을 변경하기 위한 

block access는 current 항목에 계산된다. 

 

 

TX2는 TX1이 커밋되기를 기다렸다가 TX1이 끝나면 계속 진행한다. 하지만 이때 7788번 사원의 값은 1100으로 바뀐 상태이므로 TX2는 실패하게 된다. 

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

오라클은 어떻게 두 개의 읽기 모드가 공존하면서 update를 처리하는 걸까?

1. select 는 Consistent 모드로 읽는다.

2. insert, update, delete, merge는 Current 모드로 읽고 쓴다. 다만, 갱산할 대상 레코드를 식별하는 작업만큼은 Consistent 모드로 이루어 진다. 

 

앞선 사례(상황 4번)에서, Consistent 모드와 Current 모드에서 읽은 값이 서로 달라 TX2의 update는 실패했다. TX2가 update를 시작한 t2 시점 기준으로는 empno =7788 사원 레코드가 분명히 갱신 대상이였는데, 값이 달라졌다고 아무런 처리 없이 지나가도 상관없는 걸까? 그렇지 않다. 데이터 정합성에 문제가 생기는 경우가 있고, 이를 방지하려고 오라클은 'Restart 매커니즘'을 사용한다. 그때까지의 갱신을 롤백하고 update를 처음부터 다시 실행하는 것이며,

Thomas Kyte는 그의 저서에서 이것을 'Write Consistency'라고 명명하고 있다. 

 

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

일관성 없는 상태로 값이 갱신되는 오류(주로 사용자 정의 함수/프로시저, 트리거등을 사용할 때 발생) 

1. 첫번째 쿼리 

• 스칼라 서브쿼리는 특별한 이유가 없는 한 항상 Consistent 모드로 읽기를 수행한다.
• 첫 번째 문장에서 계좌2 잔고는 Current 모드로 읽는 반면 계좌1 잔고는 Consistent 모드로 읽는다. 
• 계좌 1에서 변경이 발생했더라도 쿼리가 시작했을 당시의 값을 사용한다. 

2. 두번째 쿼리

• 두번째 문장은, Current 모드로 읽어야 할 계좌2의 잔고 값을 스칼라 서브쿼리 내에서 참조하기 때문에 스칼라 서브쿼리 까지도 Current 모드로 작동한다. 
• update 문장이 진행되는 도중에 계좌1에서 변경이 발생했다면 그 새로운 값을 읽고, 해당 row에 delete가 발생했다면, 조인에 실패해 NULL 값으로 update 될 것 이다. 

 

*** update 문에 수행되는 동안 두 테이블로 부터 잔고를 변경하는 트랜잭션이 동시에 진행될 수 있는 상황이라면, 업무 특

     성에 맞게 SQL문을 작성해야 한다. ( 해당 쿼리중 업무 특성에 맞게 골라 사용해야 한다.) 

 

참조 : http://wiki.gurubee.net/pages/viewpage.action?pageId=29786169