데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

스냅숏 격리 수준(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

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
» 스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL) jevida(강성욱) 2016.11.23 4386
1972 SQL Server 파라메터 스니핑의 다양한 접근 jevida(강성욱) 2016.11.23 3252
1971 잘못된 개체를 참조하는 SQL Server 개체 찾기 jevida(강성욱) 2016.11.23 1949
1970 전체 백업에서 포함되는 트랜잭션 범위 jevida(강성욱) 2016.11.23 2640
1969 REPL_SCHEMA_ACCESS 대기 유형 jevida(강성욱) 2016.11.23 2176
1968 Database 손상시 Emergency 모드로 복구하기 jevida(강성욱) 2016.11.23 2920
1967 서버 그룹을 이용한 다중서버 쿼리하기 jevida(강성욱) 2016.11.23 2088
1966 SQL Server Agent에서 CmdExec 오류 jevida(강성욱) 2016.11.23 2308
1965 Verbose SQL Server Agent Logging jevida(강성욱) 2016.11.23 1729
1964 Windows Event Log에 SQL Server Agent Log 기록 jevida(강성욱) 2016.11.23 2283
1963 SQL Server Agent Error log 위치 변경 jevida(강성욱) 2016.11.23 2540
1962 SQL Server에서 차단을 확인하는 다양한 방법 jevida(강성욱) 2016.10.18 4606
1961 SQL Server CPU 사용률이 높은 프로세서 및 쿼리 찾기 jevida(강성욱) 2016.10.18 7147
1960 Collation 변경 jevida(강성욱) 2016.10.18 4584
1959 인덱스 리빌드 동작 (Gather Streams from SORT) jevida(강성욱) 2016.10.18 3473
1958 최소한의 다운타임으로 데이터베이스 이동하기 jevida(강성욱) 2016.10.18 3134
1957 백업 LSN 이해하기 jevida(강성욱) 2016.10.18 3425
1956 암호화 오버헤드 (작성자의 주관적인 자료임) jevida(강성욱) 2016.10.18 2852
1955 데이터베이스 연결 정보 수집 jevida(강성욱) 2016.10.18 3188
1954 FileStream Garbage Collection jevida(강성욱) 2016.10.18 3447





XE Login