11장 : 동시성 제어
MVCC
MVCC(Multi-Version Concurrency Control)는 읽기와 쓰기가 서로를 차단하지 않는 동시성 제어 기법입니다. 현대 RDBMS의 핵심 기술로, 높은 동시성과 일관성을 동시에 달성합니다.
MVCC의 개념
전통적인 락 방식에서는 누군가 데이터를 수정 중이면 다른 사람은 읽기도 대기해야 합니다. MVCC는 데이터의 여러 버전을 유지하여 이 문제를 해결합니다.
락 방식 (2PL 기반)
T1: UPDATE A = 200 → X-Lock 획득
T2: SELECT A → 대기... (T1이 COMMIT할 때까지)
T1: COMMIT → X-Lock 해제
T2: SELECT A = 200 → 이제야 읽기 가능!
→ 읽기가 쓰기를 차단, 쓰기가 읽기를 차단
MVCC 방식
T1: UPDATE A = 200 → 새 버전 생성 (A=200), 이전 버전 보관 (A=100)
T2: SELECT A = 100 → 이전 버전 읽기! 대기 없음!
T1: COMMIT
T2: SELECT A = ? → 격리 수준에 따라 100 또는 200
→ 읽기는 절대 쓰기를 차단하지 않음
→ 쓰기는 절대 읽기를 차단하지 않음1. 데이터를 수정하면 기존 값을 덮어쓰지 않고 새 버전을 생성
2. 이전 버전은 별도 공간에 보관 (Undo)
3. 읽기 트랜잭션은 자신에게 보이는 적절한 버전을 선택하여 읽기
4. 어떤 버전을 읽을지는 격리 수준에 의해 결정
핵심 질문: "이 트랜잭션에서 이 행의 어떤 버전을 봐야 하는가?"
→ 이 질문에 대한 답이 격리 수준마다 다름Oracle의 MVCC — Undo Segment와 SCN
Oracle은 Undo Segment와 SCN(System Change Number)으로 MVCC를 구현합니다.
SCN (System Change Number)
SCN = Oracle의 논리적 시각 (단조 증가하는 숫자)
모든 변경 작업에 SCN이 부여됨
SCN 1000: INSERT INTO orders VALUES (1, 'A', 100);
SCN 1001: UPDATE orders SET amount = 200 WHERE id = 1;
SCN 1002: DELETE FROM orders WHERE id = 2;
쿼리에도 시작 SCN이 부여됨
SCN 1001에 시작된 SELECT → SCN 1001 이전의 데이터만 볼 수 있음
확인
SELECT CURRENT_SCN FROM V$DATABASE;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;Undo Segment를 이용한 버전 관리
현재 데이터 블록
┌─────────────────┐
│ A = 300 │
│ SCN = 2000 │
│ Undo Ptr ───────┼──→ 이전 버전
└─────────────────┘
이전 버전 1 (Undo Segment)
┌─────────────────┐
│ A = 200 │ (SCN 1500에 의해 생성)
│ SCN = 1500 │
│ Undo Ptr ───────┼──→ 이전 버전
└─────────────────┘
이전 버전 2
┌────────────┐
│ A = 100 │
│ SCN = 1000 │
│ Undo Ptr=∅ │
└────────────┘
버전 체인: 300(SCN2000) → 200(SCN1500) → 100(SCN1000)T1이 SCN 1500에 SELECT A 실행:
1. 현재 블록 확인: A=300, SCN=2000
→ 2000 > 1500 → 이 버전은 너무 최신, 사용 불가
2. Undo 체인 따라감: A=200, SCN=1500
→ 1500 ≤ 1500 → 이 버전 사용 가능!
→ A = 200 반환
T2가 SCN 1200에 SELECT A 실행:
1. 현재 블록: SCN=2000 > 1200 → 불가
2. Undo 1: SCN=1500 > 1200 → 불가
3. Undo 2: SCN=1000 ≤ 1200 → 사용 가능!
→ A = 100 반환ORA-01555: Snapshot Too Old
문제 상황
1. T1이 SCN 1000에 대량 SELECT 시작 (오래 걸림)
2. 다른 트랜잭션들이 데이터를 계속 UPDATE 후 COMMIT
3. Undo Segment 공간이 부족해 오래된 Undo 데이터가 덮어씌워짐
4. T1이 SCN 1000 시점의 Undo를 찾으려 했으나 이미 소멸
→ ORA-01555: snapshot too old
해결 방법
* UNDO_RETENTION 파라미터 늘리기 (초 단위)
ALTER SYSTEM SET UNDO_RETENTION = 3600; -- 1시간
* Undo Tablespace 크기 늘리기
* GUARANTEE 옵션으로 Undo 보존 강제
* 대용량 조회는 업무 시간 외 실행MySQL InnoDB의 MVCC
MySQL InnoDB도 MVCC를 사용하지만 구현 방식이 다릅니다. 각 행에 숨겨진 컬럼을 추가하여 버전 체인을 관리합니다.
InnoDB 숨겨진 컬럼
사용자 정의 컬럼: id=1, name='Kim', balance=1000
숨겨진 컬럼:
DB_TRX_ID = 100 ← 이 행을 마지막으로 수정한 트랜잭션 ID
DB_ROLL_PTR = 0xAB ← Undo Log의 이전 버전을 가리키는 포인터
DB_ROW_ID = 1 ← 숨겨진 클러스터드 인덱스 (PK 없을 때)
버전 체인:
현재 행 (TRX_ID=100) ─→ Undo Log (TRX_ID=90) ─→ Undo Log (TRX_ID=80)
balance=1000 balance=800 balance=500ReadView
ReadView = "이 순간 어떤 트랜잭션이 활성 상태인가" 의 스냅샷
ReadView 구성
m_ids : 현재 활성(커밋 안 된) 트랜잭션 ID 목록
min_trx_id : 활성 트랜잭션 중 가장 작은 ID
max_trx_id : 다음에 할당될 트랜잭션 ID (아직 시작 안 됨)
creator_trx_id: 이 ReadView를 만든 트랜잭션 ID
가시성 판단
행의 DB_TRX_ID를 검사
1. DB_TRX_ID < min_trx_id → 이미 커밋된 트랜잭션 → 보임 ✓
2. DB_TRX_ID ≥ max_trx_id → 미래 트랜잭션 → 안 보임 ✗
3. DB_TRX_ID가 m_ids에 있음 → 아직 커밋 안 됨 → 안 보임 ✗
4. DB_TRX_ID가 m_ids에 없음 → 커밋된 트랜잭션 → 보임 ✓READ COMMITTED
* 매 SELECT마다 새로운 ReadView 생성
* → 다른 트랜잭션이 커밋하면 그 변경이 바로 보임
REPEATABLE READ
* 트랜잭션의 첫 번째 SELECT에서만 ReadView 생성
* → 이후 모든 SELECT가 같은 ReadView 사용
* → 트랜잭션 동안 일관된 스냅샷 유지활성 트랜잭션: T10, T20, T30
ReadView: m_ids=[10,20,30], min=10, max=31
행 A: DB_TRX_ID = 5 → 5 < 10(min) → 보임 ✓ (커밋 완료)
행 B: DB_TRX_ID = 15 → 10 ≤ 15 < 31, m_ids에 없음 → 보임 ✓
행 C: DB_TRX_ID = 20 → m_ids에 있음 → 안 보임 ✗ (아직 활성)
행 D: DB_TRX_ID = 35 → 35 ≥ 31(max) → 안 보임 ✗ (미래)
행 C가 안 보이면 → Undo Log에서 이전 버전 확인
이전 버전의 DB_TRX_ID = 8 → 8 < 10 → 보임 ✓Oracle vs MySQL MVCC 비교
| 비교 | Oracle | MySQL InnoDB |
|---|---|---|
| 버전 저장 | Undo Segment | Undo Log (공유 테이블스페이스) |
| 시점 식별 | SCN (전역 번호) | 트랜잭션 ID + ReadView |
| 스냅샷 기준 (RC) | 쿼리 시작 시점 | SELECT마다 새 ReadView |
| 스냅샷 기준 (RR) | N/A (RC가 기본) | 트랜잭션 첫 SELECT 시점 |
| 기본 격리 수준 | READ COMMITTED | REPEATABLE READ |
| SELECT 잠금 | 없음 (항상) | 없음 (MVCC 읽기) |
| Undo 고갈 | ORA-01555 | Undo Log 비대화 |
| 쓰기 vs 쓰기 | 행 잠금 대기 | 행 잠금 대기 |
PostgreSQL은 Undo를 사용하지 않는 독특한 방식
방식: 데이터 파일 자체에 이전 버전을 보관
* xmin: 이 행 버전을 만든 트랜잭션 ID
* xmax: 이 행 버전을 삭제/갱신한 트랜잭션 ID (0이면 활성)
* UPDATE → 기존 행에 xmax 설정 + 새 행 INSERT
장점: Undo 공간 관리 불필요
단점: 데이터 파일이 비대해짐 → VACUUM으로 정리 필요
* VACUUM: 더 이상 필요 없는 이전 버전(Dead Tuple) 정리
* AUTOVACUUM: 자동으로 VACUUM 수행낙관적 잠금 vs 비관적 잠금
MVCC 환경에서 실무적으로 자주 사용되는 두 가지 동시성 제어 패턴입니다.
비관적 잠금 (Pessimistic Locking)
-- 재고 차감 시: 먼저 락을 건 후 처리
-- Oracle
SELECT stock FROM products WHERE id = 101 FOR UPDATE;
-- → 해당 행에 X-Lock, 다른 트랜잭션의 UPDATE/DELETE/FOR UPDATE 차단
-- MySQL (타임아웃 설정)
SELECT stock FROM products WHERE id = 101 FOR UPDATE NOWAIT;
-- → 잠금 획득 불가 시 즉시 에러 (대기 안 함)
SELECT stock FROM products WHERE id = 101
FOR UPDATE SKIP LOCKED;
-- → 잠긴 행은 건너뛰고 잠금 가능한 행만 반환 (큐 패턴)
UPDATE products SET stock = stock - 1 WHERE id = 101;
COMMIT;FOR UPDATE와 데드락:
T1: SELECT ... FROM A FOR UPDATE; (A 잠금)
T2: SELECT ... FROM B FOR UPDATE; (B 잠금)
T1: SELECT ... FROM B FOR UPDATE; → 대기 (T2가 B 잠금 중)
T2: SELECT ... FROM A FOR UPDATE; → 대기 (T1이 A 잠금 중)
→ 데드락!
해결 방법:
* 접근 순서 통일: 항상 A → B 순서로 접근
* NOWAIT 또는 WAIT 타임아웃 사용
* 잠금 범위 최소화낙관적 잠금 (Optimistic Locking)
-- version 컬럼을 이용하는 방식
SELECT id, stock, version FROM products WHERE id = 101;
-- → stock=10, version=3
-- 읽은 version으로 조건부 UPDATE
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 101 AND version = 3;
-- → 다른 트랜잭션이 먼저 수정했으면 version이 달라서 0건 갱신
-- 영향받은 행이 0이면 충돌 발생 → 재시도 또는 에러 처리1. version 컬럼 (가장 일반적):
WHERE id = ? AND version = ?
SET version = version + 1
2. 타임스탬프:
WHERE id = ? AND updated_at = ?
SET updated_at = NOW()
3. 해시(Oracle):
WHERE id = ? AND ORA_HASH(col1||col2||col3) = ?
4. 전체 컬럼 비교 (Hibernate default):
WHERE id = ? AND col1 = ? AND col2 = ? AND col3 = ?| 비교 | 비관적 | 낙관적 |
|---|---|---|
| 방식 | 미리 락 획득 (SELECT FOR UPDATE) | 커밋 시 충돌 검사 (version) |
| 충돌 시 | 대기 (블로킹) | 재시도 또는 에러 |
| 적합 상황 | 충돌 빈번 (좌석 예약, 재고) | 충돌 드문 (프로필 수정) |
| 성능 | 동시성↓ | 동시성↑ |
| 데드락 위험 | 있음 | 없음 |
| DB 잠금 | 사용 | 미사용 |
MVCC와 쓰기 충돌
MVCC는 읽기-쓰기는 해결하지만, 쓰기-쓰기는 여전히 잠금 필요:
T1: UPDATE A SET val = 100 → A 행에 X-Lock 획득
T2: UPDATE A SET val = 200 → X-Lock 대기... (T1 끝날 때까지)
이것은 모든 MVCC DBMS에서 동일
* Oracle: 행 수준 잠금
* MySQL InnoDB: 행 수준 잠금 (+ Gap Lock)
* PostgreSQL: 행 수준 잠금
차이점은 대기 중 읽기
T3: SELECT val FROM A
→ 락 방식: T1이 끝날 때까지 대기
→ MVCC: 이전 버전으로 즉시 읽기 (대기 없음!)MVCC와 격리 수준의 관계
READ UNCOMMITTED
* MVCC를 거의 사용하지 않음
* 커밋되지 않은 최신 버전을 그대로 읽음 (Dirty Read)
* Oracle은 이 수준을 지원하지 않음
READ COMMITTED
* 매 SELECT마다 새로운 스냅샷
* Oracle 기본, "문장 수준 읽기 일관성"
* 같은 트랜잭션 내 두 번 SELECT해도 다른 결과 가능
→ Non-Repeatable Read 가능
REPEATABLE READ
* 트랜잭션 시작 시점의 스냅샷 고정
* MySQL InnoDB 기본
* 같은 트랜잭션 내 SELECT 결과 항상 동일
* InnoDB는 MVCC + Gap Lock으로 Phantom Read도 방지
SERIALIZABLE:
* Oracle: SELECT도 잠금 방식으로 동작
* PostgreSQL SSI: MVCC 기반이면서 직렬화 가능성 검증
* MySQL: 모든 SELECT가 자동으로 SELECT ... FOR SHARE시나리오
T1: BEGIN (시점 A)
T2: UPDATE X = 200 (기존 100)
T2: COMMIT
T1: SELECT X (시점 B)
READ COMMITTED
→ 시점 B에 새 ReadView 생성
→ T2 커밋됨 → X = 200 보임
REPEATABLE READ
→ 시점 A의 ReadView 유지
→ T2는 시점 A 이후 시작 → X = 100 보임 (이전 버전)MVCC에서의 Phantom Read 방지
일반적으로 MVCC만으로는 Phantom Read를 완전히 방지할 수 없음
InnoDB는 MVCC + Next-Key Lock 조합으로 해결
MVCC 레벨에서
* REPEATABLE READ에서 ReadView가 고정되므로
* 다른 트랜잭션이 INSERT한 새 행은 ReadView에 의해 안 보임
* → 순수 SELECT에서는 Phantom Read 방지 ✓
하지만 현재 읽기(Current Read)에서
* SELECT ... FOR UPDATE 는 MVCC가 아닌 최신 데이터 읽기
* 이 경우 Gap Lock으로 INSERT 자체를 차단하여 방지
예시
T1: SELECT * FROM users WHERE age > 20 FOR UPDATE;
→ age > 20 범위에 Gap Lock 설정
T2: INSERT INTO users (name, age) VALUES ('New', 25);
→ Gap Lock에 의해 대기! → Phantom Read 원천 차단MVCC 성능 고려사항
1. Undo/이전 버전 저장 공간
* Oracle: Undo Tablespace 크기 관리 필요
* MySQL: ibdata 또는 별도 Undo Tablespace
* PostgreSQL: Dead Tuple로 테이블 비대화
2. 버전 체인 길이
* 같은 행이 반복 수정되면 Undo 체인이 길어짐
* 오래된 트랜잭션이 있으면 체인 정리 불가
* 긴 체인 → SELECT 성능 저하 (체인 추적 비용)
3. 가비지 컬렉션
* Oracle: Undo Segment 자동 재사용
* MySQL: Purge Thread가 불필요한 Undo Log 정리
* PostgreSQL: VACUUM이 Dead Tuple 정리
→ VACUUM 지연 시 "Table Bloat" 발생!
4. 긴 트랜잭션의 위험
* 어떤 트랜잭션이 30분 동안 열려있으면
* 그 사이 발생한 모든 Undo를 정리할 수 없음
* Undo 공간 증가 + 테이블 비대화 + 성능 저하Oracle — Undo 사용량
SELECT tablespace_name, SUM(bytes)/1024/1024 MB
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1'
GROUP BY tablespace_name;
MySQL — Undo Log 상태
SHOW ENGINE INNODB STATUS;
→ "History list length" 값이 크면 Purge 지연
PostgreSQL — Dead Tuple
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;DBMS별 MVCC 구현 종합 비교
┌───────────────────┬──────────────┬──────────────┬──────────────┐
│ │ Oracle │ MySQL InnoDB │ PostgreSQL │
├───────────────────┼──────────────┼──────────────┼──────────────┤
│ 이전 버전 저장 │ Undo Segment │ Undo Log │ Heap 내 │
│ 시점 식별자 │ SCN │ TRX_ID │ xmin/xmax │
│ 가시성 판단 │ SCN 비교 │ ReadView │ CLOG + xid │
│ 기본 격리 수준 │ RC │ RR │ RC │
│ 버전 정리 │ 자동 재사용 │ Purge Thread │ VACUUM │
│ 공간 문제 │ ORA-01555 │ Undo 비대화 │ Table Bloat │
│ SELECT 잠금 여부 │ 없음 │ 없음 (MVCC) │ 없음 │
│ Phantom 방지 (RR) │ N/A │ MVCC+GapLock │ MVCC+SSI │
└───────────────────┴──────────────┴──────────────┴──────────────┘MVCC 실무 적용 패턴
Oracle에서는 MVCC의 Undo를 활용하여 과거 시점의 데이터를 조회할 수 있음
-- 5분 전 데이터 조회 (AS OF TIMESTAMP)
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE)
WHERE id = 101;
-- 특정 SCN 시점 조회
SELECT * FROM employees AS OF SCN 12345678;
-- 실수로 삭제한 데이터 복구
INSERT INTO employees
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
WHERE id = 101;
→ UNDO_RETENTION 범위 내에서만 가능Oracle
SELECT s.sid, s.serial#, s.username,
t.start_time, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY t.start_time;
MySQL
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) duration
FROM information_schema.innodb_trx
ORDER BY trx_started;
PostgreSQL
SELECT pid, usename, state, query_start,
NOW() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY query_start;정리
| 개념 | 핵심 내용 |
|---|---|
| MVCC | 다중 버전으로 읽기와 쓰기의 비차단 구현 |
| Oracle | Undo Segment + SCN, Consistent Read |
| MySQL InnoDB | Undo Log + 트랜잭션 ID + ReadView |
| PostgreSQL | 데이터 파일에 이전 버전 보관 + VACUUM |
| ReadView | 가시성 판단 기준 (활성 트랜잭션 스냅샷) |
| ORA-01555 | Undo 소멸로 인한 스냅샷 복원 실패 |
| 비관적 잠금 | FOR UPDATE로 미리 잠금, 충돌 빈번 시 적합 |
| 낙관적 잠금 | version 비교, 충돌 드문 경우 적합 |
MVCC의 핵심은 쓰기가 읽기를 차단하지 않는다는 것입니다. 덕분에 읽기 위주의 워크로드에서 매우 높은 동시성을 달성할 수 있습니다. 단, 이전 버전을 보관하는 Undo 공간을 적절히 관리하고, 오래 걸리는 조회가 Undo 고갈을 일으키지 않도록 주의해야 합니다.
다음 장에서는 트랜잭션의 지속성을 보장하는 복구와 로깅을 다루겠습니다.