[데이터베이스][MySQL] 트랜잭션 격리 수준과 MySQL 동작 방식
데이터베이스를 활용한 애플리케이션을 개발할 때, 동시에 여러 사용자가 같은 데이터를 읽고 쓰는 상황은 흔하게 발생한다.
이러한 동시성 환경에서 데이터의 정합성을 유지하려면 트랜잭션의 격리 수준을 적절히 설정하는 것이 중요하다. 트랜잭션 격리 수준은 하나의 트랜잭션이 다른 트랜잭션의 작업에 어느 정도까지 영향을 받지 않도록 보호할지를 정의하는 기준이다.
격리 수준이 낮을수록 성능은 높아질 수 있지만, 동시에 데이터 이상 현상에 취약해진다. 이 글에서는 MySQL을 기준으로 트랜잭션 격리 수준의 종류와 각 격리 수준에서 발생할 수 있는 문제를 정리한다.
트랜잭션 격리 수준
트랜잭션 격리 수준은 하나의 트랜잭션 내에서 실행되는 명령문들이 어떤 데이터를 볼 수 있는지를 결정한다.
다시 말해, 여러 트랜잭션이 동시에 실행되는 상황에서 한 트랜잭션이 데이터를 읽거나 수정할 때, 다른 트랜잭션이 그 데이터에 어떻게 접근할 수 있는지를 정의하는 개념이다.
트랜잭션 이상 현상
여러 트랜잭션이 동시에 실행될 때 데이터의 불일치 또는 부정확한 조회가 발생할 수 있다. 대표적인 이상 현상은 다음 세 가지이다.
Dirty Read
Dirty Read는 아직 커밋되지 않은 다른 트랜잭션의 데이터를 읽는 현상이다.
커밋되지 않은 값은 언제든지 롤백될 수 있으므로 불안정하며, 데이터 정합성을 심각하게 훼손할 수 있다.
Non-Repeatable Read
Non-Repeatable Read는 한 트랜잭션 내에서 같은 데이터를 두 번 읽었을 때, 그 사이에 다른 트랜잭션이 값을 변경해 결과가 달라지는 현상이다.
예를 들어 동일한 SELECT 문을 두 번 수행하는 도중 다른 트랜잭션이 해당 레코드 값을 UPDATE하면, 첫 번째 조회와 두 번째 조회의 결과가 달라질 수 있다.
Phantom Read
Phantom Read는 같은 조건의 SELECT 쿼리를 한 트랜잭션 안에서 두 번 이상 실행했을 때, 각 SELECT 문 실행 시점 사이에 다른 트랜잭션이 데이터를 삽입하거나 삭제하여 결과 행 개수가 달라지는 현상이다.
SQL 표준의 트랜잭션 격리 수준
트랜잭션 격리 수준(Isolation Level)이란 데이터베이스에서 여러 트랜잭션이 동시에 실행될 때 각 트랜잭션이 다른 트랜잭션으로부터 얼마나 격리되어 독립적으로 실행되는지를 결정하는 기준이다.
트랜잭션은 원자성(Atomicity), 일관성(Consistency), 고립성(Isolation), 지속성(Durability)의 ACID 특성을 만족해야 한다. 이 중 격리성(Isolation)은 하나의 트랜잭션이 진행 중일 때 다른 트랜잭션의 중간 결과에 영향을 받지 않아야 한다는 의미이다.
하지만 완벽한 격리를 보장하면 동시성(concurrency)이 떨어지고 성능 저하가 발생할 수 있다. 따라서 격리 수준을 선택적으로 조절하여 성능과 정합성 사이의 균형을 맞추는 것이 중요하다.
SQL 표준에서는 다음 네 가지 격리 수준을 정의한다.
- Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable
각 격리 수준은 허용되는 트랜잭션 이상 현상, 즉 Dirty Read, Non-Repeatable Read, Phantom Read의 종류에 따라 구분된다. 격리 수준이 높을수록 동시성은 낮아지고 일관성은 강해진다.
Read Uncommitted
Read Uncommitted는 커밋되지 않은 트랜잭션의 변경 사항에도 접근할 수 있는 격리 수준이다.
커밋 또는 롤백되지 않은 트랜잭션에서 수정 중인 데이터에도 접근할 수 있으므로 Dirty Read가 발생한다.
데이터 정합성 문제가 크기 때문에 대부분의 상용 DBMS에서는 잘 사용되지 않는다.
Read Committed
Read Committed는 커밋된 데이터만 읽을 수 있는 격리 수준이다.
다른 트랜잭션에서 커밋 또는 롤백이 완료되어 데이터베이스에 영구히 반영된 데이터만 읽는다. 따라서 Dirty Read는 발생하지 않는다.
다만 Non-Repeatable Read와 Phantom Read는 발생할 수 있다.
MySQL의 구현 방식
MySQL은 READ COMMITTED 이상의 격리 수준을 구현하기 위해 MVCC(Multi-Version Concurrency Control)를 사용한다.
Read Committed는 대략 다음과 같이 동작한다.
- 트랜잭션 T1이 시작되고 데이터 A를 수정한다.
- MySQL은 우선 테이블을 갱신하고, 언두 로그(Undo Log)에 변경되기 이전의 데이터를 저장한다.
- 이때
UPDATE연산의 경우 변경이 발생한 컬럼의 이전 값을 PK와 함께 저장한다. - 트랜잭션 T2가 데이터 A를 조회하면 테이블의 최신 값이 아니라, 언두 로그에 저장된 데이터 A의 변경 전 내용을 읽어 반환한다.
- 트랜잭션 T1이 롤백되면 언두 로그를 역순으로 탐색하며 변경 사항을 테이블에 복원한다.
Repeatable Read
Repeatable Read는 Read Committed에서 발생할 수 있는 Non-Repeatable Read를 해결하기 위한 격리 수준이다.
MySQL의 기본 격리 수준은 REPEATABLE READ이다. MySQL은 반복 가능한 읽기를 구현하기 위해 언두 로그와 MVCC를 사용한다.
MySQL에서의 구현 방식
MySQL의 Repeatable Read에서는 언두 영역에 백업된 레코드의 여러 버전 중 특정 버전에 해당하는 데이터를 찾을 수 있다.
InnoDB에서 모든 트랜잭션은 순차적으로 증가하는 고유한 값인 Transaction ID를 가진다. 언두 영역에 백업된 레코드에는 해당 트랜잭션의 고유 번호가 함께 저장된다.
이를 통해 실행 중인 가장 오래된 트랜잭션보다 이후에 변경된 레코드는 언두 영역에 보존된다. 그리고 해당 트랜잭션의 조회문에는 백업된 특정 버전의 레코드를 반환함으로써 Repeatable Read를 구현한다.
핵심 개념은 다음과 같다.
- Oldest Active TXID: 현재 실행 중인 트랜잭션 중 가장 오래된 트랜잭션의 ID이다.
- 보존 대상 언두 로그: Oldest Active TXID 이후 트랜잭션이 변경한 레코드의 이전 버전이다.
- 정리 가능 언두 로그: 더 이상 어떤 활성 트랜잭션의 스냅샷에도 필요하지 않은 이전 버전이다.
보존이 필요한 이유는 실행 중인 트랜잭션이 시작 시점의 데이터 스냅샷을 유지해야 하기 때문이다. 낮은 TXID의 언두 레코드는 해당 스냅샷을 재구성하는 데 필요하다. 또한 트랜잭션 실패 시 복구에도 사용된다.
트랜잭션이 길어질수록 해당 언두 로그 영역은 계속 증가한다.
| 트랜잭션 ID | 시작 시각 | 조회 시도 시각 | 언두 영역의 보존 레코드 버전 | 조회 결과 | 설명 |
|---|---|---|---|---|---|
| T1 (Oldest) | 10:00 | 10:02 | R1 (v1) | R1 (v1) | T1은 실행 중이며, 조회 시점에 변경 전 버전인 v1을 확인한다. |
| T2 | 10:01 | - | R1 (v2) | - | T2가 R1을 v2로 수정한다. T1보다 늦게 시작했으므로 v1이 보존된다. |
| T3 | 10:03 | 10:03 | R1 (v2) | R1 (v2) | T3은 T2 이후 시작했으므로 v2를 조회한다. |
T1은 가장 오래된 트랜잭션이다. T2가 R1을 수정해 v2로 변경했더라도, T2는 T1보다 나중에 시작된 트랜잭션이므로 T1은 언두 영역에 보존된 v1 버전을 조회한다.
반대로 T3는 T2 이후 시작되었으므로 최신 버전인 v2를 조회한다.
즉, 나중에 실행된 트랜잭션이 레코드를 수정하더라도, 먼저 시작된 트랜잭션은 언두 로그에 저장된 이전 버전을 조회한다. 이를 통해 Repeatable Read 수준에서 동일 트랜잭션 내 일관된 읽기를 보장한다.
이후 불필요해진 언두 로그의 백업 데이터는 백그라운드 스레드인 퍼지(purge) 스레드가 주기적으로 삭제한다.
일반적인 조회 상황에서 Phantom Read를 어떻게 예방할까?
MySQL에서 일반적인 조회는 MVCC를 이용하여 Phantom Read를 방지한다.
일반적인 조회에서 변경된 레코드에 대한 질의는 언두 로그에서 수행된다. 또한 해당 트랜잭션 번호보다 이후에 추가된 레코드를 읽지 않으면, 한 트랜잭션 내에서 반복된 질의에 대해 Phantom Read를 예방할 수 있다.
| 시점 | 트랜잭션 | 설명 |
|---|---|---|
| 10:00 | T1 | WHERE age > 20 조건으로 조회한다. 결과는 3명이다. |
| 10:01 | T2 | age = 25인 새로운 사용자를 INSERT한다. |
| 10:02 | T1 | 다시 WHERE age > 20 조건으로 조회한다. 여전히 3명이다. T2의 INSERT는 보이지 않는다. |
여기서 의문이 생길 수 있다. 변경된 레코드에 대한 질의는 언두 로그에서 수행된다고 했는데, INSERT는 기존 레코드를 변경하는 것이 아니다. 그렇다면 왜 Repeatable Read에서 새로 삽입된 레코드가 보이지 않는가?
MySQL의 InnoDB 엔진은 Repeatable Read에서 트랜잭션이 시작될 때 스냅샷을 하나 만든다.
즉, 특정 트랜잭션 시작 시점의 데이터베이스 상태를 스냅샷으로 고정한다. 트랜잭션이 시작되는 순간 다음 정보가 저장된다.
- 어떤 트랜잭션들이 이미 커밋되어 있었는지
- 어떤 레코드들이 존재했는지
그리고 트랜잭션 내 모든 일반 SELECT는 이 스냅샷 기준으로만 동작한다.
T1이 시작되는 시점에서 생성된 스냅샷에는 T2가 INSERT한 age = 25인 사용자가 존재하지 않는다. 따라서 T1이 아무리 다시 조회를 시도하더라도 T1의 스냅샷은 변하지 않기 때문에 Phantom Read는 발생하지 않는다.
잠금이 사용되는 상황에서 Phantom Read가 발생할까?
MySQL은 잠금이 사용되는 상황에서 Next-Key Lock을 통해 Phantom Read를 방지한다.
MySQL에서 잠금은 기본적으로 레코드 단위로 수행된다. 다른 DBMS와 다른 점은 두 레코드 사이의 간격을 잠그는 갭 락(Gap Lock)이 존재한다는 점이다.
일반적인 DBMS에서 다음 쿼리를 실행한다고 가정한다.
SELECT *
FROM user
WHERE pk >= 50
FOR UPDATE;FOR UPDATE는 쓰기 락, 즉 배타적 락을 사용한다. 잠금 대상 레코드에 대한 다른 쿼리는 읽기와 쓰기가 불가능하며 대기하게 된다.
일반적인 DBMS에는 갭 락이 존재하지 않으므로 pk = 50인 레코드에만 배타적 락을 설정한다. 다른 트랜잭션에서 pk > 50인 레코드를 추가할 경우, 해당 범위에는 락이 존재하지 않으므로 즉각적인 INSERT가 수행된다.
이후 두 번째 조회에서는 새로 삽입된 레코드가 결과에 포함되어 Phantom Read가 발생한다.
즉, SELECT ... FOR UPDATE가 조건에 맞는 기존 레코드에만 락을 설정한다면, 조건 범위 내에 아직 존재하지 않는 새로운 레코드의 삽입까지 막지 못한다.
MySQL은 이를 Next-Key Lock으로 해결한다.
Next-Key Lock = 해당 레코드 자체를 잠그는 레코드 락(Row Lock) + 레코드 범위의 앞뒤를 잠그는 갭 락(Gap Lock)
예를 들어 다음과 같은 데이터가 있다고 가정한다.
-- id = [10, 20, 30]
SELECT *
FROM users
WHERE id >= 20
FOR UPDATE;
-- 잠금 범위: 20 ~ ∞Next-Key Lock은 대상 레코드와 레코드 범위를 쓰기 잠금(X Lock)으로 통제한다. 따라서 다른 트랜잭션의 변경 쿼리는 대기하게 된다.
SELECT ... FOR UPDATE 수행 후 트랜잭션을 종료하지 않으면 해당 락은 그대로 유지된다. 다른 트랜잭션이 락 범위에 대한 연산을 수행할 수 없기 때문에 Phantom Read는 발생하지 않는다.
다만 인덱스가 중요하다. 위 쿼리를 실행할 때 테이블에 적절한 인덱스가 설정되어 있지 않다면 MySQL은 테이블 풀스캔을 수행할 수 있고, 락 범위가 의도와 다르게 넓어질 수 있다.
인덱스가 존재하는 경우 InnoDB는 삽입 전 갭 락 존재 여부를 확인한다. 따라서 락 범위에 해당하는 INSERT 쿼리는 대기하거나 실패한다.
MySQL에서 Phantom Read가 발생할 수 있는 상황
대부분의 일반적인 경우 MySQL은 MVCC를 이용하여 Phantom Read를 방지한다.
하지만 다음과 같이 일반 SELECT와 잠금 읽기인 SELECT ... FOR UPDATE를 섞어 사용하면 Phantom Read처럼 보이는 상황이 발생할 수 있다.
- 트랜잭션 T1이 일반
SELECT로 조회한다. - 트랜잭션 T2가
INSERT를 수행하고 커밋한다. - 트랜잭션 T1이 다시
SELECT ... FOR UPDATE를 수행한다.
SELECT ... FOR UPDATE를 통한 배타적 락 읽기는 스냅샷의 이전 버전을 읽지 않고, 현재 테이블에 락을 걸고 직접 읽는다. 이 때문에 처음 일반 SELECT에서는 보이지 않던 레코드가 이후 잠금 읽기에서는 보일 수 있다.
-- 트랜잭션 T1
START TRANSACTION;
SELECT *
FROM user
WHERE age > 30;
-- MVCC 스냅샷 기준으로 3명이 조회된다.
-- 트랜잭션 T2
START TRANSACTION;
INSERT INTO user (name, age)
VALUES ('김예원', 23);
COMMIT;
-- 트랜잭션 T1
SELECT *
FROM user
WHERE age > 30
FOR UPDATE;
-- 현재 테이블을 직접 조회하므로 4명이 조회될 수 있다.비관적 락을 이용하는 것은 데이터베이스의 동시성에 큰 영향을 줄 수 있다. 해당 레코드를 조회하거나 변경하려는 다른 쿼리가 배타적 락 때문에 대기할 수 있으므로 신중하게 사용해야 한다.
Serializable
Serializable은 가장 높은 수준의 격리 수준이며, 모든 트랜잭션을 순차적으로 실행한다.
정확히는 같은 데이터에 접근하는 모든 트랜잭션을 순차적으로 실행시킨다.
이 경우 동일한 데이터에 대해 여러 트랜잭션이 동시에 접근할 수 없으므로 이상 현상이 발생하지 않는다.
그러나 동일 데이터에 접근하는 모든 트랜잭션을 순차적으로 실행시키기 때문에 동시성 성능이 크게 저하된다.