InnoDB는 Repeatable Read에서 어떻게 Phantom Read를 예방할까?

Phantom Read는 데이터베이스에서 트랜잭션이 같은 쿼리를 실행하는 동안 서로 다른 결과를 반환하는 현상을 말한다. 트랜잭션이 동일한 쿼리를 실행하는 도중 다른 트랜잭션이 데이터를 삽입, 갱신 또는 삭제하는 경우 발생할 수 있다.

Phantom Read는 일반적으로 READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ 에서 발생한다.

다음 그림과 함께 보자 사용자B의 SELECT 쿼리가 emp_no>=50000의 조건으로 조회를 했을 때 Lara만 조회 되지만, 사용자A가 INSERT 한 뒤 커밋하면 사용자B의 동일한 트랜잭션 내에서 SELECT를 해도 다른 결과를 반환하게 된다.

다음과 같은 이유로 SERIALIZE가 아닌경우 Phantom Read가 발생하는데, MySQL의 InnoDB는 MVCC, Next-Key Lock 과 같은 기술을 사용해 Phantom Read를 방지한다.

MVCC

MVCC는 언두로그에 저장할 때 트랜잭션마다 고유의 ID를 주어 ID기반으로 데이터의 가시성을 판단한다. 따라서 읽기 작업은 트랜잭션의 ID보다 작은 트랜잭션에서 수정된 데이터를 읽게 된다. 이로써 대부분의 Phantom Read 상황을 커버할 수 있다.

그러나 SELECT .. FOR UPDATE같은 쿼리를 실행하면 Phantom Read가 발생한다. 언두 레코드에는 Lock을 걸 수 없기 때문에 현재 레코드의 값을 가져오기 때문이다.

따라서 MVCC는 대부분의 Phantom Read 상황에서 동작하지만, 모든 읽기 작업에서는 완전한 방지를 보장하지는 않는다. 따라서 Next-Key Lock과 같은 기법을 사용하여 Phantom Read를 방지하고 있다.

Next-Key Lock

SELECT * FROM child WHERE id > 100 FOR UPDATE; 쿼리를 날렸을 때 100 보다 큰 첫 번째 레코드에 대해 락을 걸게 된다 예를들어 101이 없고, 다음 ID가 102인 경우 101에 대한 INSERT를 막지 못하게 된다.

이를 방지하기 위해 Next-Key Lock을 사용한다. Next-Key Lock은 Gap Lock과 Record Lock을 결합한 형태로 동작한다.

Gap Lock : 현재 읽는 범위와 다음 인덱스 레코드 사이에 적용해 다른 트랜잭션이 새로운 레코드를 삽입하는 것을 방지한다.

Record Lock : 각 인덱스 레코드에 설정되며 단일 레코드에 대한 읽기 및 쓰기 작업을 보호한다.

InnoDB 스토리지 엔진은 인덱스를 검색하거나 스캔할 때 공유락 또는 배타락을 걸게 된다. 이는 레코드 레벨의 잠근인데, Next-Key Lock 같은 경우는 인덱스 레코드에 대한 락 외에도 인덱스 레코드 이전의 Gap에 대해 락을 걸어 Phantom Read를 방지한다.

예시를 들자면 ID가 100 102인 레코드가 있는 테이블에서 Next-Key Lock없이 트랜잭션이 실행되면 102 이상의 값에 대해서만 락이 걸려 데이터를 생성할 수 없다. 즉 ID가 101인 경우 레코드를 생성할 수 있는데, Next-Key Lock을 사용하게 되면 100~102 사이의 갭과 102 이상의 레코드에 락이 걸리기 때문에 Phantom Read에 대해 자유롭다.

참고

---

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