[Database] ANSI 고립 수준(Isolation Level) / 이상 현상(Anomaly)

728x90

ANSI 고립 수준(Isolation Level)

고립 수준 / 이상 현상	Dirty Read	Non-Repeatable Read	Phantom Read	일관성 수준	동시성 수준
READ UNCOMMITTED	✅ 허용	✅ 허용	✅ 허용	낮음	높음
READ COMMITTED	❌	✅ 허용	✅ 허용	중간	중간
REPEATABLE READ	❌	❌	✅ 허용	높음	낮음
SERIALIZABLE	❌	❌	❌	매우 높음	매우 낮음

이상 현상(Anomaly)

1) Dirty Read

다른 트랜잭션이 아직 COMMIT하지 않은 데이터를 읽어버리는 현상

시간	트랜잭션 A	트랜잭션 B
T1	계좌 잔액 = 100 조회
T2	계좌 잔액을 100 → 0으로 UPDATE
T3	아직 COMMIT하지 않음
T4		잔액을 조회 → 0으로 읽힘 (커밋 안 된 값)
T5	A가 ROLLBACK (0 → 100)
T6	B는 실제로는 반영되지 않은 임시 데이터를 읽은 셈

2) Non-Repeatable Read

같은 트랜잭션 안에서 같은 데이터를 두 번 읽었는데, 값이 달라지는 현상

시간	트랜잭션 A	트랜잭션 B
T1	A: SELECT 잔액 FROM 계좌 WHERE id=1; → 결과 100
T2		B: UPDATE 계좌 SET 잔액=50 WHERE id=1;
T3		B: COMMIT
T4	A: 같은 쿼리 재실행 → 결과 50

3) Phantom Read

같은 조건으로 여러 행을 읽었는데, 트랜잭션 사이에 새로운 행이 생겨 결과 집합이 달라지는 현상

시간	트랜잭션 A	트랜잭션 B
T1	A: *SELECT FROM 주문 WHERE 금액 > 10000**; → 결과 3건
T2		B: INSERT INTO 주문 VALUES (id=99, 금액=20000);
T3		B: COMMIT
T4	A: 같은 쿼리 재실행 → 결과 4건 (새로운 행 등장)

고립 수준(Isolation Level)

1) READ UNCOMMITTED (읽기 미완료)

가장 낮은 고립 수준으로, 커밋되지 않은 다른 트랜잭션의 변경 사항도 읽을 수 있습니다.

특징:
- Dirty Read 발생 가능
- Non-Repeatable Read, Phantom Read 모두 발생 가능
- 높은 동시성, 낮은 일관성
문제 예시:
- 트랜잭션 A가 데이터를 수정했지만 아직 COMMIT하지 않았는데,
- 트랜잭션 B가 그 데이터를 읽어버림 → A가 ROLLBACK하면 B는 잘못된 데이터를 본 것.

2) READ COMMITTED (읽기 커밋)

가장 일반적인 수준 (Oracle 기본값)
커밋된 데이터만 읽을 수 있습니다.

특징:
- Dirty Read 방지
- Non-Repeatable Read, Phantom Read는 여전히 가능
- 즉, 트랜잭션 A가 SELECT를 수행하는 동안, 트랜잭션 B가 UPDATE, INSERT 가능
문제 예시:
- 트랜잭션 B가 어떤 행을 읽고, 그동안 트랜잭션 A가 해당 행을 수정 후 커밋하면
- B가 다시 같은 쿼리를 실행했을 때 다른 결과를 보게 됨 (비반복적 읽기).

3) REPEATABLE READ (반복 읽기)

트랜잭션이 시작된 시점 이후로 같은 행을 읽을 때는 항상 같은 결과를 보장합니다.

