[MySQL] InnoDB에서 PHANTOM READ 를 방지하는 방법.

MySQL의 REPEATBLE READ 격리 수준에서는 PHANTOM READ 현상이 발생하지 않는다고 많이 본거 같은데 과연 진짜일까? 그런데 PHANTOM READ가 무엇이길래 중요하게 다루는 것일까?

REPEATABLE READ 이란?

REPEATABLE READ 는 동일 트랜젝션 내에서 동일한 결과를 보장하도록 하는 격리 수준을 뜻합니다. MySql의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준인데, 어떻게 동일한 결과를 보장해줄 수 있는 걸까요? 이는 MVCC(Multi Version Concurrency Control, 다중 버전 동시성 제어)의 메커니즘을 이용해 이전 버전의 데이터를 백업해두고 실제 레코드 값을 변경하는 방법으로 보장할 수 있습니다.

MVCC: 기존의 데이터를 덮어씌우는 것이 아닌 기존의 데이터를 바탕으로 이전 버전의 데이터와 비교하여 변경된 내용을 기록하는 방법으로, 여러 버전의 데이터가 존재하게 되거나 사용자는 마지막 버전의 데이터를 읽게 됩니다.

 

예제와 함께 InnoDB의 PHANTOM READ 문제

https://dotnettutorials.net/lesson/phantom-read-concurrency-problem-sql-server/

• Transaction 1
  1. 첫번째 조회:

  SELECT * FROM Employees WHERE Gender = 'Male';
  

  → 결과: 2개의 행 반환
  2. 다시 동일한 쿼리 실행:

  SELECT * FROM Employees WHERE Gender = 'Male';
  

  3. 중간 작업 수행 (Transaction 1은 아직 끝나지 않았음)
  → 결과: 3개의 행을 반환한다.
• Transaction 2
  1.중간에 INSERT 주입

  INSERT INTO Employees (Name, Gender, Age)
  VALUES ('DOHYUN', 'Male', 20);
  

  2. 그리고 커밋!

⇒ 결과

Transaction 1은 동일한 쿼리를 2번 실행하였을 때, 첫 번쨰 쿼리에서는 2명의 직원을 반환 하였지만 두 번째 쿼리에서 3명의 직원을 바로 반환하고 있습니다. 이러한 현상이 나타나는 것을 PHANTOM READ라고 합니다.

PHANTOM READ를 방지하는 것이 중요한 이유!

그러면 이런 PHANTOM READ를 방지하는 것이 왜 그렇게 중요하다고 하는 것일까요? PHANTOM READ가 단순한 쿼리 결과의 변화처럼 보일 수 있지만, 실제로는 비즈니스 로직에 큰 영향을 미치는 동시성 버그를 초래할 수 있습니다. 다음은 팬텀 리드를 방지하는 것이 중요한 이유를 알아봅시다.

1. 데이터 무결성과 정합성(Data Integrity)

문제: 특정 조건을 만족하는 레코드 수를 기반으로 중요한 판단을 내리는 경우, PHANTOM READ로 인해 결과가 바뀌면 잘못된 비즈니스 로직이 실행될 수 있음

 

⇒ 예시: "남성 직원이 2명 이하일 때만 신규 채용 허용"이라는 조건이 있는 경우, 팬텀 리드로 인해 실제로는 3명인데도 불구하고 2명이라고 판단해 중복 채용이 발생할 수 있음.

---

2. 트랜잭션 일관성(Consistency)의 붕괴

문제: 트랜잭션의 가장 핵심적인 목적 중 하나는 동일한 트랜잭션 내에서는 동일한 데이터를 본다는 것입니다. 하지만 PHANTOM READ가 발생하면, 같은 쿼리를 두 번 했는데 결과가 달라지기 때문에 트랜잭션의 일관성이 깨지게 됩니다(노우! )

 

⇒ 결과: 이는 비즈니스 정책 위반, 논리적 오류, 또는 예측 불가능한 결과로 이어질 가능성이 높습니다.

---

3. 다중 트랜잭션 환경에서 예측 불가능한 상태 발생

