[MYSQL] SNAPSHOT - Phantom read 테스트

[MYSQL] InnoDB - Phantom read 테스트

mysql은 select 문으로 데이터를 읽어올때, 해당 트랜잭션 내에서 처음으로 읽어온 SNAPSHOT을 기반으로 데이터를 읽어온다. 이 특징 때문에, mysql는 Phantom read현상이 다른 DB와 다르게 발생한다고 한다.

MYSQL 공식문서중 SNAPSHOT 부분(이미지 클릭시 이동)

이 포스팅에서는 Phantom read가 발생할 수 있는 세가지 환경

1. REPEATABLE-READ

2. READ-COMMITTED

에서 Phantom read가 발생하는지 확인하는것을 목적으로 한다.

(READ-UNCOMMITED에서는 SNAPSHOT을 만들지 않는다고 하니 테스트 하지 않을것이다)

---

[테스트 환경]

Server version: 8.0.29 Homebrew

Storage engine : InnoDB

transaction isolation : test1. REPEATABLE-READ / test2. READ-COMMITTED / test3. READ-UNCOMMITED

 

테스트 목적 : 두개의 서로 다른 트랜잭션 1과 2가 있고 트랜잭션 2에서 데이터가 추가되고 커밋되었을때, 트랜잭션 1에서 Phantom read가 발생할것인가

---

1. REPEATABLE-READ

1-1. 우선 mysql의 트랜잭션 격리수준을  REPEATABLE-READ로 변경시킨다.

 

1-2. 변경된 격리수준을 확인한다.

 

1-3. 테스트할 테이블이다.

 

1-4. 각각의 접속 세션에대해서 트랜잭션을 시작하자. (트랜잭션 실행 순서는 트랜잭션 1 -> 트랜잭션 2 이다.)

왼쪽 : 트랜잭션 1 / 오른쪽 : 트랜잭션 2

1-5. Buffer pool에 클린 페이지를 만들어주기 위해 트랜잭션 1 에서 member를 조회하자

트랜잭션 1

1-6. 트랜잭션 2에서 새로운 행을 추가시키고 커밋한다.

m_id = 16인 새로운 행이 잘 추가된것을 볼 수 있다.

 

1-7. 트랜잭션 1 에서 member테이블을 조회후 결과를 확인하자.

트랜잭션 1

격리수준이 REPEATABLE-READ일때는 Phantom Read가 발생하지 않았다.

---

2. READ-COMMITTED

2-1. MYSQL의 격리수준을 READ-COMMITTED로 변경시키자

 

2-2. 각 접속 세션에 대해서 트랜잭션을 시작하자. (트랜잭션 실행 순서는 트랜잭션 1 -> 트랜잭션 2 이다.)

 

2-3. 트랜잭션 1 에서 member테이블을 조회해 SNAPSHOT을 만든다

트랜잭션 1

 

2-4. 트랜잭션 2 에서 새로운 데이터를 추가시킨후 커밋한다

트랜잭션 2

데이터가 잘 추가되었다.

 

2-5. 트랜잭션 1 에서 member테이블을 다시 조회하고 첫번째 결과와 다른 조회결과가 나왔는지 확인하자.

조회결과 Phantom Read가 발생했다.

---

이유

결론부터 말하면 REPEATABLE-READ에서는 트랜잭션 내에서 첫 조회시 만들어진 SNAPSHOT을 기반으로 조회 결과를 반환한다.

하지만, COMMITTED-READ에서는 매 조회마다 새로운 SNAPSHOT을 만든다. 아래는 원문 사진이다.

 

REPEATABLE READ

 

READ COMMITTED

원문링크

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels

 

---

결론

REPEATABLE-READ : Phantom read가 발생하지않음

READ-COMMITTED : Phantom read가 발생함

 

보통 격리수준에 따른 특징을 보면, REPEATABLE-READ에서도 Phantom read는 발생하는것을 볼 수 있는데, MYSQL은 SNAPSHOT을 통해 Phantom read를 방지한다.