특징:
- Dirty Read, Non-Repeatable Read 방지
- Phantom Read는 여전히 가능
- 즉, 트랜잭션 A가 SELECT를 수행하는 동안, 트랜잭션 B가 UPDATE 불가, INSERT 가능
- 데드락(Dead Lock)에 빠질 수 있음
문제 예시:
- 트랜잭션 B가 WHERE 조건으로 여러 행을 읽었는데,
- 트랜잭션 A가 그 사이에 새로운 행을 INSERT → B가 다시 조회 시 새 행이 나타남 (유령 읽기).
MySQL InnoDB의 기본 수준이 REPEATABLE READ입니다.

4) SERIALIZABLE (직렬화)

가장 높은 고립 수준으로, 데이터를 변경하는 다른 사용자가 전혀 없는 데이터베이스 환경에서 작업하는 것처럼 보이도록 트랜잭션을 처리합니다.

하지만 여기엔 오해의 소지가 다분한 포인트가 있죠.

많은 분들이 SERIALIZABLE 고립 수준을 "모든 트랜잭션이 진짜로 순차적으로(직렬로) 실행된다"라고 오해하지만, 실제로는 논리적 직렬화(Logical Serialization) 을 의미합니다.

논리적 직렬화(Logical Serialization)

ANSI SQL에서 말하는 SERIALIZABLE 수준은 트랜잭션 간의 실행 결과가

어떤 순서로든 하나씩 차례로 실행한 결과와 같아야 한다는 규칙입니다.

DB는 트랜잭션들을 실제로 동시에 실행할 수 있음 (병렬 실행 가능)
단, 커밋 결과가 "하나씩 차례로 실행한 것처럼" 보여야 함
이를 위해 DB는 락(lock) 또는 MVCC 스냅샷(snapshot) 등을 사용하여 일관성 있는 결과를 보장

시간	세션 A	세션 B
T1	alter session set isolation_level=serializble;	-
T2	-	alter session set isolation_level=serializble;
T3	insert into a select count(*) from b; (테이블 a, b row 수 0건)	-
T4	-	insert into b select count(*) from a; (테이블 a, b row 수 0건)
T5	commit;	-
T6	-	commit;

위 표처럼 세션 A와 세션 B가 타임라인에 따라 실행될 경우에는,

마치 두 트랜잭션이 해당 시점에 데이터베이스에서 실행 중인 유일한 트랜잭션이었던 것처럼 처리됩니다.

즉, a, b 테이블 모두 row 수가 0건으로 처리되는 거죠.

세션 A가 아무리 많이 쿼리하더라도, 세션 B의 commit 여부와 상관없이 T1 시점에 데이터베이스에 commit된 상태만을 기준으로 값을 구하게 됩니다. 세션 B도 마찬가지고요.

특징:
- Dirty Read, Non-Repeatable Read, Phantom Read 모두 방지
- 완전한 일관성 보장
- 성능 저하 (락 범위가 커짐)
문제 예시:
- 트랜잭션 A가 SERIALIZABLE 로 시작
- A가 본 "스냅샷 시점(트랜잭션 시작 SCN)" 이후에 B가 같은 행(또는 동등한 키 범위)을 커밋으로 변경
- 그 다음 A가 그 행(또는 범위)을 변경(UPDATE/DELETE/INSERT) 하려는 순간,
  → A는 해당 문장을 실패(보통 전체 트랜잭션 롤백)

오라클의 경우
ORA-08177: can't serialize access for this transaction 발생
> https://khyup.tistory.com/289

Database별 최소 고립 수준

Oracle Database: 기본 고립 수준이 READ COMMITTED,
SELECT 시에는 Undo를 이용한 Consistent Read 메커니즘을 통해 구현됩니다.
MySQL InnoDB: 기본이 REPEATABLE READ, MVCC(Multi-Version Concurrency Control)로 구현됩니다.
PostgreSQL: 기본이 READ COMMITTED,
SERIALIZABLE 수준에서는 Serializable Snapshot Isolation 알고리즘을 사용합니다.