Real My SQL 8.0 읽고 공부하기 - ⑤-④ MySQL의 격리 수준

트랜잭션의 격리 수준 이란 여러 트랜잭션이 동시에 처리 될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나

조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

 

격리 수준을 알기 전에 용어 부터 정리해보자

 

1. DIRTY READ

 

한 트랜잭션이 다른 트랜잭션에 의해 수정되었지만 커밋되지 않은 데이터를 읽어오는 상황

만약 커밋되지 않고 롤백되어버린다면 데이터가 일관되지 않은 상태여서 잘못된 결과가 도출될 수 있다.

 

 

2. NON-REPEATABLE READ

 

동일한 트랜잭션 내에서 처음 조회한 데이터가 다른 트랜잭션에 의해 수정되고 커밋되고나서 다시 조회한

경우 수정된 데이터를 읽어오는 상황

 

 

3. PHANTOM READ

 

트랜잭션이 특정조건을 만족하는 행 집합 을 읽은 후 에 동일한 트랜잭션 내에서 혹은 다른 트랜잭션에 의해 새로운

행이 삽입되거나 기존 행 삭제 또는 수정 되는 경우 발생하는 현상

마치 유령행이 나타나거나 사라진 것처럼 다음 읽기에서 다른 행 집합을 볼 수 있게된다.

 

 

트랜잭션 격리수준에서 발생할 수 있는 현상 목록 표는 다음과 같다.

 

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

 

 

 

1) READ UNCOMMITTED

 

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

이로인해 이 격리 수준에서는 DIRTY READ가 발생하여 위의 트랜잭션이 ROLLBACK이 발생하여도 교정해주지 않게되어

일관적이지 않은 데이터 처리가 진행된다.

 

RDBMS표준에서는 해당 격리수준을 인정하지 않을 정도로 정합성에 문제가 많다.

MySQL사용시에는 최소 READ COMMITTED 이상의 격리수준을 권장한다.

 

 

---

 

 

2) READ COMMITTED

 

다른 트랜잭션에서 데이터를 변경해도 COMMIT이 완료된 데이터만 조회할 수 있도록 한다. DIRTY READ가 발생하지 않는다.

만약, 다른트랜잭션에서 변경을 진행중이면 언두 영역의 백업된 레코드에서 데이터를 가져와서 보여준다.

이에 따라 COMMIT이 완료된 데이터만 보여주는 것이다.

 

해당 격리수준에서도 NON-REPEATABLE READ라는 부정합 문제가 존재한다.

위에 설명에 따라 COMMIT 데이터들만 읽어오기 때문에 ,

하나의 데이터에 A트랜잭션 조회 -> B트랜잭션 수정 & 커밋 -> A트랜잭션 조회가 진행되면 처음에 조회했던 데이터와 다른

데이터가 조회되어서 REPEATABLE READ 정합성을 깨뜨리게 된다.

 

일반적인 웹 프로그램은 문제가 없을 수 있지만 금전적인 처리와 같은 것에 연결되면 문제가 생길 수 있다.

하나의 트랜잭션에서 실행하였던 처음 조회한 금액과 나중에 조회한 금액이 다르게 나오면 안되기 때문이다.

 

트랜잭션 내에서 실행되는 SELECT문장과 트랜잭션 없이 실행되는 SELECT는 격리수준에 따라 작동되는 차이가 있다.

READ COMMITTED 수준에서는 차이가 별로 없지만 REPEATABLE READ 수준에서는 기본적으로 SELECT 문장도

트랜잭션 범위내에서 작동하는데, BEGIN 명령으로 트랜잭션을 시장한 상태에서 온종일 동일한 쿼리를 반복해서 실행해도

동일한 결과만 보인다.

 

 

 

---

 

3) REPEATABLE READ

 

InnoDB 스토리지 엔진에서 기본으로 사용되는 격리수준이다.

NON-REPEATABLE READ 부정합이 발생하지 않는다. 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를

언두 공간에 백업해두고 실제 레코드 값을 변경한다. (MVCC)

 

READ COMMITTED도 사실 COMMIT 되기전 데이터를 보여주는데 이 둘의 차이는 여러버전 가운데 몇번째 이전 버전까지 찾아

들어가냐에 따라 달려 있다.

InnoDB의 모든 트랜잭션은 고유한 트랜잭션 번호(순차적인 값)을 가지는데 언두 영업에 백업된 모든 로그에는 변경을 발생시킨

트랜잭션 번호가 포함되어있다. 

REPEATABLE READ 수준에서는 MVCC를 보장하기 위해 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는

삭제할 수 없다. (특정 트랜잭션 번호의 구간내에서 백업된 언두 데이터가 보존되어야한다.)

 

REPEATABLE READ 수준에서도 부정합이 발생할 수 있는데 

트랜잭션 A가 INSERT하는 도중 B가 SELECT ... FOR UPDATE 쿼리

(해당 레코드에 쓰기 잠금을 걸어야 하는데 언두 레코드에는 잠금을 걸수 없다.)

로 해당 테이블을 조회 했을 때 PHANTOM READ가 발생할 수 있다. 

그래서 SELECT ... FOR UPDATE 나 SELECT ... LOCK IN SHARE MODE로 조회하는 레코드는 현재 레코드의 값을 가져오게된다.

 

---

 

4) SERIALIZABLE

 

가장 단순하면서 가장 엄격한 격리 수준이다.  동시처리 성능도 떨어진다.

InnoDB테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금 없이 실행되는데

이는 Non-locking consistent read (잠금이 필요없는 일관된 읽기) 라고 한다.

 

하지만 격리수준이 SERIALIZABLE이 되면 읽기 작업도 공유잠금(읽기 잠금)을 획득해야 하며, 다른 트랜잭션은 해당 레코드

변경이 불가능하다.

 

PHANTOM READ를 막기 위해 사용되나, InnoDB에서는 갭락, 넥스트 키락 덕분에 REPEATABLE READ 격리 수준에서도

PHANTOM READ가 발생하지 않기 때문에 굳이 사용할 필요는 없다.