격리수준(Isolation)(2)

4. REPEATABLE READ

COMMIT된 데이터만 읽도록 하며 현재 트랜잭션이 조회하는 데이터를 다른 트랜잭션에서 수정할 수 없도록 지정하여 한 트랜잭션 내에서 읽은 데이터는 항상 같은 값을 읽게 하는 격리 수준입니다. 즉, 데이터 조회 시 공유 잠금을 획득하고 트랜잭션이 완료되기 전까지 반환하지 않습니다. 다만 범위 조건 검색 시 해당하는 데이터에 UPDATE가 불가능하지만 INSERT는 가능합니다. 그래서 Drity Read, Non-Repeatable Read는 방지되지만 Phantom Read가 발생합니다.

 

[Session 57]에서 REPEATABLE READ 격리 수준으로 테이블에서 NO가 1보다 큰 데이터를 조회하였고 해당하는 키값에 공유 잠금을 획득합니다.

이후 [Session 56]에서 [NO] 값이 2인 데이터의 [GRADE]를 ‘A’로 변경하기 위해 키값에 베타 잠금 획득을 시도하지만 [Session57]의 공유 잠금과 충돌하여 대기가 발생합니다.

다시 [Session 57]에서 테이블을 조회하면 처음 조회와 동일한 결과값이 조회됩니다.

COMMIT을 통해 트랜잭션이 완료되면서 [Session 57]에서 공유 잠금을 반환하면 [Session 56]에서 베타 잠금을 획득하여 업데이트가 수행됩니다.

 

 

5. SERIALIZABLE

 

REPEATABLE READ와 같은 특성을 가지지만 범위 조건 검색 시 해당 범위에 INSERT가 불가능합니다.

그 이유는 키 단위로 공유 잠금을 하는 것이 아니라 키 범위로 공유 잠금을 하기 때문입니다.

이 격리 수준은 동시성은 매우 낮지만 데이터 정합성이 매우 중요한 경우 선택안이 될 수 있으며

Dirty Read, Non-Repeatable Read, Phamtom Read 모두 방지됩니다.

 

[Session57]에서 SERIALIZABLE 격리 수준으로 테이블에서 [NO]가 3이상 5이하인 데이터를 조회하였고 해당하는 키 범위 공유 잠금을 획득합니다.

이후 [Session56] 에서 [NO]가 4인 데이터 삽입을 시도하지만 [Session57]의 키 범위 공유 잠금과 충돌하여 대기가 발생합니다.

대기 중인 쿼리를 중지하고 이번엔 [Session57]의 조회 범위 밖인 [NO]가 7인 데이터 삽입을 시도하였더니 정상적으로 구문이 실행되며 해당 키 값에 베타 잠금을 획득합니다.

이후 [Session57]에서 NO가 3이상 5이하인 데이터를 다시 조회할 경우 처음과 같은 데이터를 확인할 수 있습니다.

 

6. SNAPSHOT

 

ALLOW_SNAPSHOT_ISOLATION 기능을 활성화하면 데이터베이스 수준에서 스냅샷 옵션을 사용하도록 설정할 수 있습니다.

이 옵션은 트랜잭션에서 스냅샷 격리를 사용하지 않는 경우에도 DML 문에서 행 버전을 생성하기 시작하고 스냅샷 트랜잭션 격리 수준을 지정할 수 있습니다.

스냅샷 격리 수준은 스냅샷 트랜잭션이 아닌 다른 트랜잭션에서 데이터가 수정되면 행 버전을 생성하고 스냅샷 트랜잭션에서 수정하려는 행의 버전을 확인하여 수정된 버전이 있다면 업데이트 충돌 오류가 발생하게 됩니다.

 

SNAPSHOT 격리 수준은 ALTER 구문을 통해 데이터베이스를 변경해 주어야 합니다.

ALTER DATABASE {데이터베이스명} SET ALLOW_SNAPSHOT_ISOLATION ON

 

 

[Session 57]에서 ALLOW_SNAPSHOT_ISOLATION 격리 수준을 활성화시킨 후 해당 격리 수준으로 테이블에서 [NO]가 3이상 5이하인 데이터를 조회합니다.

Session 56에서 [NO]가 5인 데이터의 [GRADE] 값을 ‘D’로 업데이트한 후 동일하게 데이터를 조회하면 변경된 값으로 조회됩니다.

[Session57]에서 데이터를 다시 조회하면 변경된 데이터가 아닌 [Session57]에서 처음 스냅샷된 상태인 ‘A’로 조회되고 [Session56]에서 COMMIT하여 트랜잭션을 완료한 뒤 다시 조회해도 동일한 결과를 유지합니다.

이후 해당 데이터를 ‘B’로 변경하려고 하자 [Session57]에서 이미 변경된 데이터로 인해 업데이트 충돌이 발생하게 됩니다.

 

아래의 표는 격리 수준에 따라 발생되는 문제에 대해 정리한 것입니다.

 

격리수준	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	O	O	O
READ COMMITTED	X	O	O
REPEATABLE READ	X	X	O
SERIALIZABLE	X	X	X
SNAPSHOT	X	X	X