MySQL Repeatable Read는 어떻게 Phantom을 막을까? (Next-Key lock)

그림 1. Gap-Lock과 Record Lock

 MySQL은 Repeatable Read(RR)를 지원한다. 구글링을 하거나 MySQL 메뉴얼을 참고하면 RR은 SQL-92에 명시된 이상현상을 방지하는 단계라고 적혀있다. 하지만 RR은 Phantom Read를 방지할 수 없는데, MySQL에서는 가능하다고 나와있다. 왜 이것이 가능할까? 내부적으로 어떻게 동작하고 있을까?

 

1. Lock과 MVCC

 들어가기 전에 MySQL InnoDB가 어떻게 구현되었는 지 확인할 필요가 있다. InnoDB는 MVCC(Snapshot Isolation)을 구현한 스토리지 엔진이다. MVCC는 트랜잭션마다 테이블의 여러 버전(보통 트랜잭션 시작 시간을 기준)을 만들고 트랜잭션 작업을 진행할 때 해당 버전을 참조하는 방법을 뜻한다. 이 방법은 Lock을 걸지 않고 트랜잭션 작업을 수행하기 때문에 동시에 여러 트랜잭션이 진행될 때 빠른 작업이 가능하다.

 

그림 2. RR에서 언두 로그 동작 방식. (출처: Real MySQL 8.0 1권)

 MySQL에서는 이런 MVCC 버전을 Undo Log에 보관한다. 트랜잭션이 진행될 때 위 그림과 같은 방향으로 진행되는 것이다.

 

 주제를 잠시 돌려서... 왜 MVCC을 사용할까? 이유는 트랜잭션의 동시성 제어에 있다. 여러 트랜잭션들이 직렬 가능한 스케줄을 목표로 여러 방법들을 사용하는데 대표적인 예가 MVCC(Snapshot Isolation), Lock(two-phase locking)이다. 그렇다면 MVCC만으로 직렬 가능한 스케줄을 만들 수 있을까? 이 글의 제목에서 알 수 있듯이 MVCC 만으로 직렬 가능한 스케줄을 만들 수 없다. 그래서 함께 조합해서 사용하는 것이 Lock이다. 이것은 MySQL InnoDB Transaction Model 설명에 있다.

그림 3. InnoDB Transaction Model 설명. (출처 : MySQL 8.0 Refrence Manual)

"InnoDB 트랜잭션 모델은 MVCC와 전통적인 two-phase locking의 가장 좋은 점을 조합하는 것을 목표로 한다."

 

2. Index Locking Protocol

 Phantom Read는 Range 탐색시 (예 : select ... where condition) 다른 트랜잭션이 중간에 데이터를 삽입, 삭제할 때 발생하는 문제이다. 이 문제를 해결하는 가장 간단한 방법은 테이블 전체를 잠그는 것이다. 하지만 데이터가 1억 개가 있다고 가정하면 일부분만 읽기 위해서 모든 테이블을 잠그는 것은 비효율적일 것이다. 그렇다면 조건에 해당하는 일부분만 잠그는 것이 가장 효율적일 텐데 어떻게 구현을 할까?

 

 '범위 탐색'을 위한 검증된 자료구조는 b-tree이다. b-tree로 인덱스를 구성하고 리프 노드에 실제 데이터를 정렬한다면 빠르게 탐색하여 레코드를 잠글 수가 있다.

 이런 방법을 Index Locking Protocol(인덱스 잠금 규약)이라고 한다. 이 규약은 몇 가지 조건이 있는데, 첫 조건이 '모든 릴레이션은 반드시 하나 이상의 인덱스를 가져야 한다'는 것이다. 이것이 MySQL에서 PK, Unique 조건 컬럼이 없다면 임의로 컬럼을 생성해서 인덱스를 만드는 이유이다. 그리고 이 방법을 통해서 RR 고립 수준에서 Phantom Read를 방지할 수 있다.

 

3. Gap Lock, Record Lock

 MySQL에서는 위 잠금 기법을 Gap-Lock, Record-Lock, Next-Key lock으로 구현했다. Record-Lock은 해당 레코드에 직접 걸리는 락을 뜻한다. Gap-Lock은 레코드와 레코드 사이에 걸리는 락을 뜻한다. 여기서 한 가지 의문점이 생길 수가 있다. '레코드와 레코드 사이에 갭락이 존재하는데... 레코드가 없다면 갭 락도 없는 것일까?'

 

3.1 존재하지 않는 레코드와 레코드 사이에 락이 존재할까?

 아래 예제를 확인해보자.

그림 4. 예제 테이블. id로 PK를 구성했다.

