더티리드와 논-리피터블 리드 그리고 팬텀리드: MySQL 을 중심으로

Shinsro·2023년 3월 3일

이 글에서는 트랜젝션 격리수준 (Isolation Level) 에 따라 발생할 수 있는 대표적인 현상들(phenomena)인 더티리드(Dirty Reads), 논-리피터블 리드(Non-Repeatable Reads), 팬텀리드 (Phantom Reads) 를 정리하고, 간단한 테스트 코드 예제를 통해 눈으로 확인합니다.

들어가기 앞서: 테스트 예제 사전작업

3가지 현상을 테스트해보기 전에, 테스트 환경을 공유합니다. 모든 테스트 코드는 이 링크 에서 확인하실 수 있습니다.

데이터베이스 준비

MySQL 은 미리 설치해두었고, 테스트 코드에 대한 특별한 버전이슈, 혹은 특이한 설정은 없습니다.

또한, 아래와 같이 데이터베이스와 계정을 생성해두었습니다.

create database read_phenomena_test;

create user 'shinsro'@'localhost';
create user 'karina'@'localhost';

grant all privileges on read_phenomena_test.* to 'shinsro'@'localhost';
grant all privileges on read_phenomena_test.* to 'karina'@'localhost';

언어, 라이브러리

테스트코드 예제에서 사용한 언어, 라이브러리는 아래와 같습니다.

언어 : kotlin 1.8 ( 일부 coroutines 사용 )
TEST 라이브러리 : Junit5, kotest
ORM 라이브러리 : exposed

테스트코드 사전준비

트랜젝션이 2개 이상 필요하기 때문에, 두 개 이상의 프로세스, 스레드 혹은 코루틴이 필요합니다. 저는 코루틴을 선택했고, 테스트 시나리오를 제어하기 위해 코루틴 Channel 클래스를 사용했습니다.

class TxScenario(
    val isEnd: Boolean = false,
    val statement: suspend Transaction.(username: String) -> Unit
)

각 트랜젝션이 수행할 행위를 scenario 라고 이름지었고, Channel 에 담길 메세지 클래스를 위와 같이 TxScenario 로 정의했습니다.

더티리드 (Dirty Reads)

더티리드란 "한 트랜젝션" 에서 "다른 트랜젝션" 이 커밋하지 않은 데이터를 읽는 현상을 말합니다. "다른 트랜젝션" 이 롤백된다면, 읽어드린 데이터는 더 이상 유효하지 않은 데이터 입니다. 따라서 이러한 현상은 데이터에 따라 크리티컬한 현상일 수 있습니다.

아래는 위키피디아의 ANSI/ISO 의 SQL-92 표준 상 더티리드의 시나리오 예시를 캡쳐한 이미지입니다.

출처: 위키피디아

나이정보가 age 인 테이블은 실제로 많이 없겠지만, 중요한 건 아니니 넘어가겠습니다.

Transaction_1 ( 이하 T1 ) 에서 users.id = 1 인 row 의 users.age 를 20 으로 읽습니다.
Transaction_2 ( 이하 T2 ) 가 당 row 의 age 를 21 로 대입하고, 커밋하지 않습니다.
T1 이 똑같은 쿼리를 수행했을 때, READ_UNCOMMITTED 에서는 T2 이 업데이트한 age, 21 을 읽었습니다.

READ_UNCOMMITTED 격리수준에서 T1 이 T2 가 커밋하지 않은 변경사항, age 21 을 읽었습니다.

Consistent Read 란

MySQL ( InnoDB 엔진 ) 은 READ_UNCOMMITTED 이외의 격리수준에서 트랜젝션을 열면, 스냅샷 ( aka. Read View, 멀티 버저닝 ) 정보를 기록합니다. 다른 트랜젝션이 원본 데이터를 변경했다면, Undo Log 를 바탕으로 변경된 데이터를 재구성해서, 스냅샷 기준 시점의 데이터를 읽을 수 있게끔 하기 위함입니다. 이 때, 스냅샷을 이용한 Read Operation 을 MySQL 에서는 Consistent Read 라 부르고 있습니다.

