MySQL의 트랜잭션 격리 수준

 

Real MySQL 5장 트랜잭션과 잠금을 읽고 스터디한 내용을 정리해봅니다 😃

 

 

InnoDB를 통한 트랜잭션 격리 제공

---

트랜잭션 격리는 데이터베이스의 핵심 기능 중 하나입니다.

트랜잭션 격리는 여러 트랜잭션이 동시에 수정을 일으키는 쿼리를 요청했을 때,
신뢰성, 일관성을 유지하면서도 높은 성능을 유지하는 균형에 대한 이야기입니다.
트랜잭션 격리를 하지 않고 신뢰성을 낮추는 대신 성능을 높일 수도 있고,
트랜잭션 격리를 극단적으로 가져가며 신뢰성을 높이는 대신 성능을 다소 포기할 수도 있습니다.

트랜잭션이 안전하게 수행된다는 것을 보장하는 ACID 중 I가 바로 이 격리, Isolation의 약자입니다.

이전 포스팅에서 알아봤듯이, MySQL의 스토리지 엔진 가운데
InnoDB 만이 트랜잭션을 지원하기 때문에, MySQL의 트랜잭션 격리는 곧
InnoDB의 트랜잭션 격리와도 같습니다.
InnoDB가 기본 스토리지 엔진으로 사용되기 때문에 더더욱 그러합니다.

그러면 지금부터, MySQL의 InnoDB가 어떤 트랜잭션 격리 레벨들을 제공하는지,
각각의 장단점은 어떠한지, 무엇이 기본 설정으로 사용되는지에 대해 알아보겠습니다!

 

 

 

트랜잭션 격리 수준의 종류와 설정 방법

---

InnoDB는 총 네 가지의 트랜잭션 격리 레벨을 제공합니다.
이 네 가지 중에 선택해서 사용할 수 있는 거죠.

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

이 중 REPEATABLE READ 가 InnoDB의 기본 트랜잭션 격리 수준 설정입니다.

 

그러나 기본 격리 수준이 설정되어 있다고 하더라도,
항상 그 격리 수준으로만 동작하는 것은 아닙니다.
설정을 통해 격리 수준을 동적으로 변경할 수도 있습니다.
그럼 격리 수준 설정 방법에 대해 알아볼까요!

 

첫번째는 전역 설정입니다.

--transaction-isolation 명령어를 이용해
모든 커넥션에 대한 MySQL 서버 정책으로써 트랜잭션 격리 수준을 설정할 수 있습니다.

 

두번째는 개별 설정인데요,

클라이언트가 서버에 커넥션을 맺고 요청을 전달하면,
이는 MySQL 서버 내에서 하나의 세션으로 동작하게 됩니다.
이처럼 요청할 때 클라이언트가 SET TRANSACTION 명령어를 이용해
이번 요청에 한하여 트랜잭션 격리 수준을 다르게 설정할 수 있습니다.

이처럼 요청 시점에 트랜잭션 격리 수준을 다르게 설정하는 것은
스프링의 @Transactional 애너테이션에서 사용됩니다.
해당 애너테이션에 별다른 격리 수준 설정값을 전달하지 않으면,
해당 테이블에 적용되는 기본 트랜잭션 격리 수준을 적용합니다.
그러나 다른 격리수준 설정을 전달하면, 쿼리가 전달될 때 해당 격리 수준이 함께 전달되는 것입니다.

 

 

 

그리고 IntelliJ...

---

IntelliJ 내장 DataGrip 의 트랜잭션 격리 수준 제공

인텔리제이 DB 세션 관리

 

트랜잭션 격리 수준 별 동작 방식을 확인하고자 했습니다.
이런 저런 시도들을 해보다가 결국은 인텔리제이의 기능을 이용하기로 했습니다.

손쉽게 트랜잭션 모드를 제어할 수 있고, 격리 수준별로 테스트를 하기 쉬웠기 때문입니다.

실험은 RDS로 띄운 MySQL 8 인스턴스 기준으로 진행될 예정입니다.
MySQL 8버전에서는 기본 설정으로 스토리지 엔진이 InnoDB로 선택되고,
InnoDB의 기본 트랜잭션 격리 수준은 Repeatable Read 입니다.
InnoDB에서만 특별히 Repeatable Read 수준에서도 Phantom Read가 일어나지 않기 때문인데요,
이는 아래에서 다시 자세히 알아보겠습니다.

아래는 실험에 사용하기 위해 세팅한 테이블입니다.

