[Real MySQL] MySQL의 격리 수준

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

 

격리 수준

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ
SERIALIZABLE

(밑으로 갈수록 격리 수준이 높아지고, 처리 성능이 떨어지지만 SERIALIZABLE이 아니라면 크게 성능 개선, 저하는 발생하지 않는다)

 

READ UNCOMMITTED는 트랜잭션이 커밋되지 않은 상태에서도 조회가 가능하기 때문에 이상한 값이 읽힐 수 있고 이를 더티 리드라고 하는데 일반적인 데이터베이스에서는 거의 사용하지 않는다.

SERIALIZABLE도 동시성이 중요한 데이터베이스에서 거의 사용되지 않는다.

 

READ COMMITTED는 오라클에서 기본으로 사용되는 격리 수준으로 가장 많이 선택되는 단계인데 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다. 만약 트랜잭션이 커밋되기 전에 다른 트랜잭션에서 조회를 한다면 언두 로그에 백업(복사)해둔 변경 전 데이터가 반환이 된다. 

READ COMMITTED는 NON-REPEATABLE READ라는 부정합의 문제가 남아있는데 처음 조회를 했을 때는 바뀌지 않은 데이터가 중간에 다른 트랜잭션에서 UPDATE를 하고 커밋을 한 뒤에 다시 조회를 했을 때 바뀐 데이터가 반환되는 것이다.

 

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준으로 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 이상의 격리 수준을 사용해야 한다.

InnoDB 스토리지 엔진은 트랜잭션이 롤백될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경하는데 이러한 변경 방식을 MVCC(Multi Version Concurrency Control)라고 한다.

 

[MVCC와 언두 로그]

MVCC(Multi Version Concurrency Control)란?

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS(InnoDB 스토리지 엔진은 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공)가 제공하는 기능이다.

가장 큰 목적은 잠금을 사용하지 않는 일관적 읽기를 제공하는 데 있으며 InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현한다. (Multi Version은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미)

 

언두 로그(Undo LOG)란?

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다. 이렇게 백원된 데이터를 언두 로그(Undo Log)라고 한다.

언두 로그는 트랜잭션이 롤백 되었을 때, 변경 전 데이터로 복구하도록 도와주고 격리 수준을 보장해준다. 하지만 트랜잭션이 길게 지속될 경우 다른 트랜잭션들에서 변경된 데이터들이 언두 로그에 쌓이게 되어 조회 쿼리 성능이 떨어질 수 있다. 언두 로그 공간에 대한 문제는 MySQL 8.0 이후로 많이 좋아졌지만 트랜잭션이 장시간 유지되는 것은 여전히 좋지 않다.

그래서 언두 로그 레코드를 모니터링 하는 것이 좋은데 'SHOW ENGINE INNODB. STATUS \G' 를 입력하면 언두 로그 레코드 건수를 확인할 수 있다.

 

 

REPEATABLE READ는 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.

 

READ COMMITTED도 MVCC를 이용해 커밋되기 전의 데이터를 보여주는데 둘의 차이는 언두 영역에 백업된 레코드의 여러 개의 멀티 버전 가운데 몇번째 이전 버전까지 찾아 들어가느냐에 있다.

 

모든 InnoDB 트랜잭션은 순차적으로 증가하는 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 트랜잭션의 번호가 기록되어 있다. (언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요해지면 주기적으로 삭제)

REPEATABLE READ은 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다. 정확히는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.

 

userB(tx_id: 10)이 트랜잭션을 시작하고 다음 트랜잭션인 userA(tx_id: 12)가 시작해서 값을 바꿔도 userB는 언두 로그에 백업해둔 자기보다 낮은 tx_id의 데이터가 보인다. 하지만 REPEATABLE READ도 SELECT ... FOR UPDATE로 조회를 할 경우 현재 레코드의 값을 가져오는 부정합의 문제가 있긴 하다. 이렇게 다른 트랜잭션에서 변경을 해서 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 한다.