[MSSQL/SQL Server] 트랜잭션 격리 수준

1-1. 개요

-트랜잭션 격리 수준이란 동시에 여러 트랜잭션이 처리될 때, 트랜잭션끼리 얼마나 서로 고립되어 있는지를 나타낸다.

-어떠한 격리 수준이 설정되어 있느냐에 따라 트랜잭션이 다르게 작동하며, 한 번에 한 가지의 격리 수준만 설정할 수 있다.

 

1-2. 트랜잭션 격리 수준의 종류

• Read uncommitted (커밋되지 않은 읽기) → 가장 동시성이 좋지만 일관성은 가장 나쁨
• Read committed (커밋된 읽기) → SQL Server의 기본 설정값
• Repeatable Read (반복 읽기)
• Snapshot (스냅숏)
• Serializable (직렬화 기능) → 동시성은 가장 나쁘지만 일관성이 가장 좋음

종류	특징
Read uncommitted	• 트랜잭션 격리 수준 중 가장 낮은 단계 (레벨 0) • SELECT 작업 수행 시 공유 잠금(S-Lock)이 걸리지 않기 때문에 트랜잭션이 완료되지 않은 상태의 데이터도 조회 가능 • 장점: 대기 없이 빠른 속도로 조회 가능(조회 성능 향상), 데드락 방지 가능 • 단점: 커밋되지 않은 데이터를 읽기 때문에 트랜잭션이 Rollback되는 경우, 잘못된 데이터를 읽게 될 수 있음
Read committed	• SQL Server 2008 기본 격리 수준 (레벨 1) • SELECT 작업 수행 시, 공유 잠금(S-Lock)이 걸리는 계층 • DML(INSERT, UPDATE, DELETE) 작업 중인 row 또는 table에 SELECT할 경우, 해당 작업이 끝나야 SELECT를 할 수 있음 • 장점: 커밋된 데이터만 읽기 때문에 Dirty Read가 발생하지 않아, 대부분의 데이터를 신뢰할 수 있음 • 단점: 다른 사용자의 작업이 완료될 때까지 계속 대기 상태에 있어야 함
Repeatable Read	• 트랜잭션이 완료될 때까지 SELECT 작업에 사용되는 모든 데이터에 대해 S-Lock이 걸리는 계층 (레벨 2) • 트랜잭션이 범위 내에서 조회된 데이터 값이 항상 동일함을 보장함 • 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 변경 불가능 • Non-Repeatable Read 부정합이 발생하지 않으나, Phantom Read는 발생할 수 있음
Snapshot	• SQL Server의 행 버전 관리의 특성상 tempdb에 커밋된 행들의 이전 버전을 기록하는데, 이 특성을 이용한 격리수준으로 트랜잭션이 시작된 시점에 사용할 수 있는 최신의 커밋 데이터만 사용 가능함 • 트랜잭션이 진행 중인 테이블에 새 데이터를 입력하면, 그 데이터를 실제 테이블에 적용하지 않고 우선 tempdb에 적용을 시켜놓고, 원래 테이블에 트랜잭션이 커밋된 후에 tempdb에 적용해 놓은 데이터를 다시 원래의 테이블에 입력하는 것임
Serializable	• 트랜잭션이 완료될 때까지 SELECT 작업에 사용되는 모든 데이터에 대해 S-Lock이 걸리는 계층 (레벨 3) • 선행 트랜잭션이 특정 테이블을 SELECT하는 경우, S-Lock을 걸어 다른 트랜잭션에서 해당 테이블의 데이터를 UPDATE, DELETE, INSERT하지 못하도록 막음 • 가장 엄격한 격리 수준으로 완벽한 읽기 일관성 모드를 제공 • 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 수정 및 입력 불가능 • Phantom Read가 발생하지 않음

 

1-3. 동시성 문제

-여러 사용자가 동시에 하나의 데이터에 접근할 때 발생하는 문제는 다음 세 가지가 있다.

 

• Dirty Read (더티 리드) → 커밋되지 않은 데이터 읽기
• Unrepeatable Read (반복되지 않은 읽기)
• Phantom Read (팬텀 읽기, 가상 읽기)

 

1) Dirty Read (더티 리드)

 

• 메모리(데이터 캐시)에는 변경이 되었지만 아직 디스크에는 변경되지 않은 데이터(페이지)를 읽는 것
• 문제점: 아직 커밋되지 않은 데이터를 읽어오기 때문에, 더티 페이지를 읽은 후에 더티 페이지의 데이터가 Rollback된다면 이미 읽어온 데이터는 잘못된 데이터가 된다. (데이터 일관성 저하)
• 장점: 동시성이 좋아서 커밋을 기다리지 않고 값을 읽어올 수 있다.
• Read uncommitted 격리 수준은 더티 리드를 허용한다.

 

2) Unrepeatable Read (반복되지 않은 읽기)

 

• 트랜잭션 내에서 한 번 읽은 데이터가 트랜잭션이 끝나기 전에 변경되었다면, 다시 읽었을 때 새로운 값이 읽히는 것
• Unrepeatable Read 예시
• 트랜잭션 A에서 ○ ○ ○씨의 계좌 잔액 조회 → 1,000원이 조회됨 (아직 커밋되지 않은 상태임)
• 트랜잭션 B에서 ○ ○ ○씨의 계좌 잔액을 500원으로 변경 후 커밋함
• 트랜잭션 A에서 다시 ○ ○ ○씨의 계좌 잔액 조회 → 500원이 조회됨

→  이를 해결하기 위해서는 격리 수준을 Repatable Read 이상으로 설정해야 한다.

 

 

3) Phantom Read (팬텀 읽기, 가상 읽기)

 

• Repeatable Read 격리 수준에서는 트랜잭션이 진행 중인(엄밀히 말하자면 공유 잠금이 설정된) 데이터에 대해서 변경 작업을 할 수 없지만, 새로운 데이터의 입력 작업(INSERT)은 가능한데 이를 Phantom Read라고 한다.
• Phantom Read를 방지하려면 격리 수준을 Serializable으로 설정해야 한다. (또는 Snapshot 격리 수준을 설정해서 방지할 수도 있음)

 

1-4. 격리 수준과 동시성 부작용의 관계

	Dirty Read (커밋되지 않은 데이터 읽기)	Unrepeatable Read (반복되지 않은 읽기)	Phantom Read (팬텀 읽기, 가상 읽기)
Read uncommitted (커밋되지 않은 읽기)	O	O	O
Read committed (커밋된 읽기)	X	O	O
Repeatable Read (반복된 읽기)	X	X	O
Snapshot (스냅숏)	X	X	X (데이터의 입력은 됨)
Serializable (직렬화 가능)	X	X	X (데이터 입력 자체가 불가능)

 

 

 

---

참고 자료

『뇌를 자극하는 SQL Sever 2008』, 우재남 저