[오라클, Oracle] TCS 문 - 데이터 일관성

 

데이터 일관성(data consistency)은 트랜잭션에 의한 변경을 일관된 상태로 볼 수 있는 것을 의미한다.

데이터 동시성과 데이터 일관성은 트레이드오프(tradeoff) 관계를 가진다.

동시성이 높아지면 일관성이 낮아지고, 일관성이 높아지면 동시성이 낮아진다.

 

[다중 버전 읽기 일관성]

오라클 데이터베이스는 다중 버전 읽기 일관성(Multiversion Read Consistency, MVRC)을 지원한다.

(해당 기능을 데이터 동시성 관점에서 Multiversion Concurrency Control, MVCC로 부르기도 한다.)

다중 버전 읽기 일관성은 쿼리가 시작된 시점의 데이터를 조회할 수 있게 해주는 기능이다.

쿼리가 시작된 시점의 SCN과 블록의 SCN을 비교하여 쿼리가 시작된 이후에 블록이 변경되었다면 언두 세그먼트의 변경 이전 데이터를 상요하여 쿼리가 시작된 시점의 데이터를 반환한다.

 

-- 아래 예제를 순서대로 수행해보자.

S1 세션에서 데이터를 변경했더라도 커밋 전까지는 S2 세션에서 변경 전 데이터가 조회된다.

커밋을 수행하기 전까지 변경된 데이터는 데이터를 변경한 세션에서만 조회할 수 있다.

 

S2

SELECT VL FROM T1 WHERE CD = 1; -- 40

 

S1

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

SELECT VL FROM T1 WHERE CD = 1; -- 50

 

S2

SELECT VL FROM T1 WHERE CD = 1; -- 40

 

S1

COMMIT;

 

S2

SELECT VL FROM T1 WHERE CD = 1; -- 50

 

언두 세그먼트의 공간은 일정 시점이 지나면 재사용될 수 있다.

쿼리 시작 시점의 변경 이전 데이터가 저장된 공간이 재사용되면 해당 시점의 데이터를 반환할 수 없기 때문에 "ORA-01555: 너무 이전 스냅샷" 에러가 발생한다. 다중 버전 읽기 일관성의 부작용으로 생각할 수 있다.

 

[문장 수준 읽기 일관성]

문장 수준 읽기 일관성(statement-level read consistency)는 단일 쿼리 수준의 읽기 일관성이다.

오라클 데이터베이스는 다중 버전 읽기 일관성을 지원하기 때문에 락을 사용하지 않고 문장 수준 읽기 일관성을 보장한다.

 

-- 예제를 위해 SYS 사용자로 접속한 세션에서 SCOTT 사용자에게 DBMS_LOCK 패키지의 실행 권한을 부여해야 한다.

 

C:\>sqlplus sys/ora@ora12cr2 AS SYSDBA

 

GRANT EXECUTE ON DBMS_LOCK TO scott;

 

-- 아래와 같이 함수를 생성하자.

CREATE OR REPLACE FUNCTION F1 (I_SECONDS IN NUMBER) RETURN NUMBER

IS

BEGIN

DBMS_LOCK.SLEEP (I_SECONDS);

RETURN 1;

END F1;

 

 

S1 세션의 첫 번째 쿼리는 F1 함수로 인해 10초 후 결과가 반환된다.

그 사이에 S2 세션에서 데이터를 변경하고 커밋을 수행하더라도 쿼리가 시작한 시점의 값 (50)이 반환된다.

T1 테이블을 다시 조회하면 변경된 값(40)이 반환된다.

 

S1

SELECT *

FROM T1

WHERE CD = 1

AND F1 (10) = 1;

 

S2

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

COMMIT;

 

S1

SELECT * FROM T1 WHERE CD = 1;

 

[트랜잭션 수준 읽기 일관성]

트랜잭션 수준 읽기 일관성 (Transaction-level read consistency)은 말 그대로 트랜잭션 수준의 읽기 일관성이다. 동일한 트랜잭션 내의 쿼리는 다시 조회하더라도 결과가 동일해야 한다. 오라클 데이터베이스는 기본적으로 트랜잭션 수준 읽기 일관성을 보장하지 않는다. 트랜잭션 수준 읽기 일관성을 보장하려면 트랜잭션 고립화 수준을 변경해야 한다.

 

ANSI/ISO SQL 표준은 아래 네 가지의 트랜잭션 고립화 수준(transaction isolation level)을 정의하고 있다.

오라클 데이터베이스는 read committed, serializable 수준을 지원한다는 기본값은 read committed다.

 

고립화 수준 - 설명

read uncomitted - 커밋되지 않은 데이터를 읽는 것을 허용

