DB 엔지니어링 — 동시성 제어·Locking·MVCC

이 글은 데이터베이스 엔지니어링 마스터 노트 시리즈의 여섯 번째 편입니다. 1편(ACID)에서 격리 수준을 살짝 봤다면, 이번엔 그 격리 수준이 실제로 어떻게 구현되는지 — 동시성 제어.

수천 트랜잭션이 동시에 같은 데이터에 접근. Locking·MVCC가 정합성을 보장. 잘못 쓰면 Deadlock·성능 저하. 옵티미스틱·페시미스틱 선택이 시스템 처리량 결정.

동시성 제어란?

여러 트랜잭션이 동시에 같은 데이터에 접근할 때 정합성 보장.

문제 — 동시 실행 시 다음 현상 발생 가능:

• Lost Update — 한 쓰기가 다른 쓰기에 덮어 씌워짐
• Dirty Read — 미커밋 데이터를 다른 트랜잭션이 읽음
• Non-Repeatable Read — 같은 행 두 번 읽기 결과 다름
• Phantom Read — 같은 조건 두 번 조회, 결과 행 수 다름

해결 두 가지 — Locking(보수적·비관적) / MVCC(낙관적).

Pessimistic Locking — 비관적 잠금

"충돌이 일어날 거다. 미리 잠궈서 막자."

Lock 종류

종류	의미
Shared (S, Read Lock)	다른 S OK, X 차단
Exclusive (X, Write Lock)	모든 lock 차단

T1 holds S → T2 S OK / T2 X 차단
T1 holds X → T2 S 차단 / T2 X 차단

잠금 단위

단위	동시성	오버헤드
Row Lock	높음	큼
Page Lock	중간	중간
Table Lock	낮음	작음

PostgreSQL·InnoDB = 기본 row lock. 일부 DBMS는 lock escalation (row → page → table).

SELECT FOR UPDATE

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- X lock
-- 다른 트랜잭션 차단
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

여기서 시험 함정이 하나 있어요. FOR UPDATE 빠뜨리면 lock 안 걸림. 단순 SELECT는 lock X. 동시 UPDATE 시 race condition 발생.

Two-Phase Locking (2PL)

직렬화 가능성(Serializability) 보장 프로토콜.

Phase 1 (Growing): lock 획득만 (해제 X)
Phase 2 (Shrinking): lock 해제만 (획득 X)

대부분 DBMS의 격리 수준 구현 기반.

MVCC — Multi-Version Concurrency Control

"읽기는 lock 없이. 데이터 버전을 여러 개 유지."

동작 원리

T1 (시각 100): SELECT
  → 시각 100의 스냅샷 읽기

T2 (시각 105): UPDATE
  → 새 버전(v2) 생성. v1 그대로

T1 다시 SELECT (트랜잭션 안)
  → 여전히 v1 (스냅샷 일관성)

T3 (시각 110): SELECT
  → 시각 110의 스냅샷 → v2

읽기와 쓰기가 서로 차단 X. 처리량 ↑.

여기서 정말 중요한 시험 함정 — PostgreSQL·Oracle·MySQL InnoDB 모두 MVCC 사용. SQL Server는 옵션 (READ_COMMITTED_SNAPSHOT). MVCC는 거의 표준.

구현 — Tuple Versioning

PostgreSQL:

각 행마다 시스템 컬럼:
  xmin = 생성한 트랜잭션 ID
  xmax = 삭제한 트랜잭션 ID

UPDATE = 새 버전 추가 + 옛 버전 xmax 표시
DELETE = xmax 표시

오래된 버전 = VACUUM이 정리.

여기서 시험 함정이 하나 있어요. PostgreSQL VACUUM 안 하면 테이블 부풀음. 빈번한 UPDATE는 옛 버전 누적 → 디스크 ↑ + 쿼리 ↓. autovacuum 설정 필수.

MVCC 단점

• 공간 ↑ — 옛 버전 보관
• VACUUM 부담
• Long Transaction 위험 — 긴 트랜잭션 = 옛 버전 영구 보존 = 부풀음

Optimistic Concurrency Control — 낙관적

"충돌 거의 없다. 발생 시만 처리."

패턴 1 — Version Column

-- 행에 version 컬럼
SELECT id, balance, version FROM accounts WHERE id = 1;
-- → balance=100, version=5

-- 업데이트 시 version 체크
UPDATE accounts
SET balance = 90, version = 6
WHERE id = 1 AND version = 5;
-- 영향 행수 0 = 다른 트랜잭션이 먼저 수정 = 재시도

패턴 2 — Timestamp

updated_at을 비교. 같은 원리.

사용 시점

상황	선택
충돌 빈도 높음 (인기 상품 재고)	Pessimistic
충돌 빈도 낮음 (사용자 프로필)	Optimistic
READ heavy	Optimistic

여기서 정말 중요한 시험 함정 — Optimistic은 race가 있으면 재시도. 충돌 빈번하면 재시도 폭주 → 처리량 폭락. 이때는 Pessimistic이 더 빠름.

Deadlock — 교착 상태

T1: lock A 보유 → lock B 요청 (대기)
T2: lock B 보유 → lock A 요청 (대기)
→ 영원히 대기 (deadlock)

탐지

DBMS가 주기적으로 wait-for graph 검사. 사이클 발견 시 한 트랜잭션 강제 ROLLBACK (희생자 선택).

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678;
        Process 5678 waits for ShareLock on transaction 1234;
