MySQL 격리 수준

격리 수준: 하나 트랜잭션 애에서 또는 서로 다른 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

• READ UNCOMMITED는 일반적인 데이터베이스에서는 거의 사용하지않고, SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 거의 사용되지않는다.
• READ COMMITTED 격리 수준에서는 트랜잭션내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로지만, REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동한다.

 

READ UNCOMMITTED

READ UNCOMMITTED에서는 더티 리드(Dinty read)이라고 하는 현상이 발생한다.

• 사용자 A
  • employees 테이블에 emp_no=500000, first_name='Lara' INSERT
  • 아직 COMMIT 하지 않음
• 사용자 B
  • 동시에 SELECT * FROM employees WHERE emp_no=500000; 실행
  • 결과: 1건 반환 (Lara 조회됨)
• 이후 사용자 A가 COMMIT

→ A가 아직 커밋하지 않은 데이터인데, B가 그 데이터를 읽어버림

 

READ COMMITTED

오라클 DBMS에서는 주로 READ COMMITTED 수준을 많이 사용한다.

READ COMMITTED에서는 더티 리드(Dinty read)가 발생하지 않아, commit이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.

• 사용자 A
  • emp_no=500000의 first_name을 'Lara' → 'Toto'로 UPDATE
  • 아직 COMMIT 하지 않음
  • 기존 값 'Lara'는 언두(undo) 영역에 보관됨
• 사용자 B
  • 같은 행을 SELECT → 결과: 'Toto'가 아니라 'Lara' 조회됨

→ A가 아직 커밋하지 않았기 때문에, B는 언두 로그에 저장된 이전 값('Lara')을 읽음

 

하지만 READ COMMITTED에서는 "NON-REPEATABLE READ ("REPEATABLE READ 가 불가능하다)라는 부정합의 문제가 있다.

• 사용자 B
  • SELECT ... WHERE first_name='Toto' → 결과: 없음
• 사용자 A
  • emp_no=500000의 이름을 'Lara' → 'Toto'로 변경 → COMMIT
• 사용자 B (같은 트랜잭션 안에서 다시 조회)
  • 같은 SELECT 실행 → 결과: 1건 반환

→ 같은 트랜잭션 안에서 똑같은 SELECT를 했는데 처음에는 결과 없음, 나중에는 결과 1건으로 조회 결과가 달라짐.

• READ COMMITTED가 문제되는 사례
  • 상황
    • 한 트랜잭션에서 오늘 입금된 금액의 총합을 조회하고 있음
    • 동시에 다른 트랜잭션에서 입금/출금 작업이 계속 발생
  • 문제 - 격리 수준이 REPEATABLE READ가 아닐 경우
    • 총합을 계산하는 SELECT를 여러 번 실행하면
    • 실행할 때마다 결과가 달라질 수 있음

 

REPEATABLE READ

REPEATABLE READ는 MySOL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.

• 이유: 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEAT ABLEREAD 격리 수준이상을 사용해야 한다.

InnoDB 스토리지 엔진은 MVCC 방식으로 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드값을 변경한다.

REPEATABLEREAD는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.

하지만 REPEATABLE READ 격리 수준에서 PHANTOM READ라는 부정합의 문제가 있다.

• 사용자 B
  • SELECT * FROM employees WHERE emp_no >= 500000 FOR UPDATE; → 결과: Lara 1건
• 사용자 A
  • 조건 emp_no >= 500000에 해당하는 새 행(Georgi)이 추가 후, commit 됨
• 사용자 B (같은 트랜잭션 안에서 다시 조회)
  • SELECT * FROM employees WHERE emp_no >= 500000 FOR UPDATE; → 결과: 2건 (Lara, Georgi)

왜 이런 일이 생길까?

일반 SELECT는 트랜잭션 시작 시점의 스냅샷(undo 로그) 을 읽는다. 그래서 결과가 고정된다.

하지만 SELECT ... FOR UPDATE는 “이 레코드를 곧 수정할 거니까, 지금 잠가둬라”를 의미하기 때문에 잠글 대상을 정확히 잡아야 하기 때문에, 과거 스냅샷이 아니라 현재 실제 레코드를 읽어야한다.

그래서 현재 버전(최신 커밋 데이터)을 읽게되어, 새로 INSERT된 행(Georgi)이 보이게 된다.

 

SERIALIZABLE

SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못 하게된다.

SERIALIZABLE는 가장 강력한 격리 수준인 만큼, 동시 처리 성능이 매우 떨어져서 잘 사용하지 않는다.

 

SERIALIZABLE 격리 수준에서는 READ" 문제가 발생하지않는다.
하지만 InnoDB스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에REPEATABLE READ 격리 수준에서도 이미" PHANTOMREAD"가 발생하지 않는다.