CH1.오라클 아키텍처 - 6.문장수준 읽기 일관성 7.Consistent vs. Current 모드 읽기

6.문장수준 읽기 일관성

SQL문이 수행되는 도중에 다른 트랜잭션에 의해 데이터가 변경,추가,삭제가 된다면 일관성 없는 결과집합을 리턴하거나 값을 잘못갱신하는 문제가 발생할 수 있습니다. 이런 문제를 방지하기 위해 사용하는 ‘문장 읽기 일관성’에 대해서 살펴보겠습니다.

(1)문장수준 읽기 일관성이란?
단일 SQL문이 수행되는 도중에 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 일관성 있는 결과집합을 리턴하는 것을 말합니다.
오라클을 제외한 다른 DBMS는 로우 lock을 사용해 dirty read를 방지합니다. 읽기 작업에 대해서는 shared lock을 사용하여 exclusive lock이 걸린 로우를 읽지 못하도록 합니다. 하지만 이것만으로는 문장수준의 읽기 일관성이 완벽하게 보장되지 않습니다. 오라클은 shared lock을 사용하지 않고 Undo 세그먼트에 저장해 둔 undo데이터를 활용하므로 완벽한 문장수준 읽기 일관성을 보장합니다.
(다른 dbms의 경우 기본 트랜잭션 고립화 수준을 높이거나 테이블 lock을 사용해서 문장수준 읽기 일관성을 보장하게 할 수 있습니다. 하지만 동시성이 급하게 떨어지게 됩니다)

(2)Consistent 모드 블록 읽기

 

위의 그림처럼 오라클은 쿼리가 시작된 시점을 기준으로 데이터를 읽어 들입니다 쿼리가 시작되기 전에 커밋된 데이터만 읽고 쿼리 시작 이후에 커밋된 변경사항들을 읽어들이지 않습니다. 변경이 발생한 블록을 읽을 때는 현재의 Current블록으로부터 CR블록을 생성해서 쿼리가 시작된 시점으로 되돌린 후 그것을 읽습니다.
Current 블록은 디스크로부터 읽혀진 후 사용자의 갱신사함이 반영된 최종상태의 원본 블록이며, CR블록은 current블록에 복사본입니다. CR블록은 여러개 존재할 수 있지만 current블록은 하나뿐입니다. 이러한 독특한 매커니즘을 다중 버전 읽기 일관성 모델(multi -version read consistency model)이라고 합니다.
[RAC상태에서의 current 블록?
rac환경이라면 share모드의 current블록이 여러 노드에 동시에 캐싱되어 있을 수 있습니다. 하지만 share모드와 달리 변경을 위해 사용하는 exclusive 모드 current블록은 오직 한 노드에만 존재할 수 있습니다. share모드의 current블록이 여러 노드에 공유된 상태에서 특정 노드가 exclusive모드로 업그레이드 하면 나머지 노드에 캐싱된 current블록은 전부 null모드로 다운그레이드되고 그 블록을 읽을때는 디스크로부터 블록을 다시 읽어야 합니다. 따라서 rac환경에ㅔ서도 current블록 SCN이 쿼리 SCN보다 작으면 쿼리가 시작된 이후에 변경이 없었던 블록임을 보장받을 수 있습니다.]

쿼리가 시작된 시점을 기준으로 데이터를 읽는다고 했는데, 때로는 시작 시점이 아닌 데이터를 찾아간 바로 그 시점의 최종 값을 읽어야 할 때도 있습니다. 전자를 Consistent 모드 읽기, 후자를 current 모드 읽기라고 합니다.
오라클은 scn이라는 시간정보를 이용해 데이터베이스의 일관성 있는 상태를 식별하는데 이는 시스템 전반적으로 공유되는 global변수입니다. 이값을 기본적으로 사용자가 커밋을 할때마다 1씩 증가하고 커밋이 없더라고 오라클 백그라운드 프로세스에 의해 조금씩 증가합니다.
scn은 읽기 일관성과 동시성 제어를 위해 사용되고, 생성된 redo로그 정보의 순서를 식별하는데도 사용되며 데이터 복구를 위해서도 사용됩니다.
오라클은 블록이 마지막으로 변경된 시점 정보를 식별하기 위해 모든 블록 헤더에 scn정보를 관리하는데 이를 블록 scn이라고 합니다. 앞절의 커밋 scn과 별도로 관리됩니다.

