스터디/CS 스터디

[데이터베이스] MySQL 트랜잭션 격리 수준

제이온 (Jayon) 2022. 1. 2.

cs-study에서 스터디를 진행하고 있습니다.

트랜잭션 격리 수준

트랜잭션 격리 수준(Isolation level)이란 동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것이다. 격리 수준은 다음과 같이 크게 4가지로 정의할 수 있다.

READ UNCOMMITTED (커밋되지 않은 읽기)
READ COMMITTED (커밋된 읽기)
REPEATABLE READ (반복 가능한 읽기)
SERIALIZABLE (직렬화 가능)

DIRTY READ라고도 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서는 거의 사용하지 않고, SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.

4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시 처리 성능이 떨어진다. 그러나 SERIALIZABLE 격리 수준이 아니라면 크게 성능 저하가 발생하지는 않는다.

트랜잭션 격리 수준을 이야기하면 항상 함께 언급되는 세 가지 부정합의 문제점이 있다. 이 세 가지 부정합의 문제는 아래의 표처럼 격리 수준의 레벨에 따라 발생할 수도 있고, 발생하지 않을 수도 있다.

격리 수준에서 발생할 수 있는 세 가지 부정합 문제는 아래에서 차차 설명하려고 한다. 참고로 일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITTED 혹은 REPEATBLE READ 전략을 사용한다. 오라클은 READ COMMITTED, MySQL은 REPEATABLE READ를 주로 사용한다.

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부와 상관 없이 다른 트랜잭션에서 보여지게 된다.

더티 리드(Dirty Read) 발생

더티 리드란 다른 트랜잭션에서 처리한 작업이 완료되지 않았음에도 불구하고 다른 트랜잭션에서 볼 수 있게 되는 현상을 말한다.

아래 그림은 다른 트랜잭션에서 사용자 B가 실행하는 SELECT 쿼리의 결과에 사용자 A의 INSERT 쿼리가 COMMIT되기 전에 어떤 영향을 미치는지 보여주는 예시이다.

사용자 A는 emp_no = 50000 , first_name = 'JuBal' 인 새로운 사원을 삽입하고 있다. 그리고 사용자 B는 사용자 A가 변경한 내용을 커밋하기도 전에 emp_no = 50000 인 사원을 검색하고 있다. 이때 사용자 B의 SELECT 쿼리 결과에서는 사용자 A가 삽입한 커밋되지 않은 새로운 사원이 조회된다. 여기서 문제는 만약 사용자 A가 작업 도중 문제가 발생하여 삽입한 내용을 롤백해도 사용자 B는 JuBal이 정상적인 사원이라 판단하고 계속해서 처리하게 된다.

이처럼 어떠한 트랜잭션에서 처리한 작업이 완료되지 않았음에도 불구하고 다른 트랜잭션에서 볼 수 있게 되는 현상을 더티 리드라 하고, 더티 리드가 허용되는 격리 수준은 READ UNCOMMITTED이다.

더티 리드 현상은 데이터가 나타났다가 사라졌다하는 현상을 초래할 수 있으므로 개발자와 사용자를 상당히 혼란스럽게 만든다. 따라서 READ UNCOMMITTED 격리 수준은 트랜잭션의 격리 수준으로 인정하지 않을 정도로 데이터의 정합성에 악영향을 끼치므로 해당 격리 수준은 피할 것을 권장한다.

READ COMMITTED

READ COMMITTED 격리 수준은 오라클에서 기본적으로 사용되고 있으며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다. 이 레벨에서는 위 READ UNCOMMITTED 수준에서 발생할 수 있는 더티 리드와 같은 현상은 발생하지 않는다. 어떠한 트랜잭션에서 데이터를 변경하더라도 커밋이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.

그러나 READ COMMITTED 격리 수준에서는 NON_REPEATABLE READ라는 부정합 문제가 존재한다.

더티 리드(Dirty Read)해결