select * from seat_no where id between 6 and 9 for update;

 위 테이블에 6 ~ 9까지 락을 거는 실험을 진행했다. 해당 쿼리를 실행하고 테이블에 걸려있는 락 데이터를 조회하면 아래와 같은 결과가 나온다.

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140656510430368:1211:140656420953904
ENGINE_TRANSACTION_ID: 6146
            THREAD_ID: 95
             EVENT_ID: 71
        OBJECT_SCHEMA: employees
          OBJECT_NAME: seat_no
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140656420953904
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140656510430368:146:4:11:140656420951104
ENGINE_TRANSACTION_ID: 6146
            THREAD_ID: 95
             EVENT_ID: 71
        OBJECT_SCHEMA: employees
          OBJECT_NAME: seat_no
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140656420951104
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 12 // <- 12부터 걸려있는 Gap lock. 6~9 사이에는 존재하지 않는다.
2 rows in set (0.00 sec)

 6 ~ 9 사이에는 갭락이 존재하지 않는 것을 확인할 수 있다. 이것을 통해서 인덱스 잠금 규약은 동시성을 향상하는데 도움을 주지만, 동시성 제어를 포함하지 않는 것을 알 수 있다.

 

 만약 예약 테이블에서 'select ... where (예약시간)'을 조회할 때 해당 시간이 비어 있다면, 예약을 등록하는 서비스를 구현한다고 가정하자. 예약 테이블에 많은 사람들이 몰려 '비어 있는 시간을 동시에 조회해서 0이라는 결과를 동시에 받고 둘 다 예약을 등록해서 커밋을 완료했다면' 이 문제는 어떻게 해결할까? 가장 간단한 방법은 테이블 조회 시 테이블 락을 거는 것이다. 또한 '충돌 구체화(materializing conflicts)'로도 해결이 가능하다. 이 주제는 나중에 따로 글을 작성할 예정이다. (하지만 대부분 직렬가능 고립 수준으로 해결이 가능하다고 한다. 충돌 구체화를 도입하기 전에 고립 수준이 높아짐에 따라 성능저하가 발생하는지 확인할 필요가 있다.)

 

4. Next-Key Lock

 Next-Key Lock은 Gap + Record Lock을 뜻한다. MySQL Repeatable Read 고립 단계는 Next-key Lock을 기본으로 사용한다. 이를 통해서 Phantom Read를 방지할 수 있다.

 

 MySQL은 MVCC로 동작하기 때문에 중간에 데이터를 삽입해도 이전 버전을 읽으면 Phantom이 문제가 안될 것이다. 근데 왜 Phantom을 방지하기 위해서 Lock을 사용하는 것일까? 이유는 InnoDB의 아키텍처 구조에서 찾을 수 있다. Undo log는 락의 범위에 해당이 안 된다. 즉, Lock을 걸고 작업을 하면 Undo log를 읽지 않고 원본 테이블을 읽게 된다. 그렇다면 select ... for update와 같은 락 조회 쿼리를 실행하면 중간에 insert가 된 테이블을 읽게 된다. 이 문제가 바로 phantom read이다. 그리고 Next-key lock이 이 문제를 방지하는 것이다.

 

 하지만 모든 쿼리에 Gap-Lock이 걸린다면 중간에 새로운 데이터를 삽입할 때 성능 이슈가 발생할 것이다. 어차피 MVCC로 이전 버전을 읽는다면 굳이 Gap-Lock으로 레코드 사이에 새로운 데이터 삽입을 막을 필요가 있을까? MySQL에서는 이 문제를 Insert Intention Lock으로 해결했다.

 

4.1 Insert Intention Lock

 이 락은 Insert를 위한 특별한 락이다. 중간에 새로운 데이터를 삽입할 때 '서로 겹치는 키 값이 없다면 (Unique, PK가 겹치지 않는다면)' 레코드 사이에 여러 데이터를 삽입할 수 있다. 즉, 트랜잭션끼리 겹쳐도 서로 다른 값으로 삽입을 진행한다면 Gap-Lock이 걸리지 않는다는 뜻이다. (Insert 끼리는 락이 걸리지 않는다.) 하지만 락 조회(select for update)는 insert 락이 걸려 있을 땐 바로 조회하지 않고 대기를 한다. insert 작업이 끝나서 락이 해제된다면 그 이후에 락 조회가 진행된다.

 

5. 더 나아가기

• 충돌 구체화는 '데이터 중심 애플리케이션 설계'에서 자세한 설명이 나와있다.
• Insert Intention Lock은 데드락을 발생시킬 위험이 있다. 해당 예제는 MySQL Reference - InnoDB Locking에 자세히 설명되어 있다.

6. 참고 자료

Index Locking Protocl

MySQL 8.0 Reference Manual - InnoDB Locking

Abraham Silberschatz, Henry F. Korth, S. Sudarshan, 데이터베이스 시스템, 정연돈, 권준호 역 외 3명, (한빛아카데미, 2021)

Martin Kleppmann, 데이터 중심 애플리케이션 설계, 정재부, 김영준, 이도영 역, (위키북스, 2018)

백은빈, 이성욱 저,  Real MySQL 1권 개발자와 DBA를 위한 MySQL 실전 가이드, (위키북스, 2021)