Phantom Read 부정합문제 해결방안 In Mysql

이번 포스팅에서는 Mysql에서 Phantom Read 부정합 문제를 어떻게 해결하고 있는지에 대해서 알아보도록 하겠습니다. 

 

굳이 Mysql에서 라고 글을 쓴 이유는 공식문서를 여러개 찾아보던 중에 PostgreSQL과 Oracle 에선 다른 방식으로 이 Phantom Read 부정합 문제를 해결했다는 듯이 쓴 글을 몇개 봤기 때문입니다. 

 

이 부분이 저를 자극했습니다.

PostgresSQL은 얼마나 smoothly 하게 이슈를 해결했는지 꼭 알고싶어서 말이죠

 

따라서 이번 포스팅에서는 우리가 흔히 접하게 되는 Mysql에서 Phantom Read를 어떻게 해결했는지에 대해서 알아보겠습니다. 

 

 

Phantom Read란?

격리 수준에 대해서 포스팅할 때나, @Transactional 에 대한 포스팅을 할 때 잠깐잠깐 등장하고 아주 간략하게 정리했지만 이렇게 정식적으로 설명한적이 없어서 Phantom Read에 대해서 짚고 넘어가도록 하겠습니다. 

 

Phantom Read는 말 그대로 Phantom 즉 유령처럼 레코드가 보였다 안보였다 하는 상황을 말합니다. 

 

격리수준에 대해서 그리고 부정합 문제에 대해서 아시고 계신분은 조금 의아할 수도 있습니다. 

 

왜냐하면 Phantom Read 와 Non-Repeatable Read 부정합 문제는 비슷하면서도 약간 다르거든요.

 

둘 다 같은 쿼리에서 같은 결괏값을 받지 못한다는 공통점이 있지만 Non-Repeatable Read는 re-query 즉 쿼리를 다시 실행했을 때이고 Phantom Read는 re-execute 즉 재실행했을 때입니다. 

 

그러니까 "실행을 여러번 할때마다 레코드가 보였다 안보였다 하는 상황"으로 기억해주시면 좋을 것 같습니다. 

 

 

Mysql에서 Phantom Read를 해결하는 방법

우선 Mysql에서 Phantom Read를 해결하기 위한 방법을 알기 전에 우리는 Mysql에서 구현하고 있는 Lock에 대해서 짚고 넘어가야 합니다. 

 

공식문서를 찾아보면 뭐...여러가지 Lock이 보이는데 그 중 우리가 핵심적으로 알아야 하는 것은 이 네가지입니다.

 

• Shared Locks / Exclusive Locks
• Record Locks
• Gap Locks
• Next-Key Locks

 

차례대로 하나씩 설명해드리겠습니다. 

 

Shared Locks / Exclusive Locks

해석해보면 공유된 락, 독점적인 락 이렇게 볼 수 있는데 이름이 어느정도 이해를 위해 도움이 됩니다. 머리속에 잘 기억하고 계시고 이해해보시면 좋을 것 같습니다. 

 

InnoDB(=Mysql)에서는 기본적으로 row-level lock을 구현하고 있습니다. 그리고 두가지 기본적인 Lock이 있는데요 바로 Shared Lock, Exclusive Lock 입니다. 공식문서에서 나와있듯이 우리도 Shared Lock은 (s)Lock으로 Exclusive Lock은 (x)Lock으로 표현하도록 하겠습니다. 

 

(s)Lock은 행을 읽기위한 Lock을 획득한 트랜잭션을 허용해줍니다. 이 말은 (s)Lock은 행을 읽기위해 사용되는 Lock입니다. 로 해석될 수 있습니다. 

 

(x)Lock은 업데이트와 삭제를 위해 사용되는 Lock입니다. 

 

만약 행 R에 대하여 트랜잭션 T1이 (s)Lock을 획득했다고 가정해봅시다. 그리고 나서 다른 트랜잭션인 T2가 행R에 대해 Lock을 요청하면 다음과 같은 절차를 따릅니다. 

 

• T2의 요청이 (s)Lock인 경우 Lock을 즉시 획득할 수 있으며 행R에 대해 동시에 Lock을 잡고있을 수 있습니다.
• T2의 요청이 (x)Lock인 경우 Lock을 즉시 획득할 수 없습니다. = Lock이 풀릴때까지 기다려야 합니다.