아래 그림은 READ COMMITTED 격리 수준에서 사용자 A가 변경한 내용이 사용자 B에게 어떻게 조회되는지 보여 준다.

사용자 A는 emp_no = 50000 인 사원의 first_name 을 JuBal에서 Toto로 수정하였는데, 이때 새로운 값인 Toto는 employees 테이블에 즉시 기록되고 이전 값인 JuBal은 Undo 영역으로 백업이 된다.

따라서 사용자 A가 이러한 변경 내역을 커밋하기 전에 사용자 B가 emp_no = 50000 인 사원을 조회하면 결과 값은 Toto가 아닌, 이전 값인 JuBal이 조회된다. 여기서 사용자 B의 SELECT 쿼리 결과는 employees 테이블이 아닌 Undo 영역의 백업된 레코드에서 가져온 결과이다.

READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없다. 최종적으로 사용자 A가 변경된 내용을 커밋하면 그때부터는 다른 트랜잭션에서도 백업된 Undo 영역의 데이터인 JuBal이 아닌, 새롭게 변경된 Toto 값을 참조할 수 있게 된다.

언두(Undo) 로그

언두 영역은 UPDATE 문장이나 DELETE와 같은 문장을 데이터를 변경했을 때 변경되기 전의 데이터를 보관하는 곳이다. INSERT 문장의 경우, 해당 데이터의 row id를 저장하고 이를 이용하여 물리적 메모리에 바로 접근할 수 있도록 보장한다.
크게 두 가지로 용도로 사용한다.
- 트랜잭션의 롤백 대비용
- 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공

NON-REPEATABLE READ 발생

NON-REPEATABLE READ란, 하나의 트랜잭션 내에서 동일한 SELECT 쿼리를 실행했을 때 항상 같은 결과를 보장해야 한다는 REPEATABLE READ 정합성에 어긋나는 것을 말한다.

위 그림에서 사용자 B는 BEGIN 명령으로 트랜잭션을 시작하고 first_name = 'Toto' 인 사원을 조회하면 일치하는 데이터가 존재하지 않는다. 하지만 이후에 사용자 A가 emp_no = 50000 인 사원의 이름을 Toto로 수정하고 커밋한 후 사용자 B가 똑같은 SELECT 쿼리로 조회하면 이번에는 1건의 결과가 조회된다. 이는 별다른 문제는 없어 보이지만, 사용자 B가 하나의 트랜잭션 내에서 동일한 SELECT 쿼리를 실행했을 때 항상 같은 결과를 보장해야 한다는 REPEATABLE READ 정합성에 어긋나게 된다.

이러한 부정합 현상은 일반적인 웹 애플리케이션에서는 크게 문제가 되지 않지만, 하나의 트랜잭션에서 동일한 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 있다. 예를 들어, 다른 트랜잭션에서 입금과 출금 처리를 계속 진행하고 있을 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회한다고 가정해 보자. 이때 READ COMMITTED 격리 수준을 사용한다면 REPEATABLE READ가 보장되지 않으므로 총합을 계산하는 SELECT 쿼리를 실행할 때마다 다른 결과를 가져오는 큰 문제가 발생할 수 있다.

REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다. 이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 NON-REPEATABLE READ 부정합이 발생하지 않지만, PHANTOM READ 부정합이 발생한다. (아래에서 이야기하지만, InnoDB 스토리지 엔진을 사용할 경우 PHANTOM READ 부정합이 발생하지 않음.)

NON-REPEATABLE READ 해결

REPEATABLE READ는 언두(Undo) 영역에 백업된 이전 데이터를 통해 트랜잭션 내에서는 동일한 결과를 보여 주도록 보장하여 NON-REPEATABLE READ 문제를 해결한다. 사실 READ COMMITTED 격리 수준 또한 언두 영역에 백업된 이전 데이터를 보여 주지만, 두 격리 수준에는 언두 영역을 활용하는 방식이 다르다. REPEATABLE READ 격리 수준은 ‘언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 버전을 보여 주냐’에 차이가 있어 NON-REPEATABLE READ 문제를 해결할 수 있다.

