[정리] MySQL의 격리 수준
서론
항상 공부했어도 시간이 조금만 지나는 격리 수준 개념을 지금 시점에서 다시 한번 깔끔하게 정리해보기 위한 글이다.
격리 수준(Isolation Level)
격리 수준이란, 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션이 변경하거나 조회하는 데이터를 접근 할 수 있게 허용하는걸 어느 수준 까지 할 지 제어할지 정하는 것을 의미한다.
격리 수준은 다음과 같이 분류 될 수 있다.
- READ COMMITED
- READ UNCOMMITED
- REPEATABLE READ
- SERIALIZABLE
아래로 갈수록 격리 수준이 높아지는 반면 동시 처리 성능은 떨어진다. 다만, SERIALIZABLE 수준이 아니라면 다른 격리 수준에서는 크게 성능 편차는 발생안한다고 한다.
이런 격리 수준을 이야기하면 항상 언급되는 세 가지 부정합성 문제가 있으며 다음 표로 정리할 수 있다.
표 그림 ㄱ.ㄱ
SQL-92 or SQL-99 표준에 따르면 REPEATABLE READ 수준에서는 팬텀 리드가 발생할 수 있지만, InnoDB의 독특한 특성 덕분에 REPEATABLE READ 수준에서도 팬텀 리드 부정합성 문제가 발생안한다.
MySQL은 기본적으로 REPEATABLE READ, Oracle은 기본적으로 READ COMMITED 수준을 쓴다.
READ UNCOMMITED
한 트랜잭션에서 변경된 내용이 커밋이나 롤백 여부와 관계없이 다른 트랜잭션에서 볼 수 있다.
예를 들어 새로운 레코드를 insert 명령을 때리고 아직 커밋을 하지 않았는데도 다른 트랜잭션에서 이 레코드를 볼 수가 있다.
이렇게 어떤 트랜잭션에서 처리한 작업이 아직 완료(커밋)되지 않았는데도 다른 트랜잭션이 볼 수 있는 현상은 부정합한 경우 중 하나로 볼 수 있고, 이를 Dirty Read 라 지칭한다.
READ UNCOMMITED 는 더티 리드가 허용되는 격리 수준인것이다. 더티 리드는 한 트랜잭션 범위에서 데이터가 나타났다 사라졌다하는 현상을 초래하여 개발자/유저의 혼란을 유발한다.
이 READ UNCOMMITED 격리 수준은 RDBMS 표준에서도 격리 수준으로도 인정 하지 않을 정도로 정합성에 문제가 많다.
READ COMMITED
Oracle의 기본 격리 수준인 READ COMMITED. Online 환경에서 가장 많이 선택되는 격리 수준이라 한다.
READ COMMITED는 커밋이 완료된 데이터만 다른 트랜잭션에서 볼 수 있기에 자연스럽게 더티 리드가 방지된다.
예를 들어
- 갑은 1번 사원의 이름을 변경
- 새로운 값은 원본 테이블에 즉시 기록
- 이전 기록은 언두 로그에 기록
- 갑이 커밋 수행 전 을이 1번 사원을 조회하면 이전 값으로 조회 됨(언두 로그에 백업되있는 레코드에서 가져온 것)
이 격리 수준도 부정합 문제가 존재한다. 바로 NON REPEATABLE READ.
한 트랜잭션 과정에서 같은 쿼리로 조회(SELECT)했는데 각 시점마다 결과가 달라지는 경우가 NON REPEATABLE READ 현상이라 할 수 있다.
첫 시점은 A였고 중간에 다른 트랜잭션이 커밋을 하여 데이터가 변경되 B로 변경되었다. 이후 같은 트랜잭션에서 똑같은 쿼리로 조회시 이게 B로 조회가 되는것이다.
이런 현상이 벌어지는 이유는 커밋이 되어 언두 로그를 안보고 원본 테이블을 바로 봐서이다.
이 부정합성 문제가 금전과 관련된 서비스와 관련해서 생기면 문제가 심각해진다.
다른 트랜잭션에서 입출금이 계속 진행될 때 다른 트랜잭션에서 오늘 입금 총액을 조회할 때 REPEATABLE READ가 보장 안되서 총합을 계산하는 조회(SELECT) 쿼리는 실행 시점마다 다를 것이다.
왜냐하면 총합을 계산하는 도중 다른 여러 트랜잭션들에서 입금을 계속한다면 수치가 계속 변할꺼고 총합을 계산하는 도중 여러번 이 입금액을 조회할때마다 다른 수치가 조회되고 이걸로 총액을 계산하게 되면 필연적으로 이상한 계산이 될 수 밖에 없을 것이다.
중요한것은 사용중인 격리 수준에 의해 실행되는 쿼리가 어떤 결과를 가져올 것인지 예측할 줄 알아야 한다는 것이다. 이를 위해 당연히 각 격리 수준의 동작 과정을 알야야되는 것이고...
가끔 트랜잭션 내에서 실행되는 SELECT와 트랜잭션 없이 실행되는 SELECT의 차이를 혼동하는 경우가 많다.
READ COMMITED 수준에서는 트랜잭션 내부와 외부가 별로 차이가 없다.
하지만 이후 나올 REPEATABLE READ 수준에서는 기본적으로 SELECT도 트랜잭션 범위 내에서만 작동한다.
즉, START TRANSACTION(BEGIN)으로 트랜잭션을 시작한 상태에서 온종일 동일한 쿼리를 반복해서 실행해봐도 동일한 결과만 계속 보게 되는것이다.(아무리 다른 트랜재션에서 커밋, 롤백 난리를 쳐도 말이다.)
이런 개념을 몰라서 정합성을 깨뜨리고 버그를 발생시키다면 원일 찾기가 꽤 까다로울거 같다.
REPEATABLE READ
InnoDB 스토리지 엔진의 디폴트 격리 수준이다. 바이너리 로그를 가진 MySQL에서는 최소 REPEATABLE READ 수준 이상을 써야한다.
이 수준에서는 READ COMMITED에서 일어나는 NON REPEATABLE READ 부정합이 방지된다.
InnoDB는 트랜잭션이 롤백 될 가능성에 대비하여 변경 전 레코드를 언두(UNDO) 공간에 백업해 두고 실제 레코드 값을 변경한다.
이런 방식을 MVCC(Multi Version Concurrency Control)이라 한다.
REPEATABLE READ 는 이 MVCC 이용해서 언두 영역에 백업된 이전의 데이터를 활용해 동일 트랜잭션 내에서는 동일한 조회 결과를 보여 줄 수 있게 보장한다.
사실 READ COMMITED 도 MVCC 활용해 커밋 이전의 데이터를 보여준다고 한다.
REPEATABLE READ와 READ COMMITED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가느냐에 있다.
- READ COMMITTED: 매 SELECT 쿼리가 실행될 때마다, 쿼리 실행 직전에 커밋된 최신 데이터를 찾아감
- REPEATABLE READ: 트랜잭션이 처음 시작된 시점, 트랜잭션 내내 첫 번째 읽기 때 만든 버전만 찾아감
모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호를 가진다. 이 번호는 순차적으로 증가하는 특징을 가지고있다.
언두 영역에 백업된 모든 레코드에는 변경을 발생 시킨 트랜잭션의 번호가 포함되어 있다.
그리고 언두 영역에 백업된 데이터는 InnoDB가 판단해서 불필요하다는 시점에 주기적으로 삭제한다.
REPEATABLE READ 에서는 MVCC 보장을 위해 실행중인 트랜잭션 중에서 가장 오래된 트랜잭션 번호보다 앞선 언두 영역의 데이터는 삭제가 불가하다.
그렇다고 가장 오래된 트랜잭션 번호 이전 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한건 아니다. 정확히는 트랜잭션 번호 구간 내에서 백업된 언두 데이터가 보존되있으면 된다.
199p 그림
사용자가 10번 트랜잭션 번호를 부여받았으면 이후 실행되는 모든 SELECT는 트랜잭션 번호가 10보다 작은 번호에서 변경된것만 보게된다.
그림 5.6에서는 언두 백업 데이터가 하나만 있는걸로 표현되었지만, 사실 하나의 레코드에 대해 백업이 하나 이상 얼마든 존재 가능하다.
어떤 사용자가 트랜잭션 시작하고 장시간 종료하지 않는다면 언두 영역의 백업 데이터가 무한정 커져 성능 저하가 발생할 수도 있다.
REPEATABLE READ 수준에서 발생 가능한 부정합 문제는 Phantom Read 이다.
그림 5.7
그림 5.7에서 갑이 INSERT 실행 도중 을이 SELECT ... FOR UPDATE로 조회 시 어떤 결과가 발생하는지 보여준다.
이렇게 레코드가 보였다 안보였다 하는게 팬텀 리드 현상.
SELECT ... FOR UPDATE의 결과가 다른 이유는 SELECT ... FOR UPDATE는 SELECT하는 레코드에 쓰기 잠금을 걸어야하는데, 언두 로그에는 잠금이 불가하다.
그래서 SELECT ... FOR UPDATE나 SELECT ... LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는게 아닌 현재 레코드값을 가져오는 것이다.
SERIALIZABLE
가장 단순하면서 가장 엄격한 격리 수준. 다만 순수한 SELECT 작업(INSERT ... SELECT ... 또는 CREATE TABLE ... AS SELECT ... 가 아닌)은 아무런 레코드 락도 걸지않고 실행시킨다.
InnoDB 메뉴얼에 자주 등장하는 Non-locking consistent read(잠금 필요 없는 일관된 읽기)라는 말이 이를 의미한다.
하지만 SERIALIZABLE 일 때 읽기 작업 시 공유 잠금(읽기 잠금)을 획득 해야 하며 이러면 동시에 다른 트랜잭션은 해당 레코드 변경이 불가해진다.
즉, 한 트랜잭션에서는 읽고 쓰고있는 레코드를 다른 트랜잭션에서는 절대 접근 못하는 것이다.
SERIALIZABLE 에서는 일반적인 DBMS에서 발생하는 팬텀리드가 발생하지 않겠지만, InnoDB의 경우는 갭 락, 넥스트 키 락 덕분에 REPEATABLE READ 수준에서도 팬텀 리드가 발생하지 않기에 굳이 SERIALIZABLE를 쓸 건 없어보인다.
엄밀하게 하면 SELECT ... FOR UPDATE or SELECT ... FOR SHARE 경우는 REPEATABLE READ에서 팬텀 리드가 발생 할 수도있다.
하지만 레코드 변경 이력(언두 로그)에 잠금을 걸수는 없기에, 이런 잠금을 동반한 SELECT 쿼리는 예외상황으로 볼 수 있다.