데이터베이스 개발자 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
1990 클러스터된 SQL 서버 인스턴스에 대한 호스트 이름 확인 jevida(강성욱) 2017.01.11 1366
1989 sys.dm_tran_locks 를 이용한 잠금 정보 확인 jevida(강성욱) 2017.01.11 1525
1988 외래키 제약 조건 삭제 후 재작성 스크립트 생성하기 jevida(강성욱) 2017.01.11 1780
1987 페이지 ID로 테이블 이름 찾기 jevida(강성욱) 2017.01.11 1434
1986 DBCC CHECKPRIMARYFILE 사용법 jevida(강성욱) 2017.01.11 1326
1985 컬럼스토어 인덱스 대용량 데이터 로드 jevida(강성욱) 2017.01.11 1743
1984 컬럼스토어 인덱스 INSERT 작업과 동시성 jevida(강성욱) 2017.01.11 1491
1983 컬럼스토어 인덱스 동시성 jevida(강성욱) 2017.01.11 1780
1982 컬럼스토어 인덱스 ROW와 ROWGROUP 영향 jevida(강성욱) 2016.11.23 2943
1981 테이블 변수와 TF 2453 jevida(강성욱) 2016.11.23 3184
1980 Sp_trace_create MaxfileSize 오류 jevida(강성욱) 2016.11.23 2393
1979 RANDBETWEEN 함수 만들기 jevida(강성욱) 2016.11.23 4589
1978 Optimize for hint 쿼리 최적화 jevida(강성욱) 2016.11.23 3509
1977 TempDB 파일 사이즈 증가 시 경고 받기 jevida(강성욱) 2016.11.23 3044
1976 블록킹 세션을 찾아 우선순위 낮은 세션 종료하기 jevida(강성욱) 2016.11.23 3245
1975 다양한 포맷의 이름 파싱 하기 jevida(강성욱) 2016.11.23 2662
1974 비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리 jevida(강성욱) 2016.11.23 3148
» 스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL) jevida(강성욱) 2016.11.23 4265
1972 SQL Server 파라메터 스니핑의 다양한 접근 jevida(강성욱) 2016.11.23 3125
1971 잘못된 개체를 참조하는 SQL Server 개체 찾기 jevida(강성욱) 2016.11.23 1940





XE Login