이렇게 특징을 놓고보니 왜 Shared인지 Exclusive인지 대충 아실겁니다. (s)Lock인 경우 Lock을 두 트랜잭션이 공유할 수 있으며, (x)Lock인 경우 하나의 트랜잭션만이 독점적으로 Lock을 획득할 수 있기 때문입니다. 

 

자 여기까지 이 글을 이해하기위한 기본적인 내용인 (s)Lock과 (x)Lock에 대한 내용입니다. 다음은 비교적 간단한 Record Locks에 대해서 알아보죠

 

 

Record Locks

Record Lock은 인덱스 레코드를 잠그는 것을 의미합니다. 예를 들어서 아래와 같은 쿼리가 날아갔다고 가정해보죠

 

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE

 

이 경우 값이 t.c1 = 10인 행에 대한 삽입, 삭제, 수정에 대한 다른 트랜잭션의 접근을 막습니다. 참 쉽죠?

 

이 Record Lock은 한가지 특징이 있는데요. Record Lock은 항상 인덱스 레코드에서 Lock을 획득한다는 것입니다. 

 

인덱스에 대해서 조금 생소하실 수도 있어서 잠깐 막간을 이용해 설명해보도록 하겠습니다. 

 

인덱스

인덱스는 쉽게 말해서 데이터베이스 검색 성능을 향상시키기 위한 데이터베이스 튜닝 방법 중 하나입니다. 하지만 튜닝이 되는건 읽기작업에 한해서이고 삽입, 수정, 삭제에 대한 성능은 더 떨어집니다. 

 

이렇게 성능 차이가 나는 이유는 인덱스를 구성하기 위한 비용 즉 UPDATE, DELETE, INSERT 쿼리를 날리기 위한 인덱스 구성 비용 즉 추가 연산이 들어갑니다. 

 

그리고 추가적으로 인덱스는 인덱스를 유지하기위해 데이터베이스의 10퍼센트가량되는 저장공간을 추가적으로 확보해줘야 합니다. 

 

음...말하다보니 단점만 얘기했는데 이 단점을 뒤집는 아주 강력한 무기가 있습니다. 

 

바로 검색성능이 미친듯이 향상된다는 것이죠. 제가 아직 경험은 없지만 듣기로는 실무에서 검색이 80~90퍼센트정도를 차지하고 나머지 삽입, 수정, 삭제가 10~20퍼센트정도를 차지한다고 알고 있습니다. 

 

자 그러니까 이 Record Lock은 이 인덱스가 적용된 행에 대해서만 Lock이 일어난다는 것입니다. 

 

만약 인덱스로 설정하지 않았다면 Record Lock이 실행되지 않을까요? 

 

아닙니다 인덱스로 설정하지 않았다면, hidden clustered index를 만들어서 인덱스가 있는것처럼 작동시킵니다. 인덱스가 적용되지 않은것도 인덱스가 적용된것처럼 만들고 작동한다니 이렇게 보니 인덱스를 무조건 써야될 것 같은 느낌도 듭니다. 

 

다음은 핵심인 Gap Lock에 대해서 알아보도록 하죠

 

 

Gap Lock

Gap Lock 은 흔히 Gap Locking이라고도 많이 불리는데요. 이 Gap Lock역시 인덱스 레코드 사이의 gap에 대한 Lock을 진행하는 것입니다. 

 

그냥 간단하게 가장 작은 값부터 가장 큰 값까지 모두 Lock을 걸어버린다는 것입니다. 예를 들어서 아래와 같은 쿼리를 날렸다고 가정해봅시다.

 

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE

 

이런 쿼리가 날아갔다면 t.c1 = 15인 값은 Gap Lock에 의해 삽입이 불가능해집니다. Gap Lock은 잠궈놓은 gap만큼의 값중에서 해당 값이 존재하지 않던 존재하던 상관없이 그 지역을 모두 잠궈버립니다. 

 

Gap Lock은 성능과 동시성문제 사이에서 등가교환의 한 부분입니다. 즉, 성능을 끌어올리려고 했더니 동시성 문제가 발생하고 동시성문제를 해결하자니 성능이 낮아지니 두개 개념 사이에서 알맞는 지점을 찾은 것이라고 해석하면 됩니다. 

 

Gap Locking은 단 하나밖에 없는 행을 찾기위해 단 하나의 유니크한 인덱스를 사용하는 행을 잠그는 상태를 강요하지 않습니다. 즉, 행이 하나밖에 없는데 이걸 Gap Locking 할 필요가 없다는 것이죠.

 

