InnoDB의 REPEATABLE READ가 PHANTOM READ를 방지하는 원리

Real MySQL 8.0의 5.4.4절에서는 다음처럼 이야기하고 있다.

• InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않는다

위의 문장 때문에 학습에 혼선이 많았는데 왜냐하면 내가 실험한 결과에서는 REPEATABLE READ 격리 수준에서 Next-Key Lock을 사용하지 않고 일관된 읽기가 보장된 경우가 있었기 때문이다. (해당 실험은 이어지는 글에서 후술한다.)

나는 Next-Key Lock이 개입하지 않는데 PHANTOM READ가 방지된 이유는 무엇이었는지, Next-Key Lock이 개입하여 PHANTOM READ를 방지하는 시나리오는 어떤 시나리오인지 궁금해졌고 이에 여러 실험을 진행해보았다.

결론부터 이야기하면, 일반 SELECT 문과 같은 잠금 없는 읽기에서는 MVCC만을 통해 Next-Key Lock 없이 PHANTOM READ가 방지 되는 시나리오가 있을 수 있다. (후술할 나의 실험 처럼..) 하지만 이는 새로운 행의 삽입을 막지 못해 PHANTOM READ 문제를 완벽히 해결하지 못하는 반쪽짜리 해결책이고, 이에 InnoDB의 REPEATABLE READ 격리 수준이 Next-Key Lock을 활용한 잠금 읽기 메커니즘을 제공하고 있다는 것을 이해하게 되었다. 이번 포스팅에서는 이를 이해하기 위해 진행한 학습, 그리고 실험들을 공유해보려고 한다.

여담으로 실험을 통해 알게된 내용들은 공식 문서에 자세하게 서술되어 있음을 뒤늦게 알게되었다.. (역시 공식 문서 기반의 학습..🫠)
결과만 확인하고 싶으신 분들은 아래의 공식문서를 참고하고 떠나도 좋을 듯 하다.

MySQL Innodb Transaction Isolation Level REPEATABLE READ
For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 17.7.1, “InnoDB Locking”.

REPEATABLE READ 격리 수준에서 다른 검색 조건(잠금 읽기)의 경우, InnoDB는 갭 잠금 또는 다음 키 잠금을 사용하여 스캔된 인덱스 범위를 잠그고, 범위로 덮인 갭에 다른 세션이 삽입하는 것을 차단합니다. 갭 잠금 및 다음 키 잠금에 대한 정보는 섹션 17.7.1, "InnoDB 잠금"을 참조하십시오.

---

PHANTOM READ

실험에 앞서 PHANTOM READ가 무엇인지 알아보자. Phantom Read 문제는 하나의 트랜잭션에서 동일한 쿼리를 두 번 실행했을 때, 첫 번째 실행 시에는 없었던 “새로운 행(Phantom Row)“이 두 번째 실행 시 나타나는 현상이다. 다른 트랜잭션이 새로운 행을 삽입하거나 기존 행을 수정하여 쿼리 조건에 맞게 만들 때 발생할 수 있다.

트랜잭션 A	트랜잭션 B
START TRANSACTION;
SELECT * FROM employees WHERE salary > 5000;
	START TRANSACTION;
	INSERT INTO employees (name, salary) VALUES ('David', 6000);
	COMMIT;
SELECT * FROM employees WHERE salary > 5000;

위의 시나리오를 살펴보면 트랜잭션 A는 두번 SELECT 쿼리를 실행하게 된다. 처음 SELECT와 두번째 SELECT 사이에 트랜잭션 B가 새로운 데이터를 삽입하고 있는 것을 확인할 수 있다. 이로 인해서 트랜잭션 A의 첫번째 SELECT 쿼리와 두번째 SELECT 쿼리의 결과가 달라진다면 이는 PHANTOM READ 현상이다.

---

RR 격리 수준에서 Next-Key Lock을 사용하는지 확인해보자

PHANTOM READ가 무엇인지 알았으니 이제 책의 내용을 검증해 볼 때이다. Real MySQL 8.0 책에서는 RR 격리 수준에서 Next-Key Lock을 활용해 PHANTOM READ 문제를 방지한다고 했는데 실제로 그런지 살펴보자. Next-Key Lock은 조회 쿼리의 데이터 스냅샷에 영향을 줄 수 있는 데이터의 새로운 삽입을 막기에 만약 Next-Key Lock이 활용된다면 새로운 트랜잭션이 삽입하려고 하는 시도는 실패할 것이다. 이를 확인하기 위한 시나리오를 단계별로 준비해보았다. 다음을 따라가보자.

1. 먼저 로컬 환경에서 테이블과 데이터를 준비해준다.

-- 샘플 테이블 생성
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    salary INT,
    INDEX idx_salary (salary)
);

-- 초기 데이터 삽입
INSERT INTO employees (name, salary) VALUES 
('libi', 4000),
('kaki', 5500),
('hoti', 6000),
('hogi', 7000);

