InnoDB가 Phantom Read를 피하는 법

Real MySQL 복습을 진행하면서 가볍게 읽고 넘어갔지만 원리가 궁금해진 부분이 생겼다.

 

InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락을 어떻게 사용하길래 REPEATABLE READ 격리 수준에서도 팬텀 리드가 발생하지 않을까?

 

이 궁금증을 해소하고자 한다.

 

REPEATABLE READ, 갭락과 넥스트 키 락에 대한 내용은 아래 포스팅에서 확인할 수 있다.

 

https://devdebin.tistory.com/252

트랜잭션과 잠금

팬텀 리드

팬텀 리드란 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안보였다 하는 현상을 의미한다.

팬텀 리드는 여러 상황에서 발생하지만 일반적으로 아래와 같은 패턴을 가진다.

1. 특정 범위를 검색한다.
2. 범위 결과에 작업(Create, Update, Delete)을 수행한다.
3. 작업(Create, Update, Delete)은 원래 범위 결과에 직접적인 영향을 미친다.

쓰기 왜곡을 방지하는 가장 간단한 방법은 배타적 잠금(SELECT FOR UPDATE)을 사용하는 것이다.

그러나 언두 레코드에는 잠금을 걸 수 없다.

 

InnoDB 스토리지 엔진에서는 갭 랍과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 팬텀 리드가 발생하지 않는다고 한다.

이제 이 원리에 대해 알아보자.

팬텀 리드를 피하는 원리

핵심은 넥스트 키 락이다. 

넥스트 키락은 레코드 락 + 갭 락이다.

즉 레코드 자체에도 락이 걸리고 레코드와 바로 인접한 레코드 사이의 간격도 잠군다.

첫 번째 예시

팬텀 리드 발생 예시

1. employee 테이블의 초기 두 레코드는 번호가 6인 트랜잭션에 의해 INSERT 됐다.
2. 사용자 B가 번호가 10인 트랜잭션을 사용해 emp_no가  50 이상인 데이터 레코드를 SELECT FOR UPDATE 쿼리를 진행했다.
   조회한 데이터 레코드의 이름 컬럼 값은 Lara.
3. 사용자 A가 번호가 12인 트랜잭션을 사용해 emp_no가 51인 데이터를 INSERT 했다. 이름 컬럼 값은 Georgi
4. 번호가 10인 트랜잭션을 사용해 emp_no가 50 이상인 데이터 레코드를 SELECT FOR UPDATE하면 결과는 Lara, Georgi 결과 2건이 나오게 된다.

팬텀 리드 발생하지 않는 예시 (넥스트 키 락 사용)

1. employee 테이블의 초기 두 레코드는 번호가 6인 트랜잭션에 의해 INSERT 됐다.
2. 사용자 B가 번호가 10인 트랜잭션을 사용해 emp_no가  50 이상인 데이터 레코드를 SELECT FOR UPDATE 쿼리를 진행했다.
   조회한 데이터 레코드의 이름 컬럼 값은 Lara.
3. 사용자 A가 번호가 12인 트랜잭션을 사용해 emp_no가 51인 데이터를 INSERT 했다. 이름 컬럼 값은 Georgi.
4. 그러나 넥스트 키 락으로 인해 대기 상태가 된다. 50 이상의 영역은 갭락으로 인해 락이 걸려서 해당 영역에 레코드 삽입이 불가능하다.
5. 추후에는 wait time out 설정에 의해 롤백된다.

두 번째 예시 (Update)

팬텀 리드 발생 예시

1. t 테이블에 i가 21, 25,30인 레코드가 있다.
2. 사용자 A의 트랜잭션 A가 SELECT * FROM t WHERE i > 20 FOR UPDATE;를 실행한다.
3. 사용자 B는 INSERT INTO t VALUES(26);를 실행하고 커밋된다.
4. 이후 트랜잭션 A에서 select * from t where i > 20 FOR UPDATE;를 수행하면 21, 25, 26, 30이 나온다.

팬텀 리드 발생하지 않는 예시 (넥스트 키 락 사용)

1. t 테이블에 i가 21, 25,30인 레코드가 있다.
2. 사용자 A의 트랜잭션이 SELECT문을 사용해 21, 25, 30을 조회함.
3. 사용자 A의 트랜잭션 A가 DELETE FROM t WHERE i=25;를 실행한다. 이 시점에서는 레코드 25만 잠겨있다고 가정하자.
   또한 사용자 A의 트랜잭션은 아직 끝나지 않음
4. 이후 사용자 B는 INSERT INTO t VALUES(26);를 실행한다. 그러나 이는 넥스트 키 락, 즉 갭 잠금으로 인해 실패한다. 
   
5. 26 대신에 29, 23을 넣어도 실패한다. 21부터 30까지 모두 갭 잠금이 걸린 것 이다.
6. 31을 넣으면 insert가 성공한다.

이렇게 넥스트 키 락을 사용해 어떻게 팬텀 리드를 막는지 원리를 알아보았습니다. 감사합니다.

참고 자료

REAL MySQL 8.0 1권

https://dev.to/lazypro/solve-phantom-read-in-mysql-e7g

Solve Phantom Read in MySQL

https://www.percona.com/blog/innodbs-gap-locks/

InnoDB Gap Locks