Blog
Anki
데이터베이스 이론

Read Committed와 Repeatable Read의 Undo Log 활용 방식 차이

꼬리 질문
  • ReadView는 각 격리 수준에서 언제 생성되는가?
  • Undo Log의 Version Chain은 어떻게 동작하는가?
  • Non-repeatable read는 왜 READ COMMITTED에서만 발생하는가?
  • 동시성 성능 차이는 어느 정도인가?
답변 보기

  • 핵심 포인트 💡:

    • READ COMMITTED는 각 SELECT 문마다 새로운 ReadView 생성
    • REPEATABLE READ는 트랜잭션의 첫 SELECT에서만 ReadView 생성
    • Version Chain은 roll_pointer로 연결된 Undo Log 체인으로 과거 버전 관리
    • 둘 다 MVCC(Undo Log + ReadView)를 사용하지만 ReadView 생성 시점이 다름

  • 1. ReadView 생성 시점의 차이

    • READ COMMITTED 동작: 
      SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
BEGIN;
SELECT * FROM users WHERE id = 1;  -- ReadView #1 생성
-- 다른 트랜잭션이 id=1을 수정하고 COMMIT
SELECT * FROM users WHERE id = 1;  -- ReadView #2 생성 (새로운!)
-- ❌ 다른 결과 반환 (Non-repeatable read 발생)
COMMIT;
    • REPEATABLE READ 동작: 
      SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
BEGIN;
SELECT * FROM users WHERE id = 1;  -- ReadView #1 생성
-- 다른 트랜잭션이 id=1을 수정하고 COMMIT
SELECT * FROM users WHERE id = 1;  -- ReadView #1 재사용!
-- ✅ 동일한 결과 반환 (일관된 읽기)
COMMIT;

  • 2. Undo Log의 Version Chain 동작 메커니즘

    • 2-1. 레코드 구조와 Hidden Columns

      • 각 레코드는 3개의 hidden columns를 가짐:
        • DB_TRX_ID (6 bytes): 마지막 수정 트랜잭션 ID
        • DB_ROLL_PTR (7 bytes): Undo Log 레코드를 가리키는 포인터
        • DB_ROW_ID (6 bytes): 자동 증가 row ID (PK 없을 때만)
      [현재 레코드]