drop table if exists member;

create table member
(
    id      bigint auto_increment primary key,
    name    varchar(255),
);

 

 

 

READ UNCOMMITTED

---

• 가장 낮은 격리 수준입니다
• Dirty Read 가 발생합니다
• Non-Repeatable Read 가 발생합니다
• Phantom Read 가 발생합니다
• 일반적인 데이터베이스에서 거의 사용되지 않습니다

이 격리 수준은 사용할 일이 거의 없으니 가볍게 보고 넘어가셔도 좋습니다.
(공식 문서에서는 데이터 정합성이 필요하지 않고, 대량의 데이터를 핸들링해야 하는 경우,
READ UNCOMMITTED 를 사용할 수도 있다고 이야기합니다)

 

Dirty Read on READ UNCOMMITTED

dirty read on read uncommitted

Read Uncommitted 에서는 Dirty Read에 대해서만 알아보고 넘어가곘습니다.

Dirty Read란, 한 트랜잭션에서 완료되지 않은 임시 데이터를 읽을 수 있는 것을 말합니다.
시뮬레이션에서 테스트한 시나리오는 다음과 같습니다.
A 트랜잭션을 열고 1번 멤버의 이름을 '레벨3 리차드'에서 '레벨4 리차드'로 UPDATE
B 트랜잭션을 열고 1번 멤버의 이름을 READ UNCOMMITTED 로 조회 -> 레벨4 리차드
B 트랜잭션을 열고 1번 멤버의 이름을 READ COMMITTED 로 조회 -> 레벨3 리차드
A 트랜잭션에서 Rollback
B 트랜잭션에서 READ UNCOMMITTED, READ COMMITTED로 조회 시 모두 레벨3 리차드 

아직 커밋되지 않은, 다른 트랜잭션에서 반영 시도중인 내용까지 읽는 것이 READ UNCOMMITTED 입니다
거의 사용할 일이 없을 거라 생각합니다.

 

 

 

READ COMMITTED

---

• READ UNCOMMITTED 보다 한 단계 높은 격리 수준입니다
• Dirty Read 가 발생하지 않습니다 👍
• Non-Repeatable Read 가 발생합니다
• Phantom Read 가 발생합니다
• Real MySQL책에서는, 이 격리 수준이 가장 많이 사용된다고 소개하고 있습니다.
• 오라클의 기본 트랜잭션 격리 수준입니다

 

READ UNCOMMITTED 에서 Dirty Read 이슈만 해소될 정도로 높아진 격리 수준입니다.
커밋으로 반영되지 않은 데이터가 읽히는 경우는 없는 거죠.

그러나 아직 남아있는 이슈가 있습니다. 
바로 한 트랜잭션에서 값을 반복해서 읽는데 결과가 달라지는 경우가 발생한다는 것입니다.

 

Non-Repeatable Read on READ COMMITTED

non-repeatable read

시나리오는 이렇습니다.
A 트랜잭션이 1번 멤버의 이름을 최초 조회했을 땐 '레벨3 리차드'입니다
B 트랜잭션이 '레벨4 리차드'로 값을 업데이트하고 커밋까지 해버립니다
A 트랜잭션이 다시 1번 멤버의 이름을 조회하면 이제는 '레벨4 리차드'입니다.

최종 커밋본 데이터가 변경되었기 떄문에 같은 트랜잭션에서 같은 데이터를 조회했음에도 값이 달라진 겁니다.
이 역시 데이터 정합성이 깨진 상황으로 볼 수 있습니다.
이런 상황을 Non-Repeatable Read 라고 합니다.
한 트랜잭션에서 같은 값을 반복해서 다시 조회했음에도 데이터가 변경될 수 있다는 것입니다.
A 트랜잭션이 최초 조회 이후 B 트랜잭션에서 변경, 커밋하고 A 트랜잭션이 다시 조회하면 값이 달라지는 겁니다.

 

 

 

REPEATABLE READ

---

• READ COMMITTED 보다 한 단계 높은 격리 수준입니다
• Dirty Read 가 발생하지 않습니다 👍
• Non-Repeatable Read 가 발생하지 않습니다 👍
• InnoDB 한정, Phantom Read 가 발생하지 않습니다 👍
• InnoDB 의 기본 트랜잭션 격리 수준입니다

 

REPEATABLE READ

repeatable read

READ COMMITTED 의 Non-Repeatable Read 문제가 해소된 격리 수준입니다.

