MySQL 공부 4 - 격리 수준, Lock, B+Tree
3편에서 Redo Log, Undo Log, MVCC의 동작 원리를 봤다. 이번 편에서는 그 위에서 동작하는 트랜잭션 격리 수준과 Lock 메커니즘, 그리고 인덱스의 근간이 되는 B+Tree를 다룬다.
격리 수준이 필요한 이유
격리 수준은 동시성과 일관성의 트레이드오프를 조절하는 도구다.
- 높은 동시성 (여러 트랜잭션 동시 실행) → 성능 향상, but 데이터 불일치 가능
- 높은 일관성 (트랜잭션 간 격리) → 데이터 정확성, but 대기 시간 증가
격리 수준이 낮을수록 발생할 수 있는 문제가 세 가지 있다.
Dirty Read — 커밋되지 않은 데이터를 읽는 현상
초기 상태: Alice = 1000원, Bob = 500원
t=0 Transaction A 시작
UPDATE: Alice → Bob 500원 이체
Alice = 500원 (아직 커밋 X)
t=1 Transaction B 시작
SELECT balance WHERE name = 'Alice'
→ 결과: 500원 ← Dirty Read!
B가 이 값을 기반으로 비즈니스 로직 실행
t=2 Transaction A ROLLBACK
Alice = 1000원으로 복구
결과: B는 존재하지 않는 데이터를 읽고 잘못된 판단을 수행Non-Repeatable Read — 같은 쿼리를 두 번 실행했을 때 다른 결과
t=0 Transaction A 시작
SELECT price WHERE id = 1 → 10000원
t=1 Transaction B 시작
UPDATE price = 12000 WHERE id = 1
COMMIT
t=2 Transaction A 다시 조회
SELECT price WHERE id = 1 → 12000원 ← 값이 바뀜!
결과: 같은 트랜잭션 내에서 다른 결과 → 보고서 데이터 일관성 깨짐Phantom Read — 같은 범위 조회를 두 번 했을 때 행 개수가 달라지는 현상
t=0 Transaction A 시작
SELECT * FROM employees WHERE dept_id = 1 → 3명
SELECT SUM(salary) WHERE dept_id = 1 → 10500원
t=1 Transaction B 시작
INSERT INTO employees (dept_id=1, name='David', salary=3200)
COMMIT
t=2 Transaction A 다시 조회
SELECT * FROM employees WHERE dept_id = 1 → 4명 ← 유령 발생!
SELECT SUM(salary) WHERE dept_id = 1 → 13700원
결과: 직원 수 3명으로 알고 있었는데 총 급여가 맞지 않음격리 수준 4단계
현재 설정 확인:
SELECT @@transaction_isolation;
SHOW VARIABLES LIKE 'transaction_isolation';READ UNCOMMITTED (레벨 0)
커밋 안 된 데이터도 읽을 수 있다. Dirty Read / Non-Repeatable Read / Phantom Read 모두 발생. Lock을 거의 사용하지 않아 성능은 좋지만 데이터 무결성이 없다.
금융, 결제 등 정확성이 중요한 곳에서는 절대 사용 금지.
READ COMMITTED (레벨 1)
커밋된 데이터만 읽는다. 매 SELECT마다 새로운 Read View를 생성한다.
Transaction A
START TRANSACTION;
SELECT balance WHERE id = 1; → Read View #1 생성, 결과: 1000
-- 다른 트랜잭션이 1500으로 커밋
SELECT balance WHERE id = 1; → Read View #2 생성 (새로 만듦)
결과: 1500 ← 변경 감지!
COMMIT;- Dirty Read 방지
- Non-Repeatable Read, Phantom Read 발생
- Oracle, PostgreSQL 기본값
- 성능과 일관성의 균형점. 동시성 높음.
REPEATABLE READ (레벨 2)
첫 SELECT 시 Read View를 한 번만 생성하고 재사용한다.
Transaction A
START TRANSACTION;
SELECT balance WHERE id = 1; → Read View 생성, 결과: 1000
-- 다른 트랜잭션이 1500으로 커밋
SELECT balance WHERE id = 1; → 같은 Read View 재사용
결과: 1000 ← 변함없음!
COMMIT;- Dirty Read, Non-Repeatable Read 방지
- Phantom Read는 InnoDB에서 Next-Key Lock으로 부분 방지
- MySQL InnoDB 기본값
InnoDB의 Phantom Read 방지:
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;
-- age 20~30 범위에 Gap Lock 설정
-- 다른 트랜잭션이 이 범위에 INSERT 불가 → Phantom Read 방지💡 팁: REPEATABLE READ가 기본값인데도 MySQL이 Phantom Read를 대부분 막는 이유는 MVCC와 Next-Key Lock 덕분이다. 단,
SELECT ... FOR UPDATE처럼 잠금을 거는 경우에는 실제 데이터를 읽으므로 예외가 생길 수 있다. 긴 트랜잭션에서는 Undo Log 체인이 길어져 성능이 저하될 수 있으니 주의.
SERIALIZABLE (레벨 3)
모든 SELECT에 자동으로 S-Lock을 건다. 트랜잭션이 순차적으로 실행되는 것처럼 동작한다.
Transaction A
START TRANSACTION;
SELECT balance WHERE id = 1; → S-Lock 획득
다른 트랜잭션은 UPDATE 불가
COMMIT; → S-Lock 해제- 모든 동시성 문제 방지
- 성능 최악, Deadlock 빈번, 동시성 급감
- 매우 드물게 사용 (레거시 마이그레이션, 금융 감사, 임시 디버깅)
격리 수준 비교 정리
| 격리 수준 | Dirty Read | Non-Repeatable Read | Phantom Read | 성능 |
|---|---|---|---|---|
| READ UNCOMMITTED | 발생 | 발생 | 발생 | 최고 |
| READ COMMITTED | 방지 | 발생 | 발생 | 높음 |
| REPEATABLE READ | 방지 | 방지 | 부분 방지 | 중간 |
| SERIALIZABLE | 방지 | 방지 | 방지 | 최악 |
Lock 메커니즘
S-Lock (Shared Lock, 공유 잠금) — 읽기 보호
여러 트랜잭션이 동시에 읽기 가능하지만, 수정은 차단한다.
- S-Lock + S-Lock = 호환 (동시 읽기 OK)
- S-Lock + X-Lock = 충돌 (수정 대기)
SELECT ... LOCK IN SHARE MODE 또는 SELECT ... FOR SHARE 로 획득.
-- Transaction A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- S-Lock 획득
-- balance: 1000
-- Transaction B (동시 실행)
-- Case 1: 읽기 시도
SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- 성공! S-Lock끼리 호환
-- Case 2: 수정 시도
UPDATE accounts SET balance = 2000 WHERE id = 1;
-- 대기 발생! Transaction A가 커밋할 때까지X-Lock (Exclusive Lock, 배타 잠금) — 쓰기 보호
한 트랜잭션만 독점적으로 수정 가능. 다른 트랜잭션의 읽기(Lock 있는)/쓰기 모두 차단.
- X-Lock + S-Lock = 충돌
- X-Lock + X-Lock = 충돌
SELECT ... FOR UPDATE,UPDATE,DELETE,INSERT시 자동 획득
-- Transaction A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- X-Lock 획득
-- Transaction B
-- Case 1: 일반 SELECT (Lock 없음)
SELECT balance FROM accounts WHERE id = 1;
-- 성공! MVCC 덕분에 Undo Log에서 읽음 (간접 호환)
-- Case 2: S-Lock 시도
SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- 대기 발생!
-- Case 3: X-Lock 시도
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 대기 발생!
-- Case 4: 수정 시도
UPDATE accounts SET balance = 2000 WHERE id = 1;
-- 대기 발생!💡 팁: 일반
SELECT는 MVCC 덕분에 X-Lock과 간접적으로 호환된다. Undo Log에서 이전 버전을 읽기 때문에 Lock이 걸린 행도 그냥 읽을 수 있다. Lock을 걸고 읽고 싶다면FOR SHARE또는FOR UPDATE를 명시해야 한다.
Lost Update 해결 방법
SELECT FOR UPDATE (비관적 잠금)
-- 세션 1
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- X-Lock 획득
-- balance: 1000
-- new_balance = 1000 - 500 = 500
SELECT SLEEP(10);
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;
-- 세션 2
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- X-Lock 충돌 → 세션 1 커밋까지 대기
-- 커밋 후 실행 → 최신 값 500을 읽음
-- new_balance = 500 - 300 = 200
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;
-- 최종 결과: 200원 (Lost Update 방지!)UPDATE로 직접 계산
-- 나쁜 방법 (Race Condition 위험)
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 읽기
-- new_balance = balance - 500 (애플리케이션에서 계산)
UPDATE accounts SET balance = 500 WHERE id = 1; -- 쓰기
COMMIT;
-- 좋은 방법
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- 한 번에!
COMMIT;UPDATE가 자동으로 X-Lock을 획득하고 현재 값을 읽어 계산하고 업데이트한다. 네트워크 왕복이 1회로 줄고 Race Condition이 원천 차단된다.
낙관적 잠금 vs 비관적 잠금
비관적 잠금 (Pessimistic Locking)
충돌이 일어날 것으로 가정하고 미리 Lock을 건다. X-Lock (FOR UPDATE)을 주로 사용한다.
START TRANSACTION;
SELECT stock FROM inventory WHERE product_id = 100 FOR UPDATE;
-- stock = 50
-- 재고 충분하면
UPDATE inventory SET stock = stock - 5 WHERE product_id = 100;
INSERT INTO orders (product_id, quantity) VALUES (100, 5);
COMMIT;
-- 다른 트랜잭션은 SELECT FOR UPDATE 대기 → 순차 처리, 재고 부족 방지- 장점: 확실한 방지, 재시도 로직 불필요, 복잡한 비즈니스 로직에 적합
- 단점: Lock 대기 발생, Deadlock 위험, 동시성 감소
- 사용 사례: 금융 거래, 재고 관리, 좌석 예약, 충돌이 빈번한 경우
낙관적 잠금 (Optimistic Locking)
충돌이 거의 없을 것으로 가정하고 DB Lock 없이 나중에 충돌을 감지한다. Version 컬럼을 활용한다.
-- 세션 1
START TRANSACTION;
SELECT id, balance, version FROM accounts WHERE id = 1;
-- id=1, balance=1000, version=0
-- new_balance = 1000 - 500 = 500
SELECT SLEEP(10);
UPDATE accounts SET balance = 500, version = version + 1
WHERE id = 1 AND version = 0; -- 읽었던 버전과 같은지 확인
-- 영향받은 행: 0 (세션 2가 먼저 커밋해서 version이 바뀜)
-- Lost Update 감지 → ROLLBACK
-- 세션 2
START TRANSACTION;
SELECT id, balance, version FROM accounts WHERE id = 1;
-- id=1, balance=1000, version=0
UPDATE accounts SET balance = 700, version = version + 1
WHERE id = 1 AND version = 0;
-- 영향받은 행: 1 (성공!)
COMMIT;- 장점: Lock 없어서 성능 우수, Deadlock 없음, 동시성 높음
- 단점: 충돌 시 재시도 로직 필요, 충돌이 많으면 오히려 느림
- 사용 사례: 충돌이 드문 경우, 읽기가 많고 쓰기가 적은 경우 (위키, 게시글 수정)
| 비관적 잠금 | 낙관적 잠금 | |
|---|---|---|
| 사용 Lock | X-Lock / S-Lock | 없음 (Version 컬럼) |
| 충돌 처리 | 미리 방지 | 나중에 감지 |
| 성능 | 낮음 | 높음 |
| Deadlock | 위험 | 없음 |
| 재시도 로직 | 불필요 | 필요 |
| 적합한 경우 | 충돌 빈번 | 충돌 드문 경우 |
B+Tree — 인덱스의 근간
구조
B+Tree 3층 구조 (Primary Key 인덱스)
[Root Node]
50
┌────┴────┐
[Internal] [Internal]
20 30 60 70 80
┌──┼──┐ ┌──┼──┼──┐
[Leaf][Leaf][Leaf][Leaf][Leaf][Leaf]
10 20 30 50 60 70 80
↓ ↓ ↓ ↓ ↓ ↓ ↓
Data Data Data Data Data Data Data
◄─────────────────────────────────► Linked List- Root Node: 검색 시작점
- Internal Node: Key와 포인터만 저장. 실제 데이터 없음.
- Leaf Node: 실제 데이터 저장. Linked List로 연결.
모든 Leaf까지의 깊이가 동일한 Balanced Tree다.
왜 빠른가 — 4가지 이유
1. 낮은 트리 높이 (Fanout 덕분)
한 Node에 Key를 많이 저장할수록 자식 수(Fanout)가 늘어나고 트리 높이가 낮아진다.
INT Primary Key 기준:
Page 크기 16KB, Key + Pointer = 4 + 8 = 12 bytes
Fanout = 16384 / 12 ≈ 1365
이진 트리 (Fanout=2): 높이 = log_2(100,000,000) = 27 → I/O 27회 = 270ms
B+Tree (Fanout=1365): 높이 = log_1365(100,000,000) = 3 → I/O 3회 = 30ms2. Buffer Pool 캐싱
3층 구조에서 Root와 Internal Node는 거의 항상 메모리에 있다. Leaf Node 접근만 디스크 I/O가 필요하다.
Root Node → 항상 메모리 (16KB)
Internal → 대부분 메모리
Leaf Node → 디스크 or 메모리
- 캐시 히트: 0.0001ms
- 캐시 미스: 10ms💡 Root와 Internal에는 데이터가 없는데 왜 캐시되면 빨라지냐고? Root/Internal은 "데이터가 있는 Leaf의 위치"를 찾는 네비게이션 역할이다. 이들이 메모리에 있으면 Leaf 페이지 하나만 디스크에서 읽으면 된다.
3. Leaf Node Linked List
범위 검색 시 처음 위치만 Tree로 찾고, 이후는 Linked List를 따라가며 순차 읽기를 한다.
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- 1. Tree 탐색으로 age=20 찾기 (3번 I/O)
-- 2. Linked List 따라가며 순차 읽기
-- 3. age > 30 만나면 중단4. Sequential I/O
Leaf Node가 디스크에 순차적으로 저장되어 있어 범위 검색 시 디스크 헤드가 한 방향으로만 이동한다.
Sequential Read: HDD 100MB/s, SSD 500MB/s
Random I/O: HDD 200 IOPS, SSD 10,000 IOPS💡 Fanout이 크면 너비가 넓어져서 오히려 느린 거 아니냐고? 노드 내부에서 Key를 비교하는 것은 메모리 내 이진 탐색이라 거의 비용이 없다. 반면 노드 간 이동(수직 이동)은 새로운 페이지를 디스크에서 읽어야 해서 비싸다. 트리 높이가 낮을수록 수직 이동이 줄어들고, 그게 B+Tree가 빠른 핵심 이유다.
Primary Key vs Secondary Index
Primary Key (Clustered Index)
Leaf에 실제 데이터 행 전체 저장
[Leaf Node]
id=10, name='Alice', email='alice@...' ← 전체 행
id=20, name='Bob', email='bob@...'- 테이블당 1개만 존재
- 데이터가 PK 순서로 정렬 저장
SELECT * FROM users WHERE id = 50→ 3 I/O로 끝
Secondary Index
Leaf에 PK만 저장 (데이터 없음)
[Leaf Node]
email='alice@...', id=10 ← PK만
email='bob@...', id=25- 여러 개 생성 가능
- Leaf에 PK만 저장해서 공간 절약
- 실제 데이터를 읽으려면 PK로 Clustered Index를 한 번 더 탐색
SELECT * FROM users WHERE email = 'alice@...';
→ Secondary Index 탐색 (3 I/O) → PK=10 획득
→ Clustered Index 탐색 (3 I/O) → 실제 데이터 반환
→ 총 6 I/O💡 팁: Secondary Index 조회가 느린 이유가 이 이중 탐색 때문이다. Covering Index를 쓰면 이 문제를 해결할 수 있다.
SELECT id, email FROM users WHERE email = 'alice@...'처럼 SELECT 절이 인덱스 컬럼만 포함하면 Clustered Index 재탐색 없이 Secondary Index Leaf에서 바로 답을 얻는다.
자료구조별 비교
| 자료구조 | 검색 | 삽입 | 범위 검색 |
|---|---|---|---|
| Array | O(n) | O(n) | O(n) |
| Sorted Array | O(log n) | O(n) | O(k) |
| Hash Table | O(1) | O(1) | 불가능 |
| Binary Tree | O(log n) | O(log n) | O(k) |
| B+Tree | O(log n) | O(log n) | O(k) |
Hash Table이 검색은 O(1)로 가장 빠르지만 범위 검색이 불가능해서 인덱스로 쓸 수 없다. B+Tree는 검색, 삽입, 범위 검색 모두 균형 잡힌 성능을 내는 유일한 자료구조다.
마치며
이번 편의 핵심 연결고리를 정리하면:
- 격리 수준은 MVCC 위에서 동작한다. READ COMMITTED는 매 SELECT마다 Read View를 새로 만들고, REPEATABLE READ는 첫 SELECT의 Read View를 재사용한다.
- Lock은 MVCC가 해결 못하는 Write-Write 충돌을 막는다. 일반 SELECT는 Lock 없이 MVCC로 읽지만, 쓰기 전에 읽어야 하는 경우엔
FOR UPDATE로 명시적으로 Lock을 걸어야 한다. - B+Tree는 높은 Fanout으로 트리 높이를 낮추고, Leaf Linked List로 범위 검색을 빠르게 한다. Secondary Index는 PK를 경유하기 때문에 2배의 I/O가 필요하다.
