[Oracle] 오라클 Consistent vs. Current 모드 읽기

지난 번에 Consistent 모드로 블록을 읽으면 읽기 일관성이 보장된다는 것을 알았다.

☑[Oracle] 오라클 다중 버전 읽기 일관성 모델 알아보기

이제 오라클에 존재하는 Consistent 모드 읽기와 Current 모드 읽기의 차이점에 대해 알아보도록 하자.

Consistent 모드 읽기

- 쿼리 실행 시간에 상관없이 항상 쿼리가 시작된 시점의 데이터를 가져옴

[ SQL Trace ]

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.41       1.15         66      28691       7235        7071

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.41       1.16         66      28691       7235        7071

[ AutoTrace ]

Statistics

----------------------------------------------------------

          0  recursive calls

       7235  db block gets

      28691  consistent gets

         66  physical reads

     663872  redo size

        935  bytes sent via SQL*Net to client

        979  bytes received via SQL*Net from client

          6  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

       7071  rows processed

   

① CR copy 생성 없이 Current 블록을 읽어도 집계에 포함

② SELECT 문에서 읽은 대부분의 블록

③ CR 블록을 생성하려고 읽어들인 Undo 세크먼트 블록도 집계에 포함

Current 모드 읽기

- 쿼리가 시작된 시점이 아닌 데이터를 찾아간 그 시점의 최종 값을 가져옴

[ SQL Trace ]

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.41       1.15         66      28691       7235        7071

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.41       1.16         66      28691       7235        7071

[ AutoTrace ]

Statistics

----------------------------------------------------------

          0  recursive calls

       7235  db block gets

      28691  consistent gets

         66  physical reads

     663872  redo size

        935  bytes sent via SQL*Net to client

        979  bytes received via SQL*Net from client

          6  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

       7071  rows processed

   

① DML문 수행 시

② SELECT FOR UPDATE문 수행 시

③ disk sort가 필요할 정도의 대량 데이터를 정렬 시

음.. 뭔가 차이가 있다는 건 알겠는데 실제로 어떻게 적용되는건지 확 와닿지가 않..;;

예제를 통해 좀 더 생각해보자.

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

TX1		TX2
UDPATE EMP SET SAL = SAL + 100   WHERE EMPNO = 7788;	t1
	t2	UDPATE EMP SET SAL = SAL + 200   WHERE EMPNO = 7788;
COMMIT;	t3
	t4	COMMIT;

① TX2는 TX1에 걸린 Lock 대기 (Row Lock)

② t2 시점에 SAL은 1000이었므로 TX1이 commit된 후 1,000을 가지고 UDPATE (Read Committed라고 가정)

③ 최종값은 1200이 되고 TX1의 처리 결과는 사라짐, Lost Update 발생

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

TX1		TX2
UDPATE EMP SET SAL = 2000   WHERE EMPNO = 7788     AND SAL = 1000;	t1
	t2	UDPATE EMP SET SAL = 3000   WHERE EMPNO = 7788     AND SAL = 2000;
COMMIT;	t3
	t4	COMMIT;

① TX2는 TX1에 걸린 Lock 대기 (Row Lock)

② SAL 값이 2,000으로 UPDATE 된 것을 확인 후 UPDATE 수행

③ 최종 값은 3,000

위 경우로만 보면 Current 모드로 갱신하는 것이 매우 이상적으로 보이지만 여기에도 맹점은 있다.

아래의 경우를 보자.

* T: 1~100,000 까지의 Unique한 번호를 가진 테이블

TX1		TX2
UPDATE T SET NO = NO + 1   WHERE NO > 50000;	t1
	t2	INSERT INTO T VALUES(100001,100001);
	t3	COMMIT;
COMMIT;	t4

① 인덱스(no)를 경유해 순차적으로 UPDATE를 진행하는 경우

→ 50,001건이 UPDATE

② Full Table Scan 방식으로 UPDATE 진행하는 경우

→ 레코드가 INSERT 되는 위치에 따라 UPDATE 결과 건수가 달라짐

(이미 지나간 블록에 삽입된다면 50,000 건 UPDATE, 앞으로 지나갈 블록에 삽입된다면 50,001 건 UPDATE)

※ UPDATE나 DELETE 에 대해서도 위와 같은 현상이 일어날 수 있다.

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

- 실제 오라클이 UPDATE를 처리하는 방식

TX1		TX2
UDPATE EMP SET SAL = SAL + 100   WHERE EMPNO = 7788     AND SAL = 1000;	t1
	t2	UDPATE EMP SET SAL = SAL + 200   WHERE EMPNO = 7788     AND SAL = 2000;
COMMIT;	t3
	t4	COMMIT;

☞ Consistent 모드에서 수행한 조건 체크를 Current 모드로 액세스하는 시점에 한 번 더 수행

Consistent 모드는 쿼리 시작 시점의 SCN을 기준으로 하기 때문에 UPDATE 대상이 되는 데이터들이 사실상 미리 정해지는 거라고 보면 이해가 쉽다. 

- 중간에 다른 세션에 의해 데이터가 INSERT 되어도 쿼리 시작 이후의 SCN이므로 UPDATE 대상이 아님

- 중간에 다른 세션에 의해 대상 데이터가 UPDATE 된다면 Current 모드로 액세스하는 시점에 한 번 더 조건 체크 할 때 제외 처리됨 

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

* 스칼라 서브쿼리는 특별한 이유가 없는 한 항상 Consistent 모드로 읽기를 수행

UPDATE 계좌2

   SET 총잔고 = 계좌2.잔고 +

      (SELECT 잔고 FROM 계좌1 WHERE 계좌번호 = 계좌2.계좌번호)

 WHERE 계좌번호 = 7788;

-- 계좌2는 Current 모드, 계좌1은 Consistent 모드로 읽어 잘못된 결과를 도출할 수 있다.

↓

UPDATE 계좌2

   SET 총잔고 = (SELECT 계좌2.잔고 + 잔고 FROM 계좌1 

          WHERE 계좌번호 = 계좌2.계좌번호)

 WHERE 계좌번호 = 7788;

-- Current 모드로 읽어야 할 계좌2.잔고를 스칼라 서브쿼리 내에서 참조하기 때문에 스칼라 서브쿼리까지도 Current 모드로 작동

너무 헷갈리면 "오라클은 Consistent 모드로 갱신대상을 식별하고, Current 모드로 갱신한다." 만 명심하도록 하자!

아.. 머리 아프~