[MySQL] 트랜잭션 격리수준

#해당 내용들은 RealMySQL 책에 기반하고 있습니다

• MySQL 격리수준

  • 동시에 처리되는 여러 트랜잭션에 대해서, 변경/조회 데이터에 대한 수준

  • 아래로 갈수록 고립정도 심화

  • READ UNCOMMITED (DIRTY READ)

    • 각 트랜잭션에서의 COMMIT/ROLLBACK 여부에 관계 없이 다른 트랜잭션에 노출됨
    • 이후 트랜잭션이 실패하여 ROLLBACK 했음에도 불구하고 ROLLBACK전에도 데이터 읽기가 가능하고, 그것을 기반으로 처리하기 때문에 정합성에 문제가 발생할 가능성이 높음
    • 따라서 보통 최소 READ COMMITED 이상의 격리수준 사용

  • READ COMMITTED

    • DIRTY READ 현상 미발생
      • COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회 가능
        (Commit 이전까지는 undo 영역의 데이터 참조)
        - COMMIT 이전에도 테이블에는 변경된 이후 값으로 즉시 기록되어 있음
      • NON-REPEATABLE READ 발생 (첫번째 select와 두번째 select 사이에 다른 트랜잭션의 Commit이 수행된 경우)

  • REPEATABLE READ

    • InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준
    • 바이너리 로그를 가진 MySQL 장비에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야함
      -> 음 우리도 MMM 이중화 구조를 사용하기 때문에 bin log, relay log를 사용할텐데, REPEATABLE 이하 격리 수준도 사용하는 것으로 알고 있는데.. 아닐까용~?
    • NON-REPEATABLE READ 부정합 발생 X
      • 다른 트랜잭션의 Commit 여부에 관계 없이, 동일 트랜잭션 내에서는 항상 undo 영역 데이터 반환
      • 원래는 다른 트랜잭션에서 commit이 일어나면 해당 undo log는 삭제하게 되는데, REPEATABLE READ에서는 이 구조를 유지하기 위하여, 요청한 트랜잭션 이전의 트랜잭션에 의해 생성된 언두 영역은 삭제 대상에 포함시키지 않고, 해당 데이터들만 볼 수 있도록 설계되어 있음
    • Phantom READ 발생 가능

  • SERIALIZABLE

    • 가장 엄격한 격리 수준
    • 본래 InnoDB에서는 순수한 select에 대해서는 아무런 레코드 잠금을 설정하지 않음(non-locking consistent read)
    • 그러나 serializable로 세팅된 경우에는 읽기 작업도 lock을 획득해야만 가능 (다른 트랜잭션은 해당 레코드에 대한 read도, 수정도 불가능)
    • Phantom Read 발생 X
    • InnoDB에서는 REPEATABLE READ에서도 PHANTOM READ가 안일어난다? 어떻게?

  • 3가지 부정합

    • DIRTY READ
      • 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있어 데이터 정합성이 깨질 가능성이 높은 현상
    • NON-REPEATABLE READ
      • 한 트랜잭션 내에서 동일한 쿼리를 언제 수행하느냐에 따라서 결과가 달라질 수 있는 현상
      • REPEATABLE READ
        • 하나의 트랜잭션 내에서 똑같은 select 쿼리 실행 시 항상 같은 결과를 가져와야 한다
    • PHANTOM READ
      • NON-REPEATABLE READ는 커밋 여부와 무관하게 언두로그의 데이터를 보는 방식으로 해결했지만, 이럼에도 불구하고 특정 쿼리에 대해서는 수행 시기에 따라 쿼리 결과가 다르다
        • SELECT FOR UPDATE, LOCK IN SHARE, ...
        • 이러한 쿼리들은 select 하는 레코드에 쓰기 잠금을 걸어야하는데, 언두 로그에 대해서는 잠금을 걸 수 없기 때문에, 어쩔 수 없이 테이블의 값을 통해 가져오기 때문

  • READ COMMITTED, REPEATABLE READ 간의 성능 차이는 그치 않다 ~

  • statement 포맷 형태의 bin log가 활성화된 서버에서는 READ COMMITTED 격리 수준을 사용할 수 없다?
    -> 엥 저희 mixed라서 statement 포맷도 사용하지만 read committed 잘 쓰지 않나요?
    -> 사용할 수 없는 데에는 어떤 이유가 있을까요?

#질문 정리

• 바이너리 로그를 가진 MySQL 장비에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야함 (195p. 아래에서 4번째 라인)
  -> 음 우리도 MMM 이중화 구조를 사용하기 때문에 bin log, relay log를 사용할텐데, REPEATABLE 이하 격리 수준도 사용하는 것으로 알고 있는데.. 아닐까용~?
• 제가 책에서 이해하기로는
  • REPEATABLE READ: 다른 트랜잭션의 Commit 여부에 관계 없이, 동일 트랜잭션 내에서는 항상 undo 영역 데이터 반환
  • NON-REPEATABLE READ 발생 방지
    • NON-REPEATABLE READ: 한 트랜잭션 내에서 동일한 쿼리를 언제 수행하느냐에 따라서 결과가 달라질 수 있는 현상
  • PHANTOM READ 발생 가능
    - PHANTOM READ: SELECT FOR UPDATE, LOCK IN SHARE, ... 와 같은 쿼리들은 select 하는 레코드에 쓰기 잠금을 걸어야하는데, 언두 로그에 대해서는 잠금을 걸 수 없기 때문에, 어쩔 수 없이 테이블의 값을 통해 가져오게 되어 특정 쿼리들에 대해서는 수행 시기에 따라 쿼리 결과가 다른 현상
    으로 이해를 했는데요, InnoDB에서는 특별하게도 REPEATABLE READ에서도 PHANTOM READ가 일어나지 않는다고 하더라구요(198p. SERIALIZABLE 파트 마지막 줄). 언두 로그에 대해서 잠금을 걸 수 없는데, 어떻게 팬텀리드가 발생하지 않을 수 있는걸까요? 개념에 문제가 있다면 정정 부탁드릴게요 ㅎㅎ
• statement 포맷 형태의 bin log가 활성화된 서버에서는 READ COMMITTED 격리 수준을 사용할 수 없다(199p. 차트 아래 라인)?
  -> 엥 저희 mixed라서 statement 포맷도 사용하지만 read committed 잘 쓰지 않나요?
  -> 사용할 수 없는 데에 어떤 이유가 있을까요?