MySQL InnoDB MVCC 동작 원리

글쓴이 /

MVCC란?

MVCC(Multi-Version Concurrency Control)는 InnoDB가 읽기와 쓰기를 동시에 처리하는 핵심 메커니즘입니다. 읽기 작업이 쓰기 작업을 차단하지 않고, 쓰기 작업이 읽기 작업을 차단하지 않습니다. 각 트랜잭션은 데이터의 특정 시점 스냅샷을 보게 되며, 이를 위해 InnoDB는 Undo Log에 이전 버전 데이터를 보관합니다.

이 글에서는 MVCC의 내부 동작 원리, Undo Log와 Read View 구조, 격리 수준별 가시성 차이, 그리고 MVCC가 야기하는 운영 이슈까지 심층적으로 다룹니다. MySQL InnoDB Lock 구조 심화와 함께 읽으면 InnoDB 동시성 제어의 전체 그림을 이해할 수 있습니다.

MVCC 핵심 구성 요소

구성 요소 역할 위치
트랜잭션 ID (trx_id) 각 트랜잭션의 고유 식별자, 단조 증가 트랜잭션 시스템
숨겨진 컬럼 (DB_TRX_ID) 해당 행을 마지막으로 수정한 트랜잭션 ID 각 행(row)
롤 포인터 (DB_ROLL_PTR) Undo Log의 이전 버전을 가리키는 포인터 각 행(row)
Undo Log 행의 이전 버전 데이터 체인 Undo Tablespace
Read View 스냅샷 시점의 활성 트랜잭션 목록 트랜잭션별 생성

행의 버전 체인 구조

-- 테이블: users (id=1, name='Alice', age=25)

-- 초기 상태 (trx_id=100이 INSERT)
┌──────────────────────────────────────────┐
│ id=1 │ name='Alice' │ age=25             │
│ DB_TRX_ID=100 │ DB_ROLL_PTR → (없음)     │
└──────────────────────────────────────────┘

-- trx_id=200이 UPDATE: SET age=26
┌──────────────────────────────────────────┐
│ id=1 │ name='Alice' │ age=26  (최신)      │
│ DB_TRX_ID=200 │ DB_ROLL_PTR → Undo#1     │
└──────────────────────────────────────────┘
          │
          ▼ Undo Log #1
┌──────────────────────────────────────────┐
│ id=1 │ name='Alice' │ age=25  (이전 버전)  │
│ DB_TRX_ID=100 │ DB_ROLL_PTR → (없음)     │
└──────────────────────────────────────────┘

-- trx_id=300이 UPDATE: SET name='Bob'
┌──────────────────────────────────────────┐
│ id=1 │ name='Bob'   │ age=26  (최신)      │
│ DB_TRX_ID=300 │ DB_ROLL_PTR → Undo#2     │
└──────────────────────────────────────────┘
          │
          ▼ Undo Log #2
┌──────────────────────────────────────────┐
│ id=1 │ name='Alice' │ age=26             │
│ DB_TRX_ID=200 │ DB_ROLL_PTR → Undo#1     │
└──────────────────────────────────────────┘
          │
          ▼ Undo Log #1
┌──────────────────────────────────────────┐
│ id=1 │ name='Alice' │ age=25             │
│ DB_TRX_ID=100 │ DB_ROLL_PTR → (없음)     │
└──────────────────────────────────────────┘

-- → 체인을 따라가면 과거 어느 시점의 데이터든 복원 가능!

Read View 동작 원리

Read View는 “이 트랜잭션이 어떤 데이터를 볼 수 있는가”를 결정하는 스냅샷입니다.

-- Read View 구조
{
  m_ids: [200, 250],    -- Read View 생성 시점의 활성(미커밋) 트랜잭션 목록
  min_trx_id: 200,      -- m_ids 중 최솟값
  max_trx_id: 301,      -- 다음에 할당될 트랜잭션 ID
  creator_trx_id: 280   -- Read View를 생성한 트랜잭션 ID
}