MySQL 에서의 테스트

간단한 테스트 코드를 통해 더티리드 현상을 눈으로 확인하겠습니다. Full Codes 링크

READ_UNCOMMITTED 의 더티리드

object Employees : IntIdTable() {
    val fullName = varchar("full_name", 30)
    val daysOff = integer("days_off")
}

...

"Dirty Reads 는 READ_UNCOMMITTED 격리수준에서만 발생한다" {

   	val shinsRoIsolation = Connection.TRANSACTION_READ_UNCOMMITTED
    val karinaIsolation = Connection.TRANSACTION_READ_COMMITTED
    
    ...
    
    val shinsRoInbound = Channel<TxScenario>()
    val karinaInbound = Channel<TxScenario>()
    
    val shinsRoJob = launch { ... }
	val karinaJob = launch { ... }
    
    ...
    
    // 1. 신스로가 신스로의 휴가일수를 조회
    shinsRoInbound.send(TxScenario { username ->
        val daysOff = Employees.findDaysOff(shinsRoFullname)

        daysOff shouldBeExactly 0
        println("[$username] $shinsRoFullname 의 휴가일수는 $daysOff 입니다.")
    })

    // 2. 카리나가 신스로의 휴가일수를 +1
    karinaInbound.send(TxScenario { username ->
        val daysOff = Employees.increaseDaysOff(shinsRoFullname, 1)

        daysOff shouldBeExactly 1
        println("[$username] $shinsRoFullname 의 휴가일수를 +1 했습니다.")
    })

    // 3. 신스로가 신스로의 휴가일수를 조회
    shinsRoInbound.send(TxScenario { username ->
        val daysOff = Employees.findDaysOff(shinsRoFullname)

        daysOff shouldBeExactly 1
        println("[$username] $shinsRoFullname 의 휴가일수는 $daysOff 입니다.")
    })

    shinsRoInbound.send(TxScenario(isEnd = true) { commit() })
    karinaInbound.send(TxScenario(isEnd = true) { commit() })
}

위키의 시나리오에서 users 테이블이 DirtyReadEmployeeTable 로 변경되었을 뿐, 본질적인 시나리오 흐름은 동일합니다. shinsRoInbound 와 karinaInbound 는 테스트 코드가 각각 shinsRoJob 과 karinaJob 에 명령하기 위한 Channel 입니다.

신스로가 신스로의 휴가일수를 조회합니다.
카리나가 신스로의 휴가일수를 1 만큼 증가시킵니다.
신스로가 신스로의 휴가일수를 다시 조회합니다.

결과는 아래와 같습니다.

[shinsro] Shins Ro 의 휴가일수는 0 입니다.
[karina] Shins Ro 의 휴가일수를 +1 했습니다.
[shinsro] Shins Ro 의 휴가일수는 1 입니다.
[shinsro] 트랜젝션 Commit.
[karina] 트랜젝션 Commit.

위키 상 시나리오처럼, karina 트랜젝션이 커밋하지 않은 update 내용을 읽어들였습니다. 과연 READ_UNCOMMITTED 의 이름대로입니다.

한편, 다른 격리수준은 이 더티리드가 일어나지 않는 것이 보장되어야합니다.

다른 격리수준에서의 더티리드

같은 테스트 코드에서, 격리수준만 변경하여 테스트를 진행해봅니다.