쉽게 말하자면, 언두 영역에 백업된 모든 데이터에는 변경을 발생한 트랜잭션의 번호가 포함되어 있는데, REPEATABLE READ 격리 수준에서는 실행 중인 트랜잭션보다 작은 트랜잭션에서 변경한 데이터만 보게 하여 NON-REPEATABLE READ 문제를 해결한다.

위 그림은 사용자 A가 emp_no = 50000 인 사원의 이름을 변경하는 과정에서 사용자 B가 emp_no = 50000 인 사원을 SELECT할 때 REPEATABLE READ 격리 수준이 작동하는 방식을 보여준다. 먼저 employees 테이블은 번호가 6인 트랜잭션에 의해 JuBal 사원이 삽입되었다고 가정하자.

사용자 A의 트랜잭션 번호는 12이고, 사용자 B의 트랜잭션 번호는 10이다. 이때 사용자 A는 사원의 이름을 Toto로 변경하고 커밋을 수행한다. 이때 사용자 B는 emp_no = 50000 인 사원을 A 트랜잭션이 변경을 실행하기 전과 실행한 후 각각 조회를 하였지만, 데이터는 항상 동일한 JuBal라는 결과가 나온다.

그 이유는 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하면서 10번이라는 트랜잭션 번호를 부여 받았는데, 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 자신의 트랜잭션인 10번보다 작은 트랜잭션 번호에서 변경한 데이터만 볼 수 있기 때문이다. 그래서 사용자 A의 12번 트랜잭션에서 변경한 데이터는 열람할 수 없다.

PHANTOM READ 발생

PHANTOM READ란, SELECT ... FOR UPDATE 쿼리와 같은 쓰기 잠금을 거는 경우 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안 보였다가 하는 현상을 말한다.

위 그림은 사용자 A가 employees 테이블에 INSERT를 실행하기 전과 후에 사용자 B가 SELECT ... FOR UPDATE 쿼리로 employees 테이블을 조회했을 때의 결과이다.

NON-REPEATABLE READ 문제 해결에서 설명한 것처럼 동일한 트랜잭션 내에서의 동일한 쿼리는 항상 같은 결과를 출력해야 한다. 그러나 위 그림에서 두 번의 SELECT ... FOR UPDATE 는 다른 결과를 보여 주고 있다. 그 이유는 SELECT ... FOR UPDATE 쿼리의 경우 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 영역에는 잠금을 걸 수 없기 때문이다. 따라서 어쩔 수 없이 SELECT ... FOR UPDATE 나 SELECT ... LOCK IN SHARE MODE 로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져온다.

InnoDB 스토리지 엔진에서 PHANTOM READ 해결

InnoDB 스토리지 엔진은 레코드 락과 갭 락을 합친 넥스트 키 락을 사용한다. t 테이블에 c1 = 13 , c = 17 인 두 레코드가 있다고 가정하자. 이때 SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE 쿼리를 수행하면, 10 <= c1 <= 12, 14 <= c1 <= 16, 18 <= c1 <= 20 인 영역은 전부 갭 락에 의해 락이 걸려서 해당 영역에 레코드를 삽입할 수 없다. 또한 c = 13, c = 17인 영역도 레코드 락에 의해 해당 영역에 레코드를 삽입할 수 없다. 참고로 INSERT 외에 UPDATE, DELETE 쿼리도 마찬가지이다.

이러한 방식으로 InnoDB 스토리지 엔진은 넥스트 키 락을 이용하여 PHANTOM READ 문제를 해결한다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다. InnoDB 테이블에서 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행되지만, 트랜잭션 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 한다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다. 따라서 SERIALIZABLE 격리 수준에서는 모든 부정합 문제가 발생하지 않는다. 그러나 동시 처리가 거의 불가능하므로 사용을 권장하지 않는다.

정리