-- 가시성 판단 알고리즘 (행의 DB_TRX_ID 기준)
function isVisible(row_trx_id, readView):
  
  -- 1. 자기 자신이 수정한 행 → 보임
  if row_trx_id == creator_trx_id:
    return VISIBLE
  
  -- 2. Read View 생성 전에 커밋된 트랜잭션 → 보임
  if row_trx_id < min_trx_id:
    return VISIBLE
  
  -- 3. Read View 생성 후에 시작된 트랜잭션 → 안 보임
  if row_trx_id >= max_trx_id:
    return NOT_VISIBLE
  
  -- 4. min과 max 사이: 활성 목록에 있으면 안 보임 (아직 미커밋)
  if row_trx_id in m_ids:
    return NOT_VISIBLE
  else:
    return VISIBLE  -- m_ids에 없으면 이미 커밋됨
  
  -- NOT_VISIBLE이면 → DB_ROLL_PTR 따라 Undo Log의 이전 버전으로 이동
  -- 보이는 버전을 찾을 때까지 체인을 탐색

격리 수준별 Read View 생성 시점

격리 수준 Read View 생성 결과
READ COMMITTED 매 SELECT마다 새로 생성 다른 트랜잭션 커밋 즉시 반영 (Non-Repeatable Read)
REPEATABLE READ 트랜잭션 첫 SELECT 시 1회 생성 트랜잭션 동안 일관된 스냅샷 (Consistent Read) 
-- REPEATABLE READ 예시
-- 시간순서: trx_id=100(커밋됨), trx_id=200(활성), trx_id=250(활성)

-- TX A (trx_id=280): 첫 SELECT
SELECT * FROM users WHERE id = 1;
-- → Read View 생성: m_ids=[200,250], min=200, max=301
-- → id=1의 DB_TRX_ID=100 < min(200) → VISIBLE → age=25 반환

-- TX B (trx_id=200): UPDATE + COMMIT
UPDATE users SET age = 30 WHERE id = 1;
COMMIT;

-- TX A: 두 번째 SELECT (같은 Read View 재사용!)
SELECT * FROM users WHERE id = 1;
-- → id=1의 DB_TRX_ID=200, m_ids에 200 있음 → NOT_VISIBLE
-- → Undo Log 따라감 → DB_TRX_ID=100 < min(200) → VISIBLE → age=25
-- → 여전히 age=25! (Repeatable Read 보장)

-- READ COMMITTED에서는:
-- 두 번째 SELECT 시 새 Read View 생성
-- → m_ids에 200이 없음 (이미 커밋) → DB_TRX_ID=200 VISIBLE → age=30

Undo Log 관리와 Purge

-- Undo Log는 두 가지 용도:
-- 1. 트랜잭션 롤백 (ROLLBACK 시 이전 값 복원)
-- 2. MVCC 읽기 (과거 버전 제공)

-- Purge Thread: 더 이상 필요 없는 Undo Log를 정리
-- "필요 없음" 조건: 모든 활성 Read View보다 오래된 버전

-- Undo Log 상태 모니터링
SHOW ENGINE INNODB STATUSG
-- TRANSACTIONS 섹션:
-- History list length 1234
-- → 아직 purge되지 않은 Undo Log 수
-- → 이 값이 계속 증가하면 문제!

-- 상세 모니터링
SELECT 
  COUNT AS history_list_length
FROM information_schema.innodb_metrics
WHERE NAME = 'trx_rseg_history_len';

-- Purge 지연 원인
-- 1. 장기 트랜잭션 (오래된 Read View가 purge 차단)
SELECT 
  trx_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
  trx_rows_locked,
  trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;
-- → duration_sec이 큰 트랜잭션이 Undo 정리를 막고 있을 수 있음

MVCC와 SELECT FOR UPDATE의 차이

-- 일반 SELECT: Consistent Read (MVCC, 스냅샷)
-- → Lock 없음, Undo Log에서 과거 버전을 읽음
SELECT * FROM users WHERE id = 1;
-- → Read View 기준 보이는 버전 반환 (과거 데이터일 수 있음)

