Dev Wiki

Ch05-4 MySQL의 격리 수준

10 min read

한 줄 정의

격리 수준(Isolation Level)은 여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 어디까지 볼 수 있는지 결정하는 것입니다.

쉽게 말하면

격리 수준은 투명 유리의 불투명도 와 같습니다:

  • READ UNCOMMITTED — 완전 투명: 옆 칸에서 작업 중인 것까지 다 보입니다 (취소될 수도 있는 작업까지)
  • READ COMMITTED — 반투명: 옆 칸에서 완료한 결과만 보입니다
  • REPEATABLE READ — 불투명: 내가 작업을 시작한 시점의 상태만 보입니다
  • SERIALIZABLE — 완전 차단: 옆 칸의 작업이 끝날 때까지 내 작업도 멈춥니다

핵심 내용

격리 수준별 부정합 발생 여부

격리 수준DIRTY READNON-REPEATABLE READPHANTOM READ
READ UNCOMMITTED발생발생발생
READ COMMITTED없음발생발생
REPEATABLE READ없음없음발생 (InnoDB는 없음)
SERIALIZABLE없음없음없음
  • MySQL의 기본 격리 수준은 REPEATABLE READ 입니다
  • 오라클의 기본 격리 수준은 READ COMMITTED입니다
  • SERIALIZABLE이 아니라면 격리 수준 간 성능 차이는 크지 않습니다

READ UNCOMMITTED

다른 트랜잭션에서 커밋되지 않은 변경 내용 까지 조회할 수 있는 격리 수준입니다.

sequenceDiagram
    participant A as 사용자 A
    participant DB as employees 테이블
    participant B as 사용자 B

    A->>DB: INSERT (emp_no=500000, 'Lara')
    Note over DB: 아직 COMMIT 전
    B->>DB: SELECT WHERE emp_no=500000
    DB-->>B: 'Lara' 반환 (Dirty Read!)
    A->>DB: ROLLBACK
    Note over B: 'Lara'가 유효하다고 생각하고<br/>처리를 계속하지만, 실제로는 없는 데이터
  • 이처럼 커밋되지 않은 데이터를 읽는 것을 더티 리드(Dirty Read) 라고 합니다
  • 데이터가 나타났다가 사라지는 현상을 초래하므로, RDBMS 표준에서는 격리 수준으로 인정하지 않을 정도입니다
  • MySQL을 사용한다면 최소 READ COMMITTED 이상 을 사용해야 합니다

READ COMMITTED

커밋이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있는 격리 수준입니다. 오라클의 기본값이며, 온라인 서비스에서 가장 많이 선택되는 수준입니다.

sequenceDiagram
    participant A as 사용자 A
    participant DB as employees 테이블
    participant Undo as 언두 로그
    participant B as 사용자 B

    Note over DB: emp_no=500000, first_name='Lara'
    A->>DB: UPDATE SET first_name='Toto'<br/>WHERE emp_no=500000
    DB->>Undo: 변경 전 데이터 백업 ('Lara')
    Note over DB: 버퍼 풀: 'Toto'
    B->>DB: SELECT WHERE emp_no=500000
    DB-->>Undo: 아직 커밋 전이므로
    Undo-->>B: 'Lara' 반환 (언두 로그에서)
    A->>DB: COMMIT
    B->>DB: SELECT WHERE emp_no=500000
    DB-->>B: 'Toto' 반환 (커밋된 데이터)
NON-REPEATABLE READ 문제
sequenceDiagram
    participant B as 사용자 B
    participant DB as employees 테이블
    participant A as 사용자 A

    B->>B: BEGIN
    B->>DB: SELECT WHERE first_name='Toto'
    DB-->>B: 결과 없음 (Empty)
    A->>DB: UPDATE SET first_name='Toto'<br/>WHERE emp_no=500000
    A->>DB: COMMIT
    B->>DB: SELECT WHERE first_name='Toto'
    DB-->>B: 1건 조회됨!
    Note over B: 같은 트랜잭션 내에서<br/>같은 쿼리의 결과가 다름

하나의 트랜잭션 안에서 동일한 SELECT 쿼리의 결과가 달라지는 이 현상이 NON-REPEATABLE READ 입니다. 금전 처리처럼 트랜잭션 내에서 일관된 데이터가 필요한 경우 문제가 됩니다.

REPEATABLE READ

MySQL InnoDB의 기본 격리 수준 입니다. 트랜잭션 내에서 동일한 SELECT는 항상 동일한 결과를 반환합니다.

동작 원리

모든 InnoDB 트랜잭션은 고유한 트랜잭션 번호 (순차 증가)를 가지며, 언두 영역의 백업 레코드에도 변경을 발생시킨 트랜잭션 번호가 포함됩니다.

