스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL)

스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL)

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

트랜잭션 격리 수준은 SQL Server에 연결하여 실행하는 T-SQL문의 잠금 및 행 버전 관리 기능을 제공한다. 격리 수준은 여러 종류가 있으며 격리 수준 기능은 다음과 같다.

 

SNAPSHOT 격리 수준은 트랜잭션에서 읽은 데이터가 다른 트랜잭션으로부터 일관성이 유지되도록 한다. 트랜잭션은 시작되기 전에 커밋된 데이터 내용만 인식할 수 있다. 현재 트랜잭션이 시작된 후 다른 트랜잭션에서 수정한 데이터는 현재 트랜잭션에서 실행되는 결과에 표시되지 않는다. 따라서 트랜잭션의 결과는 트랜잭션 시작 당시 커밋된 데이터의 스냅숏을 가져오는 것처럼 보인다.

 

데이터베이스가 복구 중인 경우를 제외하면 SNAPSHOT 트랜잭션은 데이터를 읽는 동안 잠금을 요청하지 않는다. 데이터를 읽는 SNAPSHOT 트랜잭션은 다른 트랜잭션의 데이터 쓰기를 차단하지 않으며 데이터를 쓰는 트랜잭션은 SNAPSHOT 트랜잭션의 데이터 읽기를 차단하지 않는다.

 

데이터베이스 복구를 롤백하는 동안 SNAPSOT 트랜잭션은 롤백 중인 다른 트랜잭션이 잠근 데이터를 읽으려는 시도가 있을 경우 잠금을 요청한다. SNAPSHOT 트랜잭션은 해당 트랜잭션이 롤백될 때까지 차단된다. 잠금은 부여된 후 바로 해제 된다.

 

[스큐에 쓰기]

스냅숏 격리는 스큐 쓰기에 대해 취약하다. 스큐는 두 개의 동시 트랜잭션이 서로 다른 트랜잭션에서 서로 다른 데이터를 수정하기 때문에 쓰기 충돌이 발생하지 않는다.

Session 1	Session 2
CREATE TABLE A (x integer NOT NULL); CREATE TABLE B (x integer NOT NULL);
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION; INSERT A (x) SELECT COUNT_BIG(*) FROM B;
	SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION; INSERT B (x) SELECT COUNT_BIG(*) FROM A; COMMIT TRANSACTION;
COMMIT TRANSACTION;
select * from a	select * from b

 

 

결과를 살펴보면 스냅숏 격리에서 해당 스크립트에 있는 테이블(A, B)의 값은 0 값을 포함하는 하나의 행을 반환한다. 이것은 정확한 결과이지만 serializable 결과는 아니다. serializable 트랜잭션에서는 하나의 트랜잭션은 다른 트랜잭션이 시작되기 전에 완료되어야 한다. 그래서 두 번 째 트랜잭션은 첫 번째로 삽입된 행을 계산해야 한다. 강력한 serializable를 보증하기 위해서는 트랜잭션 레벨을 serializable로 적용 해야한다.

 

 

[충돌 감지 요소]

스냅숏 트랜잭션이 시작된 후 다른 트랜잭션에서 수정하려고 할 때 스냅숏 쓰기 충돌이 발생 한다.

Session 1	Session 2
-- Test table CREATE TABLE dbo.Conflict ( ID1 integer UNIQUE, Value1 integer NOT NULL, ID2 integer UNIQUE, Value2 integer NOT NULL );   -- Insert one row INSERT dbo.Conflict (ID1, ID2, Value1, Value2) VALUES (1, 1, 1, 1);
BEGIN TRANSACTION;   UPDATE dbo.Conflict SET Value1 = 1 WHERE ID1 = 1;
	SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION;   UPDATE dbo.Conflict SET Value2 = 1 WHERE ID2 = 1;
COMMIT TRANSACTION;

 

 