2. MySQL 세션 두개를 준비해줌으로써 서로 다른 트랜잭션이 접근하는 상황을 모방하자.

필자는 터미널로 두개의 세션을 준비했다.

3. 각 세션의 트랜잭션 격리 수준을 REPEATABLE READ로 설정하자 (InnoDB의 Default 격리 수준이긴 하다)

-- 세션의 트랜잭션 격리 수준을 RR로 설정
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SELECT @@transaction_isolation;

4. 아래의 표 순서대로 SQL을 각 세션별로 실행하자

트랜잭션 A (A)	트랜잭션 B (B)
START TRANSACTION;
SELECT * FROM employees WHERE salary > 5000;
	START TRANSACTION;
	INSERT INTO employees (name, salary) VALUES ('David', 6000);
	COMMIT;
SELECT * FROM employees WHERE salary > 5000;

5. 결과를 확인하여 Phantom Read 문제가 발생했는지 확인하자

Next-Key Lock이 활성화 되었다면 트랜잭션 B의 INSERT 쿼리 실행은 블락되어야 한다. 하지만 아무런 오류 없이 실행되는 것을 확인할 수 있다.

트랜잭션 B의 Insert 쿼리가 오류없이 실행되는 모습

그리고 트랜잭션 A도 새로운 행을 감지하지 못하고 일관된 읽기가 유지되고 있는 것을 알 수 있다. (PHANTOM READ 방지)

트랜잭션 B가 David를 INSERT하고 COMMIT 했음에도 트랜잭션 A의 두번째 SELECT 쿼리가 오염되지 않은 모습

Real MySQL 8.0책의 내용처럼 Next-Key Lock을 활용했을까? 그렇지 않다.

Next-Key Lock이 활용되지 않았다

---

실험에서 발견한 의문 지점들

책에서는 InnoDB가 Next-Key Lock을 활용하여 PHANTOM READ문제를 방지한다고 설명하고 있다. 하지만 Next-Key Lock을 활용하지 않고도 PHANTOM READ문제가 예방되고 있는 것을 위의 실험으로 확인할 수 있다.

이러한 실험은 나로 하여금 다음 두가지 질문을 던지게 만들었다.

• Next-Key Lock이 사용되지 않았음에도 왜 PHANTOM READ가 방지되었을까?
• 왜 Next-Key Lock을 사용하여 PHANTOM READ를 방지하지 않았던 것일까?

이어지는 글에서 하나씩 이유를 살펴보자.

---

Next-Key Lock없이 PHANTOM READ가 방지되었던 이유

위의 시나리오에서 PHANTOM READ가 방지된 이유는 REPEATABLE READ의 MVCC를 활용한 잠금 없는 읽기 때문이다.

REPEATABLE READ 격리 수준은 커밋되지 않은 데이터를 읽지 않기 위해 버퍼 풀이 아닌 언두 영역으로부터 데이터를 읽어온다. 또한 트랜잭션 도중 데이터의 변경, 삽입으로 인한 NON-REPEATABLE 부정합 문제를 방지하기 위해 TRX_ID가 자신보다 작은 데이터만을 복사한다. 이 과정을 도식화된 그림으로 살펴보면 다음과 같다.

그림 출처: Real MySQL 8.0

이를 고려하면 위의 실험에서 PHANTOM READ가 방지되었던 이유는 RR 격리 수준에서 새롭게 삽입된 트랜잭션 B의 행은 자신보다 높은 트랜잭션 ID를 지니기에 복사되지 않았기 때문임을 알 수 있다.

즉, 트랜잭션 B의 데이터 삽입은 MVCC에 의해서 트랜잭션 A의 데이터 스냅샷에 영향을 주지 못했고 이를 통해 PHANTOM READ가 Next-Key Lock 없이 방지된 것이다.

---

그러면 PHANTOM READ는 Next-Key Lock 없이 완벽히 방지될 수 있을까?

위의 MVCC설명을 이해했다면 PHANTOM READ문제를 방지하기 위해 Next-Key Lock이 필요 없을 것이라고 이해할 수도 있겠다. 하지만 MVCC만으로는 PHANTOM READ 문제를 완벽히 방지할 수 없다. MVCC는 트랜잭션이 시작될 때의 스냅샷을 기반으로 읽기를 수행하지만, 새로운 행의 삽입은 막을 수 없기 때문이다. 즉 새로운 행의 삽입 자체는 MVCC만으로 막지 않기에 다음의 시나리오에서 PHANTOM READ가 발생할 여지가 있게 만든다.

트랜잭션 A (A)	트랜잭션 B (B)
START TRANSACTION;
SELECT * FROM employees WHERE salary > 5000;
	START TRANSACTION;
	INSERT INTO employees (name, salary) VALUES ('David', 6000);
	COMMIT;
SELECT * FROM employees WHERE salary > 5000 FOR UPDATE;

