[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 모드로 갱신한다." 만 명심하도록 하자!
아.. 머리 아프~
