[Oracle] 문장수준 읽기 일관성 Consistent Mode, Current Mode 차이 :: 마이자몽

Dirty Read

Commit되지 않은 데이터를 다른 Transaction에서 읽을 수 있다면 어떤 현상이 발생할 수 있을까요?

최종 연봉 계산하는 예시로 발생할 수 있는 현상을 알아 보겠습니다. 최종 연봉은 현재 연봉 + 퇴직금 + 인센티브로 최종 결정되는 것으로 가정해보겠습니다. 만약 Commit되지 않은 데이터를 다른 Transaction에서 읽을 수 있다면, 데이터를 읽는 시점에 따라 다른 결과 값을 읽습니다. 퇴직금까지만 합산했을 때 데이터를 읽으면 3900이되고 인센티브까지 합산했을 때 4200의 값을 읽습니다. 즉, 일관성 없이 데이터를 읽기 때문에 연산 과정에서 최종값이 아닌 중간값을 받아오는 문제가 발생할 수 있습니다.

 

이렇게 Commit되지 않은 데이터를 다른 Transaction에서 읽는 것을 허용하는 것이 Dirty Read 입니다. Dirty Read 처럼 일관성 없는 결과를 방지하기 위해 읽기 일관성을 보장해줘야 합니다.

 

 

 

문장수준 읽기 일관성이란?

단일 SQL문이 실행되는 도중 다른 Transaction이 데이터를 추가, 삭제, 변경하더라도 일관성 있는 결과를 반환하는 것.

Dirty Read를 허용한다면 문장수준 읽기 일관성은 보장되지 않습니다. 그럼 Dirty Read를 방지하기 위해서는 어떻게 해야할까요?

 

 

 

Oracle 이외의 DBMS Dirty Read 방지 방법

Oracle 이외의 DBMS에서는 Row Lock을 사용합니다. Lock이 걸린 ROW에 대해서는 읽지 못하게 Lock을 걸어버리는 방법을 사용합니다. 이렇게 되면 Commit되기 전까지 해당 행을 읽을 수 없기 때문에 Commit되지 않은 데이터를 읽을 수 없어 Dirty Read를 방지할 수 있습니다.

 

그런데 Dirty Read를 방지하는 것 만으로 문장수준 읽기 일관성을 보장할 수 있을까요?

 

 

 

급여의 합계를 구하는 과정에서 중간에 다른 Transaction이 INSERT 작업을 합니다. 급여합계 읽기 작업이 끝나기도 전에 Commit까지 완료해버립니다. 이때 급여의 데이터를 어디까지 읽었는지, 그 시점에 따라 값이 다르게 나올 수 있습니다.

 

 

CASE1 : 이미 읽기 작업이 지나가고 INSERT 되었을 때

INSERT 시점이 읽기 작업이 지나가고 Commit되었기 때문에 급여 합계에서 누락됩니다.

 

 

CASE2 : 읽기 작업 전에 INSERT 되었을 때

INSERT 시점이 읽기 작업이 이루어지기 전에 Commit되었기 때문에 급여 합계에 포함됩니다.

 

 

Dirty Read를 방지해도 INSERT 작업이 이루어지는 시점에 따라 혹은 읽고 있는 위치에 따라 상이된 결과를 얻을 수 있습니다. 그럼 문장수준 읽기 일관성을 보장하기 위해서 어떻게 해야할까요?

 

 

 

두가지 방법이 있습니다. Table Level Lock을 하여 Commit되기 전까지 Table을 읽지 못하게하거나, Transaction Isolation Level(트랜젝션 고립화 수준)을 높이는 방법이 있습니다. 지금까지는 Oracle 이외의 DBMS에서 문장수준 읽기 일관성을 보장하는 방법을 알아봤습니다. 그럼 Oracle은 어떻게 읽기 일관성을 보장할까요?

 

 

 

Oracle 문장수준 읽기 일관성

ORACLE에서는 Undo Segment에 저장해놓은 Undo Data를 활용해서 완변한 문장수준 읽기 일관성을 보장합니다.

 

Oracle에서는 Consistent Mode와 Current Mode 읽기 방식이 있습니다. 두 가지 읽기 모드를 통해서 문장수준 읽기 일관성을 보장하는데 하나씩 알아보도록 하겠습니다.

 

 

 

Consistent Mode 읽기란?

Query가 시작된 시점을 기준으로 Commit된 데이터를 읽는다.

Query가 시작된 시점으로 데이터를 읽어 일관성 있는 데이터를 보도록합니다. 그럼 좀 더 자세하게 읽는 과정을 알아보겠습니다.

 

 

 

