MySQL 트랜잭션 격리 수준 (Isolation Level)

MySQL의 트랜잭션 격리 수준

격리수준이란? →

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

예를 들면 A트랜잭션과 B 트랜잭션이 돌아갈 때, A트랜잭션에서 B트랜잭션에서 변경된 데이터를 볼 수 있게 허용할지 말지 결정하는 수준.

크게 다음과 같다.

1. READ UNCOMMITTED (DIRTY READ) : 커밋되지 않은 읽기(오손 데이터 읽기) : 일반 DB에선 잘 안씀
2. READ COMMITTED : 커밋된 읽기
3. REPEATABLE READ : 반복 가능 읽기
4. SERIALIZABLE : 직렬화 가능 : 동시성이 중요한 DB에서는 잘 안씀

오름차순으로 트랜잭션간의 데이터 격리 정도가 높아지고, 동시 처리 성능이 떨어진다.

그새러 격리 수준이 높을 수록 MySQL 서버의 처리 성능이 떨어질 것으로 생각할 수 있는데 4단계가 아닌 이상 고만고만하다.

---

격리수준과 3가지 데이터 부정합 문제

	DIRTY READ	NON REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	O	O	O
READ COMMITTED	X	O	O
REPEATABLE READ	X	X	O (InnoDB 제외)
SERIALIZABLE	X	X	X

일반적인 온라인 서비스 용도 데이터베이스는 READ COMMITTED나 REPEATABLE READ를 쓴다.

오라클 DB는 주로 READ COMMITTED 를 쓰고. MySQL은 주로 REPEATABLE READ를 쓴다.

---

5.4.1 READ UNCOMMITTED : 커밋되지 않은 읽기

READ UNCOMMITTED 격리수준은 각 트랜잭션에서의 변경 내용이 COMMIT / ROLLBACK 여부와 상관 없이 변경 내용이 다른 트랜잭션에서 보인다.

원래 AUTOCOMMIT = FALSE 인 상황에서 SQL 적용시 커밋하지 않으면 SQL을 적용한 유저에게만 변경한 상태로 테이블이 관측된다. 근데 격리 수준이 READ UNCOMMITTED 이면 커밋이나 롤백이 아니어도 데이터 관측이 가능하다.

사용자 A가 어떤 데이터를 insert 하고 커밋은 하지 않았는데도 사용자 B가 해당 상태를 조회할 수 있는 격리 상태.

문제는 만약 사용자 A가 저 데이터를 결국 롤백해버렸는데 사용자 B는 그걸 모르고 커밋되지 않은 데이터를 기준으로 작업을 계속 진행할 수도 있다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 Dirty Read(오손 데이터 읽기) 라고 하고 한다. 그리고 이 Dirty Read가 허용되는 격리 수준이 바로 READ UNCOMMITTED 수준이다.

위 예시처럼 개발자와 사용자의 데이터 정합성이 깨져 혼란을 유발할 수 있기에, MySQL 사용시 최소 READ COMMITTED 격리 수준 사용 권장

---

5.4.2 READ COMMITTED

READ COMMITTED 격리 수준은 오라클 DBMS에서 사용되는 기본 격리 수준이고, 온라인 서비스에서 가장 많이 선택되는 격리 수준.

READ COMMITTED 수준에선 Dirty read가 발생하지 않음.

어떤 트랜잭션에서 데이터를 변경했더라도 커밋이 되어야만 다른 트랜잭션에서 조회 가능.

사용자1이 USER 테이블에서 1번 회원인 LIM을 LEE로 바꾸려고 시도한다.

이때 새로운 값인 LEE는 USER 테이블에 즉시 기록되고 언두(UNDO) 영역에 이전 데이터를 백업한다.

사용자 A가 커밋을 하기 전에 사용자 B가 1번 회원 조회를 시도하면 사용자 B의 쿼리 결과는 실제 테이블이 아닌 백업 영역에서 데이터를 가져오게 된다. 즉 변경 이전 데이터가 보인다.

좀 있어보이게 정리하면 READ COMMITTED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋 되기 전까지는 다른 트랜잭션에서 변경 내역을 조회할 수 없다.

이후 A가 커밋을 하게 되면 다른 트랜잭션에서 새롭게 변경된 값을 참조할 수 있게 된다.

하지만 이 READ COMMITTED 격리수준에서도 NON-REPEATABLE READ(REPEATEABLE READ가 불가능) 라는 부정합 문제가 존재한다.

예시 상황)

1. 트랜잭션 B가 begin으로 트랜잭션 시작
2. 트랜잭션 B가 SELECT * FROM USER WHERE NAME = ‘LEE’ 로 조회 → 검색 결과 없음
3. 트랜잭션 B가 아직 끝나지 않음 주의
4. 트랜잭션 A가 begin으로 트랜잭션 시작
5. UPDATE문으로 이제서야 회원 이름을 LEE로 변경 + COMMIT
6. 트랜잭션 B는 아직 진행중인데, 무한 새로고침 하다가 갑자기 데이터가 조회된다

이게 뭐가 문제냐면, REPEATABLE READ는 한 트랜잭션 내에서 같인 SELECT문을 실행하면 항상 같은 결과를 가져와야한다는 조건이 있는데, 이 정합성이 어긋남

 

일반적인 웹 프로그램에서는 크게 문제가 안될 수 있지만 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 돈이랑 연관되면 문제가 된다.

예를 들어 입금 / 출금을 담당하는 트랜잭션 A가 계속 돌고 있고, 오늘의 입금 금액의 총합을 조회하는 트랜잭션 B가 있다고 가정한다.

REPEATABLE READ 수준에 의하면 한 트랜잭션 내부에서 SELECT시 같은 결과가 출력 되어야하는데, 트랜잭션 A에 의해 값이 계속 변경되어 정합성이 깨짐. 아래 내용 요약해봄.

