5.4 MySQL의 격리 수준

트랜잭션의 격리 수준(isolation level)이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

크게 4가지로 나뉜다.

1. READ UNCOMMITED (=DIRTY READ)
   • 거의 사용하지 않는다.
2. READ COMMITED
   • Oracle 같은 DBMS에서 주로 사용
3. REPEATABLE READ
   • MySQL에서 사용
4. SERIALIZABLE
   • 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.

아래로 내려갈 수록 트랜잭션 간 데이터 격리(고립) 정도가 높아지며, 동시 처리 성능은 낮아진다.

하지만, SERIALIZABLE이 아닌 나머지 3개는 크게 성능의 개선이나 저하는 발생하지 않는다.

5.4.1 READ UNCOMMITED

각 트랜잭션에서의 변경 내용이 COMMIT, ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.

• A 트랜잭션에서 새로운 레코드를 추가하고 commit을 하기 전인데도 B 트랜잭션에서는 새로운 데이터를 조회할 수 있다.
• 하지만, 문제는 A 트랜잭션이 롤백이 되었을 경우이다. B 트랜잭션에서는 해당 데이터가 정상적이라 판단하고 추후 데이터 처리를 할 것이기 때문이다.

데이터가 사라졌다 보였다하는 이러한 현상을 더티 리드(Dirty read)라고 한다.

절대 사용되어선 안되는 격리 수준이다.

5.4.2 READ COMMITED (NON-REPEATABLE READ)

온라인 서비스에서 가장 많이 선택되는 격리 수준이다. 더티 리드 현상은 여기서 절대 발생하지 않는다.

어떤 데이터든 COMMIT이 완료된 데이터만 다른 트랜잭션에서 보이기 때문이다.

• COMMIT 하기 전까지는 실제 데이터를 가져오는 방식이 아닌, 언두 로그에 있는 데이터를 가져온다.
• COMMIT이 완료된 이후에는 실제 데이터를 가져오게 된다.
• 여기서 문제가 발생하게 되는데..
  • 한 트랜잭션에서 동일한 SELECT는 동일한 결과를 내야한다는 REPEATABLE READ 정합성에 어긋난다.

💡 트랜잭션 내에서 실행되는 SELECT와 트랜잭션 없이 실행되는 SELECT의 차이를 혼동하지 말자. → READ COMMITED 격리 수준에서는 둘의 차이가 별로 없다. → 하지만, REAPEABLE READ 격리 수준에서는 기본적으로 SELECT 문장도 트랜잭션 범위 내에서만 작동한다.

5.4.3 REPEATABLE READ

MySQL의 InnoDB 스토리지 엔진에 기본적으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.

REPEATABLE READ 격리 수준에서는 다음과 같은 특징이 있다.

• READ COMMITED 격리수준에서 발생한 데이터 조회의 부정합이 발생하지 않는다.
• MVCC(Multi Version Concurrency Control) 을 이용해서 언두 로그에 백업한 데이터를 이용해 동일한 결과를 보장하는 것이다. (MVCC 설명은 4.2.3 절 확인)
• READ COMMITED와 차이점은 언두 영역에 백업된 레코드의 여러 버전 가운데 몇번째 이전 버전까지 찾아 들어가는지에 차이가 있다.

트랜잭션과 MVCC에 대해서..

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가진다. 언두 영역에는 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함되어있다. (아래 참조)

+--------+--------+---------+
| TRX-ID | field1 | field 2 |
+--------+--------+---------+
|   6    | 10000  |  test1  |
+--------+--------+---------+
|   12   | 50000  |  test9  |
+--------+--------+---------+

언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단한 시점에 주기적으로 삭제를 한다.

REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다.

REPEATABLE READ 작동 방식

아래 그림은 REPEATABLE READ 작동 방식을 보여준다.

1. 10번(트랜잭션)이 SELECT를 실행했다.
2. 이후 12번(트랜잭션)이 Lara → Toto로 변경을 했다.
   1. 변경되기 전 데이터는 언두로그에 백업된다.
3. 12번(트랜잭션)이 커밋을 했다.
4. 10번(트랜잭션)이 다시 Lara를 조회했다.
   1. 여기서 만약, 커밋된 데이터가 조회되면? REPEATABLE READ의 데이터 정합성에 어긋나게 된다.
   2. 사실 조회할 수도 없다. REPEATABLE READ 격리 수준에서는 자신의 트랜잭션 번호보다 같거나 작은 트랜잭션 번호의 데이터 만 볼 수 있게 된다. (중요!)
5. 그렇기 때문에 언두로그에 백업된 데이터를 반환하게 된다.

PHANTOM READ (PHANTOM ROW)

💡 팬텀 리드(또는 팬텀 로우)는 다른 트랜잭션에서 수행한 변경 작업에 의해 데이터가 보였다 안 보였다 하는 현상을 의미한다.

아래 시나리오를 살펴보자.

1. 10번 트랜잭션이 emp_no가 50000 이상인 값을 조회하고 있다. (1건 조회)
2. 12번 트랜잭션이 50001번의 데이터를 추가하고 커밋을 했다.
3. 10번 트랜잭션이 똑같이 SELECT를 했다.
   1. 이때 원래 같았으면 언두로그에 있는 값을 가져와서 이전처럼 1건의 데이터만 조회해와야 한다. 하지만 12번 트랜잭션이 추가한 데이터까지 총 2개의 조회 결과가 반환이 된다.

왜 이런 상황이 발생할까?

💡 SELECT … FOR UPDATE란?

단순 SELECT가 아닌, 데이터를 변경하기 위한 조회를 한다고 명시하는 것이다.
그렇기 때문에 조회와 동시에 해당 레코드에 LOCK을 걸게 된다.

언두 레코드에는 잠금을 걸 수 없다. 그렇기 때문에 SELECT … FOR UPDATE, SELECT … LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 잠금할 수 없기 때문에 현재 레코드의 값(잠금 없이)을 가져오게 되는 것이다.

SELECT FOR UPDATE vs LOCK IN SHARE MODE

5.4.4 SERIALIZABLE

• 가장 단순하고 엄격한 격리 수준이다.
• 4가지 격리수준에서 동시 처리 성능이 제일 떨어진다.
• 순수한 SELECT 조회도 잠금을 걸게 된다.
  • 그렇기 때문에 PHANTOM READ가 발생하지 않게 된다.

💡 그렇다면 PHANTOM READ 때문이라도 SERIALIZABLE 격리 수준을 택해야 할까?

→ InnoDB 스토리지 엔진에서는 갭 락, 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 *발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요성은 없다.

*물론 위에서 설명한 SELECT … FOR UPDATE, SELECT … LOCK IN SHARE MODE 쿼리같이 예외적인 상황은 제외하고 말이다.