(3) Consistent모드 블록 읽기의 세부원리
오라클에서 수행되는 모든 쿼리는 global변수인 scn값을 먼저 확인하고 나서 읽기 작업을 시작하는데 이를 쿼리 scn 또는 스냅샷 scn이라고 합니다.
쿼리 scn을 들고 다니며 읽는 블록마다 블록 scn과 비교해 읽을 수 있는 버전인지를 판단하는 것입니다.
consistent모드로 읽을때 블록 상태에 따라 어떻게 일관성을 유지하면서 데이터를 읽는지 3가지의 경우로 살펴보겠습니다.

A. Current 블록 SCN <= 쿼리 SCN 이고 committed 상태
consistent모드에서는 블록 scn이 쿼리 scn보다 작거나 같은 블록만 읽을 수 있습니다. 이 경우네는 쿼리가 시작된 이후에 해당 블록에 변경이 가해지지 않았습니다. CR블록을 생성하지 않고 current 블록을 그대로 읽으면 됩니다.

B. Current 블록 SCN > 쿼리 SCN이고 committed 상태
current 블록이 committed상태이지만 블록 SCN이 쿼리 SCN보다 크다면 쿼리가 시작된 이후 해당 블록에 변경이 가해지고 커밋이 일어났다는 뜻입니다.
블록 원본에 해당하는 current블록 scn이 쿼리 scn보다 크면 블록 복사본(cr블록)을 먼저 생성합니다.(cr cloning) cr블록을 생성하여 과거상태로 되돌릴 때 사용하는 정보가 undo 정보이며, ITL 슬롯에서 uba가 가리키는 undo 블록을 찾아가 변경 이전의 값을 읽습니다.
cr블록으로 한단계 이전 상태로 돌렸는데 거기에 커밋되지 않은 변경사항이 포함되어 있거나 여전히 블록 scn이 높다면 다시 ITL슬롯에 있는 uba가 가리키는 undo 레코드를 찾아 블록을 이전상태로 되돌리는 작업을 계속합니다.(언제까지? 블록 scn이 쿼리 scn보다 작거나 같으면서 커밋되지 않은 내용은 전혀 포함하지 않은 상태가 될 때까지!) uba을 통해 계속적인 롤백이 가능한 것은 ITL에 대한 변경내역까지도 undo 레코드에 기록되기 때문입니다. 같은 레코드를 계속 변경할 때 그 레코드를 조회하는 다른 세션에서의 CR블록 읽기 횟수도 계속 증가하는 것을 확인할 수 있습니다(statistics의 consistent gets 항목이 계속 증가하는 것으로 확인 할 수 있습니다) 10g부터 사용하는 IMU(in-memory undo)기능이 작동하면 cr블록 생성을 위해 undo를 참조하지 않고 shared pool 내의 IMU Pool에 저장된 값을 이용하므로 uppdate를 일정 횟수 반복하기 전까지(IMU pool이 가득찰 정도의 횟수)consistent gets가 증가하지 않습니다.
이기능을 사용하면 shared pool 내의 IMU Pool에 undo 데이터를 생성합니다. IMU pool이 다차면 그떄까지 저장해 둔 undo 데이터를 undo 세그먼트로 일괄 기록하며(IMU flush),이후 계속 발생하는 undo데이터는 undo 세그먼트에 저장합니다. 작은 트랜잭션을 위해 고안된 기능이며 이 기능을 통해 undo 세그먼트는 헤더 블록과 undo 세그먼트 블록 버퍼에 대한 래치 경합 및 pinning을 줄일 수 있습니다.
cr블록을 과거 상태로 되돌리는 과정에서 필요한 undo 정보가 덮어 쓰여져 계속 롤백을 할 수 없을때 snapshot too old 에러가 발생합니다.

C. Current 블록이 Active 상태, 즉 갱신이 진행 중인 것으로 표시 되어 있을때
읽으려는 레코드에 lock byte가 설정되어 있는데 ITL에 아직 커밋 정보가 기록되지 않았다면 현재 갱신이 진행중인 것으로 인식할 수 있습니다. 하지만 오라클은 커밋 시 항상 곧바로 블록 클린아웃하지 않기 때문에 ITL상태만 보고 갱신이 진행 중이라고 단정할수 없습니다. 따라서 active 상태의 블록일 때는 일단 트랜잭션 테이블로부터 커밋정보를 가져와 블록 클린아웃을 시도해보고 그 결과 쿼리 SCN이전에 이미 커밋된 블록으로 확인된다면 A의 경우. 쿼리 SCN 이후에 커밋된 블록으로 확인되거나, 커밋되지 않아 아직 클린아웃할 수 없다면 B의 경우처럼 읽어야 합니다.