HINT: See server log for query details.

회피 전략

1. Lock 순서 통일 — 항상 같은 순서로 (id 작은 것 먼저)

   -- ◯ 항상 id 오름차순
   SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
   
   -- X 순서 다름
   T1: SELECT WHERE id = 1 FOR UPDATE; SELECT WHERE id = 2 FOR UPDATE;
   T2: SELECT WHERE id = 2 FOR UPDATE; SELECT WHERE id = 1 FOR UPDATE;
   

2. 트랜잭션 짧게 — Lock 보유 시간 ↓

3. 자동 재시도 로직 — Deadlock 시 SLEEP + 재시도

4. NOWAIT / SKIP LOCKED — 대기 없이 실패 또는 건너뛰기

여기서 정말 중요한 시험 함정 — Deadlock 100% 회피 X. 어차피 발생, 자동 재시도 로직으로 우아하게 처리.

Phantom Read 방어

T1: SELECT COUNT(*) FROM orders WHERE date = '2024-05';  -- 100
T2: INSERT INTO orders (date, ...) VALUES ('2024-05', ...);
T2: COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE date = '2024-05';  -- 101 (Phantom!)

방어법

방법	설명
Range Lock	범위 자체에 lock (다른 트랜잭션 INSERT 차단)
Predicate Lock	조건에 lock
Serializable 격리	직렬화 가능 = 모든 phantom 방어
Snapshot Isolation	MVCC 스냅샷으로 phantom 회피

PostgreSQL Serializable = SSI (Serializable Snapshot Isolation) 사용.

격리 수준 구현 (1편 보충)

격리 수준	구현
Read Uncommitted	거의 안 씀, lock X
Read Committed	쓰기 lock 까지만
Repeatable Read	읽기 lock 트랜잭션 끝까지 (또는 MVCC)
Serializable	2PL 또는 SSI

여기서 시험 함정이 하나 있어요. PostgreSQL 기본 격리 수준 = Read Committed. MySQL InnoDB 기본 = Repeatable Read. 같은 SQL이라도 DB마다 다르게 동작 가능.

Lock 모니터링

-- PostgreSQL: 현재 lock
SELECT * FROM pg_locks
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid;

-- 대기 중인 query
SELECT pid, query, state, wait_event_type
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- MySQL InnoDB
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SHOW ENGINE INNODB STATUS;

예시 — 재고 차감 안전한 패턴

-- ◯ 안전 (FOR UPDATE)
BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- 검증 후
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- ◯ 더 좋은 방법 (원자적 UPDATE)
UPDATE products SET stock = stock - 1
WHERE id = 1 AND stock > 0;
-- 영향 행수 0 = 재고 부족, 원자적이라 race 없음

여기서 정말 중요한 시험 함정 — 재고 차감 같은 핫 패턴은 원자적 UPDATE가 가장 빠르고 안전. SELECT 후 UPDATE는 race 위험. 원자적 UPDATE로 한 번에.

시험 직전 한 번 더 — 자주 헷갈리는 함정 모음

여기까지가 6편의 핵심입니다. 시험 직전 또는 실무에서 헷갈릴 때 다시 펼쳐 볼 수 있게 압축 노트로 마무리할게요.

• 동시성 제어 = 여러 트랜잭션 동시 접근 시 정합성 보장
• 4 현상 — Lost Update / Dirty Read / Non-Repeatable / Phantom
• 두 접근 — Locking(비관적) / MVCC(낙관적)
• Shared (S) = 읽기, S끼리 공존 / Exclusive (X) = 쓰기, 모든 lock 차단
• 잠금 단위 — Row > Page > Table
• 작을수록 동시성 ↑ 오버헤드 ↑
• SELECT FOR UPDATE = 읽기에 X lock
• 이거 빠뜨리면 race condition
• 2PL (Two-Phase Locking) = Growing → Shrinking
• MVCC = 데이터 버전 여러 개, 읽기-쓰기 차단 X
• PostgreSQL·Oracle·MySQL InnoDB 표준
• VACUUM 안 하면 PostgreSQL 부풀음
• Long Transaction = 옛 버전 누적 → 디스크 ↑
• Optimistic Locking = version 컬럼 또는 timestamp
• 충돌 시 재시도, 충돌 적을 때만
• 충돌 많으면 Pessimistic이 빠름
• Deadlock = 순환 대기, DBMS 자동 탐지 후 ROLLBACK
• 회피 — Lock 순서 통일 / 짧은 트랜잭션 / NOWAIT / SKIP LOCKED
• 자동 재시도 로직 필수
• Phantom Read 방어 — Range Lock / SSI / Serializable
• PostgreSQL Serializable = SSI (Serializable Snapshot Isolation)
• PostgreSQL 기본 = Read Committed / MySQL = Repeatable Read
• 재고 차감 = 원자적 UPDATE가 가장 빠르고 안전
• UPDATE ... WHERE stock > 0 패턴

시리즈 다른 편

• 1편 — ACID·트랜잭션
• 2편 — 인덱싱
• 3편 — 파티셔닝
• 4편 — 샤딩
• 5편 — 복제
• 6편 — 동시성 제어 (현재 글)
• 7편 — 내부 구조
• 8편 — 고급 주제

공식 문서: PostgreSQL MVCC 에서 더 깊이.

다음 글(7편)에서는 DB 내부 구조 — Storage Engine, B-Tree 페이지, WAL, Buffer Pool, 쿼리 실행 단계까지 풀어 갑니다.