하지만 유니크한 인덱스가 여러개의 컬럼을 포함하고있는 상황이라면 얘기가 다릅니다. 이런 경우엔 Gap Locking이 작동합니다. 

 

Gap Locking은 서로다른 두 트랜잭션에 의해 gap을 잡고있는 Lock이 충돌하는 상황을 논할 필요가 없습니다. 위에서 설명했던 (s)Lock과 (x)Lock의 법칙을 그대로 따라가거든요. 

 

충돌이 난 gap에서 두 트랜잭션이 (s)Lock이다? 그럼 공유가 가능합니다. (공식문서에서는 merged 라고 표현했습니다. 아마 이 두개의 트랜잭션 Lock이 하나가 되는 상황도 고려해봐야 할 것 같습니다.)

 

하지만 (x)Lock이다? 그럼 공유되지 않고 한쪽의 Lock이 풀릴 때 까지 기다립니다. 

 

Gap Locking은 명시적으로 선언하지 않아도 뒤에서 알아서 작동됩니다. 하지만 만약 격리수준을 Read Committed로 바꿨다면 Gap Locking은 인덱스를 찾고 스캔하는 것은 할 수 없으며 오로지 외래키 제약조건을 체크하거나 중복되는 기본키를 체크하는 용도로밖에 쓸 수 없습니다. 

 

그러니까 Mysql 쓸거면 격리수준 맘대로 바꾸지 마라 이런 뜻인 것 같네요. 

 

 

그럼 이제 결정적인 Next-Key Locks에 대해서 알아보죠

 

 

Next-Key Locks

Next-Key Lock은 Record Lock과 Gap Lock을 결합시킨 것입니다. Record Lock의 특징과 Gap Lock의 특징이 모두 짬뽕된 완벽한 Lock이라는 것이죠. 음...Record Lock과 Gap Lock을 너무 자세히 써놔서 둘이 합쳤다는 말 말고는 딱히 할 말이 없네요. 

 

예시만 보고 넘어가도록 하겠습니다. 

 

만약 10, 11, 13, 20의 값이 삽입되었다고 가정해봅시다. 그럼 Next-Key Lock은 내부적으로 이 모두를 감싸는 gap을 만들고 만든 gap을 감쌉니다. 그리고 Lock을 걸어버리죠. 

 

즉, 10부터 20까지의 레코드를 모두 잠궈버리는 것입니다. 그로인해 다른 트랜잭션이 접근해서 값을 삽입, 수정, 삭제하려고 한다면 이 접근을 막아버립니다. 하지만 앞서 설명했듯이 (s)Lock은 공유가 가능합니다. 

 

 

 

이 글귀를 보려고 이 난리를 쳤네요. 결과적으로 Repeatable Read를 쓰는 InnoDB는 Repeatable Read에서 발생하는 부정합 문제인 Phantom Read를 Next-Key Lock을 이용해 막을 수 있습니다. 

 

 

자 여기까지 Mysql에서 Phantom Read를 해결하는 방법인 Next-Key Lock을 알기위해 알아야하는 Lock에 대한 전반적인 내용을 알아봤습니다. 

 

우리는 이번 포스팅에서 Phantom Read가 무엇인지 확인하였고, Mysql에서 구현한 Lock의 종류(Shared Lock, Exclusive Lock, Record Lock, Gap Lock, Next-Key Lock)에 대해서 알아봤습니다. 

 

사실 이거 말고도 Intention Lock이라던가 AUTO-INC Lock이라던가 Insert Intention Lock이라던가 Predicate Lock도 있었지만... 읽어보지는 않았습니다. Phantom Read를 해결하기 위한 결정적인 Lock은 아닌것 같아서요. 

 

저는 Phantom Read를 해결하기 위한 방법으로 gap-locking만 알고있었는데 이번 기회에 아주 빠삭하게 배운 것 같습니다. Mysql이 Phantom Read를 해결하는 방식만 하나의 포스팅이 나올 것 같아서 쪼갰는데 다행인 것 같네요. 

 

면접에서 이렇게까지 자세하게 물어보지는 않을 것 같습니다. 데이터베이스마다 해결하는 방식이 다르기도 하고 이렇게까지 자세히 알 필요도 없다고 생각합니다. 하지만 Mysql을 사용하는 사람의 입장에서 기본 상식으로는 아주 적합한 내용이었습니다. 

 

 

이렇게 글 마쳐보도록 하겠습니다. 긴 글 읽어주셔서 감사합니다. 오늘도 즐거운 하루 되세요~

 

 

출처

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking