MySQL의 격리 수준(Isolation Level)

 MySQL의 격리 수준

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

격리 수준은 크게 4가지로 나뉜다.

1. Read UNCOMMITTED
2. Read COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

1번부터 4번까지 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시 처리 성능도 일반적으로 떨어질 수 있다.
격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 생각할 수 있지만, 사실 SERIALIZABLE 수준이 아니라면 크게 성능 저하가 발생하지는 않으니 안심해도 된다.

 

하나 특별한 부분이 있다. SQL-92 or 99 표준에 따르면 REPEATABLE READ 격리 수준에서는 PHANTOM READ가 발생할 수 있다.
하지만, InnoDB는 독특한 특성 때문에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않는다. 

 

일반적인 온라인 서비스 용도의 DB에서는 READ COMMITTED와 REPETABLE READ 중 하나를 사용한다.
ORACLE과 같은 DBMS는 READ COMMITTED 수준을 많이 사용하고, MySQL에서는 REPEATABLE READ를 주로 사용한다.

 

 

1. READ UNCOMMITTED

각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.

사용자 A가 INSERT를 수행한 후 COMMIT 하기도 전에 사용자 B가 해당 데이터를 검색하는 경우, 이를 막지 않는다. 
이 상황에서 사용자 A가 처리 도중 어떠한 문제로 인해 ROLLBACK을 진행한다면 여전히 사용자 B는 존재하지 않는 데이터를 가지고 작업을 진행할 것이라는 것이다.

 

문제점

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았음에도 다른 트랜잭션에서 볼 수 있는 현상을 Dirty Read라고 한다.
이러한 문제 때문에 READ UNCOMMITTED는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많다.

MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것!

 

 

2. READ COMMITTED

Oracle DBMS에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다.

해당 격리 수준부터는 Dirty Read와 같은 현상이 발생하지 않는다.
어떠한 트랜잭션이 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.

READ COMMITTED 격리 수준에서는 언두 로그를 사용한다.
데이터 변경이 발생하면 언두 로그 영역에 이전 레코드 정보가 백업된다. 따라서 사용자 A가 데이터를 변경하고 아직 COMMIT이 되기 전이라면 사용자 B는 테이블을 조회하는 것이 아니라 언두 영역에 백업된 레코드를 가져온다.

 

문제점

READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ라는 레코드가 반복되어 읽어질 때 발생하는 데이터 부정합 문제가 있다.

기본적으로 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 따를 수 있어야 한다.

하지만, 해당 격리 수준에서는 사용자 B가 먼저 트랜잭션을 시작하고 처음 특정 조건으로 레코드를 검색했을 때는 결과가 없었지만 그 사이에 사용자 A가 새로운 데이터를 COMMIT 하였고, 다시 사용자 B가 같은 조건으로 레코드를 검색하면 데이터가 1개 검색되는 문제인 것이다. 이는 REPEATABLE READ 정합성에 어긋나는 것이다.

 

이러한 부정합 현상은 일반적인 상황에서는 문제가 되지 않을 수 있지만 하나의 트랜잭션에서 동일한 데이터를 여러 번 읽고 변경하는 작업이 금전적으로 관련되면 문제가 될 수 있다.

 

 

3. REPETABLE READ

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

트랜잭션 내부에서 실행되는 SELECT와 외부에서 실행되는 SELECT의 차이가 없는 READ-COMMITTED 격리 수준과는 다르게,
REPEATABLE READ 격리 수준은 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 동작한다.

 

해당 격리 수준 부터는 NON-REPEATABLE READ 현상이 발생하지 않는다.  InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해 두고 실제 레코드 값을 변경한다.
이러한 변경 방식을 MVCC(Multi Version Concurrency Control) 이라고 한다.

 

REPEATABLE READ는 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.
READ_COMMITTED 격리 수준 또한 MVCC를 이용해 COMMIT 되기 전의 데이터를 보여주기는 한다. 하지만, 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.

 

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차 증가)를 가진다. 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함된다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단되는 시점에 주기적으로 삭제한다.

실행 중인 트랜잭션 중에 가장 오래된 트랜잭션 번호를 기준으로 그보다 앞선 트랜잭션 번호를 가진 언두 영역의 특정 구간 데이터는 삭제할 수 없고 보존해야 한다.

 

위 그림 예시에서 사용자 A의 트랜잭션 번호는 12이고, 사용자 B는 10이다. 이때 사용자 A가 레코드를 변경했다고 하더라도 사용자 B는 자신보다 작은 트랜잭션 번호 즉, 10보다 작은 트랜잭션 번호를 가진 변경사항만을 보게 된다.

 

문제점

REPEATABLE READ 격리 수준에서도 PHANTOM READ 부정합 현상이 발생할 수 있다. 

REPEATABLE READ에서는 하나의 트랜잭션에서 두 번의 SELECT 쿼리에 대한 결과는 똑같아야 한다고 했다.
하지만 SELECT ... FOR UPDATE 쿼리나 SELECT ... LOCK IN SHARE MODE로 조회되는 것은 결과가 다를 수 있다.

SELECT FOR UPDATE 쿼리?
가장 먼저 LOCK을 획득한 세션의 검색된 레코드들이 UPDATE 쿼리 후 COMMIT 되기 이전까지 다른 세션들은 해당 레코드들을 수정하지 못하도록 하는 기능

그 이유는 SELECT ... FOR UPDATE 쿼리의 경우 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없기 때문이다. 따라서, 언두 영역의 변경 전 데이터를 가져오는 것이 아닌 현재 레코드의 값을 가져오게 되는 현상이 발생한다.



이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 한다. 

 

✅ InnoDB 스토리지 엔진에서도 SELECT FOR UPDATE 또는 SELECT FOR SHARE 등 잠금을 동반한 SELECT 쿼리에서는 PHANTOM READ가 발생할 수 있지만, 이것은 예외적인 상황이다. 일반적인 상황에서는 InnoDB 스토리지 엔진의 REPEATABLE READ 격리 수준에서는 갭 락과 넥스트 키 락 덕분에 PHANTOM READ가 발생하지 않는다고 한다.

 

 

4. SERIALIZABLE

해당 격리 수준은 가장 단순하면서 동시에 가장 엄격하다. 그만큼 동시 처리 성능도 다른 트랜잭션의 격리 수준보다 많이 떨어진다.

 

InnoDB 테이블에서는 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 없이 실행된다. 
하지만 트랜잭션 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다. 

한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다.

 

SERIALIZABLE 격리 수준에서는 일반적인 DBMS에서 일어나는 PHANTOM READ 문제가 발생하지 않는다.

 

하지만 InnoDB에서는 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않으므로 굳이 SERIALIZABLE을 사용할 필요성은 없다