자주 갱신되는 특정 테이블에 조회까지 많이 발생하면 심한 경우 버퍼 캐시가 같은 블록에 대한 cr copy로 가득차는 일이 발생할수도 있습니다. 이를 방지하려고 오라클은 기본적으로 하나의 데이터 블록마다 6개까지만 cr copy를 허용하며 이를 제어하는 파라미터가 _db_block_max_cr_dba입니다. 그리고 cr copy는 lru리스트 상에서 항상 end쪽에 위치하기 때문에 free버퍼가 필요할 때 1순위로 밀려납니다.

7. Consistent vs. Current 모드 읽기

(1) 두개 모드 읽기의 차이점
Consistent모드 읽기는 scn확인 과정을 거치며 쿼리가 시작된 시점을 기준으로 일관성 있는 상태로 블록을 액세스하는 것을 말합니다. 이 모드로 데이터를 읽을 때는 쿼리가 얼마나 오래 걸리든 항상 쿼리가 시작된 시점의 데이터를 가져옵니다.
sql트레이스 call통계에서 볼수있는 query항목과 autotrace에서 consistent gets 항목이 consistent모드에서 읽은 블록 수를 의미합니다. select문에서 읽은 블록은 대부분 여기에 해당하며 여기에 cr블록을 생성하려고 undo 세그먼트로부터 읽어들이는 블록수까지 더해집니다.
Current모드 읽기는 SQL문이 시작된 시점이 아니라 데이터를 찾아간 바로 그 시점의 최종 값을 읽으려고 블록을 액세스하는 것을 말합니다. 블록 scn이 쿼리 scn보다 높고 낮음을 따지지 않고 그 시점에 이미 커밋된 값이라면 그대로 받아들이고 읽습니다.
sql트레이스 call통계에서 볼 수있는 current항목과 autotrace의 db block gets 항목이 current모드에서 읽은 블록 수를 의미합니다.
-DML문을 수행할 때 주로 나타나고 인덱스가 많을수록 더 많이 나타납니다.
- select for update문을 수행할 때도 current모드 읽기를 발견할 수 있습니다.
-8i 이전버전에는 full 스캔을 포함한 select문에서도 나타났지만, locally managed 테이블스페이스를 주로 사용하기 시작한 9i부터는 full table scan을 하더라도 발생하지 않습니다. index rowid를 이용한 테이블 액세스시에는 테이블 익스텐트 정보가 필요하지 않으므로 버전에 상관없이 발생하지 않습니다.
-디스크 소트가 필요할 정도로 대량의 데이터를 정렬할때도 current 모드 읽기가 나타납니다.

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

TX1	TX2
update emp set sal = sal +100 where empno = 7788;
	update emp set sal = sal + 200 where empno = 7788;
commit;
	commit;

TX2 update는 tx1에 의해 걸린 lock을 대기하다가 tx1의 commit이후에 진행을 계속합니다. sal의 원래 값이 1000이라고 할때 두개의 트랜잭션 후에 sal의 값은 1200 or 1300 어느것일까요. 답:1300
오라클 사용자들은 항상 consistent모드 읽기 중심으로 생각합니다. 따라서 consistent모드 읽기로 값을 갱신한다면 tx1의 update값은 유실될 것이고 최종값은 1200이 될 것입니다. (늘 쿼리가 시작된 시점의 값을 기준으로 읽기때문에 두개의 트랜잭션 다 1000을 기준으로 update를 진행하게 됩니다)
이런 lost update문제를 회피하려면 갱신작업만큼은 current모드를 사용해야 합니다. 위의 상황에서 tx2는 exclusive lock때문에 대기했다가 tx1트랜잭션이 커밋한 후 current모드로 그 값을 읽어 진행을 계속합니다.

(3) Current 모드로 갱신할 때 생기는 현상
아래의 예시는 오라클의 실제 처리 결과가 아니라 current모드로 처리했을때의 결과를 묻는 것입니다.
수행 전의 sal은 1000입니다.

