MySQL 트랜잭션 격리 수준 이해

Introduction

데이터베이스 트랜잭션의 격리 수준(Isolation Level)은 동시성 제어를 위한 핵심 개념입니다. 동시 트랜잭션이 실행될 때 데이터 일관성을 어떻게 보장할 것인지를 결정하며, 각 격리 수준은 읽기 이상(read anomaly)과 성능 간의 트레이드오프를 제공합니다. 이 글에서는 MySQL의 격리 수준과 실제 활용 방법을 다루겠습니다.

Environment

-- MySQL 격리 수준 확인
SELECT @@transaction_isolation;
-- REPEATABLE-READ (MySQL 기본값)

-- 격리 수준 변경
SET SESSION transaction_isolation = 'READ-COMMITTED';

# MySQL 트랜잭션 관련 설정 확인
mysql -u root -p -e "SHOW VARIABLES LIKE '%isolation%';"
mysql -u root -p -e "SHOW VARIABLES LIKE '%lock%';"

Problem

격리 수준에 따른 문제 상황 재현:

-- 세션 1에서 실행
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1;
-- balance = 1000

-- 세션 2에서 동시에 실행
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;

-- 세션 1에서 다시 확인
SELECT balance FROM accounts WHERE user_id = 1;
-- REPEATABLE-READ: balance = 100 (원래 값 유지 - Phantom Read 방지)
-- READ-COMMITTED: balance = 900 (최신 커밋된 값 반영)

-- Phantom Read 시나리오
-- 세션 1
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- 결과: 5건

-- 세션 2에서 새로운 pending 주문 추가 및 커밋
INSERT INTO orders (status, user_id) VALUES ('pending', 100);
COMMIT;

-- 세션 1에서 다시 확인
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- REPEATABLE-READ: 5건 (Phantom Read 발생!)
-- SERIALIZABLE: 6건 (Phantom Read 방지)

Analysis

MySQL의 격리 수준별 동작을 분석했습니다:

-- 격리 수준별 읽기 이상현상 분석
-- 1. Dirty Read: 커밋되지 않은 데이터 읽기
-- 2. Non-Repeatable Read: 동일 쿼리에서 다른 결과
-- 3. Phantom Read: 새로운 행이 나타나는 현상

-- 격리 수준별 특성 테이블
-- 격리 수준            Dirty Read  Non-Repeatable Read  Phantom Read
-- READ UNCOMMITTED    가능         가능                 가능
-- READ COMMITTED      불가         가능                 가능
-- REPEATABLE READ     불가         불가                 가능 (InnoDB는 방지)
-- SERIALIZABLE       불가         불가                 불가

-- InnoDB의 MVCC 동작 원리 분석
EXPLAIN SELECT * FROM accounts WHERE user_id = 1;
-- InnoDB는 MVCC(Multi-Version Concurrency Control)를 사용하여
-- 각 트랜잭션의 시작 시점에 일관된 스냅샷을 제공

Solution

1단계: 격리 수준 테스트

-- READ UNCOMMITTED 테스트
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';

-- 세션 1
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE user_id = 1;
-- 커밋하지 않음

-- 세션 2 (READ UNCOMMITTED)
SELECT balance FROM accounts WHERE user_id = 1;
-- balance = 500 (Dirty Read 발생!)

-- READ COMMITTED 테스트
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- 세션 1
START TRANSACTION;
UPDATE accounts SET balance = 600 WHERE user_id = 1;
COMMIT;

-- 세션 2
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1;  -- 500 (원래 값)
-- 세션 1의 커밋 후
SELECT balance FROM accounts WHERE user_id = 1;  -- 600 (새로운 값)
-- Non-Repeatable Read 발생!
COMMIT;

2단계: 격리 수준 선택 가이드

-- 격리 수준 선택 기준
-- 1. READ COMMITTED: 
--    - 웹 애플리케이션에서 일반적으로 사용
--    - 높은 동시성 필요 시
--    - Phantom Read가 허용되는 경우

-- 2. REPEATABLE READ (MySQL 기본값):
--    - 금융 트랜잭션 등 데이터 일관성 중요 시
--    - InnoDB는 Next-Key Locking으로 Phantom Read 방지

-- 3. SERIALIZABLE:
--    - 최고 수준의 일관성 필요 시
--    - 동시성보다 정확성이 중요한 경우

-- 세션 레벨 격리 수준 설정
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- 트랜잭션 레벨 격리 수준 설정
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE user_id = 1;
COMMIT;

3단계: 격리 수준별 잠금 전략

-- READ COMMITTED: Row-level Locking만 사용
-- REPEATABLE READ: Row-level Locking + Next-Key Locking
-- SERIALIZABLE: 모든 읽기에 Shared Lock 적용

-- 잠금 상태 확인
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 데드락 모니터링
SHOW ENGINE INNODB STATUS;

-- 현재 실행 중인 트랜잭션 확인
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND = 'Query';

Lessons Learned

기본값 이해: MySQL의 기본 격리 수준인 REPEATABLE READ는 InnoDB의 Next-Key Locking으로 Phantom Read를 방지합니다
성능 고려: 높은 격리 수준은 동시성을 저하므로, 필요에 따라 적절한 수준을 선택해야 합니다
애플리케이션 설계: 격리 수준에 따라 애플리케이션의 트랜잭션 로직을 조정해야 합니다
잠금 분석: 정기적으로 잠금 상태를 모니터링하여 데드락을 방지해야 합니다
트랜잭션 최소화: 불필요한 긴 트랜잭션을 피하고 가능한 짧은 트랜잭션을 유지해야 합니다

이 블로그는 외부 스폰서십, 제휴 마케팅 또는 광고 수익을 받지 않습니다.

MySQL 트랜잭션 격리 수준 이해: 데이터 일관성 보장