A 커넥션에서 트랜잭션을 열고 1번 멤버의 이름을 조회했을 때 '레벨3 리차드'입니다.
다른 트랜잭션이 1번 멤버의 이름을 '레벨4 리차드'로 업데이트하고 커밋했습니다.
A 커넥션에서 열어두었던 트랜잭션에서 다시 1번 멤버의 이름을 조회해도 '레벨3 리차드'입니다.
A 커넥션에서 트랜잭션을 닫고, 새로운 트랜잭션을 열어서 조회하면 '레벨4 리차드'입니다.

Repeatable Read 즉, 한 트랜잭션이 같은 데이터를 반복해서 조회해도 같은 결과가 반환되는 것입니다.

이는 트랜잭션 ID의 존재로 가능합니다.
다른 트랜잭션에서 업데이트, 커밋을 진행할 경우 
새로운 값은 버퍼풀에, 기존 값은 언두 로그에 기록됩니다.
그리고 커밋이 발생하며 버퍼풀에 있던 값이 디스크에 기록되는 건데요,
언두 로그에 있던 값을 바로 삭제하는 것이 아니라, 다른 트랜잭션에서의 사용도 완료될 때 삭제합니다.
따라서 A 커넥션에서 열었던 트랜잭션은 자신의 트랜잭션 ID를 알고 있고,
자신의 ID보다 이후에 생성된 트랜잭션으로 인해 작업된 내용은 확인하지 않고,
언두로그에 있는 자신의 트랜잭션 이전에 처리되었던 최종 커밋본을 읽는 것입니다.

 

 

 

InnoDB REPEATABLE READ는 Phantom Read가 없다

---

READ COMMITTED (Phantom Read)

Phantom Read on READ COMMITTED

 

REPEATABLE READ (No Phantom Read)

No Phantom Read On REPEATABLE READ on InnoDB

 

Non Repeatable Read는 하나의 레코드의 값이 한 트랜잭션 내에서 다르게 조회될 수 있음을 이야기한다면,
Phantom Read는 하나의 트랜잭션 내에서 조회 쿼리 결과 레코드의 수가 달라질 수 있음을 이야기합니다.

일반적으로 Oracle을 포함한 다른 데이터베이스의 트랜잭션 격리 수준에서
Repeatable Read는 Phantom Read가 발생할 수 있다고 이야기합니다.

그러나 MySQL의 InnoDB에서는 next-key 잠금 알고리즘을 이용해서
Phantom Read를 방지한다고 합니다. 관련하여 더 자세한 내용은 공식 문서 링크를 참고해주세요.

InnoDB 는 REPEATABLE READ 수준에서도 Phantom Read가 발생하지 않기에,
부득이 READ COMMITTED 수준에서 Phantom Read가 유발되는 것과 비교하여 캡쳐하였습니다.

위의 영상인 READ COMMITTED를 보시면
한 트랜잭션에서 같은 쿼리를 보냈음에도 레코드 수가 1개에서 2개로 증가했습니다.
다른 트랜잭션에서 INSERT와 커밋을 진행했기 때문입니다.

반면 아래 영상인 REPEATABLE READ를 보시면
한 트랜잭션에서 같은 쿼리를 보냈을 때 레코드의 수가 1개에서 그대로 유지됩니다.
중간에 다른 트랜잭션에서 INSERT와 커밋을 진행했음에도 말이죠.

REPEATABLE READ 수준에서도 Phantom Read를 방지해주는 것은
InnoDB 만의 강점이라고 할 수 있으며, 다른 DB에서 동일하게 지원해주는 기능은 아닙니다.

 

 

 

Oracle과의 비교

---

Oracle의 트랜잭션 격리 수준

오라클의 기본 격리 수준은 Read committed 입니다.

MySQL(InnoDB)에서는 Repeatable read 수준에서도 Phantom Read가 발생하지 않지만,
오라클에선 Repeatable read 격리 수준에서 Phantom Read가 발생한다고 명시하고 있군요!
오라클의 아키텍처가 MySQL과 어떻게 다른지, 어떤 배경에서 이러한 설정을 갖게 됐는지
궁금한 마음도 생기지만 이 부분에 대해서는 추후 필요할 때 알아보도록 하겠습니다.

 

 

 

학습 출처

---

Real MySQL 8.0 - 8장, 트랜잭션과 잠금

토르의 짜릿한 Real MySQL 스터디

MySQL Docs - Transaction Isolation Levels