Consistent Mode로 읽었을 때의 흐름을 표현해봤습니다. Query가 시작된 시점을 기준으로 데이터를 읽는다고 했는데, 이것을 가능하게 하기위해 SCN(System Commit Number)를 사용합니다. SCN은 Commit된 시간을 번호로 지정한 것으로 이해하면 될 것 같습니다. 

 

그럼 Query가 실행되었을 때부터 한 단계씩 알아보겠습니다. 흐름은 Transaction1이 끝나기 전에 Transaction2에서 3000이란 급여를 INSERT하고 Commit까지 끝낸 상황입니다.

 

1. Query 실행

급여의 합계를 읽는 쿼리가 실행됩니다. 이때 System상 SCN 번호를 들고 탐색하게됩니다. 테이블의 데이터를 읽어가는 과정에서

Query SCN >= Block SCN이면  Current Block에서 데이터를 읽습니다.

 

 

2. 다른 Transaction Insert & Commit

급여 합계를 구하기 위해 테이블의 데이터를 읽고 있는데 그 사이에 데이터가 삽입되고 Commit까지 완료되었습니다.

 

 

3. Query SCN < Block SCN 만났을때 

CR Block을 생성합니다. CR Block은 Current Block의 복사본입니다. 이전의 데이터를 읽기 위해 생성합니다.

 

 

4. Undo Block 읽어 이전 데이터로 되돌리기

Undo Data를 읽어 한단계 이전 상태로 CR BLock을 되돌립니다. 이때 한단계 되돌려도 Query SCN < Block SCN 이면 Query SCN >= Block SCN이 될 때 까지 계속 Undo Data를 탐색합니다.

 

 

5. Undo 완료된 CR Block 읽기

4번의 작업으로 Query SCN >= Block SCN이 되면 해당 CR Block의 내용을 읽습니다.

 

 

이후 테이블의 탐색을 위의 과정대로 계속 진행합니다.

- Query SCN >= Block SCN 이면 Current Block을 읽는다.

- Query SCN < Block SCN 이면  CR Block을 생성해서 Undo Data를 읽는다.

 

이 처럼 Consistent Mode를 사용해서 읽는다면 문장수준 읽기 일관성을 Query의 시작을 기준으로 보장 가능하지만, 갱신 작업할때 Consistent Mode로 데이터를 읽는다면 문제가 없을까요?

 

 

 

Consistent Mode Update 문제점

Consistent Mode는 Query 실행 시점에서의 데이터를 읽는다.
Transaction1 Update 시점 SAL = 3000
Transaction2 Update 시점 SAL = 3000

위 순서대로 실행이 된다면 어떤 결과를 예상하시나요? 흐름상으로 봤을 때 SAL에 1000을 더하고 그 다음 SAL에 3000을 더합니다. 그럼 처음 SAL에서 4000이 더해진 7000이 논리적으로 맞는 답변 이겠죠? 하지만, Consistent Mode로 Update 작업을 한다면 3000만 더해진 6000이 결과로 나옵니다.

 

Transaction1에서 Update한 내용이 사라지는 Lost Update 현상이 발생합니다. Transaction2에서 Update하는 시점에서 Transaction1이 Commit되지 않았기 때문에 SAL을 아직 3000으로 보고 있습니다. Transaction1이 Commit되어도 Transaction2에는 SAL의 값을 3000으로 보고 있기 때문에 결과로 3000만 더해진 6000이 결과로 나옵니다.

 

이런 Lost Update에 대한 문제를 Current Mode를 사용하면 방지가 가능합니다.

 

 

 

Current Mode 읽기란?

현재 읽는 시점을 기준으로 Commit된 데이터를 읽는다.

Consistent Mode로 읽었을 때 문제가 되었던 예시를 Current Mode로 해보겠습니다.

Current Mode는 현재 읽는 시점에서의 데이터를 읽는다.
Transaction1 Update 시점 SAL = 3000
Transaction2 Update 시점 SAL = 4000

위 순서대로 실행했을 때는 논리적으로 맞는 7000이라는 값이 결과로 나옵니다. 좀 더 자세히 살펴보면,  Transaction1에서 Update 작업을하고, Transaction2에서 Update하려고 할때 Lock이 걸려있는 행이기 때문에 기다립니다. Commit이 완료되고 Transaction2에서는 Consistent Mode처럼 Query 실행 시점에서의 SAL값을 읽는게 아니라, 현재 데이터를 읽는 시점을 기준으로 하기 때문에 SAL을 4000으로 보고 있는 것 입니다.

 

Current Mode를 사용해서 Consistent Mode로 했을 때의 문제를 해결할 수 있습니다. 그럼 여기서 이제 알 수 있는게 한가지 있습니다. Oracle에서는 Consistent Mode로 읽고 Current Mode로 삽입, 갱신, 삭제 작업을 하겠구나? 맞는 말입니다. 하지만 좀 더 생각해보겠습니다. Current Mode로 갱신할 때 발생하는 문제는 없을까요?

 

 

 

