CRUD는 이미 익숙할 거예요. 이번 장은 "동시성·정합성·성능"의 진짜 밑바닥을 팝니다. 격리 수준이 만드는 이상현상, MVCC가 읽기와 쓰기를 떼어놓는 원리, 락과 데드락, 인덱스가 타고 안 타는 경계, 그리고 복제·샤딩으로 확장하는 법 — 장애 대응과 시니어 면접의 단골이에요.
EXPLAIN으로 판단해요.트랜잭션의 I(Isolation)는 "동시에 도는 트랜잭션이 서로를 얼마나 볼 수 있는가"를 정하는 다이얼이에요. 격리를 느슨하게 하면 동시성(처리량)은 오르지만 이상한 값이 보이고, 빡빡하게 하면 정합성은 좋아지지만 대기·경합이 늘어요. 먼저 막고 싶은 세 가지 이상현상부터 봅시다.
| 이상현상 | 무슨 일 |
|---|---|
| Dirty Read | 다른 트랜잭션이 아직 커밋 안 한(롤백될 수도 있는) 값을 읽음 |
| Non-repeatable Read | 같은 행을 두 번 읽었는데 사이에 남이 UPDATE·커밋해서 값이 달라짐 |
| Phantom Read | 같은 조건으로 두 번 조회했는데 사이에 남이 INSERT·커밋해서 행 개수가 달라짐 |
ANSI SQL 표준은 격리 수준을 4단계로 정의하고, 각 단계가 어떤 이상현상을 허용/차단하는지로 규정해요.
| 격리 수준 | Dirty | Non-repeatable | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | ❌ 허용 | ❌ 허용 | ❌ 허용 |
| READ COMMITTED | ✅ 차단 | ❌ 허용 | ❌ 허용 |
| REPEATABLE READ | ✅ 차단 | ✅ 차단 | ❌ 허용* |
| SERIALIZABLE | ✅ 차단 | ✅ 차단 | ✅ 차단 |
REPEATABLE READ, PostgreSQL·Oracle·SQL Server의 기본은 READ COMMITTED예요. 같은 애플리케이션 코드라도 MySQL에선 트랜잭션 내내 스냅샷이 고정되고, PostgreSQL에선 매 문장마다 최신 커밋을 보게 됩니다.FOR UPDATE) 시 Phantom을 상당 부분 막습니다. PostgreSQL의 REPEATABLE READ는 아예 스냅샷 격리라 순수 SELECT에선 Phantom이 안 보여요.
그래서 "이론상 허용" ≠ "이 DB에서 발생"이에요. Phantom을 표준적으로 확실히 막는 최소 수준은 여전히 SERIALIZABLE입니다.
옛날 방식은 "읽으려면 읽기 락, 쓰려면 쓰기 락"이라 읽기와 쓰기가 서로를 막았어요. MVCC는 데이터를 덮어쓰는 대신 여러 버전(version)을 유지해서, 각 트랜잭션이 자기 시점의 스냅샷을 읽게 합니다. 그 결과 읽기는 쓰기를 막지 않고, 쓰기도 읽기를 막지 않아요. 쓰기끼리만 충돌하죠.
SELECT. MVCC로 내 시점의 과거 버전을 락 없이 읽어요. 남이 바꿔도 안 보임.SELECT ... FOR UPDATE, FOR SHARE, UPDATE/DELETE. 최신 커밋 버전을 읽고 락을 겁니다.
"재고를 읽고 차감"처럼 읽은 값 기준으로 갱신할 땐 스냅샷 읽기로는 갱신 유실(lost update)이 나요. 그래서 SELECT ... FOR UPDATE로 현재 읽기 + 락을 잡습니다.
-- 재고 차감: 반드시 현재 읽기(FOR UPDATE)로 잠그고 확인 후 갱신
BEGIN;
SELECT stock FROM product WHERE id = 42 FOR UPDATE; -- 최신값 + 배타 락
UPDATE product SET stock = stock - 1 WHERE id = 42;
COMMIT;| 구분 | 비관적 락(Pessimistic) | 낙관적 락(Optimistic) |
|---|---|---|
| 전제 | 충돌이 자주 난다 | 충돌이 드물다 |
| 방식 | 미리 락을 잡음(FOR UPDATE) | 락 없이 진행, 커밋 때 버전 비교 |
| 충돌 시 | 남은 대기(blocking) | 실패→재시도(버전 불일치) |
| 적합 | 경합 심함·짧은 트랜잭션 | 읽기 위주·경합 낮음·웹 폼 |
낙관적 락은 보통 버전 컬럼으로 구현해요. 읽을 때 버전을 같이 읽고, 갱신할 때 그 버전을 조건에 넣습니다. 그 사이 누가 먼저 바꿔서 버전이 올라갔다면 업데이트된 행 수가 0 → 충돌 감지 → 재시도.
-- 낙관적 락: 읽은 version이 그대로일 때만 갱신됨
UPDATE account
SET balance = 900, version = version + 1
WHERE id = 7 AND version = 3; -- 영향 행 0이면 = 누군가 먼저 수정함 → 충돌@Version 필드를 두면 JPA가 위 UPDATE의 WHERE version = ?를 자동으로 만들고, 불일치 시 OptimisticLockException을 던져요. 비관적 락은 @Lock(PESSIMISTIC_WRITE)가 FOR UPDATE를 붙입니다.대부분의 관계형 인덱스는 B-tree(정확히는 B+tree)예요. 값이 정렬되어 있어 특정 값·범위를 로그 시간에 찾고, 리프가 연결돼 범위 스캔·정렬(ORDER BY)에도 강해요. 핵심은 "인덱스는 정렬된 자료구조라, 정렬 순서를 활용할 수 있을 때만 효과가 난다"입니다.
(a, b, c) 복합 인덱스는 a로 먼저 정렬, 같은 a 안에서 b, 그 안에서 c로 정렬돼요. 그래서
a, a, b, a, b, c 조건은 인덱스를 타지만, b만 또는 c만으로는 못 타요(선두 컬럼이 빠졌으니까). ORDER BY a, b 같은 정렬도 이 인덱스로 추가 정렬 없이 해결됩니다.
그래서 복합 인덱스는 컬럼 순서 설계가 절반이에요. 자주 쓰는 조건·선택도(cardinality)가 높은 컬럼을 앞에 두는 게 정석입니다.
EXPLAIN에서 MySQL은 Using index로 표시돼요.반대로, 인덱스가 있어도 안 타는 대표적인 경우들이 있어요. 공통 원리는 "인덱스에 저장된 원본 값의 정렬을 그대로 못 쓰게 만드는 순간 무력화된다"입니다.
| 안 타는 경우 | 이유 |
|---|---|
WHERE YEAR(created_at) = 2026 | 컬럼에 함수·연산을 적용하면 인덱스의 원본 값을 못 씀 → 범위 조건으로 바꿔라 |
WHERE name LIKE '%kim' | 앞 와일드카드는 시작점을 못 잡아 스캔. 'kim%'는 탈 수 있음 |
WHERE phone = 1012345678 (컬럼은 문자열) | 암묵적 형변환이 컬럼 쪽에 걸리면 인덱스 무력화 |
| 낮은 선택도(예: 성별=남) | 대상 행이 너무 많으면 옵티마이저가 풀 스캔을 택하기도 함 |
EXPLAIN. 실행계획으로 어떤 인덱스를 탔는지·스캔 방식(type)·예상 행 수(rows)·Extra를 확인하세요. MySQL의 type에서 ref/range는 인덱스 활용, ALL은 풀 테이블 스캔이에요. 실측이 필요하면 EXPLAIN ANALYZE로 실제 시간까지 봅니다. 함수 조건이 불가피하면 함수 기반 인덱스(PostgreSQL·최신 MySQL) 또는 생성 컬럼(generated column) + 인덱스로 우회해요.트래픽이 커지면 수직 확장(스케일 업)엔 한계가 와요. 그때 쓰는 세 가지 카드가 읽기 복제·샤딩·반정규화입니다.
user_id % N 또는 범위/해시). 쓰기·저장 용량이 서버 수만큼 늘어납니다. 대신 대가가 커요.
샤드를 넘나드는 조인·트랜잭션·정렬·집계가 어려워지고, 샤드 키 선택이 운명을 가릅니다. 키가 편향되면 특정 샤드만 뜨거워지는(hotspot) 데이터 쏠림이 생겨요. 그래서 고르게 분산되고, 대부분의 쿼리가 한 샤드로 떨어지는 키를 골라야 합니다.
FOR UPDATE=현재 읽기+락.EXPLAIN.