-- SELECT FOR UPDATE: Current Read (최신 커밋 데이터 + Lock)
-- → Record Lock 획득, 항상 최신 커밋된 데이터를 읽음
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- → Undo Log 무시, 실제 레코드의 최신 값 반환

-- ⚠️ REPEATABLE READ에서의 함정!
-- TX A:
BEGIN;
SELECT * FROM users WHERE id = 1;  -- age=25 (스냅샷)

-- TX B: UPDATE age=30 + COMMIT

-- TX A:
SELECT * FROM users WHERE id = 1;            -- age=25 (스냅샷 유지)
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- age=30 (최신값!)
-- → 같은 트랜잭션 안에서 다른 값이 보임!

-- 실전 의미:
-- 잔액 확인 후 차감할 때:
BEGIN;
SELECT balance FROM accounts WHERE id = 1;            -- 1000원 (과거)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 500원 (현재)
-- 반드시 FOR UPDATE로 최신값을 읽고 차감해야 함!

MVCC 운영 이슈와 대응

1. 장기 트랜잭션 → Undo Log 비대화

-- 문제: 열려있는 트랜잭션이 Undo purge를 차단
-- → History list length 무한 증가 → 디스크 공간 고갈 + 쿼리 성능 저하

-- 진단
SELECT 
  trx_id,
  trx_started,
  TIMESTAMPDIFF(MINUTE, trx_started, NOW()) AS minutes_old
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(MINUTE, trx_started, NOW()) > 10
ORDER BY trx_started;

-- 예방: 트랜잭션 타임아웃 설정
SET GLOBAL innodb_rollback_on_timeout = ON;
SET GLOBAL wait_timeout = 300;           -- 유휴 연결 5분 후 종료
SET GLOBAL interactive_timeout = 300;

-- 앱 레벨: 커넥션 풀의 idle timeout 설정
-- HikariCP: idleTimeout=300000 (5분)
-- Prisma: connection_limit + pool_timeout

2. 대량 UPDATE 시 Undo Log 폭발

-- ❌ 100만 행 한 번에 UPDATE → Undo Log 수 GB 생성
UPDATE orders SET status = 'archived' WHERE created_at < '2024-01-01';

-- ✅ 배치로 나누어 처리
REPEAT
  UPDATE orders 
  SET status = 'archived' 
  WHERE created_at < '2024-01-01' AND status != 'archived'
  LIMIT 10000;
  
  -- 각 배치 사이에 잠깐 대기 (purge 시간 확보)
  SELECT SLEEP(0.5);
UNTIL ROW_COUNT() = 0 END REPEAT;

3. Consistent Read의 성능 함정

-- 상황: 자주 업데이트되는 행을 오래된 트랜잭션이 읽을 때
-- → Undo Log 체인이 매우 길어짐
-- → SELECT가 체인을 끝까지 탐색해야 함 → 쿼리 느려짐

-- 예: id=1 행이 1000번 UPDATE됨 + 오래된 Read View 존재
-- → SELECT id=1 시 Undo Log 1000개를 순회해야 보이는 버전 도달

-- 모니터링: Undo Log 페이지 읽기 수
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';  -- 디스크 읽기
-- Undo가 버퍼풀에서 밀려나면 디스크 I/O 급증

MySQL Optimizer Hints 쿼리 튜닝 가이드에서 쿼리 최적화 전략도 함께 확인하세요.

정리

InnoDB MVCC는 Undo Log의 버전 체인과 Read View의 가시성 판단으로 동작합니다. REPEATABLE READ에서는 트랜잭션 첫 SELECT 시 생성된 Read View를 재사용하여 일관된 스냅샷을 제공하고, READ COMMITTED에서는 매 SELECT마다 새 Read View를 생성합니다. 핵심 운영 포인트는 장기 트랜잭션 방지(Undo purge 차단 예방), 대량 DML 배치 처리(Undo Log 비대화 방지), 그리고 SELECT vs SELECT FOR UPDATE의 읽기 차이 인식입니다.

위로 스크롤
WordPress Appliance - Powered by TurnKey Linux