<오라클 성능 고도화 원리와 해법1> Ch02-02 트랜잭션 수준 읽기 일관성

Posted Feb 20, 2024 Updated Apr 4, 2024

By Mijong Go 15 min read

오라클 성능 고도화 원리와 해법1 - Ch02 트랜잭션과 Lock - 02 트랜잭션 수준 읽기 일관성

(1) 트랜잭션 수준 읽기 일관성이란?

문장 수준 읽기 일관성(Statement-Level Read Consistency)은 쿼리가 시작된 시점을 기준으로 데이터를 일관성 있게 읽어들이는 것을 말하며, 앞장에서 충분히 설명하였다. 트랜잭션 수준 읽기 일관성(Transaction-Level Read Consistency)은 트랜잭션이 시작된 시점을 기준으로 일관성 있게 데이터를 읽어들이는 것을 말한다. 트랜잭션이 진행되는 동안 다른 트랜잭션에 의해 변경 사항이 발생하더라도 이를 무시하고 계속해서 일관성 있는 데이터를 보고자 하는 업무 요건이 있을 수 있다. 물론 트랜잭션이 진행되는 동안 자신이 발생시킨 변경 사항은 읽을 수 있어야 한다.

오라클은 완벽한 문장 수준의 읽기 일관성을 보장하지만, 트랜잭션에 대해서는 기본적으로 보장하지 않는다. 다른 DBMS도 트랜잭션 수준의 읽기 일관성은 보장하지 않는다. 트랜잭션 수준으로 읽기 일관성을 강화하려면 트랜잭션 고립화 수준을 높여주어야 한다.

(2) 트랜잭션 고립화 수준

트랜잭션 고립화 수준(Transaction Isolation Level)은 다분히 학술적인 내용인데다 DBMS마다 구현 방식이 달라 본서에서 자세히 다루는 것은 적절치 않다고 생각한다. 따라서 ANSI/ISO SQL standard(SQL92)에서 정의하고 있는 아래 네 가지 트랜잭션 고립화 수준을 간단히 요약하는 것으로 설명을 대신하겠다.

레벨 0 (= Read Uncommitted)
- 트랜잭션에서 처리중인, 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용
- Dirty Read, Non-Repeatable Read, Phantom Read 현상 발생
- Oracle은 이 레벨을 지원하지 않음
레벨 1 (= Read Committed)
- Dirty Read 방지: 트랜잭션이 커밋되어 확정된 데이터만 읽는 것을 허용
- 대부분의 DBMS가 기본 모드로 채택하고 있는 일관성 모드
- Non-Repeatable Read, Phantom Read 현상은 여전히 발생
- DB2, SQL Server, Sybase의 경우 읽기 공유 Lock을 이용해서 구현. 하나의 레코드를 읽을 때 Lock을 설정하고 해당 레코드를 빠져 나가는 순간 Lock 해제
- Oracle은 Lock을 사용하지 않고 쿼리 시작 시점의 Undo 데이터를 제공하는 방식으로 구현
레벨 2(= Repeatable Read)
- 선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 후행 트랜잭션이 갱신하거나 삭제하는 것을 불허함으로써 같은 데이터를 두 번 쿼리했을 때 일관성 있는 결과를 리턴
- Phantom Read 현상은 여전히 발생
- DB2, SQL Server의 경우 트랜잭션 고립화 수준을 Repeatable Read로 변경하면 읽은 데이터에 걸린 공유 Lock을 커밋할 때까지 유지하는 방식으로 구현
- Oracle은 이 레벨을 명시적으로 지원하지 않지만 for update 절을 이용해 구현 가능. SQL Server 등에서도 for update 절을 사용할 수 있지만 커서를 명시적으로 선언할 때만 사용 가능함
레벨 3(=Serializable)
- 선행 트랜잭션이 읽은 데이터를 후행 트랜잭션이 갱신하거나 삭제하지 못할 뿐만 아니라 중간에 새로운 레코드를 삽입하는 것도 막아줌
- 완벽한 읽기 일관성 모드를 제공

