[MySQL] InnoDB에서는 과연 Phantom Read 이상현상이 발생할까?

이상훈·2024년 12월 2일

CS

목록 보기

2/28

다음은 트랜잭션 격리 레벨에 따른 이상현상을 나타낸 표이다. 얼마 전까지는 이 표가 정확하다고 알고 있었으나, MySQL InnoDB에서는 아래 표와 다르게 동작할 수 있다는 사실을 알게 되었다.

	Dirty Read	Non Repeatable Read	Phantom Read
Read Uncommitted	O	O	O
Read Committed	X	O	O
Repeatable Read	X	X	O
Serializable	X	X	X

트랜잭션 격리 레벨

트랜잭션 격리 레벨이란 동시에 여러 트랜잭션이 실행될 때 트랜잭션끼리 서로 얼마나 고립되어 있는지 나타내는 것을 의미한다. 트랜잭션 격리 레벨은 격리 레벨이 낮은 단계부터 Read Uncommitted, Read Committed, Repeatable Read, Serializable가 존재한다.

Read Uncommitted

Read Uncommitted는 가장 낮은 격리 레벨이며 다른 트랜잭션이 커밋하지 않은 정보를 읽을 수 있다. Read Uncommitted 레벨은 Dirty Read, Non-Repeatable Read, Phantom Read 이상현상이 모두 발생해 정합성에 많은 문제가 있기에 거의 활용되지는 않는다. 간혹 로깅같은 데이터 정합성이 중요하지 않은 곳에서 사용하기도 한다.

Dirty Read 발생 O

사용자 A는 emp_no = 500000, first_name = "Lara"인 새로운 사원을 insert한다.
사용자 B는 커밋되지 않은 "Lara" 사원을 조회한다.
사용자 A가 롤백 된다고 하더라도 사용자 B는 "Lara"가 정상적인 사원이라 여긴다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 Dirty Read라 한다. Dirty Read는 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 개발자와 사용자를 상당히 혼란스럽게 만든다.

Read Committed

Read Committed는 커밋 완료된 데이터에 대해서만 조회할 수 있으며 커밋 되지 않은 정보는 읽지 못한다. PostgreSQL, SQL Server, Oracle에서 기본값으로 설정되어 있다. 가장 많이 사용되는 격리 레벨이다. Read Committed 레벨에서는 Dirty Read는 발생하지 않지만, Non-Repeatable Read, Phantom Read 이상현상은 발생한다.

Dirty Read 발생 X

사용자 A가 emp_no = 500000인 사원의 first_name을 "Toto"로 변경하면 "Toto"는 employees 테이블에 즉시 기록되고 이전 값인 "Lara"는 언두 영역에 백업된다.
사용자 B가 emp_no = 500000인 사원을 조회하면 언두 영역에서 "Lara"를 조회한다.
사용자 A가 커밋을 하면 언두 영역의 데이터는 삭제된다.

언두 영역 덕분에 Read Committed에서는 Dirty Read가 발생하지 않는다. 이처럼 언두 영역을 활용해 여러개의 버전을 관리하는 것을 MVCC라 한다.

📌 MVCC(Multi-Version Concurrency Control) : 데이터베이스에서 동시성을 제어하기 위해 사용하는 방법으로 새로운 값을 업데이트하면 이전 값은 언두(UNDO) 영역에 관리함으로써 하나의 레코드에 대해 여러 개의 버전을 동시에 관리하는 기법을 의미한다. MySQL에서 Read Committed와 Repeatable Read는 MVCC로 동작한다.

Non-Repeatable Read 발생 O

사용자 B가 first_name = "Toto"를 조회하면 결과는 없다.
사용자 A가 Lara의 first_name을 "Toto"로 변경하고 커밋한다.
사용자 B는 first_name = "Toto"를 조회하면 1건이 조회된다.

Read Committed 레벨에서는 하나의 트랜잭션 내에서 똑같은 select 쿼리를 실행했을 때 다른 결과가 나타나는 Non-Repeatable Read 이상현상이 발생한다.

Repeatable Read

Repeatable Read는 커밋 완료된 데이터에 대해서만 조회할 수 있으며 반복해서 행을 조회하더라도 똑같은 행을 보장하는 단계이다. 이는 MySQL8.0의 innoDB 기본값이다. Repeatable Read 레벨에서는 Dirty Read, Non-Repeatable Read 이상현상은 발생하지 않으나 Phantom Read 관련해서는 일반적인 RDBMS와 MySQL InnoDB가 서로 다르게 동작한다.

Non-Repeatable Read 발생 X

사용자 B가 emp_no = 500000를 조회하면 Lara가 반환된다.
사용자 A가 emp_no = 500000인 사원의 이름을 Toto로 바꾸고 커밋한다.
사용자 B가 emp_no = 500000를 조회하면 Lara가 반환된다.

Repeatable read에서 각각의 트랜잭션은 순차 증가하는 고유의 트랜잭션 번호가 존재하며, 해당 번호를 통해 어느 트랜잭션에 의해 쓰였는지 알 수 있다. 그리고 트랜잭션 번호를 참고하여 자신보다 먼저 실행된 트랜잭션의 데이터만을 조회한다. 만약 테이블에 자신보다 이후에 실행된 트랜잭션의 데이터가 존재한다면 언두 로그를 참고해서 데이터를 조회한다. 따라서 사용자 B는 실제 테이블이 아닌 언두 로그를 통해 데이터를 조회하므로 Non repeatable read 이상 현상이 발생하지 않는다.

Phantom Read

여기서부터가 정말 중요하다. Phantom Read는 일반적인 RDBMS와 MySQL의 InnoDB가 서로 다르게 동작한다. 아래의 4가지 경우에 대해 알아보자.

