[MSSQL] 격리수준-낙관적 동시성

 

SQL Server 에서는 "낙관적 동시성(optimistic concurrency)"을 기반으로 하는 

 

두 가지 격리 수준도 제공하고 있다.

 

1) SNAPSHOT

2) READ COMMITTED SNAPSHOT

 

 

 

**행버전 관리를 이용하는 격리 수준

SQL Server에는 커밋된 행들의 이전 버전을 tempdb 에 보관하는 기능이 있다. 

 

SQL Server에서는 이러한 행 버전 관리 기술을 기반으로 

 

SNAPSHOT 과 READ COMMITTED SNAPSHOT 이라고 하는 두 가지 격리 수준을 제공한다. 

 

SNAPSHOT 격리 수준은 논리적으로는 일관성 문제가 발생하지 않는다는 점에서 

 

SERIALIZABLE 격리 수준과 유사하다. 

 

또한, READ COMMITTED SNAPSHOT 격리 수준은 READ COMMITTED 격리 수준과 유사하다.

그러나, 행 버전 관리를 기반으로 하는 격리 수준을 사용하는 읽기 작업에서는

 

공유 잠금(S-LOCK)을 발생시키지 않는다.

따라서 요청된 데이터에 배타적 잠금(X-LOCK)이 설정되어 있더라도 대기하지 않는다.

읽기 작업에서는 SERIALIZABLE 이나 READ COMMIITTED 와 동일한 수준의 일관성 수준을 얻을 수 있다.

 

 

 

1) SNAPSHOT

SNAPSHOT 격리 수준에서 데이터를 읽게 되면, 

 

트랜잭션이 시작된 시점에서 사용할 수 있는 최신의 커밋된 데이터를 사용할 수 있다. 

 

이는 커밋된 읽기와 반복 가능한 읽기를 보장받을 뿐만 아니라, 

 

SERIALIZABLE 격리 수준과 같이 팬텀 읽기 등이 발생하지 않는다는 것도 보장 받을 수 있다.

하지만, 공유 잠금(S-LOCK) 대신 행 버전 관리 기능을 이용한다는 점에서 차이가 있다.

 

 

쿼리창1)

ALTER DATABASE ADMIN SET ALLOW_SNAPSHOT_ISOLATION ON;

BEGIN TRAN  
UPDATE dbo.TBLINSA SET name = N'이순재' WHERE num = 1002 

SELECT * FROM dbo.TBLINSA WHERE num = 1002

 

위처럼 SNAPSHOT  격리수준을 사용하기 위해서

 

현재 데이터베이스를 SNAPSHOT 격리수준이 적용되도록 바꿔줘야 한다.

 

위의 상황은 num = 1002 에 대응하는 name 을 이순신 -> 이순재로 변경하였다.

 

이번예제에서도 쿼리창1에서 트랜잭션을 열어놓은 상태로 데이터 변경을 진행한다.

 

 

쿼리창2)

BEGIN TRAN 

SELECT * FROM dbo.TBLINSA  WHERE num = 1002

쿼리창1의 트랜잭션이 READ COMMITTED 격리 수준 내에서 수행되고 있더라도, 

 

SQL Server는 없데이트 하기 전의 행에 대한 복사본을 tempdb에 저장하게 된다. 

 

SNAPSHOT 격리수준이 데이터베이스 수준에서 활성화 되어 있기 때문이다.

따라서, 어떤 쿼리창이라 해도 SNAPSHOT 격리 수준을 사용해서 트랜잭션을 시작한다면, 

 

이 트랜잭션에서는 업데이트 이전 버전에 대한 값을 조회할 수 있게된다.

만약,  격리수준이 SERIALIZABLE 이었다면,  

 

쿼리창 2에서 블로킹이 발생하여 쿼리창 1에서 트랜잭션이 완전히 종료될때까지 기다리게 되지만(블로킹), 

 

이제 쿼리는 SNAPSHOT 격리수준에서 수행되고 있기 때문에 트랜잭션이 시작되는 시점에서 

 

사용 가능한 마지막 커밋된 버전의 행을 얻을 수 있게 된다.

 

 

 

쿼리창1)

COMMIT TRAN

 

이제 위와 같이 COMMIT TRAN을 하게 되면 이제 

 

쿼리창2에서 SELECT 를 날려보면 변경된 값으로 받아올수 있다고 생각하지만 

 

실상은 그렇지 않다 아래의 결과를 확인하자.

 

 

쿼리창2)

SELECT * FROM dbo.TBLINSA  WHERE num = 1002

COMMIT TRAN

자세히 보면 쿼리창2 에서 트랜잭션을 걸어두고 완료하지 않았었으며

 

현재 트랜잭션을 완료해준 것을 확인할 수 있다.

 

 

 

SNAPSHOT 격리 수준은 트랜잭션이 시작한 시점에서 

 

최신의 커밋된 데이터를 가져온다고 하였으므로 

 

쿼리창2에서는 쿼리창 1이 커밋되었든 말던 상관 없이 

 

이미 TEMP DB 에서 마지막 커밋된 값을 가져오게 되는것이다.

그래서 값이 바뀌지 않은것을 볼 수 있다.

 

 

 

 

 

 

2) READ COMMITTED SNAPSHOT

 

READ COMMITTED SNAPSHOT 격리 수준 역시 행 버전 관리를 기반으로 한다. 

 

트랜잭션이 시작되는 시점에 커밋된 최종 행 버전을 사용하는 SNAPSHOT 격리수준과는 달리, 

 

이 격리 수준에서는 읽기 작업의 구문이 시작될 때 커밋된 최종 행버전을 사용한다는 점에서 차이가 있다.  

 

READ COMMITTED 격리 수준과 상당히 유사하지만, 

 

읽기 작업에서 공유잠금(S-LOCK)을 필요로 하지 않고, 

 

리소스가 배타적으로 잠겨 있을 경우 기다리지 않아도 된다는 점에서는 차이가 있다.

 

 

쿼리창1)

ALTER DATABASE ADMIN SET READ_COMMITTED_SNAPSHOT ON;

BEGIN TRAN 
UPDATE dbo.TBLINSA SET name = N'이순재' WHERE num = 1002

SELECT * FROM dbo.TBLINSA WHERE num = 1002

 

 

 

쿼리창2)

BEGIN TRAN

SELECT * FROM dbo.TBLINSA  WHERE num = 1002

 

 

쿼리창1)

COMMIT TRAN

 

 

위와 같은상태로 이번에는 쿼리창2) 에서 SELECT 를 해보자.

 

 

쿼리창2)

SELECT * FROM dbo.TBLINSA  WHERE num = 1002

COMMIT TRAN

 

이제 코드는 READ COMMITTED SNAPSHOT 격리 수준에서 실행되기 때문에, 

 

트랜잭션이 시작할 때의 값 대신 구문이 시작되는 시점에서 사용 가능한  

 

마지막 커밋된 버전의 값을 가져오게 된다.