[Real MySQL 8.0] 05. 트랜잭션과 잠금 (2) MySQL의 격리 수준

MySQL의 격리 수준 (Isolation level)

트랜잭션의 격리 수준이란?

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

 

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

위 표와 같이 크게 4가지의 격리 수준으로 나뉜다.

• 격리 수준은 (표의 순서 기준으로) 아래로 갈수록, 다음과 같은 성격을 가지는 것이 일반적이다.
  • 데이터 격리(고립) 정도가 높아진다.
  • 동시 처리 성능도 떨어진다.
• 하지만 SERIALIZABLE 격리 수준 외에는, 격리 수준이 서버 처리 성능을 크게 좌우하지는 않는다.
• 격리 수준에 따라 표에 정리된 세 가지 부정합이 발생할 수도, 발생하지 않을 수도 있다.
• 일반적인 온라인 서비스 용도의 DB에서는 READ COMMITED와 REPEATABLE READ 중 하나를 사용한다.
  • 그중에서도 MySQL에서는 REPEATABLE READ를 주로 사용한다.

 

(1) READ UNCOMMITED

• 각 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK여부에 상관없이 다른 트랜잭션에서 보인다.
• Dirty Read 현상을 초래하여 개발자 및 사용자를 혼란스럽게 만들며, 정합성에 문제가 많다.
• MySQL을 사용한다면 최소한 READ COMMITED 이상의 격리 수준을 사용하자 ! 

💡 Dirty Read

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도, 다른 트랜잭션에서 볼 수 있는 현상

 

(2) READ COMMITTED

• 트랜잭션의 변경 내용 중 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.
  • 어떻게? InnoDB의 변경 방식인 MVCC는 언두 로그에 변경 전 레코드를 백업하기 때문에, COMMIT 되기 전의 데이터를 보여줄 수 있다.
• Dirty Read는 발생하지 않지만, REPEATABLE READ 정합성에 어긋난다.
  • 같은 SELECT 쿼리를 여러 번 실행했을 때, 그 사이에 다른 트랜잭션에서 변경된 내용이 COMMIT 될 수 있기 때문이다.
  • 이 정합성이 지켜지지 않으면, 입금 및 출금 처리와 같이 동일 데이터를 여러 번 읽고 변경하는 작업에서 큰 문제가 될 수 있다.
• READ COMMITED 격리 수준과 달리, 이 격리 수준에서는 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동한다.
  • 트랜잭션을 시작한 상태에서 동일한 쿼리를 반복해서 실행하면, 다른 트랜잭션의 커밋과 관계 없이 계속 동일한 결과만 보게 된다.

💡 REPEATABLE READ 정합성

하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때, 항상 같은 결과를 가져와야 한다는 뜻

💭 그럼 REPEATABLE READ 정합성이 어긋난다는 건 트랜잭션 외부에서 SELECT하는 경우만 말하는 건가?? -> 맞는 것 같다.
트랜잭션 외부에서 SELECT한다는 게 정확히 뭐지? 트랜잭션을 시작하지 않고 SELECT를 하면 어떻게 동작하는 거지? -> BEGIN/COMMIT 없이 단일 조회 쿼리를 실행한다. 그래서 책에서 이 부분을 설명하는 SQL 예제의 AUTOCOMMIT은 모두 OFF 라고 설명한 것 같다. 트랜잭션을 시작하지 않으면, COMMIT 없이 바로 DB에 적용되고 격리 수준이 적용되지 않는다.

 

(3) REPEATABLE READ

• 앞서 설명한 REPEATABLE READ 정합성을 보장하는 격리 수준이다.
• MySQL의 InnoDB 스토리지 엔진에서 이 격리 수준이 기본으로 사용된다.
  • 바이너리 로그를 가진 MySQL 서버에서는 적어도 이 격리 수준 이상을 적용해야 한다.
• 이 격리 수준은 마찬가지로 MVCC 변경 방식을 통해 가능하다. 언두 영역에 백업된 이전 데이터를 이용해, 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보여준다.
• 이를 보장하기 위해서는 언두 영역의 데이터 중, 실행 중인 트랜잭션 번호의 구간 내에 존재하는 데이터는 보존돼야 한다.
  • 예를 들어
    1. TRX-ID 10 시작, SELECT
    2. TRX-ID 12가 INSERT, COMMIT 
    3. TRX-ID 10이 다시 SELECT -> 1번과 같은 결과를 보장함
    이 때 2번 동작 이후, TRX-ID 10과 TRX-ID 12 사이의 데이터가 보존되어있어야 3번 동작이 가능하다.
• 특정 트랜잭션을 시작하고, 장시간 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수 있으므로 주의하자.
• 그러나 이 격리 수준에서는 Phantom Read 현상을 발생한다.
  • SELECT ... FOR UPDATE  와 같은 조회 쿼리는 언두 로그가 아니라 현재 레코드의 값을 가져오기 때문이다. (SELECT할 레코드에 쓰기 잠금을 걸어야 하지만, 언두 레코드에는 잠금을 걸 수 없다. 그래서 현재 레코드의 값을 가져온다.)
  • 그래서 COMMIT 된 현재 데이터를 가져오게 된다.
  •  InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락을 사용하면 발생하지 않게 만들 수 있다.

💡 Phantom Read

다른 트랜잭션에서 수행한 변경 작업에 의해, 레코드가 보였다 안보였다 하는 현상

 

(4) SERIALIZABLE

• 이 격리 수준에서는, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다.
  • InnoDB 테이블에서 순수 SELECT 는 아무런 레코드 잠금도 설정하지 않고 실행되지만, 이 격리 수준에서는 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 한다.
  • 동시에 다른 트랜잭션은 잠금된 레코드를 변경하지 못한다.
  • Phantom Read가 발생하지 않지만, InnoDB 스토리지 엔진에서는 REPEATABLE READ 격리 수준에서도 충분히 방지할 수 있다. (굳이 이 격리 수준을 사용할 이유가 별로 없다) 

 

 

그래서 MVCC가 뭔데...(Multi Version Concurrency Control)

• InnoDB 스토리지 엔진의 특징으로, 트랜잭션 격리 레벨에 따라 조회되는 데이터가 달라지는 기술.
• 레코드에 잠금을 걸지 않고도, 트랜잭션 격리 레벨에 따라 일관된 읽기를 할 수 있다.
• 락으로 인한 동시성 문제를 보완할 수 있다.

이미지 출처: 테코톡 - 우기의 MySQL 아키텍처 캡쳐