MySQL의 격리수준

일반적으로 database를 공부하다 보면 아래와 같은 표의 형식으로 4가지 격리수준을 공부하게 된다.

보통, Repeatable Read 수준에선 Phantom Read가 발생하는 것으로 알려져있다. 하지만 MySQL의 스토리지 엔진으로 InnoDB Engine을 사용하게 되면 Phantom Read가 발생하지 않는다.

이를 이해하기 위해선 MySQL의 InnoDB 스토리지 엔진의 Lock과 MVCC(Multi Version Concurrency Control)이라는 개념을 이해해야 한다.

InnoDB Lock

InnoDB는 Lock을 인덱스에 거는 특징이 있다. 인덱스가 없는 테이블이어도 내부적으로 생성한 인덱스를 사용해 잠금을 설정한다.

InnoDB에선 다음과 같이 3가지 종류의 인덱스 락이 있다.

1. Record Lock

이는 단일 레코드 하나의 인덱스에 Lock을 설정하는 것이다.

2. Gap Lock

이는 Record Lock과는 다르게 특정 레코드와 레코드 사이에 생기는 그 간격을 잠그는 Lock이다. 해당 Lock이 설정된 구역은 무언가 삽입되거나 제거되는 작업을 방지할 수 있다. Gap Lock은 서버 내부적으로 Shared Gap Lock 형태만 존재한다. 그래서 Update, Delete, Select For Update 구문에 의한 Gap Lock(보통은 Exclusive Gap Lock) 이라 하더라도 여러 트랜잭션의 Gap Lock은 서로 충돌하지 않는다.

Gap Lock은 간격을 잠그는 Lock 이다 보니 예기치 못한 데드락이 많이 발생한다. 일반적으로 가장 쉽게 예상할 수 있는 데드락은 트랜잭션 1은 id=1 인 레코드를 변경하고 트랜잭션 2는 id = 2인 레코드를 변경(X-lock을 쥠)하는 상태에서, 서로 상대 트랜잭션이 변경한 레코드에 대해 변경을 요청할 경우 발생한다. 이런 경우는 업데이트 할 때 id를 오름차순으로 정렬해 실행하면 예방할 수 있다. 하지만, 더 복잡한 상황들이 존재한다.

3. Next-Key Lock

Record Lock과 Gap Lock을 합쳐 Next-Key Lock이라고 부른다.

동등조건 비교 시 사용되는 잠금

Primary Key or Unique Index
- 쿼리 조건이 1건일 경우, Record Lock만 사용
- 쿼리 조건이 1건 초과일 경우, Record Lock + Gap Lock 사용
Non-Unique Secondary Index
- 항상 Record Lock + Gap Lock

MVCC(Multi Version Concurrency Control)

MySQL의 InnoDB 스토리지 엔진은 비교적 높은 격리수준에서도 lock 없이 일관된 읽기(Consistent Read)를 보장한다. MySQL에선 조회할 때, 다른 트랜잭션에서 데이터를 변경해도 undo log에 이를 snapshot 으로 기록해 동일한 결과를 반환한다. 이를, MVCC(Multi Version Concurrency Control)이라고 한다.

1. Read Uncommitted

- 가장 낮은 격리수준으로 MVCC를 사용하지 않는다. 따라서 기존의 SQL 표준과 같은 문제들이 발생한다.

2. Read Committed

- MVCC가 적용된다. 레코드마다 snapshot이 최신화되어 가장 최신 버젼의 레코드를 읽음으로 Non Locking Consistent Read를 보장한다.

3. Repeatable Read

- Read Comitted 단계와 비슷하게 MVCC를 통해 Consistent Read를 보장한다. 다른 점은, Read Comitted 단계는 undo log 영역에서 항상 최신의 레코드를 읽어온다. 하지만, Repeatable Read에선 undo log 영역의 자신의 트랜잭션 id 보다 낮은 버젼 중 가장 최신의 것을 읽음으로 Unrepeatable Read 문제를 방지한다. 또한, Phantom Read의 경우도 snapshot을 읽어옴으로써 방지할 수 있다.