TX1	TX2
update emp set sal = 2000 where empno = 7788 and sal = 1000
	update emp set sal =3000 where empno = 7788 and sal = 2000;
commit;
	commit;

Current모드로 처리한다면 tx2의 트랜잭션은 tx1이 커밋되기를 기다렸다가 sal값이 2000으로 갱신되는것을 확인하고 정상적으로 update를 수행합니다. 따라서 최종 sal은 3000입니다.

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

TX1	TX2
update emp set sal = sal+100 where empno = 7788 and sal = 1000;
	update emp set sal = sal + 200 where empno = 7788 and sal = 1000;
commit;
	commit;

tx2는 tx1이 커밋되기를 기다렸다가 tx1이 끝나면 계속 진행한다. 하지만 이때 7788의 sal의 값은 1,100이므로 tx2의 update는 실패하게 됩니다.

(5) Consistent 모드로 갱신대상을 식별하고 Current 모드로 생신
실제 오라클은 어떻게 두 개의 읽기 모드가 공존하면서 update를 처리하는 것일까요?
이해를 위해 pesudo 코드로 표현해보았습니다.
for c in
(
select rowid rid,empno, sal from emp where empno = 7788 and sal = 1000 ——consistent
)
loop
update emp set sal = sal + 200 where empno = c.empno and sal = c.sal and rowid = c.rid; ————-current
end loop;

두 단계로 나눠서 설명해보겠습니다
1. where 절에 기술된 조건에 따라 수정/삭제할 레코드의 rowid를 consistent모드로 찾습니다.(dml문이 시작된 시점 기준)
2.앞에서 읽은 rowid가 가리키는 레코드를 찾아가 로우 lock을 설정한 후에 current모드로 실제 update/delete로 수행한다(값이 변경되는 시점 기준). 이 단계에서 current모드로 다시 한번 조건을 체크하고 갱신할 값을 읽어 수정/삭제합니다.
1을 수행해 update/delete대상 건을 모두 추출하고 나서 2를 수행한다고 생각하면 안되고, 1에서 커서를 열어서 fetch하면서 2를 건건이 반복 수행하는 것입니다.
1이 필요한 이유는 시작된 시점 기준으로 수정/삭제할 대상을 식별하려고 consistent모드 읽기를 사용할 뿐이며 갱신이 진행되는 동안 추가되거나 변경을 통해 범위 안에 새로 들어오는 레코드를 제외하고자 하는것입니다. 이미 범위 안에 포함돼 있던 레코드는 2에서 변경이 이루어지는 바로 그 시점 기준으로 값을 읽고 갱신합니다. 이 때 블록 scn은 쿼리 scn보다 높고 낮음을 따지지 않고 그 시점에 이미 커밋된 값이라면 그대로 받아들이고 읽습니다.

정리하면
-select 는 consistent로 읽습니다.
- insert,update,delete, merge는 current모드로 읽고 씁니다. 다만, 갱신할 대상 레코드를 식별하는 작업만큼은 consistent모드로 이루어집니다.

 

(6) 오라클에서 일관성 없게 값을 갱신하는 사례
1.update 계좌2 set 총잔고 = 계좌2.잔고 + (select 잔고 from 계좌1 where 계좌번호 = 계좌2.계좌번호) where 계좌번호 = 7788;
2.update 계좌2 set 총잔고 = (select 계좌2.잔고 + 잔고 from 계좌1 where 계좌번호 = 계좌2.계좌번호) where 계좌번호 = 7788;
스칼라 서브쿼리는 특별한 이유가 없는 한 consistent모드로 읽기를 수행합니다. 따라서 1번의 계좌2.잔고는 current모드로 읽고 select안에 있는 계좌 1의 잔고는 consistent 모드로 읽습니다. 하지만 2번은 스칼라서브 쿼리 안에 current모드로 읽어야 할 계좌2.잔고값을 참조하기 때문에 스칼라 서브쿼리 까지도 current모드로 읽게됩니다. 위 update 문장이 진행하는 도중에 계좌1에서 변경사항이 발생했다면 그 새로운 값을 읽고, delete가 발생했다면 조인에 실패해 null값으로 update를 하게 될 것입니다.

이처럼 오라클에서도 일관성 없게 값을 갱신할 가능성은 존재합니다. 하지만 사용자가 오라클만의 독특한 읽기 모드를 정확히 이해하고 주의 깊게 SQL을 작성한다면 피해갈 수 있는 문제입니다.