DBMS에서 제공하는 트랜잭션 고립화 수준 조정 기능을 이용해 트랜잭션 레벨 읽기 일관성을 확보하려면 아래 문장 하나만 수행해주면 된다.

  
set transaction isolation level serializable;

낮은 단계의 트랜잭션 고립화 수준을 사용할 때 발생하는 아래 세 가지 현상에 대해 살펴보자.

Dirty Read (= Uncommitted Dependency)
Non-Repeatable Read (= Inconsistent Analysis)
Phantom Read

(3) Dirty Read(= Uncommitted Dependency)

아직 커밋되지 않은 수정 중인 데이터를 다른 트랜잭션에서 읽을 수 있도록 허용할 때 발생한다. 만약 Dirty Read를 허용하는 상황에서 아래 문장이 수행되는 도중에 다른 트랜잭션에 의해 특정 계좌의 잔고가 변경된다면 쿼리의 최종 결과 값이 비일관성(inconsistency) 상태에 놓이게 될 수 있다. 왜냐하면, 그 트랜잭션이 어떤 이유에서건 롤백될 수 있기 때문이다.

  
select sum(잔고) from 계좌;

대부분의 DBMS가 기본 트랜잭션 고립화 수준을 레벨 1(= Read Committed)로 설정하고 있기 때문에 Dirty Read 현상은 발생하지 않는다. 즉, 커밋된 데이터만 읽을 수 있도록 허용한다. SQL Server, Sybase 등은 이를 구현하기 위해 데이터를 읽을 때 공유 Lock을 사용한다. 갱신 중인 레코드에는 배타적 Lock이 걸리는데, 이는 공유 Lock과 호환되지 않으므로 갱신 중인 레코드를 읽지 못한다. 그런 DBMS에서는 Lock에 의한 동시성 저하가 자주 문제시되므로 쿼리에 옵션을 사용해서 Dirty Read를 허용하도록 프로그래밍하는 경우를 종종 보게 된다. 즉, 트랜잭션 고립화 수준을 레벨 0(=Read Uncommitted)으로 낮추는 것이다. 하지만 오라클은 다중 버전 읽기 일관성 모델을 채택함으로써 Lock을 사용하지 않고도 Dirty Read를 피해 일관성 있는 데이터 읽기가 가능하고, 따라서 레벨 0 수준으로 트랜잭션 고립화 수준을 낮추는 방법을 아예 제공하지 않고 있다.

(4) Non-Repeatable Read(= Inconsistent Analysis)

한 트랜잭션 내에서 같은 쿼리를 두 번 수행할 때, 그 사이에 다른 트랜잭션이 값을 수정하거나 삭제함으로써 두 쿼리의 결과가 상이하게 나타나는 비일관성(inconsistency)이 발생하는 것을 말한다.

Non-Repeatable Read 현상을 방지하려면 TX1이 ①번 문장을 수행할 때 for update 절을 추가해 주어야 한다. 그러면 123번 고객 레코드에 Lock이 걸리므로 TX2 트랜잭션은 블로킹 됐다가 TX1이 커밋하는 t5 시점 이후에 진행을 계속한다.

(5) Phantom Read

한 트랜잭션 안에서 일정 범위의 레코드들을 두 번 이상 읽을 때, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상을 말한다. 트랜잭션 도중에 새로운 레코드가 삽입되는 것을 허용하기 때문에 나타나는 현상이다.

이런 Phantom Read 현상을 방지하려면 TX1이 ①번 문장을 수행하기 전에 아래 문장을 통해 트랜잭션 고립화 수준을 레벨 3으로 올려주어야 한다.

  
set transaction isolation level serializable;

고립화 수준을 레벨 3으로 높이면 SQL Server에서는 Lock을 통해 t2 시점에 새로운 고객이 추가되는 것을 막아 버린다. 따라서 고립화 수준을 높이면 데이터 일관성은 확보되지만 동시성이 현격히 저하되는 결과를 초래한다. 반면, 오라클은 Lock을 전혀 사용하지 않은 상태에서 ①번과 ②번 쿼리 모두 SCN 확인 과정을 통해 t1 시점에 존재했던 고객만을 대상으로 집계를 수행하므로 동시성을 저하시키지 않으면서 일관성을 유지한다.