메시지 3960, 수준 16, 상태 2, 줄 5 업데이트 충돌로 인해 스냅숏 격리 트랜잭션이 중단되었습니다. 스냅숏 격리를 사용하여 데이터베이스 'SW_TEST'의 테이블 'dbo.Conflict'에 직접 또는 간접적으로 액세스하여 다른 트랜잭션에 의해 수정되거나 삭제된 행을 업데이트, 삭제 또는 삽입할 수 없습니다. 트랜잭션을 다시 시도하거나 UPDATE/DELETE 문에 대한 격리 수준을 변경하십시오.

 

 

[외래키 문제]

충돌 감지는 외래키 관계에 있는 부모행에 적용 된다. 스냅숏 격리에서 자식행을 수정하는 경우 다른 트랜잭션의 부모행에 대한 변경은 충돌을 일으킬 수 있다. 실행계획에서 자동으로 외래키 검사를 요구하는 자식 테이블에 대한 모든 작업은 예상치 못한 충돌이 발생 할 수 있다.

CREATE TABLE dbo.Dummy ( x integer NULL );   CREATE TABLE dbo.Parent ( ParentID integer PRIMARY KEY, ParentValue integer NOT NULL );   CREATE TABLE dbo.Child ( ChildID integer PRIMARY KEY, ChildValue integer NOT NULL, ParentID integer NULL FOREIGN KEY REFERENCES dbo.Parent );   INSERT dbo.Parent (ParentID, ParentValue) VALUES (1, 1);   INSERT dbo.Child (ChildID, ChildValue, ParentID) VALUES (1, 1, 1);

 

Session 1	Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION; SELECT COUNT_BIG(*) FROM dbo.Dummy;
	UPDATE dbo.Parent SET ParentValue = 1 WHERE ParentID = 1;
UPDATE dbo.Child SET ParentID = NULL WHERE ChildID = 1; UPDATE dbo.Child SET ParentID = 1 WHERE ChildID = 1;

 

 

(1개 행이 영향을 받음) 메시지 3960, 수준 16, 상태 2, 줄 10 업데이트 충돌로 인해 스냅숏 격리 트랜잭션이 중단되었습니다. 스냅숏 격리를 사용하여 데이터베이스 'SW_TEST'의 테이블 'dbo.Parent'에 직접 또는 간접적으로 액세스하여 다른 트랜잭션에 의해 수정되거나 삭제된 행을 업데이트, 삭제 또는 삽입할 수 없습니다. 트랜잭션을 다시 시도하거나 UPDATE/DELETE 문에 대한 격리 수준을 변경하십시오.

 

 

[truncate table issue]

트랜잭션이 시작된 이후 액세스하는 테이블이 truncate 된 경우 스냅숏 트랜잭션은 오류와 함께 실패 한다.

Session 1	Session 2
CREATE TABLE dbo.AccessMe ( x integer NULL );   CREATE TABLE dbo.TruncateMe ( x integer NULL );
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION; SELECT COUNT_BIG(*) FROM dbo.AccessMe
	TRUNCATE TABLE dbo.TruncateMe;
SELECT COUNT_BIG(*) FROM dbo.TruncateMe;

 

 

메시지 3961, 수준 16, 상태 1, 줄 8 문에서 액세스한 개체가 이 트랜잭션이 시작된 후 다른 동시 트랜잭션의 DDL 문에 의해 수정되어 데이터베이스 'SW_TEST'에서 스냅숏 격리 트랜잭션이 실패했습니다. 메타데이터에 버전이 지정되지 않았으므로 이 트랜잭션은 허용되지 않습니다. 스냅숏 격리를 함께 사용하여 메타데이터에 대해 동시 업데이트를 수행하면 일관되지 않은 결과가 발생할 수 있습니다.

 

 

 

[참고자료]

• SET TRANSACTION ISOLATION LEVEL :

http://msdn.microsoft.com/ko-kr/library/ms173763.aspx

• SNAPSHOT Isolation Level :

http://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level

 

 

2014-07-08 / 강성욱 / http://sqlmvp.kr

 

 

트랜잭션 격리 수준, 스냅숏 격리 수준, ISOLATION LEVEL, SQL Server, MSSQL, 잠금, 트랜잭션관리, 쿼리튜닝