Current Mode 갱신 문제점

Current Mode로 갱신 작업할때 발생하는 문제는 Dirty Read를 했을 때랑 비슷한 모습을 보입니다. Current Mode로 갱신작업을 한다고 하면 위에 예시처럼 범위를 지정해서 Update할때 INSERT하는 시점과 데이터를 읽는 시점에 따라 갱신하는 데이터의 갯수가 달라질 수 있다는 것 입니다. 그래서 Oracle에서는 Consistent Mode로 읽고 Current Mode로 갱신하구나!라고 이제 이해를 하셨을 겁니다.

 

왜 각각 Mode를 사용하는지 알게 되었는데요. 오히려 머리속이 복잡하고 헷갈릴 수 있다고 생각합니다. 그렇지 않다면 제가 헷갈리게 만들어 볼게요. 제가 안 알려드린게 하나 있는데요? 그럼 위의 예제는 5행이 갱신되는게 맞을까요? 아니면 6행 갱신되는게 맞는 것 일까요?

 

5행만 갱신되는게 맞겠죠? UPDATE가 먼저 실행되었고 그 다음에 INSERT가 실행되었으니까.... 5행만 갱신되는게 맞겠죠? 그래서 Consistent Mode로 읽고 Current Mode로 갱신한것 이겠죠? 엇 그런데 위의 상황에서는 SELECT문이 없는데요? 그럼 CASE2의 상황에서는 UPDATE할때 Current Mode로 읽는 것이니까 6행이 갱신되는게 맞는데요? 그럼 CASE1이랑 CASE2를 해결 못하는거 아닌가요??

 

예시를 하나 더 보겠습니다.

4000이 나오는게 맞는 것일까요? 아니면 6000이 나오는게 맞는 것 일까요?  Current Mode만 생각한다면 전혀 헷갈릴 것이 없죠. 현재 시점으로 보기 때문에 3000이란 값은 없어 결과로 4000이 나오겠죠. 그런데 Oracle에서는 Consistent Mode로 읽고 Current Mode로 갱신한데요... 이 말을 들으니까 뭔가Update할때 데이터를 읽는 과정에서 Consistent Mode로 Query가 시작하는 시점에서의 데이터를 읽어 6000이 될 것 같아요.... 그럼 Lost Update가 발생하는데.... 너무 헷갈리네요....

 

헷갈리게 해서 죄송하고요... 정말 어떻게 이 두가지 Mode가 Oracle에서 사용되는지 정리해 드릴게요.

 

 

 

Consistent Mode로 대상을 식별하고, Current Mode로 갱신한다.

일단 논리적으로 어떤 결과가 맞는지 생각해보세요. 시간상으로 봤을 때 Transaction1이 먼저 실행되었고 Transaction2가 나중에 실행되었어요. Transaction2가 실행될때는 이미 변경된 데이터로 간주해서 Update가 안되도 되요. 문제가 없다는 것이죠.

 

Oracle에서는 Consistent Mode로 대상을 식별하고, Current Mode로 갱신한다고 했는데요. 이거를 다른 말로 풀어보면

"Query를 실행하는 순간이랑 실제 갱신작업이 이루어지는 순간에 데이터가 다르다면 갱신하지 않겠다." 라는 말 입니다.

이말은 진짜 더 단순하게 풀어보면 "내가 읽은 것만 갱신한다." 입니다.

이게 보장이 되면 문장수준 읽기 일관성이 보장되는 것 입니다.

 

 

 

일관성 없게 Query 작성 주의사항

Oracle에서 완벽한 문장수준 읽기 일관성이 보장된다고 했습니다. 하지만 Oracle이 어떻게 문장수준 읽기 일관성을 보장하는지 이해하지 못하고 Query를 작성하면 문장수준 읽기 일관성이 깨질 수 있습니다. Oracle Scott계정을 사용한 예시를 통해 알아보겠습니다.

 

 

30번 부서 테이블 생성

--30번 부서 테이블 생성
CREATE TABLE DEPT30
AS
SELECT * FROM EMP
WHERE DEPTNO = 30;
 
--보너스 포함 급여 컬럼 
ALTER TABLE DEPT30
ADD (BONUS_SUM_SAL NUMBER);

 

 

부서별 보너스 테이블 생성

--부서별 보너스 테이블 생성
CREATE TABLE DEPT_BONUS(DEPTNO NUMBER, BONUS NUMBER);
 
--데이터 추가
INSERT INTO DEPT_BONUS VALUES(30, 1000);
 
--커밋
COMMIT;

 

 

