5.4 MySQL의 격리 수준

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

• READ UNCOMITTED(Dirty Read 발생)은 일반적인 데이터베이스에서 사용하지 않는다. 
• SERIALIZABLE은 동시성이 중요한 데이터베이스에서 거의 사용되지 않는다. 
• SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다. 
• REPEATABLE READ 격리수준에서 PHANTOM READ는 Inno DB 한정해서 발생하지 않는다. 
  
  

 

READ UNCOMMITED 

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

• 해당 예시에서 사용자 A가 Insert된 내용을 Rollback한다고 하더라도 여전히 사용자 B는 "Lara"가 정상적인 사원이라고 생각하고 처리한다. 

 

• Dirty Read란? 
  •  어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상 
    • 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 혼란을 초래
    • MySQL을 사용한다면 최소 READ COMMITTED 이상의 격리수준을 사용할 것을 권장 

READ COMMITTED 

• 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회 가능하다. 
  • Dirty Read가 발생하지 않는다. 

• 사용자 A가 emp_no = 500000인 사원의 first_name을 "Lara"에서 "Toto"로 변경
  • 새로운 값인 "Toto"는 employees 테이블에 즉시 기록된다. 
  • 이전 값인 "Lara"는 언두 영역으로 백업된다. 
• 사용자 A가 커밋을 수행하기 전에 사용자 B가 emp_no = 500000인 사원을 SELECT하면 테이블이 아니라 언두 영역에 백업된 레코드에서 값을 가져오기 때문에 "Toto"가 아니라 "Lara"가 조회된다. 

 

• NON-REPEATABLE READ란? 
  • 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 "REPEATABLE READ" 정합성에 어긋나는 것 

• 사용자 B의 경우 같은 트랜잭션에서 같은 쿼리에 대한 결과가 다르다 (결과 없음 -> 결과 1건 반환) 
• 문제 상황 예시 
  • 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되었을 때 
    • 다른 트랜잭션에서 입금/출금 처리가 진행될 때 오늘 입금된 금액의 총합을 조회 
    • REPEATABLE READ가 보장되지 않기 때문에 총합을 계산하는 SELECT 쿼리는 실행될 때마다 다른 결과를 가져온다. 
• SELECT 트랜잭션 내부 vs 외부
  • READ COMMITTED 
    • 차이가 별로 없다 
  • REPEATABLE READ 
    • 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 동작한다. 
    • START TRANSACTION(BEGIN) 명령으로 트랜잭션을 시작한 상태에서 온종일 동일한 쿼리를 반복해서 실행해봐도 동일한 결과만 보게 된다. 

REPEATABLE READ 

• InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준 
• 바이너리 로그를 가진 MySQL서버에서는 최소 REPEATABLE READ 격리 수준을 사용해야 한다. 
• MVCC 
  • 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 Undo 공간에 백업해두고 실제 레코드 값을 변경한다. 
  • 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다. 
  • cf) READ COMMITTED도 MVCC를 이용해 COMMITT되기 전의 데이터를 보여준다. 
• REPEATABLE READ와 READ COMMITTED의 차이는 Undo 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다. 
• MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 앞선 Undo 영역의 데이터는 삭제할 수 없다. 

• TX 번호 
  • 사용자 A : 12 , 사용자 B : 10 
• 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경한 것만 보이게 된다. 
• 트랜잭션이 장시간 지속된다면 undo이 백업된 데이터로 무한정 커질 수 있어 서버 처리 성능이 떨어질 수 있다. 
• PHANTOM READ란? 
  • 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안 보였다 하는 현상

• 일반적으로 REPEATABLE READ에서는 Phantom Read가 발생하지 않는다.

  

  출처 : https://mangkyu.tistory.com/299

  • 잠금 있는 읽기(SELECT FOR UPDATE)를 실행할 경우 데이터 조회가 언두 로그가 아닌 테이블에서 일어난다. 
  • 이와 같이 MVCC로 해결할 수 없지만, Inno DB의 경우 갭락을 이용하여 이를 해결한다. 
    • 다른 트랜잭션 (12번)에서의 INSERT문은 갭락으로 인해 트랜잭션(10번)이 끝날 때 까지 대기 상태에 있다. 
  • SERIALIZABLE에서도 언급되겠지만, Inno DB의 경우 순수한 SELECT의 경우 잠금 없이 수행되기 때문에 일반적인 SELECT의 경우에는 MVCC로 해결된다. 
• Phantom Read가 발생할 수 있는 예외적인 경우 
  • 같은 트랜잭션 내 SELECT => SELECT FOR UPDATE

    

    출처 : https://mangkyu.tistory.com/299

  • 트랜잭션(10번)에서 처음 SELECT의 경우 읽기 잠금 없이 읽기 때문에 갭락이 걸리지 않는다.
  • 갭락이 걸리지 않기 때문에 다른 트랜잭션(12번)에서 새로운 값이 INSERT 가능하다. 
  • 트랜잭션(12번)에서 SELECT FOR UPDATE로 데이터를 조회하는데, 위에서 언급한 대로 읽기 있는 잠금은 테이블을 조회하기 때문에, MVCC를 통해 해결이 불가능하며, 갭락이 없었기 때문에 이미 새로운 데이터는 추가되어있다. 
• 케이스 정리 
  • SELECT FOR UPDATE => SELECT : 갭락으로 해결 
  • SELECT FOR UPDATE => SELECT FOR UPDATE : 갭락으로 해결 
  • SELECT => SELECT : MVCC를 통해 해결, 언두 로그 조회 
  • SELECT => SELECT FOR UPDATE : 팬덤 리드 해결 불가능 

 

SERIALIZABLE 

• 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다. 
  • 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다. 
• 가장 단순 / 엄격한 격리 수준 
• 동시 처리 성능이 떨어진다. 
• PHANTOM READ가 발생하지 않는다. 
  • Inno DB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ에서도 PHANTOM READ가 발생하지 않기 때문에 사용할 필요성이 떨어진다. 
• Inno DB의 경우 기본적으로 순수한 SELECT의 작업은 아무 레코드 잠금도 설정하지 않고 실행된다. 

 

참고 

https://product.kyobobook.co.kr/detail/S000001766482

Real MySQL 8.0 (1권) | 백은빈 - 교보문고

 

https://mangkyu.tistory.com/299

[MySQL] 트랜잭션의 격리 수준(Isolation Level)에 대해 쉽고 완벽하게 이해하기