MySQL의 격리 수준

격리 수준이란

• 트랜잭션의 격리 수준(isolation level)이란, 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.
• 격리 수준 4가지
  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 상태가 높아지며, 동시 처리 성능도 떨어진다.

세 가지 부정합의 문제

💡 부정합 문제란
여러 트랜잭션이 동시에 실행될 때 데이터의 일관성이 유지되지 않는 상황을 말한다. (데이터 부정합)

• 격리 수준의 레벨에 따라 발생할 수도 있고 발생하지 않을 수도 있다.

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

DIRTY READ

• 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도(커밋하지 않은 상태) 다른 트랜잭션에서 볼 수 있는 현상
• 데이터가 나타났다가 사라졌다 하는 현상을 초래한다.
• 다른 트랜잭션이 잘못된 데이터를 읽을 수 있다.

NON-REPEATABLE READ

• 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 “REPATABLE READ” 정합성에 어긋난다.
  • 한 트랜잭션 내에서 여러 번 같은 데이터를 읽을 때 읽는 값이 서로 다른 경우 발생
• 다른 트랜잭션이 값을 변경하고 커밋했을 경우, 읽는 값이 달라지면서 데이터의 일관성을 유지하지 못하게 된다.
• 일반적인 웹 프로그램에서는 크게 문제되지 않을 수 있지만, 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 있다.
  • 예를 들어 한 트랜잭션에서 입금과 출금 처리가 계속 진행될 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회하는 경우, 조회할 때마다 총합이 달라진다.

PHANTOM READ

• 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상
  • 한 트랜잭션 내에서 여러 번 같은 쿼리를 실행할 때 처음에는 존재하지 않았던 데이터가 추가된 것처럼 보이는 경우 발생
• 예를 들어 다른 트랜잭션이 데이터를 추가할 경우 발생한다.
  • 사용자 B가 SELECT ... FOR UPDATE 를 여러 번 수행할 때, 중간에 사용자 A가 INSERT한다면 SELECT ... FOR UPDATE 의 쿼리 결과가 서로 달라진다.
  • SELECT ... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다.
  • 그래서 SELECT ... FOR UPDATE 나 SELECT ... LOCK IN SHARE MODE 로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다.

데이터베이스에서 사용하는 격리 수준

• 일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITED와 REPEATABLE READ 중 하나를 사용한다.
• 오라클 같은 DBMS에서는 주로 READ COMMITED 수준을 많이 사용한다.
• MySQL에서는 READTABLE READ를 주로 사용한다.

Auto Commit

• 데이터베이스에서 각 SQL 쿼리가 실행될 때 자동으로 커밋되는 기능이다.
  • 쿼리마다 COMMIT 명령을 따로 내릴 필요 없이 자동으로 트랜잭션이 완료된다.
• 기본적으로 활성화되어 있다.
  • SET AUTOCOMMIT=OFF 로 비활성화할 수 있다.
• 데이터 부정합 문제가 발생할 수 있다.
  • 중요한 데이터를 변경하거나 삭제할 때는 Auto Commit을 비활성화하는 것이 좋다.
  • 실수로 데이터를 변경하거나 삭제할 경우, 즉시 롤백할 수 있도록 백업을 유지하는 것이 좋다.

READ UNCOMMITTED

• 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.
• 예를 들어, 사용자 B는 사용자 A가 INSERT한 정보를 커밋되지 않은 상태에서도 조회할 수 있다. 이때, 사용자A가 커밋하지 않고 INSERT된 내용을 롤백하더라도 사용자 B는 여전히 해당 정보가 정상이라고 생각하고 계속 처리하게 된다.
  
• DIRTY READ라고도 한다.
• DIRTY READ를 유발시키므로, RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않는다.
  • 정합성에 문제가 많은 격리 수준이다.
• 일반적인 데이터베이스에서는 거의 사용하지 않는다.
  • MySQL을 사용한다면 최소한 READ UNCOMMITED 이상의 격리 수준을 사용할 것을 권장한다.

READ COMMITED

• 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.
• DIRTY READ 현상은 발생하지 않는다.
• NON-REPEATABLE READ라는 부정합의 문제가 있다.
• 중요한 것은 사용 중인 트랜잭션의 격리 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지를 정확히 예측할 수 있어야 한다.
• READ COMMITED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없다.
• 오라클 DBMS에서 기본으로 사용되는 격리 수준이다.
• 온라인 서비스에서 가장 많이 선택되는 격리 수준이다.

REPEATABLE READ

• MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.
  • MVCC: 트랜잭션이 롤백될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경하는 방식
• NON-REPEATABLE READ 부정합이 발생하지 않는다.
• PHANTOM READ 부정합이 발생한다.
• REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동한다.
• MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.
  • 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.

READ COMMITED 격리 수준과의 차이

• 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 영역에 백업된 모드 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함되어 있다.

언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.

REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다.

그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한 것은 아니다.

더 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존되어야 한다.

• 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.
  • 사용자 B가 먼저 조회를 시작하여 트랜잭션 번호가 10이고 이후 사용자 A가 데이터를 변경할 때 받은 트랜잭션 번호가 12라면, 사용자 B가 조회할 때 사용자 A(10보다 큰 트랜잭션 번호)가 변경하기 전 데이터만 보게 된다.

SERIALIZABLE

• 가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다.
• 다른 트랜잭션 격리 수준보다 동시 처리 성능이 덜어진다.
• InnoDB 테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다.
  • 순수한 SELECT 작업: INSERT ... SELECT ... 또는 CREATE TABLE ... AS SELECT ... 가 아닌 작업
  • InnoDB 매뉴얼에서 자주 나타나는 “Non-locking consistent read(잠금이 필요 없는 일관된 읽기)”라는 말이 이를 의미한다.
• 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 한다.
  • 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.
  • 즉, 한 트랜잭션에 서 읽고 쓰는 레코드를 다른 크랜잭션에서는 절대 접근할 수 없다.
• 일반적인 DBMS에서 일어나는 PHANTOM READ라는 문제가 발생하지 않는다.
• InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에, 굳이 SERIALIZABLE을 사용할 필요성은 없어보인다.
  • 엄밀하게는 SELECT ... FOR UPDATE 나 SELECT ... FOR SHARE 의 경우 PHANTOM READ가 발생할 수 있지만, 레코드의 변경 이력(언두 레코드)에 잠금을 걸 수는 없기 때문에 이러한 잠금을 동반한SELECGT 쿼리는 예외적인 상황으로 볼 수 있다.
• 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.

Reference

참고 서적

📔 Real MySQL 8.0