"Dirty Reads 는 READ_UNCOMMITTED 격리수준에서만 발생한다" {
    forAll(
        table(
            headers("신스로의 격리수준", "카리나의 격리수준"),
            row(Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED),
            row(Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_READ_COMMITTED),
            row(Connection.TRANSACTION_REPEATABLE_READ, Connection.TRANSACTION_READ_COMMITTED),
            
			
			// InnoDB 의 경우, SERIALIZABLE 에서 모든 select 가 select ... for share 로 치환됩니다.
			// lock base 로 다른 트랜잭션을 기다리기 때문에 더티리드가 발생하지 않음이 보장되니, 따로 테스트하진 않겠습니다.
			// row(Connection.TRANSACTION_SERIALIZABLE, Connection.TRANSACTION_READ_UNCOMMITTED)
        )
    ) { shinsRoIsolation, karinaIsolation ->
    	
        ...
        
        // 3. 신스로가 신스로의 휴가일수를 조회
        shinsRoInbound.send(TxScenario { username ->
            val daysOff = Employees.findDaysOff(shinsRoFullname)

            daysOff shouldBeExactly when (shinsRoIsolation) {
                Connection.TRANSACTION_READ_UNCOMMITTED -> 1
                Connection.TRANSACTION_READ_COMMITTED -> 0
                Connection.TRANSACTION_REPEATABLE_READ -> 0

                else -> throw NotImplementedError()
            }
            println("[$username] $shinsRoFullname 의 휴가일수는 $daysOff 입니다.")
        })


	    ...
    }

결과는 아래와 같습니다.

신스로의 격리수준이 READ_UNCOMMITTED 일 때는 아래와 같습니다.
[shinsro] Shins Ro 의 휴가일수는 0 입니다.
[karina] Shins Ro 의 휴가일수를 +1 했습니다.
[shinsro] Shins Ro 의 휴가일수는 1 입니다.
[shinsro] 트랜젝션 Commit.
[karina] 트랜젝션 Commit.

신스로의 격리수준이 READ_COMMITTED 일 때는 아래와 같습니다.
[shinsro] Shins Ro 의 휴가일수는 0 입니다.
[karina] Shins Ro 의 휴가일수를 +1 했습니다.
[shinsro] Shins Ro 의 휴가일수는 0 입니다.
[shinsro] 트랜젝션 Commit.
[karina] 트랜젝션 Commit.

신스로의 격리수준이 REPEATABLE_READ 일 때는 아래와 같습니다.
[shinsro] Shins Ro 의 휴가일수는 0 입니다.
[karina] Shins Ro 의 휴가일수를 +1 했습니다.
[shinsro] Shins Ro 의 휴가일수는 0 입니다.
[shinsro] 트랜젝션 Commit.
[karina] 트랜젝션 Commit.

신스로의 격리수준이 READ_UNCOMMITTED 일 때와는 달리, READ_COMMITTED 와 REPEATABLE_READ 에서는 Dirty Reads 가 일어나지 않는 것을 볼 수 있습니다.

한편, 제일 상단의 주석처럼 신스로의 격리수준이 SERIALIZABLE 인 경우, 카리나가 days_off 를 업데이트 하기 위해 신스로의 트랜젝션이 종료되길 기다립니다. 결과적으로 더티 리드가 일어나지 않음을 보장할 수 있는 셈입니다.

논-리피터블 리드 (Non-Repeatable Reads)

논-리피터블 리드란 트랜잭션 내에서 같은 select 에 대해 항상 같은 결과가 나오지 않는 현상입니다. 그의 역은 격리수준의 하나로 익숙한 리피터블 리드 ( REPEATABLE READ ) 입니다.

더티리드와 마찬가지로 위키의 사례를 컙쳐하여 보여드리겠습니다.

리피터블이란 단어가 어색하신가요?

SELECT 는 다시 수행할 수 있는데? 그럼 리피터블한 것 아니야? 할 수 있죠. Repeatable 은 결과가 반복적인 형태를 표현한 것이라고 생각하시면 될 것 같습니다. 같은 결과가 반복적으로 나타날 수 있느냐해서 리피터블이겠습니다. 차라리 reproducible 정도면 편했을텐데, 제가 영어권 사람이 아니다보니 표현이 어색하게 다가왔던 기억이 있네요.

T1 에서 users.id = 1 인 row 의 users.age 를 20 으로 읽습니다.
T2 가 당 row 의 age 를 21 로 대입하고, 커밋합니다.
T1 이 똑같은 쿼리를 수행했을 때, READ_UNCOMMITTED 와 READ_COMMITTED 에서는 age 를 T2 이 업데이트한 age 인 21 으로 읽고, REPEATABLE_READ 와 SERIALIZABLE 은 20 으로 읽었습니다.

MySQL 에서의 테스트

마찬가지로 테스트 코드를 현상을 재현해보겠습니다. Full Codes 링크

"READ_UNCOMMITTED, READ_COMMITTED 에서는 NonRepeatableReads 현상이 발생한다" {
    forAll(
        table(
            headers("신스로의 격리수준", "카리나의 격리수준"),
            row(Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED),
            row(Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_READ_COMMITTED),
            row(Connection.TRANSACTION_REPEATABLE_READ, Connection.TRANSACTION_READ_COMMITTED)
        )
    ) { shinsRoIsolation, karinaIsolation ->
        println("신스로의 격리수준이 ${connectionValueOf(shinsRoIsolation)} 일 때는 아래와 같습니다.")
        
        ...
        
        // 1. 신스로가 신스로의 휴가일수를 조회
        shinsRoInbound.send(TxScenario { username ->
            val daysOff = Employees.findDaysOff(shinsRoFullname)

            daysOff shouldBeExactly 0
            println("[$username] $shinsRoFullname 의 휴가일수는 $daysOff 입니다.")
        })

        // 2. 카리나가 신스로의 휴가일수를 +1 하고 커밋
        karinaInbound.send(TxScenario(isEnd = true) { username ->
            val daysOff = Employees.increaseDaysOff(shinsRoFullname, 1)
            commit()

            daysOff shouldBeExactly 1
            println("[$username] $shinsRoFullname 의 휴가일수를 +1 했습니다.")
        })

        karinaInbound.close()
        karinaJob.join()

        // 3. 신스로가 신스로의 휴가일수를 조회
        shinsRoInbound.send(TxScenario { username ->
            val daysOff = Employees.findDaysOff(shinsRoFullname)

            daysOff shouldBeExactly when (shinsRoIsolation) {
                Connection.TRANSACTION_READ_UNCOMMITTED -> 1
                Connection.TRANSACTION_READ_COMMITTED -> 1
                Connection.TRANSACTION_REPEATABLE_READ -> 0

                else -> throw NotImplementedError()
            }
            println("[$username] $shinsRoFullname 의 휴가일수는 $daysOff 입니다.")
        })

        shinsRoInbound.send(TxScenario(isEnd = true) { commit() })
        shinsRoInbound.close()
        
        ...
    }

신스로가 신스로의 휴가일수를 조회합니다.
카리나가 신스로의 휴가일수를 1 만큼 증가시키고 커밋합니다.
신스로가 신스로의 휴가일수를 다시 조회합니다.

결과는 아래와 같습니다.

신스로의 격리수준이 READ_UNCOMMITTED 일 때는 아래와 같습니다.
[shinsro] Shins Ro 의 휴가일수는 0 입니다.
[karina] Shins Ro 의 휴가일수를 +1 했습니다.
[shinsro] Shins Ro 의 휴가일수는 1 입니다.

신스로의 격리수준이 READ_COMMITTED 일 때는 아래와 같습니다.
[shinsro] Shins Ro 의 휴가일수는 0 입니다.
[karina] Shins Ro 의 휴가일수를 +1 했습니다.
[shinsro] Shins Ro 의 휴가일수는 1 입니다.

신스로의 격리수준이 REPEATABLE_READ 일 때는 아래와 같습니다.
[shinsro] Shins Ro 의 휴가일수는 0 입니다.
[karina] Shins Ro 의 휴가일수를 +1 했습니다.
[shinsro] Shins Ro 의 휴가일수는 0 입니다.

신스로 Tx 가 READ_UNCOMMITTED 와 READ_COMMITTED 일 때, 카리나 Tx에 의해 커밋된 days_off, 1 를 트랜젝션 중에 읽어들임을 볼 수 있습니다.

Dirty Reads 는 커밋되지 않은 ( 미확정된 ) 데이터를 읽는 현상이지만, Non-Repeatable Reads 에서 읽은 데이터는 모두 커밋된 사항인 점에서 특별한 차이가 있습니다.

사실, 정의만 봐서는 Non-Repeatable 이 Dirty Reads 의 상위호환처럼 해석할 수도 있겠지만, 이 단어 자체가 리피터블 리드 원칙을 깨는, 커밋된 데이터들에 대해 일어나는 현상을 지칭하기 위해 탄생한 단어입니다. Dirty Reads 와 Non-Repeatable 은 분리된 각 현상을 지칭하고 있으니, 소통에 유념하시기 바랍니다.

팬텀 리드 (Phantom Reads)

팬텀 리드란 같은 쿼리에 대한 결과 집합이 달라지는 현상입니다. 예를 들어 "한 트랜젝션" 이 특정 범위의 데이터를 조회할 때, "다른 트랜젝션" 이 그 범위 상 데이터를 삽입 혹은 삭제하면 두 번째 쿼리에서 결과 집합이 다를 수 있습니다.

아래는 위키의 사례입니다.

T1 에서 users.age > 17 인 row 들을 읽습니다.
T2 가 users.age == 26 인 Carol 을 인서트하고 커밋합니다.
T1 이 1) 과 똑같은 쿼리를 수행했을 때, SERIALIZABLE 이외의 격리수준에서 Carol row 를 읽어들였습니다.

MySQL 에서의 테스트

마찬가지로 테스트 코드를 현상을 재현해보겠습니다. Full Codes 링크

"RU, RC 격리수준에서 PhantomRead 를 확인할 수 있다" {
    forAll(
        table(
            headers("신스로의 격리수준", "카리나의 격리수준"),
            row(Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED),
            row(Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_READ_COMMITTED),
            row(Connection.TRANSACTION_REPEATABLE_READ, Connection.TRANSACTION_READ_COMMITTED)
        )
    ) { shinsRoIsolation, karinaIsolation ->
        println("신스로의 격리수준이 ${connectionValueOf(shinsRoIsolation)} 일 때는 아래와 같습니다.")
        
        transaction {
            Employees.replace { row ->
                row[Employees.id] = 3
                row[fullName] = "ShinsRo 2"
                row[daysOff] = 5
            }
            Employees.replace { row ->
                row[Employees.id] = 4
                row[fullName] = "ShinsRo 3"
                row[daysOff] = 5
            }
        }
        
        ...
        
        // 1. 신스로가 휴가를 3번 이상 사용한 직원을 검색
        shinsRoInbound.send(TxScenario { username ->
            val employees = Employees.findAllByDaysOffGte(3)

            employees.count() shouldBeExactly 2
            println("[$username] 휴가를 3 번 이상 사용한 직원 수는 ${employees.count()} 입니다.")
        })

        // 2. 카리나가 휴가를 5번 사용한 "ShinsRo 2" 를 삭제
        karinaInbound.send(TxScenario { username ->
            Employees.deleteWhere { fullName eq "ShinsRo 2" }
            println("[$username] \"ShinsRo 2\" 를 삭제했습니다.")
        })

        karinaInbound.send(TxScenario(isEnd = true) { commit() })
        karinaInbound.close()

        // 3. 신스로가 휴가를 3번 이상 사용한 직원을 검색
        shinsRoInbound.send(TxScenario { username ->
            val employees = Employees.findAllByDaysOffGte(3)

            employees.count() shouldBeExactly when (shinsRoIsolation) {
                Connection.TRANSACTION_READ_UNCOMMITTED -> 1
                Connection.TRANSACTION_READ_COMMITTED -> 1

                // 위키와는 다른 부분입니다.
                // MySQL 에서 REPEATABLE_READ 는 팬텀리드를 회피합니다!
                // (참고로 Phantom Write 현상은 존재합니다.)
                Connection.TRANSACTION_REPEATABLE_READ -> 2

                else -> throw NotImplementedError()
            }
            println("[$username] 휴가를 3 번 이상 사용한 직원 수는 ${employees.count()} 입니다.")
        })

        shinsRoInbound.send(TxScenario(isEnd = true) { commit() })
        shinsRoInbound.close()
        
        ...
    }

위키와는 달리, T2 에서 인서트가 아닌 삭제를 하여 팬텀리드를 확인합니다.

휴가를 3번 이상 사용한 직원 "ShinsRo 2", "ShinsRo 3" 의 2명을 추가
신스로가 휴가를 3번 이상 사용한 직원을 조회
카리나가 "ShinsRo 2" 를 삭제
신스로가 휴가를 3번 이상 사용한 직원을 조회

결과는 아래와 같습니다.

신스로의 격리수준이 READ_UNCOMMITTED 일 때는 아래와 같습니다.
[shinsro] 휴가를 3 번 이상 사용한 직원 수는 2 입니다.
[karina] "ShinsRo 2" 를 삭제했습니다.
[shinsro] 휴가를 3 번 이상 사용한 직원 수는 1 입니다.

신스로의 격리수준이 READ_COMMITTED 일 때는 아래와 같습니다.
[shinsro] 휴가를 3 번 이상 사용한 직원 수는 2 입니다.
[karina] "ShinsRo 2" 를 삭제했습니다.
[shinsro] 휴가를 3 번 이상 사용한 직원 수는 1 입니다.

신스로의 격리수준이 REPEATABLE_READ 일 때는 아래와 같습니다.
[shinsro] 휴가를 3 번 이상 사용한 직원 수는 2 입니다.
[karina] "ShinsRo 2" 를 삭제했습니다.
[shinsro] 휴가를 3 번 이상 사용한 직원 수는 2 입니다.

다른 격리수준에서는 팬텀리드를 확인할 수 있었지만, REPEATABLE_READ 에서는 위키와는 다르게 팬텀리드를 확인할 수 없었습니다. ( 참고로, 팬텀 write 는 발생합니다. ) 흥미롭습니다.

왜 MySQL 의 RR 격리수준에서는 팬텀리드 현상이 나타나지 않는가.

InnoDB 엔진이 MVCC ( Multi-Version Concurrency Control ) 을 사용하기 때문입니다. 앞서 언급한 Consistent Read 와 관련한 사항입니다. RR ( REPEATABLE_READ ) 수준에서 InnoDB 는 첫 셀렉트 문으로부터 스냅샷을 저장하고, Undo Log 를 참조하여 데이터를 다시 읽습니다. 하여 위 예제의 카리나 가 삭제한 "ShinsRo 2" 를 읽어드린 셈입니다.

끝으로: 정리

지금까지 확인해본 현상들의 정의를 정리하면 아래와 같습니다.

Dirty Reads : 다른 트랜젝션이 커밋하지 않은 데이터를 읽는 현상
Non-Repeatable Reads : 트랜잭션 내에서 같은 select 에 대해 항상 같은 결과가 나오지 않는 현상
Phantom Reads : 트랜잭션 내에서 같은 select 에 대한 결과 집합이 달라지는 현상

이상으로 트랜젝션 격리수준 (Isolation Level) 에 따라 발생할 수 있는 대표적인 현상 3가지를 살펴보고, 간단하게 테스트코드를 작성해보았습니다. 혹시 오류가 있다면, 편히 말씀 부탁드립니다, 정정하겠습니다.

읽어주셔서 감사합니다.

Shinsro

이전 포스트

더티리드와 논-리피터블 리드 그리고 팬텀리드: MySQL 을 중심으로

들어가기 앞서: 테스트 예제 사전작업

데이터베이스 준비

언어, 라이브러리

테스트코드 사전준비

더티리드 (Dirty Reads)

Consistent Read 란

MySQL 에서의 테스트

READ_UNCOMMITTED 의 더티리드

다른 격리수준에서의 더티리드

논-리피터블 리드 (Non-Repeatable Reads)

리피터블이란 단어가 어색하신가요?

MySQL 에서의 테스트

팬텀 리드 (Phantom Reads)

MySQL 에서의 테스트

왜 MySQL 의 RR 격리수준에서는 팬텀리드 현상이 나타나지 않는가.

끝으로: 정리

Python 으로 간단한 CLI 를 만드는 두가지 방법

0개의 댓글