실습진행

두개의 Transaction으로 실습을 진행했습니다. 아래의 흐름대로 진행했습니다.

 

 

1. Transaction1 : 30번 부서 보너스 증가

30번 부서의 모든 사원들에게 1000 증가된 보너스(1000 + 1000)를 제공하기 위해 추가했습니다.

 

 

2. Transaction1 : 7499 사원 급여 증가

7499번 사원의 급여를 500 증가 시켰습니다.

 

 

3. Transaction2 : 보너스 적용

다른 Transaction에서 30번 부서의 사원들에게 보너스가 적용된 급여를 BONUS_SUM_SAL에 갱신합니다. DEPT_BONUS의 30번 부서 BONUS를 Transaction1에서 갱신작업을 하고 있어서 여기서 Waiting하게 됩니다.

 

 

4. Transacion1 : Commit

Transaction1 Commit을 하면서 Transaction2의 Waiting이 풀립니다.

 

 

5. Transaction2 : Commit

Transaction2까지 Commit합니다.

 

 

위의 작업이 완료된 후 결과를 생각해보겠습니다. 흐름대로 30번 부서에 보너스를 1000추가하여 2000이 최종적으로 추가될 보너스입니다. 그다음 7499번 사원의 급여를 500 증가시켰습니다. 이 두가지 작업이 끝난 후 다른 Transaction에서 보너스 적용작업을 했습니다. 우리가 지금까지 배운 Consistent Mode로 식별하고 Current Mode로 갱신한다고 했을때, 7749번 사원은 급여가 2500이 증가하고 나머지 사원들은 2000이 증가되어 있어야하는게 맞습니다. 그럼 결과를 확인해 보겠습니다.

 

SELECT
    EMPNO
    ,ENAME
    ,SAL
    ,BONUS_SUM_SAL
    ,SAL + BONUS
FROM DEPT30 D, DEPT_BONUS B
WHERE D.DEPTNO = B.DEPTNO;

실제로 갱신한 BONUS_SUM_SAL 컬럼과 그냥 존재하는 사원의 급여와 보너스를 더한 값이 왜 다를까요?

7499번 사원의 이전 급여는 1600이었습니다. 급여에 500을 더한 갱신은 이상없이 적용 되었는데, 보너스가 1000 합산되었습니다. 실제로 2000이 더해졌어야하는데 1000만 더해진 것 입니다. 왜이럴까요?

 

Transaction2의 3번 Update문을 살펴 보겠습니다.

UPDATE DEPT30
SET BONUS_SUM_SAL = SAL + (
    SELECT BONUS FROM DEPT_BONUS
    WHERE DEPTNO = DEPT30.DEPTNO
);

스칼라 Subquery(단일 값)는 특별한 이유가 없는한 Consistent Mode로 읽습니다. Subquery는 똑같이 Select문을 사용하는 것이니 Consistent Mode로 값을 읽어 오겠죠?

 

SAL 값은 Current Mode로 읽었는데 Subquery 안에 있는 BONUS값은 Consistent Mode로 읽어 Query 가 실행되는 시점을 기준으로 이전 값인 1000을 받아온 것 입니다. 그럼 재대로된 값으로 UPDATE하기 위해서는 어떻게 해야할까요?

 

 

UPDATE DEPT30
SET BONUS_SUM_SAL = (
    SELECT 
        DEPT30.SAL +
        BONUS
    FROM DEPT_BONUS
    WHERE DEPTNO = DEPT30.DEPTNO
);

Update는 DEPT30 테이블의 값을 갱신하고 있습니다. 즉, DEPT30테이블의 값은 Current로 읽는 다는 얘기죠. Current Mode로 읽는 SAL 값을 Subquery안에 넣어줘서 Subquery까지 Current Mode로 읽도록 하는 것입니다. 이렇게 UPDATE를 해줬다면 SELECT 했을 때 아래와 같이 문제 없는 갱신작업을 합니다.

 

 

Subquery 말고도 사용자가 정의하는 내용들에 대해서는 주의 해야합니다. 

 

USER_AVG라는 사용자함수를 만들었다고 가정해보겠습니다. EMP테이블의 급여 평균을 반환해주는 함수입니다. 이런 함수를 Query내에 반복적으로 사용했을때 일관된 결과를 얻지 못할 수 있다는 것 입니다. 사용자 함수 내의 SELECT 문은 Consistent Mode로 읽기 때문에 이런 문제가 발생하는 것 입니다. 이외에 Procedure나 Trigger사용 시 발생될 수 있습니다.

 

Oracle에서 완변한 문자수준 읽기 일관성을 보장한다고 해도 Query사용 시 꼭 내용을 알고 주의해서 작성해야합니다.