[MySQL] 6. 트랜잭션 격리 수준 (with InnoDB)

 

1. Dirty Read

커밋되지 않은 트랜잭션이 변경한 데이터를

다른 트랜잭션 조회쿼리에서 읽는 것.

 

만약 변경을 실행했던 트랜잭션이 롤백되기라도 하면

조회 쿼리가 말도안되는 데이터를 읽은 셈이 된다.

 

♣ InnoDB read committed 격리수준에서는 dirty read 문제를 어떻게 해결하는가?

커밋되지 않은 트랙잭션에 의해 변경된 데이터를 읽을 때

undo 로그를 이용해서 이전 상태를 읽는다.

 

 

2. Non-Repeatable Read

한 트랜잭션 내에서 똑같은 조회 쿼리가 두번 실행되었을 때

결과가 서로 다른 것.

 

♣ InnoDB repeatable read 격리수준에서는 non-repeatable read 문제를 어떻게 해결하는가?

트랜잭션이 커밋되었다고 무조건 undo 로그를 지워버리는 대신,

커밋된 트랜잭션보다 먼저 시작된 트랜잭션이 아직 실행중이라면

해당 undo 로그를 유지한다.

그럼으로써

실행중인 트랜잭션은 항상

자신이 시작되기 전의 데이터를 읽게된다.

 

3. Phantom Read

한 트랜잭션 내에서 똑같은 조회 쿼리가 두번 실행되었을 때

있던 레코드가 없어졌거나

없었던 레코드가 생겨있는 것.

 

InnoDB의 경우 repeatable read 격리수준에서도 이러한 문제가 발생하지 않는다.

(select ~ for update 같은 경우 제외)

 

♣ InnoDB의 repeatable read 격리수준에서는 phantom read 문제를 어떻게 해결하는가?

데이터 생성 & 삭제 역시 undo 로그로 유지되기 때문에

조회 트랜잭션보다 이후에 일어난 데이터 생성/삭제가 조회 쿼리에 반영되지 않는다.

 

♣ select ~ for update 의 예외

select ~ for update 문장의 경우, 읽기 쿼리지만 변경 쿼리와 같이 레코드에 exclusive lock 을 건다.

이친구는 조회는 조횐데 undo 로그도 사용하지 않는다.

 

잔고가 1000원 이상인 사용자 목록을 select ~ for update 로 두번 조회한다고 해보자.

exclusive lock 덕분에 repeatable read 현상은 발생하지 않는다.

하지만 만약 잔고가 1000원 이상인 새로운 데이터가 생성된다면?

그러면 두번째 select ~ for update 문에서 이 레코드도 조회 될 것이다.

 

이 문제까지 완벽하게 막고싶다면?

InnoDB 스토리지 엔진에서는 넥스트 키 락(을 통해 사용하는 갭 락) 을 이용해서 막을 수 있다.

(완벽하게 막을 수 있나? 인덱스에 따라서 다를것같기도 하고.. 갭 락에 대해 정확히 알지를 못해서...)

innodb_locks_unsafe_for_binlog 시스템변수가 비활성화되면(0으로 설정되면)

변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다.

 

4. Serializable

한 트랜잭션에서 읽고 쓰는 레코드를

다른 트랜잭션에서 절대 접근할 수 없는 것.

대신 동시처리 성능도 다른 격리 수준들에 비해 떨어진다.

 

innoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 repeatable read 격리수준에서도 이미 phantom read 가 발생하지 않는다고 한다. 따라서 굳이 serializable을 사용할 필요성은 없어보인다고 한다.

 

 

---

참고

1

Real MySQL 8.0 (백은빈, 이성욱)

2

[DATABASE] SELECT ~ FOR UPDATE 란?

https://dololak.tistory.com/446

3

[MySQL] 트랜잭션, 잠금(Lock)

https://velog.io/@fortice/MySQL-트랜잭션-잠금Lock