Case 1 : Select ... Select
Case 2 : Select for update ... Select
Case 3 : Select for update ... Select for update
Case 4 : Select ... Select for update

Case 1 : Select ... Select : RDBMS, MySQL InnoDB

사용자 B가 id>=2를 조회하면 1건이 반환된다.
사용자 A가 id=3인 데이터를 삽입하고 커밋한다.
사용자 B가 id>=2를 조회하면 1건이 반환된다.

RDBMS, MySQL InnoDB 모두 MVCC로 동작해서 자신보다 늦게 실행된 사용자 A의 트랜잭션 연산을 반영하지 않기에 Phantom Read 이상현상이 발생하지 않는다.

Case 2.1 : Select for update ... Select : RDBMS

사용자 B 쪽에서 id=2인 레코드에 락이 걸리고 1건이 반환된다.
사용자 A 쪽에서 id=3인 데이터를 삽입하고 커밋한다.
사용자 B 쪽에서 id>=2를 조회하면 1건이 반환된다.

사용자 B 쪽에서 id=2인 레코드에 락이 걸린다. 하지만 Case 1과 마찬가지로 MVCC로 동작하기 때문에 Phantom Read 이상현상이 발생하지 않는다.

Case 2.2 : Select for update ... Select : MySQL InnoDB

사용자 B 쪽에서 id>=2인 레코드에 넥스트 키락(갭락 + 레코드락)을 걸고 1건이 반환된다.
사용자 A 쪽에서 id=3인 데이터를 삽입하려 하지만 갭락이 걸려있기에 대기 상태가 된다.
사용자 B 쪽에서 id>=2를 조회하면 1건이 반환된다.

MySQL InnoDB에서는 넥스트 키락이 존재하기 때문에 Phantom Read가 발생하지 않는다.

Case 3.1 : Select for update ... Select for update : RDBMS

사용자 B 쪽에서 id=2인 레코드에 락이 걸리고 1건이 반환된다.
사용자 A 쪽에서 id=3인 데이터를 삽입하고 커밋한다.
사용자 B 쪽에서 id>=2인 데이터를 조회하면 2건이 반환된다.

Select for update는 언두 로그가 아닌 실제 테이블에서 데이터를 조회한다. 즉 MVCC로 동작하지 않기 때문에 새로운 데이터 moomin3가 조회되는 Phantom Read 이상현상이 발생한다.

Case 3.2 : Select for update ... Select for update : MySQL InnoDB

사용자 B 쪽에서 id>=2인 레코드에 넥스트 키락(갭락 + 레코드락)을 걸고 1건이 반환된다.
사용자 A 쪽에서 id=3인 데이터를 삽입하려 하지만 갭락이 걸려있기에 대기 상태가 된다.
사용지 B 쪽에서 id>=2를 조회하면 1건이 반환된다.

어차피 갭락이 걸려서 사용자 A쪽에서 데이터를 삽입하지 못한다. 따라서 Phantom Read 이상현상이 발생하지 않는다.

Case 4.1 : Select ... Select for update : RDBMS, MySQL InnoDB

사용자 B가 id>=2를 조회하면 1건이 반환된다.
사용자 A가 id=3인 데이터를 삽입하고 커밋한다.
사용자 B가 id>=2를 조회하면 2건이 반환된다.

Select for update는 언두 로그가 아닌 실제 테이블에서 데이터를 조회한다. 따라서 RDBMS, MySQL InnoDB 모두 Phantom Read 이상현상이 발생한다.

이상으로 Repeatable Read 레벨에서 각 case들을 정리하면 아래와 같다.

Phantom Read 발생 여부	일반적인 RDBMS	MySQL InnoDB
Select ... Select	X	X
Select for update ... Select	X	X
Select for update ... Select for update	O	X
Select ... Select for update	O	O

💡 일반적으로 MySQL InnoDB를 사용하면 Phantom Read는 넥스트키락으로 인해 발생하지 않는다. 하지만 Select ... Select for update로 조회하는 경우 드물게 Phantom Read가 발생한다. 그런데 이러한 케이스는 거의 존재하지 않는다고 한다.

주의사항 1 : 트랜잭션 외부의 Select

Read Committed 레벨에서는 커밋이 완료된 데이터만 조회하기 때문에 트랜잭션 외부의 Select와 내부의 Select는 차이가 없다.

하지만 Repeatable Read 격리 레벨에서는 차이가 발생한다. 트랜잭션 내부의 Select는 항상 같은 결과를 보장해야 하는 Repeatable read 정합성을 보장받지만, 트랜잭션 외부의 Select는 정합성을 보장받지 못하기에 다른 트랜잭션이 데이터를 변경하게 되면 Select마다 다른 결과가 나타날 수 있다.

주의사항 2 : 성능

흔히 Read Uncommitted, Read Committed, Repeatable Read, Serializable 레벨로 갈수록 정합성은 높아지고 성능은 떨어진다고 생각하기 쉽다.

하지만 Read Committed와 Repeatable Read는 성능이 비슷하다. 어차피 MVCC로 동작해 언두 영역에서 데이터를 조회하기 때문이다.

Serializable

Serializable는 트랜잭션을 순차적으로 진행시키는 것을 의미한다. 여기서는 모든 Select 문에 Select for share가 걸린다. 따라서 Serializable에서는 동시 처리 성능이 많이 떨어지지만 어떠한 이상현상도 발생하지 않는다. 보통은 Serializable 대신 기본 격리 수준(Read Committed, Repeatable Read)을 사용하면서 개발자가 직접 동시성 처리를 하여 데이터 정합성을 보장하는 편이다.