결론적으로 REPEATABLE READ는 트랜잭션 내 같은 쿼리 같은 결과 보장을 제공하지만 실시간 데이터 변화 대응에 어렵다.

또한 트랜잭션 없이 실행하는 SELECT 문과 트랜잭션 내부에서 실행되는 SELECT 문의 차이를 알아야함.

READ COMMITTED 격리 수준에선 두개가 별로 차이가 없다. 왜냐하면 SELECT시 어차피 가장 최근에 커밋된 데이터를 기준으로 데이터를 조회하기 때문.

하지만 REPEATABLE READ 격리 수준 트랜잭션에서는 최초 조회 시점에 스냅샷을 따고, 그 스냅샷을 기준으로 트랜잭션 내부에서 같은 SELECT 문에 대해 같은 결과를 반복해서 보여주기 때문에 개발자와 사용자 사이의 데이터 정합성 문제가 발생할 수 있다.

 

---

5.4.3 REPEATABLE READ

REPEATEABLE READ 수준은 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준.

MySQL은 최소 REPEATABLE READ 격리 수준 이상을 사용해야함 (SERIALIZABLE == 가장 높음).

여기서는 NON REPEATABLE READ 부정합 문제가 발생하지 않는다.

InnoDB 스토리지 엔진은 트랜잭션이 롤백될 가능성을 염두하여 변경 전 데이터를 언두 영역에 백업해두고 레코드 값을 변경한다. → 이러한 방법을 MVCC 라고 함 (Multi Version Concurrency Control)

REPEATABLE READ 는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내 동일한 결과를 보여준다.

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호 (AUTO_INCREAMENT)를 가지고 있고, 언두 영역에 백업된 데이터는 자기가 어느 트랜잭션에 의해 여기에 저장되었는지까지 기록되어 있다.

언두 영역에 데이터는 스토리지 엔진이 알아서 불필요하다고 판단되는 시점에 삭제한다. (아마 커밋 시점?)

REPEATABLE READ 격리 수준에서 MVCC를 보장하기 위해서는 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없음. → 쉽게 생각해서 아직 사용중인 옛날 버전의 데이터는 아무리 오래되어도 지울 수 없다. (커밋되지 않은 데이터는 아무리 오래 되어도 지울 수 없다)

좀더 쉽게 설명하면, 자기 자신의 트랜잭션 번호보다 작은 (먼저 생성된) 트랜잭션 번호에서 변경한 것만 보임.

여기서 발생할 수 있는 정합성 문제는 PHANTOM READ(유령 데이터 읽기) 이다.

트랜잭션 안에서 같은 조건으로 반복 조회 했는데 없던 데이터가 갑자기 생기는 현상을 의미한다.

먼저 MVCC 방식을 떠올리면, 실제 테이블에 새로운 데이터를 기록하고 이전 데이터는 백업 영역에 관리하여 동시성을 높이고 정합성을 유지하는 방식이다.

그래서 트랜잭션은 자신이 시작한 시점의 데이터를 Undo 영역에서 가져와서 읽어 한 트랜잭션 안에서 같은 쿼리가 같은 결과를 보장한다.

그래서 우리는 위와 같이 MVCC 방식으로 예를 들어 한 트랜잭션 안에서 같은 결과가 계속 출력되길 기대하고 똑같은 SELECT 문을 계속 입력했는데 갑자기 새로운 행이 추가되어 조회가 됨. (PHANTOM READ)

알고보니 다른 트랜잭션에서 새로운 행을 UPDATE하고 COMMIT하여 새로운 데이터가 보이게 되었음.

이유는 REPEATEABLE READ는 기존 레코드의 값을 스냅샷으로 고정하지만, 새로운 행의 INSERT는 막지 못하기 때문에 유령 데이터가 조회가 된다.

또한 REPEATABLE READ 수준 트랜잭션에서는 언두 영역의 과거 버전을 읽어오는데, 이를 스냅샷으로 가지고 있기 때문에 처음 자신이 조회한 값이 한 트랜잭션안에서 계속 같은 값으로 유지된다.

---

SELECT…FOR UPDATE로 특정 행의 락을 획득하고 해당 행에 변경을 막는다.

하지만 Undo 영역은 잠금을 지원하지 않기 때문에 과거 버전 대신 현재 최신 레코드를 읽어야 한다.

즉 SELECT .. FOR UPDATE 는 락을 걸기 위해서는 어쩔 수 없이 최신 레코드를 읽어야 하므로 UNDO 영역을 무시하고 현재 데이터를 읽는다.

---

5.4.4 SERIALIZABLE

가장 단순하면서 엄격한 격리 수준 → 동시 처리 성능 느림, 다른 격리수준보다 떨어짐.

InnoDB 스토리지 엔진 테이블에서 기본적으로 순수한 SELECT은 레코드 잠금 필요 없음(Non-locking consistent read , 잠금이 필요 없는 일관된 읽기).

근데 만약 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 반드시 해당 레코드에 대한 락이 필요하다. 동시에 다른 트랜잭션들은 그 레코드에 변경 불가.

즉, 한 트랜잭션이 그 레코드를 잡고 있으면 다른 트랜잭션에선 아예 접근조차 불가.

그러다보니, REPEATABLE READ 수준에서는 트랜잭션 내 동일한 쿼리 동일한 결과를 보장했지만 다른 트랜잭션에서 데이터를 삽입하는 것을 막지 못하여 PHANTOM READ 문제가 발생하였지만, 여기서는 다른 트랜잭션이 접근조차 할 수 없기 때문에 PHANTOM READ 정합성 문제가 발생하지 않음.