문제: PHANTOM READ는 여러 트랜잭션이 동시에 실행되는 환경에서만 발생합니다. 따라서 PHANTOM READ를 허용하면, 트랜잭션 간 간섭이 빈번하게 일어나며 테스트로 발견하기 어렵고 재현이 힘든 문제가 발생할 수 있습니다.

 

⇒ 결과: 실제 운영 환경에서만 발생하는 간헐적 오류(bug), 데이터 불일치 등으로 이어집니다.

 

즉! 팬텀 리드를 방지하지 않으면, 트랜잭션 내 논리 오류와 비즈니스 정책 위반이 발생할 수 있으며, 특히 실시간 처리 시스템에서는 심각한 운영 장애로 이어질 수 있습니다.

---

PHANTOM READ 방지 방법 1 - GAP LOCK(갭락)

GAP LOCK(갭락)은 특정 인덱스 값 사이의 공간을 잠그는 락입니다. 기존 레코드 간의 간격을 보호하여 새로운 레코드의 삽입을 방지합니다. 갭 락은 범위 내에 특정 레코드가 존재하지 않을 때 적용됩니다. 트랜잭션이 특정 범위 내에서 데이터의 삽입을 막아 팬텀 읽기(Phantom Read) 현상을 방지합니다.

• 예제

-- Transaction 1 (REPEATABLE READ 격리 수준)
START TRANSACTION;

SELECT * FROM Employees WHERE Age BETWEEN 20 AND 30 FOR UPDATE;
-- → 이 쿼리는 (20, 30) 범위에 Gap Lock을 건다

-- Transaction 2 (동시에 실행)
START TRANSACTION;

INSERT INTO Employees (Name, Gender, Age) VALUES ('DOHYUN', 'Male', 25);
-- Gap Lock 때문에 이 삽입은 대기(block) 또는 실패

-- Transaction 1이 COMMIT 해야 Transaction 2가 진행 가능

⇒ 결과

1. 다른 트랜잭션은 id=25 같은 값을 INSERT할 수 없습니다.

2. 기존 row에는 락이 걸리지 않지만, 중간 gap에는 락이 걸립니다.

---

PHANTOM READ 방지 방법 2 - Next-Key Lock(넥스트키 락).

스트키 락은 레코드 락과 갭락을 결합한 형태로, 특정 인덱스 레코드와 그 주변의 갭을 동시에 잠그는 락입니다. 이를 통해 레코드 자체의 변경과 함께 그 주변 공간의 변경도 동시에 제어할 수 있습니다.

넥스트키 락은 특정 레코드와 그 주변 공간을 잠그기 때문에, 다른 트랜잭션이 새로운 레코드를 삽입하여 팬텀 리드를 발생시키는 것을 방지합니다.

• 예제

-- 현재 Employees 테이블 상태:
-- id: 10, 20, 30 이 존재

-- Transaction 1
START TRANSACTION;

SELECT * FROM Employees WHERE id > 10 FOR UPDATE;
-- 이 쿼리는 다음 영역에 Next-Key Lock을 건다:
-- (10,20], (20,30], (30,∞)
-- 즉, id=15나 id=25 같은 삽입은 모두 차단됨

-- Transaction 2
START TRANSACTION;

INSERT INTO Employees (id, Name, Gender, Age) VALUES (25, 'DOHYUN', 'Male', 20);
-- 위 next-key 락에 의해 삽입 불가 또는 대기

⇒ 결과

1. id > 10 범위에 포함되는 모든 구간과 레코드에 락이 걸리고

2. 기존 레코드 + 사이 gap 모두 보호 → 매우 강력한 팬텀 리드 방지합니다.

마무리

이러한 방법들을 알아보며 InnoDB에서 REPEATABLE READ 상태일 떄, PHANTOM READ를 방지하는 방법에 대해서 알아보았습니다. 이를 기반으로 나은 매커니즘을 만드는 개발자가 되시길 바랍니다.

 

인용:
https://dotnettutorials.net/lesson/phantom-read-concurrency-problem-sql-server/
https://parkmuhyeun.github.io/woowacourse/2023-11-28-Repeatable-Read/