MySQL의 격리 수준

[ MySQL의 격리 수준이란? ]

 트랜잭션과 락은 떨어질 수가 없는 관계이다. 그리고 락에 따라서 더 데이터가 정합적으로 작동하게 만들 수록 동시성에 관한 문제가 생길 수 밖에 없다. 대부분의 DB는 이 정도를 옵션으로 정하게 해준다. MySQL에는 총 4가지의 옵션이 있는데, 이 옵션들에 관해서 알아보고 어떻게 이를 처리하는지 알아보자. 실습을 위한 세팅을 어떻게 하는지는 다음 글을 참고 바란다.

 

격리 수준 별 문제점 요약 정보:

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

 

[ READ UNCOMMITED ]

 이름부터가 커밋되지 않은 내용을 읽는다는 의미이다. 이 상황은 서로 다른 두 세션에서 접근을 했을 때 한 세션에서 데이터를 INSERT를 하거나 혹은 DELETE 등의 내부 DB의 값을 write를 했을 때, 아직 커밋하지 않은 상태임에도 그 상태를 볼 수 있다. 왜냐하면 InnoDB 내부의 엔진 내부에서 undo 로그를 따다가 지금 요청 번호보다 앞에 걸 줘야 하는데 그러지 않기를 요청했기 때문이다. 그러면 아직 dirty 상태에서 flush되지 않은 데이터를 다른 세션에서 열람할 수 있는데 이 현상을 DIRTY READ라고 한다.

 

 사실 이건 트랜잭션 격리 수준이 아니라 일반 코드로 비유하면 스프링에서 service나 controller 같은 데에 public static으로 열어놓은 lock없는 변수와 같다. 그냥 쓰면 안된다. DIRTY READ를 설명하기 위해서 넣어놨다.

 

확인용 코드 및 상황 이미지:

[ READ COMMITTED ]

 커밋돼서 DB에 있는 내용만 읽는 모드이다. 오라클 DBMS 같은데서 기본적으로 사용되는 격리 수준으로 앞에서 일어나는 DIRTY READ 상태가 일어나지 않게 해준다. 위의 예시에서 세션을 READ COMMITTED 모드로 바꾼 뒤, 모든 Member 내부 데이터를 싹 다 지우고 다시 실행을 해보면 아래와 같은 결과가 나온다.

 

확인용 코드 및 상황 이미지:

 그런데 이 모드의 치명적인 단점은 NON-REPEATABLE READ가 일어난다는 점이다. NON-REPEATABLE READ는 동시 트랜잭션 내에서 조회할 때마다 항상 값이 같아야 하는데, 다른 현상을 의미한다. 이는 금융 거래 같은 경우에서 일어난다면, 치명적인 에러가 일어날 수 있다. 예시는 아래에서 확인할 수 있다. 아직 세션 2번은 ROLLBACK이나 COMMIT 같은 명령으로 트랜잭션을 끝내지 않았음에도 같은 트랜잭션 내에서 하나는 DATA가 조회되고 다른 하나는 DATA가 조회되는 현상이 일어남을 확인할 수 있다.

확인용 코드 및 상황 이미지:

[ REPEATABLE READ ]

 Oracle DB와 다르게 MySQL에서는 InnoDB 스토리지 엔진은 해당 옵션을 기본적으로 사용하며 이 격리 수준에서는 위에서 일어나는 문제점이었던 NON-REPEATABLE READ를 방지해준다. 이것이 가능한 이유는 Undo 로그와 TRX-ID 덕분인데, TRX-ID는 트랜잭션이 일어난 순서를 기록하는 용도로 사용된다. Undo 로그는 이전 상태에 대해서 TRX-ID와 데이터를 기록해두어 이 값을 통해서 ROLBACK시 복구하거나 아니면 Transaction에서 NON-REPEATABLE READ가 일어나는 것을 방지해준다.

 

 아래의 이미지를 보면 세션 1과 세션 2의 TRX_ID를 주의깊게 보면 테이블을 만든 그 상태가 TRX_ID = 0이라고 하고 세션 2가 트랜잭션 시작하는 부분이 TRX_ID = 1, 세션 1이 트랜잭션 시작하는 부분을 TRX_ID = 2라고 적혀있다. 이렇게 트랜잭션 단위의 순서를 기억해놓음으로써 세션 2에서는 본인보다 뒤 번호의 트랜잭션이 일어나도 이를 무시하고 앞 번호의 데이터를 조회한다. 이는 redo 로그가 있기 때문에 가능한 일이다.  또한 이런 방법으로 구현이 되면 더이상 non-repeatable read는 일어나지 않는다.

 

확인용 코드 및 상황 이미지:

[ SERIALIZABLE ]

 SERIALIZABLE은 가장 엄격한 격리 수준이다. 마치 모든 수동 트랜잭션 또한 자동 트랜잭션 커밋 취급해버린다. 트랜잭션이 한 곳이 모두 끝나야 다른 트랜잭션을 시작할 수 있다. 이는 직접 해보길 바란다. 실행을 해보면 알겠지만 트랜잭션이 세션 1에서 열리고 세션 2에서도 열 수는 있다. 그런데 세션 1에서 SQL문 아무거나 하나 날려보고 세션 2에서 SQL문 하나 아무거나 날려보면 TIMEOUT까지 세션 2는 대기한다. 왜냐하면 세션 1이 끝날 때까지 기다리고 있기 때문이다.

 

[ Phantom Read ]

 Phantom Read는 MySQL에서 내부 구현을 InnoDB 스토리지 엔진을 사용한 다음부터 일어나지 않는다고 한다. 하지만 다른 DB는 일어날 수 있는데, Phantom Read는 Non-Reapeatable Read랑 비슷한데 Repeatable Read 모드에서 일어나는 현상이다. 간단하게 설명하면 아래와 같다.

 

1. 세션 A에서 트랜잭션 1을 열어 Member 테이블을 선택만 함.

2. 세션 B에서 트랜잭션 2을 열어 Member 테이블을 INSERT 혹은 DELETE를 함.

3. 세션 2에서 해당 데이터를 커밋함.

4. 세션 1에서 Member 테이블을 조회함.

5. 데이터가 이전과 다름. 

 

 Repeatable read에서 예시가 INSERT라 뭔가 잘못된 것 같기도 한데, 보통 non-repeatable read는 수정하는 상황에서 일어나고 phantom read는 삽입 혹은 삭제 상황에서 일어난다. 그리고 일어나는 현상은 같은 트랜잭션 내에서 같은 테이블 조회시 데이터가 동일하지 않은 현상을 나타내는 현상들이라고 기억해두면 될 것 같다.

[ 참고 자료 ]

https://steemit.com/kr/@yjiq150/db-transaction-isolation

VLDB - Lab 강의 자료

Real MySQL 8.0 - 백은빈, 이성욱 지음