┌─────────────────────────────────────┐
│ id=1 | balance=150 | name='Alice'   │
│ DB_TRX_ID = 103                     │
│ DB_ROLL_PTR → [Undo Log #2]        │
└─────────────────────────────────────┘

    • 2-2. Version Chain 생성 과정 (UPDATE 실행 시)

      초기 상태:

      현재 레코드: balance=100, DB_TRX_ID=50
Undo Log: 비어있음

      T1 (trx_id=101) 실행: UPDATE account SET balance = 130 WHERE id = 1;

      단계동작상태
      1현재 레코드를 Undo Log에 복사Undo Log #1: balance=100, trx_id=50
      2현재 레코드 수정현재: balance=130, trx_id=101
      3roll_pointer 설정roll_ptr → Undo Log #1
      [현재 레코드]
balance=130, trx_id=101
roll_ptr ↓

[Undo Log #1]
balance=100, trx_id=50
roll_ptr → NULL

      T2 (trx_id=102) 실행: UPDATE account SET balance = 150 WHERE id = 1;

      단계동작상태
      1현재 레코드를 Undo Log에 복사Undo Log #2: balance=130, trx_id=101, roll_ptr→#1
      2현재 레코드 수정현재: balance=150, trx_id=102
      3roll_pointer 설정roll_ptr → Undo Log #2
      [현재 레코드]
balance=150, trx_id=102
roll_ptr ↓

[Undo Log #2]  ← 최신 Undo Log
balance=130, trx_id=101
roll_ptr ↓

[Undo Log #1]  ← 이전 Undo Log
balance=100, trx_id=50
roll_ptr → NULL
      • ✅ 이렇게 roll_pointer로 연결된 체인이 Version Chain
      • 각 Undo Log 레코드는 "이전 버전을 복구하는 정보" 포함

    • 2-3. Version Chain 탐색 과정 (SELECT 실행 시)

      T3 (trx_id=103) 실행: SELECT balance FROM account WHERE id = 1;

      🔑 핵심 개념: 트랜잭션 ID는 시간 축이다

      트랜잭션 ID는 InnoDB에서 단조 증가하는 값이므로, 작은 ID = 과거, 큰 ID = 미래를 의미합니다.

      시간 축 (Transaction ID Timeline):
←─────────────────────────────────────────→
과거                                    미래

50        101       102       103       104
↓         ↓         ↓         ↓         ↓
(커밋됨)  (활성)    (활성)    (현재)    (미할당)

      ReadView 생성 (시점 103의 스냅샷):

      creator_trx_id = 103          // 현재 SELECT를 실행하는 트랜잭션
m_ids = [101, 102]            // ReadView 생성 시점에 활성 중인 트랜잭션 목록
min_trx_id = 101              // 활성 트랜잭션의 최소 ID (m_ids의 최솟값)
max_trx_id = 104              // 다음에 할당될 트랜잭션 ID (미래의 경계)

      💡 ReadView의 의미:

      • ReadView는 "시간의 스냅샷"
      • min_trx_id(101) = 활성 트랜잭션의 시작점
      • max_trx_id(104) = 미래의 경계선
      • m_ids = 현재 진행 중인 트랜잭션들 (커밋 여부 불확실)

      가시성 판단 알고리즘:

      단계확인 대상trx_id가시성 판단이유결과
      1현재 레코드102102 ∈ m_ids → 안 보임아직 실행 중인 트랜잭션의 변경 (Dirty Read 방지)Version Chain 탐색
      2Undo Log #2101101 ∈ m_ids → 안 보임아직 실행 중인 트랜잭션의 변경 (Dirty Read 방지)계속 탐색
      3Undo Log #15050 < min_trx_id(101) → 보임!모든 활성 트랜잭션보다 먼저 시작 → 이미 커밋됨 ✅balance=100 반환

      가시성 규칙 (ReadView와 trx_id 비교):

      IF trx_id == creator_trx_id:
    → 보임
    (이유: 자신이 수정한 데이터는 당연히 보여야 함)

ELSE IF trx_id < min_trx_id:
    → 보임 ✅
    (이유: 모든 활성 트랜잭션보다 먼저 시작했고,
           ReadView 생성 시점에 이미 종료됨
           → 커밋된 데이터로 확정)

ELSE IF trx_id >= max_trx_id:
    → 안 보임
    (이유: ReadView 생성 이후에 시작된 미래 트랜잭션
           → 시간 여행 방지)

ELSE IF trx_id IN m_ids:
    → 안 보임
    (이유: 현재 실행 중인 트랜잭션의 변경
           → 커밋 여부 불확실 → Dirty Read 방지)

ELSE:
    → 보임
    (이유: min_trx_id ≤ trx_id < max_trx_id 이지만 m_ids에 없음
           → ReadView 생성 전에 커밋 완료)

      탐색 과정 시각화:

      SELECT 시작 (T3, ReadView 생성)
  ↓
[1단계: 현재 레코드 확인]
balance=150, trx_id=102
판단: 102 ∈ m_ids[101,102] → 안 보임 (활성 트랜잭션)
  ↓ roll_ptr 따라가기
[2단계: Undo Log #2 확인]
balance=130, trx_id=101
판단: 101 ∈ m_ids[101,102] → 안 보임 (활성 트랜잭션)
  ↓ roll_ptr 따라가기
[3단계: Undo Log #1 확인]
balance=100, trx_id=50
판단: 50 < min_trx_id(101) → 보임! ✅ (이미 커밋됨)
  ↓
balance=100 반환

      ✅ 왜 trx_id=50은 "이미 커밋됨"이 확실한가?

      • 50 < min_trx_id(101): 모든 활성 트랜잭션(101, 102)보다 먼저 시작
      • ReadView 생성 시점(103)에 활성 목록에 없음
      • 결론: 50번 트랜잭션은 101번이 시작되기 전에 이미 종료(커밋 또는 롤백)됨
      • InnoDB는 커밋된 데이터만 남기므로, 50은 안전하게 읽을 수 있는 커밋된 데이터

    • 2-4. Version Chain의 핵심 특징

      • ✅ 비잠금 읽기 (Non-locking Read): SELECT는 락 없이 적절한 버전 찾음
      • ✅ 공간 vs 시간 트레이드오프: Undo Log 저장 공간 사용하여 동시성 향상
      • ✅ Garbage Collection: Purge 프로세스가 불필요한 Undo Log 정리
      • ❌ 긴 트랜잭션 문제: 오래된 ReadView가 Undo Log 삭제 방해

  • 3. Non-repeatable Read 발생 메커니즘

    시간트랜잭션 1 (READ COMMITTED)트랜잭션 2balanceVersion Chain
    t1BEGIN;100현재: 100
    t2SELECT balance FROM account WHERE id = 1;
    → ReadView #1 생성
    → 결과: 100    		100현재: 100
    t3BEGIN;100현재: 100
    t4UPDATE account SET balance = 150 WHERE id = 1;150현재: 150, trx_id=102
    ↓
    Undo: 100, trx_id=50
    t5COMMIT;150현재: 150 (커밋됨)
    t6SELECT balance FROM account WHERE id = 1;
    → ReadView #2 생성 (새로 생성!)
    → trx_id=102 < max_trx_id
    → 102 ∉ m_ids (커밋됨)
    → 결과: 150 ❌    		150현재: 150 (보임!)
    t7COMMIT;150
    • READ COMMITTED: t6에서 새 ReadView 생성 → T2의 커밋 반영 → 다른 값 읽음
    • REPEATABLE READ: t6에서 기존 ReadView 재사용 → T2 여전히 활성으로 간주 → 여전히 100 읽음

  • 4. MVCC 메커니즘 완전 정리

    • ReadView 구조:

      ReadView {
    creator_trx_id: 103,           // 현재 트랜잭션
    m_ids: [101, 102],              // 활성 트랜잭션 목록
    min_trx_id: 101,                // 최소 활성 트랜잭션
    max_trx_id: 104                 // 다음 할당될 트랜잭션
}

    • MVCC = Undo Log (Version Chain) + ReadView (가시성 규칙)

      SELECT 실행
  ↓
ReadView 생성 또는 재사용
  ↓
현재 레코드의 trx_id 확인
  ↓
가시성 규칙 적용
  ↓
보임? → 반환
안 보임? → roll_pointer로 이전 버전 탐색
  ↓
반복 (보이는 버전 찾을 때까지)

  • 5. 성능 트레이드오프

    • READ COMMITTED:
      • ✅ 더 높은 동시성 (최신 커밋 즉시 반영)
      • ✅ Gap Lock 미사용 (INSERT 블로킹 없음)
      • ✅ Undo Log 빨리 정리 가능
      • ❌ Non-repeatable read 발생 가능
      • ❌ 트랜잭션 일관성 낮음
    • REPEATABLE READ:
      • ✅ 트랜잭션 일관성 보장
      • ✅ Phantom read 방지 (next-key lock)
      • ❌ Gap Lock으로 인한 동시성 저하
      • ❌ Undo Log 더 오래 유지 (트랜잭션 종료까지)
      • ❌ 긴 트랜잭션 시 Undo Log 누적

  • 6. 실무 권장사항

    • 일반적인 웹 애플리케이션: REPEATABLE READ (MySQL 기본값)
    • 높은 동시성이 중요하고 일관성 요구가 낮은 경우: READ COMMITTED
    • 긴 트랜잭션에서 최신 데이터 필요: READ COMMITTED
    • 보고서 생성 등 일관된 스냅샷 필요: REPEATABLE READ
    • ⚠️ 주의: 긴 트랜잭션은 Undo Log 정리 방해 → 테이블스페이스 증가

출처:

Repeatable Read와 Serializable의 차이점

꼬리 질문
  • SELECT 문의 잠금 동작은 어떻게 다른가?
  • Write Skew는 왜 REPEATABLE READ에서 발생하는가?
  • Lost Update를 진짜로 방지하려면 어떻게 해야 하는가?
  • 성능 차이와 데드락 위험은?
답변 보기

  • 핵심 포인트 💡:

    • REPEATABLE READ: 일반 SELECT는 non-locking read (MVCC)
    • SERIALIZABLE: 일반 SELECT를 자동으로 SELECT ... FOR SHARE로 변환 (autocommit OFF 시)
    • REPEATABLE READ: Write Skew, Lost Update 발생 가능
    • SERIALIZABLE: 모든 이상 현상 방지하지만 데드락 위험 증가

  • 1. SELECT 문의 잠금 동작 차이

    • REPEATABLE READ: 
      SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
BEGIN;
SELECT * FROM products WHERE id > 100;  
-- Non-locking read (MVCC 사용)
-- ✅ 다른 트랜잭션이 해당 행을 UPDATE 가능
COMMIT;
    • SERIALIZABLE (autocommit OFF): 
      SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET autocommit = 0;
 
BEGIN;
SELECT * FROM products WHERE id > 100;  
-- 자동으로 "SELECT ... FOR SHARE"로 변환
-- ❌ 다른 트랜잭션의 UPDATE가 블록됨 (Shared Lock)
COMMIT;
    • 핵심: SERIALIZABLE은 읽기에도 락을 걸어 완전한 격리 제공

  • 2. Write Skew 발생 메커니즘 (REPEATABLE READ)

    • 시나리오: 최소 1명의 의사가 당직을 서야 함
    시간T1 (Alice)T2 (Bob)on_call 상태
    t1BEGIN;Alice: TRUE, Bob: TRUE
    t2SELECT COUNT(*) FROM doctors
    WHERE on_call = TRUE;
    → 2명 (MVCC로 읽음)    		Alice: TRUE, Bob: TRUE
    t3BEGIN;Alice: TRUE, Bob: TRUE
    t4SELECT COUNT(*) FROM doctors
    WHERE on_call = TRUE;
    → 2명 (MVCC로 읽음)    		Alice: TRUE, Bob: TRUE
    t5(검증: 2 ≥ 2 → OK)(검증: 2 ≥ 2 → OK)Alice: TRUE, Bob: TRUE
    t6UPDATE doctors
    SET on_call = FALSE
    WHERE name = 'Alice';    		Alice: FALSE, Bob: TRUE
    t7COMMIT;Alice: FALSE, Bob: TRUE
    t8UPDATE doctors
    SET on_call = FALSE
    WHERE name = 'Bob';    		Alice: FALSE, Bob: FALSE
    t9COMMIT;❌ 당직 의사 0명!
    • 왜 발생하는가:
      • 두 트랜잭션이 서로 다른 행을 수정
      • 각자의 ReadView에서는 조건을 만족함
      • 비즈니스 제약 조건 위반 감지 불가

  • 3. SERIALIZABLE의 Write Skew 방지

    시간T1 (Alice)T2 (Bob)잠금 상태
    t1BEGIN;
    t2SELECT COUNT(*) FROM doctors
    WHERE on_call = TRUE;
    → 모든 매칭 행에 S Lock    		S Lock: Alice, Bob
    t3BEGIN;
    t4SELECT COUNT(*) FROM doctors
    WHERE on_call = TRUE;
    → S Lock 공유 가능    		S Lock: Alice, Bob (T1, T2)
    t5UPDATE doctors
    SET on_call = FALSE
    WHERE name = 'Alice';
    → X Lock 필요 → 대기    		S Lock: Alice, Bob (T1, T2)
    t6UPDATE doctors
    SET on_call = FALSE
    WHERE name = 'Bob';
    → X Lock 필요 → 대기    		DEADLOCK 발생!
    t7롤백됨
    t8COMMIT;✅ Bob만 당직 포기 (Alice 유지)
    • 메커니즘:
      • SELECT이 조건에 맞는 모든 행에 S Lock
      • UPDATE 시 X Lock 필요하지만 상대방이 S Lock 보유
      • 데드락 발생 → 한 트랜잭션 롤백 → 제약 조건 보호

  • 4. Lost Update는 Serializable로도 완전히 방지 못 함

    • ❌ 잘못된 이해: 
      SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET autocommit = 0;
 
-- T1과 T2가 각각:
BEGIN;
SELECT balance FROM account WHERE id = 1;  -- 둘 다 100 (S Lock)
-- 애플리케이션에서: 100 + 30 = 130 계산
UPDATE account SET balance = 130 WHERE id = 1;  -- 데드락 발생
-- 한 트랜잭션만 살아남아 130 또는 120 저장
-- ❌ 150이 아님!
    • ✅ 올바른 해결책 - 원자적 연산: 
      BEGIN;
UPDATE account SET balance = balance + 30 WHERE id = 1;
-- 데이터베이스가 현재 값을 읽고 계산
-- ✅ 150이 됨
COMMIT;
    • ✅ 대안 - FOR UPDATE: 
      BEGIN;
SELECT balance FROM account WHERE id = 1 FOR UPDATE;  -- X Lock
-- 이제 안전하게 계산 가능
UPDATE account SET balance = balance + 30 WHERE id = 1;
COMMIT;

  • 5. 각 문제별 올바른 해결책

    문제 유형REPEATABLE READSERIALIZABLE진짜 해결책
    Phantom Read✅ next-key lock으로 방지✅ 방지-
    Write Skew❌ 발생✅ 데드락으로 방지SELECT ... FOR UPDATE
    또는 SERIALIZABLE
    Lost Update❌ 발생⚠️ 데드락만 유발✅ 원자적 연산
    UPDATE ... SET x = x + 1
    ✅ SELECT ... FOR UPDATE

  • 6. 성능 vs 일관성 트레이드오프

    • REPEATABLE READ:
      • ✅ 높은 동시성 (non-locking read)
      • ✅ 대부분의 애플리케이션에 충분
      • ❌ Write Skew 발생 가능
      • 권장: 일반적인 OLTP 워크로드
    • SERIALIZABLE:
      • ✅ 완전한 격리 (Write Skew 방지)
      • ❌ 낮은 동시성 (모든 SELECT에 락)
      • ❌ 데드락 위험 증가
      • ❌ 가장 느린 성능
      • 권장: XA 트랜잭션, 엄격한 격리 필요 시

  • 7. 실무 권장사항

    • 일반적인 경우: REPEATABLE READ + 명시적 FOR UPDATE
    • 계산/증감 연산: 원자적 연산 사용 (UPDATE ... SET x = x + 1)
    • 복잡한 비즈니스 제약: SERIALIZABLE 또는 FOR UPDATE
    • 높은 동시성 필요: READ COMMITTED + 낙관적 잠금 (version 컬럼)

출처:

MySQL Isolation 별로 언제 Lock이 걸리고 어떤 Lock이 걸리나요?

답 보기
  • Lock은 인덱스 스캔시에 발생할 수 있음 락은 XLock이나 SLock을 명시적으로 거는 경우에 발생함(MVCC)
  • Repeatable Read에서는 Next Key Lock, Read Commited에서는 Record 락이 걸린다.
    • Shared Lock, Exclusive Lock 동일한듯

클러스터링 인덱스와 논클러스터링 인덱스의 차이점은 무엇인가요?

꼬리 질문
  • SSD 환경에서도 순차 I/O가 랜덤 I/O보다 빠른 이유는 무엇인가요?
답변 보기

✅ 클러스터링 인덱스는 데이터 페이지의 물리적 순서를 결정하는 인덱스이고, 논클러스터링 인덱스는 별도의 구조체로 데이터 위치를 가리키는 인덱스입니다.

  • 클러스터링 인덱스 🗂️

    • 데이터 페이지의 물리적 저장 순서를 결정
    • 테이블당 1개만 생성 가능 (Primary Key가 일반적)
    • 책의 페이지 번호와 같은 개념
    • 순차 I/O 패턴으로 높은 성능

  • 논클러스터링 인덱스 📋

    • 별도의 인덱스 구조체에서 데이터 페이지를 가리킴
    • 테이블당 여러 개 생성 가능
    • 책의 색인(Index)과 같은 개념
    • Key Lookup 과정에서 랜덤 I/O 발생 가능

  • HDD vs SSD 차이점 💾

    • HDD: 기계적 디스크 헤드 이동으로 순차 I/O가 압도적으로 유리
    • SSD: 기계적 움직임 없지만 여전히 순차 I/O가 2-3배 빠름

  • 꼬리질문: SSD 환경에서도 순차 I/O가 랜덤 I/O보다 빠른 이유는 무엇인가요?

    • 병렬 처리 최적화 ⚡
      • SSD 내부 구조 이해 🏗️
        • 채널(Channel): SSD 컨트롤러와 NAND 플래시 간의 독립적 데이터 통로 (일반적으로 4-8개)
        • 다이(Die): 각 채널 내의 독립적으로 작동하는 NAND 플래시 칩 (채널당 2-4개)
        • 플레인(Plane): 다이 내에서 병렬 처리가 가능한 단위 (다이당 2-4개)
      • 순차 I/O의 병렬 활용 🔄
        • 연속된 데이터를 여러 채널에 분산 저장 (Striping)
        • 예: 16KB 데이터 → 4개 채널에 각각 4KB씩 동시 처리
        • 모든 채널이 동시에 작업하여 총 처리량 4배 증가
      • 랜덤 I/O의 병렬성 제한 ⚠️
        • 불규칙한 주소로 인해 특정 채널/다이에 집중
        • 일부 채널은 대기 상태로 유휴 자원 발생
        • 채널 간 로드 밸런싱 불균형
    • 컨트롤러 최적화 🧠
      • 연속된 주소 패턴 감지로 Prefetch 버퍼 활용
      • 예측 가능한 패턴에서 캐시 적중률 향상
    • Write Amplification 차이 📈
      • 순차 쓰기: 1:1 비율로 효율적
      • 랜덤 쓰기: 가비지 컬렉션 유발로 30% 오버헤드
    • 대역폭 활용도 📊
      • 순차 I/O: 최대 대역폭 활용 (~3.5GB/s)
      • 랜덤 I/O: 제한적 활용 (~500MB/s, 약 7배 차이)
Java데이터베이스 문제