하지만 기본 트랜잭션 고립화 수준(레벨 1)에서는 TX2 트랜잭션에 의해 t2~t3 시점에 추가된 로그 데이터까지 TX1 트랜잭션에 의해 지워진다.

TX1이 ①번 문장을 수행하기 전에 트랜잭션 고립화 수준을 레벨 3으로 올려주면 이를 방지할 수 있다.

물론 트랜잭션 고립화 수준을 상향 조정하지 않더라도 TX1의 ①번과 ②번 문장에 ‘일시’ 조건을 추가해줌으로써 일관성 있게 트랜잭션을 처리할 수 있다.

지금까지 낮은 단계의 트랜잭션 고립화 수준에서 어떤 문제들이 발생할 수 있는지, 그리고 트랜잭션 내에서 일관성 있게 데이터를 조회, 추가, 수정, 삭제하기 위해 트랜잭션 고립화 수준을 조정하는 방법에 대해 살펴보았다.

트랜잭션 고립화 수준을 높이면 일관성(Consistency)은 향상되지만, 더 넓은 범위의 Lock을 더 오랫동안 유지하는 방식을 사용하므로 동시성(Concurrency)은 저하된다. 그림 2-2는 그것을 표현하고 있다. 하지만 오라클은 트랜잭션 고립화 수준을 높이더라도 Lock을 사용하지 않으므로 동시성이 저하되지는 않는다.

SERIALIZABLE_ABORTS
다른 DBMS는 트랜잭션 고립화 수준을 구현하기 위해 Lock을 이용하지만, 오라클은 for update 절을 사용하지 않는 한 절대 select 문에 Lock을 사용하지 않기 때문에 레벨을 상향 조정하더라도 이전과 동일한 동시성을 보장한다. 높은 일관성 수준에서도 높은 동시성을 제공할 수 있는 이유는, Undo 데이터를 활용하기 때문이다. 그렇지만 세상만사에 공짜는 없는 법이어서 만일 트랜잭션 레벨에서 일관성 있는 데이터를 제공할 수 없는 상황이 발생하면 다른 DBMS에는 없는 아래와 같은 에러를 던지게 된다. Lost Update가 발생하는 것이며, 오라클은 이를 방지하기 위해 ORA-08177 에러를 던진다. Undo 데이터를 활용함으로써 높은 수준의 동시성과 읽기 일관성을 유지하는 대신 다른 DBMS에 없는 Snapshot too old(ORA-01555)를 얻게 된 것과 같은 현상이라고 이해하면 된다. 참고로, v$sql에서 serializable_aborts > 0인 레코드를 조회하면 이 에러를 만났던 SQL들을 확인할 수 있다.

SERIALIZABLE_ABORTS

다른 DBMS는 트랜잭션 고립화 수준을 구현하기 위해 Lock을 이용하지만, 오라클은 for update 절을 사용하지 않는 한 절대 select 문에 Lock을 사용하지 않기 때문에 레벨을 상향 조정하더라도 이전과 동일한 동시성을 보장한다. 높은 일관성 수준에서도 높은 동시성을 제공할 수 있는 이유는, Undo 데이터를 활용하기 때문이다. 그렇지만 세상만사에 공짜는 없는 법이어서 만일 트랜잭션 레벨에서 일관성 있는 데이터를 제공할 수 없는 상황이 발생하면 다른 DBMS에는 없는 아래와 같은 에러를 던지게 된다.

Lost Update가 발생하는 것이며, 오라클은 이를 방지하기 위해 ORA-08177 에러를 던진다. Undo 데이터를 활용함으로써 높은 수준의 동시성과 읽기 일관성을 유지하는 대신 다른 DBMS에 없는 Snapshot too old(ORA-01555)를 얻게 된 것과 같은 현상이라고 이해하면 된다.

참고로, v$sql에서 serializable_aborts > 0인 레코드를 조회하면 이 에러를 만났던 SQL들을 확인할 수 있다.

Database, SQL Tuning

SQLP

This post is licensed under CC BY 4.0 by the author.