트랜잭션 A의 동작을 살펴보면 처음 SELECT는 잠금 없이 읽고 있고 이후 SELECT는 FOR UPDATE 구문을 통해 잠금 읽기를 수행하고 있다. 언두 로그의 스냅샷 기반으로 일관된 읽기가 진행될 것이라고 생각할 수도 있지만 위의 시나리오는 첫번째 SELECT의 결과와 두번째 SELECT의 결과가 다른 PHANTOM READ 현상이 발생한다.

트랜잭션 A의 PHANTOM READ

해당 시나리오에서 PHANTOM READ가 발생하는 이유는 두번째 SELECT에서 잠금 읽기를 시도할 때 언두 영역이 아닌 실제 레코드를 읽기 때문이다. 단순 SELECT는 언두 영역을 읽지만 Lock을 걸어야 하는 잠금 읽기에서는 언두 영역을 읽을 수 없다. 언두 레코드에는 잠금을 걸 수 없기 때문에 실제 레코드를 참조하게 되는 것이다. 즉, SELECT... FOR UPDATE 구문은 언두 영역의 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져올 수 밖에 없고 이에 PHANTOM READ 문제가 발생하는 것이다.

따라서 이러한 시나리오에서는 MVCC만을 통해서는 PHANTOM READ를 완벽히 방지될 수 없으며 이러한 경우의 PHANTOM READ 문제를 방지하기 위하여 Next-Key Lock이 잠금 읽기에서 활용되는 것이다.

---

PHANTOM READ를 방지하기 위한 또 다른 메커니즘 Next-Key Lock

앞서 MVCC가 PHANTOM READ문제를 방지할 수는 있지만 새로운 행의 삽입은 막지 못하여 특수한 시나리오에서 PHANTOM READ문제가 발생할 수 있음을 확인했다. PHANTOM READ 문제의 추가적인 방지를 위해 사용되는 개념이 Next-Key Lock인 것이다.

다른 격리 수준과는 다르게 REPEATABLE READ 격리 수준에서 잠금 읽기를 수행하는 경우에는 Next-Key Lock이 활성화된다. 이 Next-Key Lock은 조회 간격에 해당하는 새로운 레코드의 삽입을 막음으로서 PHANTOM READ 문제를 방지한다. 다음의 시나리오를 통해 Next-Key Lock이 활용되는 모습을 살펴보자.

트랜잭션 A (A)	트랜잭션 B (B)
START TRANSACTION;
SELECT * FROM employees WHERE salary > 5000 FOR UPDATE;
	START TRANSACTION;
	INSERT INTO employees (name, salary) VALUES ('David', 6000);
	COMMIT;

해당 시나리오대로 실행시키다 보면 트랜잭션 B에서의 INSERT가 데드락 혹은 TIMEOUT으로 실패하는 것을 볼 수 있다.

Next-Key Lock으로 새로운 데이터의 삽입에 실패하는 트랜잭션 B

그리고 Gap Lock과 Record 락이 결합하여 Next-Key Lock이 활성되어 있는 모습도 확인할 수 있다.

Next-Key Lock이 활성화되어 있는 모습

일반적인 읽기(단순 SELECT)가 아닌 잠금 읽기(SELECT... FOR UPDATE)의 경우는 새로운 레코드의 삽입 자체를 Next-Key Lock을 통해 막고 이를 통해 PHANTOM READ를 방지하는 모습을 확인할 수 있다.

---

정리

InnoDB 스토리지 엔진에서 REPEATABLE READ 격리 수준은 PHANTOM READ 문제를 해결하기 위해 MVCC와 Lock 메커니즘을 함께 작동시킨다. MVCC만으로 PHANTOM READ를 방지할수 있는 시나리오도 있지만 잠금 읽기에서는 ROW 삽입 자체를 막음으로서 데이터 일관성과 무결성을 한층 더 강화한다.

• InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않는다

MVCC만으로는 PHANTOM READ를 완벽히 방지할 수 없으니 책의 내용이 완전히 틀린 것 같지는 않은데, 그래도 책의 내용에 나의 선호를 뿌려서 조금 더 자세히 설명하면 다음처럼 수정될 수 있겠다.

• InnoDB 스토리지 엔진의 REPEATABLE READ 격리 수준에서는 언두 로그의 TRX_ID를 기반으로 한 MVCC와 잠금 읽기 시 활성화되는 Next-Key Lock을 통해 PHANTOM READ 문제를 방지한다.
• PHANTOM ROW가 삽입되더라도 스냅샷을 기반으로 한 일관된 읽기를 제공하는 것이 MVCC이며, PHANTOM ROW의 삽입 자체를 막는 것이 Next-Key Lock이다.
• PHANTOM ROW의 삽입을 막는 Next-Key Lock은 RR 격리 수준에서 잠금 읽기(SELECT... FOR UPDATE)시에 활성화된다.
• 잠금 없는 읽기에서의 PHANTOM READ 문제는 MVCC가 막을 수 있다. (삽입은 막지 않지만 재조회 시 새로운 ROW가 읽히지 않는다. 특수한 시나리오에서는 PHANTOM READ 발생 가능..)