MySQL Phantom Read

REPEATABLE READ

• MySQL의 기본 격리 수준인 REPEATABLE READ은 MVCC를 통해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.
• 각각의 트랜잭션은 순차 증가하는 고유한 트랜잭션 번호가 존재하며, 언두 영역에는 어느 트랜잭션에 의해 저장/수정/삭제되었는지 트랜잭션 번호를 함께 저장한다.
• REPEATABLE READ는 한 트랜잭션 내에서 동일한 결과를 보장하지만, 새로운 레코드가 추가되는 경우에 부정합이 생길 수 있다.

예시

1. employees에 emp_no=500000, first_name=Lara인 레코드가 존재한다 가정.
2. 사용자 B가 emp_no=500000인 레코드 조회, 트랜잭션은 아직 종료되지 않은 상태이다.
3. 사용자 A가 emp_no=500000인 레코드 갱신, MVCC를 통해 InnoDB 버퍼풀 데이터는 변경되지만, 변경 전 데이터가 언두 로그에 저장된다.
4. 사용자 A COMMIT
5. 사용자 B가 emp_no=500000인 레코드 다시 조회, 언두 로그의 변경 전 데이터를 반환

REPEATABLE READ Phantom Read

• REPEATABLE READ는 새로운 레코드의 추가까지는 막지 않는다.
• 따라서, 다른 트랜잭션에서 수행한 삽입 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 유령 읽기(PHANTOM READ)라고 한다.
• MVCC에 의해 잠금 없는 SELECT 조회에서는 Phantom Read 현상이 발생하지 않는다. (자신보다 나중에 실행된 트랜잭션의 레코드는 무시하고, 일관된 읽기 가능)
• 하지만, SELECT FOR UPDATE를 통해 쓰기 잠금을 건 경우 Phantom Read가 발생할 수 있다.

SELECT FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야하는데, 언두 레코드에는 잠금을 걸수없다. 그래서 SELECT FOR UPDATE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 실제 테이블의 레코드의 값을 가져오게 된다.

예시