read committed - 커밋된 데이터만 읽는 것을 허용

repeatable reads - 트랜잭션 내에서 읽은 데이터는 갱신과 삭제를 금지

serializable - 트랜잭션 내에서 읽은 테이블에 삽입을 금지

 

아래는 읽기 일관성이 보장되지 않을 때 발생할 수 있는 읽기 이상 현상(read phenomena)이다.

 

읽기 현상 - 설명

dirty read - 동일한 쿼리가 커밋되지 않은 데이터를 읽어 다른 결과를 반환

non-repeatable read - 동일한 쿼리가 갱신 또는 삭제에 의해 다른 결과를 반환

phantom read - 동일한 쿼리가 삽입에 의해 다른 결과를 반환

 

트랜잭션 고립화 수준에 따라 아래와 같은 읽기 이상 현상이 발생할 수 있다.

 

dirty read - non-repeatable read - phantom read

read uncommitted Y - Y - Y

read committed Y - Y

repeatable reads Y

serializable

 

오라클 데이터베이스는 repeatable reads 수준을 지원하지 않지만 FOR UPDATE 절을 사용하여 non-repeatable read 현상을 회피할 수 있다.

아래 예제는 S1 세션에서 FOR UPDATE 절을 사용했기 때문에 S2 세션에서 값을 갱신할 수 없다.

S1 세션에서 T1 테이블을 반복해서 조회하더라도 값이 변경되지 않는 것이 보장된다.

 

S1

SELECT * FROM T1 FOR UPDATE;

 

S2

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

-- 블로킹

 

S1

SELECT SUM (VL) AS VL FROM T1; -- 100

 

SELECT SUM (VL) AS VL FROM T1; -- 100

 

S2

ROLLBACK;

 

S1

COMMIT;

 

아래 예제는 S1 세션에서 FOR UPDATE 절을 사용했지만 S2 세션에서 신규 행을 삽입하는 것을 방지할 수 없기 때문에 변경된 값(150)이 조회된다. FOR UPDATE 절을 사용하더라도 phantom read 현상을 방지할 수 없다.

 

S1

SELECT * FROM T1 FOR UPDATE;

 

SELECT SUM (VL) AS VL FROM T1; -- 100

 

S2

INSERT INTO T1 (CD, VL) VALUES (3, 50);

COMMIT;

 

S1

SELECT SUM (VL) AS VL FROM T1; -- 150

 

COMMIT;

 

[SET TRANSACTION 문]

트랜잭션 고립화 수준을 serializable 수준으로 설정하려면 SET TRANSACTION 문을 사용해야 한다.

SET TRANSACTION 문의 구문은 아래와 같다.

기본값은 READ WRITE, READ COMMITTED다.

 

SET TRANSACTION {{READ {ONLY | WRITE}

| ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}

| USE ROLLBACK SEGMENT rollback_segment} [NAME string]

| NAME string};

 

항목 - 설명

READ ONLY - 읽기 전용 트랜잭션으로 설정 (트랜잭션 수준 읽기 일관성 보장)

READ WRITE - 읽기 쓰기 트랜잭션으로 설정 (문장 수준 읽기 일관성 보장)

SERIALIZABLE - 트랜잭션 고립화 수준을 SERIALIZABLE로 설정

READ COMMITTED - 트랜잭션 고립화 수준을 READ COMMITTED로 설정

 

아래 예제는 SET TRANSACTION 문을 사용하여 트랜잭션 고립화 수준을 SERIALIZABLE로 설정했기 때문에 phantom read 현상이 발생하지 않는다.

COMMIT을 수행하면 트랜잭션이 종료된다.

(트랜잭션 고립화 수준은 트랜잭션이 종료되면 초기화된다. 동일한 세션에서 새로운 트랜잭션을 시작하면 기본값인 READ WRITE로 트랜잭션이 생성된다.)

 

S1

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE;

 

SELECT SUM (VL) AS VL FROM T1; -- 150

 

S2

INSERT INTO T1 (CD, VL) VALUES (4, 50);

COMMIT;

 

S1

SELECT SUM (VL) AS VL FROM T1; -- 150

 

COMMIT;

 

SELECT SUM (VL) AS VL FROM T1; -- 200

 

 

-- 아래 예제는 S1 세션에서 에러가 발생한다. 트랜잭션 고립화 수준을 SERIALIZABLE로 설정하면 다른 트랜잭션에 의해 변경 후 커밋된 데이터를 변경할 수 없다.

 

S1

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE;

 

S2

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

COMMIT;

 

S1

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

 

ORA-08177: 이 트랜잭션에 대한 직렬화 액세스를 할 수 없습니다.

 

ROLLBACK;

 

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.