sequenceDiagram
    participant B as 사용자 B<br/>(TRX ID: 10)
    participant DB as employees 테이블
    participant Undo as 언두 로그
    participant A as 사용자 A<br/>(TRX ID: 12)

    B->>B: BEGIN (TRX ID=10 부여)
    B->>DB: SELECT WHERE emp_no=500000
    DB-->>B: 'Lara' (TRX ID=6, 자신보다 작으므로 보임)

    A->>DB: UPDATE SET first_name='Toto'
    DB->>Undo: 변경 전 백업 ('Lara', TRX ID=6)
    Note over DB: 현재 레코드: 'Toto' (TRX ID=12)
    A->>DB: COMMIT

    B->>DB: SELECT WHERE emp_no=500000
    Note over DB: TRX ID=12 > 10 → 안 보임
    DB->>Undo: 이전 버전 조회
    Undo-->>B: 'Lara' 반환 (TRX ID=6)

핵심 원리: 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 변경한 것만 봅니다. TRX ID=10인 사용자 B는 TRX ID=12에서 변경한 데이터를 무시하고 언두 로그에서 이전 버전을 읽습니다.

REPEATABLE READ vs READ COMMITTED
기준READ COMMITTEDREPEATABLE READ
언두 로그 참조가장 최근 커밋된 버전자신의 트랜잭션 시작 시점의 버전
같은 SELECT 결과트랜잭션 내에서 달라질 수 있음트랜잭션 내에서 항상 동일
트랜잭션 밖 SELECT차이 없음차이 없음
PHANTOM READ

REPEATABLE READ에서도 SELECT ... FOR UPDATE를 사용하면 PHANTOM READ가 발생할 수 있습니다.

sequenceDiagram
    participant B as 사용자 B
    participant DB as employees 테이블
    participant A as 사용자 A

    B->>B: BEGIN
    B->>DB: SELECT * FROM employees<br/>WHERE emp_no >= 500000<br/>FOR UPDATE
    DB-->>B: 1건 조회

    A->>DB: INSERT (emp_no=500001, 'Lara')
    A->>DB: COMMIT

    B->>DB: SELECT * FROM employees<br/>WHERE emp_no >= 500000<br/>FOR UPDATE
    DB-->>B: 2건 조회! (PHANTOM READ)

SELECT ... FOR UPDATE는 레코드에 쓰기 잠금 을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없습니다. 따라서 언두 영역이 아닌 현재 테이블의 레코드 를 읽게 되어 PHANTOM READ가 발생합니다.

단, InnoDB는 갭 락과 넥스트 키 락 덕분에 일반적인 SELECT (Non-locking read)에서는 REPEATABLE READ에서도 PHANTOM READ가 발생하지 않습니다.

SERIALIZABLE

가장 엄격한 격리 수준입니다. 읽기 작업에도 공유 잠금(읽기 잠금) 을 획득해야 하며, 다른 트랜잭션은 잠긴 레코드를 변경할 수 없습니다.

  • PHANTOM READ가 완전히 방지됩니다
  • 동시 처리 성능이 크게 떨어집니다
  • InnoDB에서는 갭 락/넥스트 키 락 덕분에 REPEATABLE READ에서도 PHANTOM READ가 발생하지 않으므로, SERIALIZABLE을 사용할 필요성이 거의 없습니다

시각화

격리 수준별 데이터 읽기 경로

flowchart TD
    subgraph 읽기_경로["SELECT 실행 시 데이터 읽기 경로"]
        RU["READ UNCOMMITTED"]
        RC["READ COMMITTED"]
        RR["REPEATABLE READ"]
        S["SERIALIZABLE"]

        BP["버퍼 풀<br/>(최신 데이터)"]
        UNDO1["언두 로그<br/>(최근 커밋 버전)"]
        UNDO2["언두 로그 체인<br/>(트랜잭션 시작 시점 버전)"]
        LOCK["공유 잠금 획득 후<br/>현재 레코드 읽기"]
    end

    RU --> BP
    RC --> UNDO1
    RR --> UNDO2
    S --> LOCK

내 생각

  • MySQL의 기본값인 REPEATABLE READ는 대부분의 온라인 서비스에 적합합니다. 굳이 READ COMMITTED로 변경할 이유가 없다면 기본값을 유지하는 것이 안전합니다.

  • SELECT ... FOR UPDATE의 PHANTOM READ 가능성을 항상 인지하고 있어야 합니다. 특히 금전 처리나 재고 관리처럼 정확한 레코드 잠금이 필요한 경우, 갭 락이 예상대로 작동하는지 EXPLAIN으로 실행 계획을 확인하는 것이 좋습니다.

  • 긴 트랜잭션은 REPEATABLE READ에서 언두 로그를 오래 보존하게 만들어 MySQL 서버 전체 성능 저하 를 유발할 수 있습니다. BEGIN 후 트랜잭션을 빠르게 종료하는 것이 중요합니다.

관련 개념

출처

  • Real MySQL 8.0 (1권), 5.4 MySQL의 격리 수준

그래프 뷰

백링크