READ UNCOMMITTED
- 한 트랜잭션의 변경된 내용을 커밋이나 롤백과 상관 없이 다른 트랜잭션에서 읽을 수 있는 격리 수준
- 모든 부정합 문제 발생
READ COMMITTED
- COMMIT이 완료된 데이터만 조회 가능한 격리 수준
- 더티 리드 해결
REPEATABLE READ
- 트랜잭션이 시작되기 전에 커밋된 내용에 관해서만 조회할 수 있는 격리 수준
- NON-REPEATABLE-READ 해결
- InnoDB에서는 PHANTOM READ 해결
SERIALIZABLE
- 한 트랜잭션을 다른 트랜잭션으로부터 완전히 분리하는 격리 수준
- 모든 부정합 문제 해결

출처

예상 면접 질문 및 답변

트랜잭션 격리 수준이란?

READ UNCOMMITTED (커밋되지 않은 읽기)
READ COMMITTED (커밋된 읽기)
REPEATABLE READ (반복 가능한 읽기)
SERIALIZABLE (직렬화 가능)

각 트랜잭션 격리 수준을 설명하라.

READ UNCOMMITTED
- 한 트랜잭션의 변경된 내용을 커밋이나 롤백과 상관 없이 다른 트랜잭션에서 읽을 수 있는 격리 수준
- 모든 부정합 문제 발생
READ COMMITTED
- COMMIT이 완료된 데이터만 조회 가능한 격리 수준
- 더티 리드 해결
REPEATABLE READ
- 트랜잭션이 시작되기 전에 커밋된 내용에 관해서만 조회할 수 있는 격리 수준
- NON-REPEATABLE-READ 해결
- InnoDB에서는 PHANTOM READ 해결
SERIALIZABLE
- 한 트랜잭션을 다른 트랜잭션으로부터 완전히 분리하는 격리 수준
- 모든 부정합 문제 해결

부정합 문제를 모두 설명하라

더티 리드
- 더티 리드란 다른 트랜잭션에서 처리한 작업이 완료되지 않았음에도 불구하고 다른 트랜잭션에서 볼 수 있게 되는 현상을 말한다.
NON-REPEATABLE READ
- NON-REPEATABLE READ란, 하나의 트랜잭션 내에서 동일한 SELECT 쿼리를 실행했을 때 항상 같은 결과를 보장해야 한다는 REPEATABLE READ 정합성에 어긋나는 것을 말한다.
PHANTOM READ
- PHANTOM READ란, SELECT ... FOR UPDATE 쿼리와 같은 쓰기 잠금을 거는 경우 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안 보였다가 하는 현상을 말한다.

부정합 문제를 해결하는 방법을 설명하라

더티 리드
- READ COMMITTED 격리 수준 이상을 사용한다.
- 언두 영역을 사용하여 커밋되기 이전 데이터만을 다른 트랜잭션에게 보여준다.
NON-REPEATABLE READ
- REPEATABLE READ 격리 수준 이상을 사용한다.
- 언두 영역에서 조회를 원하는 트랜잭션의 이전 트랜잭션에서 변화를 준 데이터만 보여준다.
PHANTOM READ
- SERIALIZABLE 격리 수준을 사용하거나, REPEATABLE READ 격리 수준을 사용하는 InnoDB 스토리지 엔진을 이용한다.

InnoDB 스토리지 엔진을 사용하면 왜 PHANTOM READ 문제가 발생하지 않는가?

이러한 방식으로 InnoDB 스토리지 엔진은 PHANTOM READ 문제를 해결한다.

저작자표시

'스터디 > CS 스터디' 카테고리의 다른 글

[데이터베이스] Connection Pool이란? (1)	2022.01.04
[데이터베이스] NoSQL - Why NoSQL (0)	2022.01.03
[데이터베이스] B-Tree 인덱스를 보편적으로 사용하는 이유 (0)	2021.12.29
[데이터베이스] MySQL 스토리지 엔진의 잠금 (0)	2021.12.27
[데이터베이스] NoSQL - What is NoSQL (0)	2021.12.26