행 버전 관리 기반 격리 수준 이해
SQL Server에서 행 버전 관리는 다음을 수행하기 위해 사용되는 일반적인 방법입니다.
트리거에 inserted 및 deleted 테이블 작성. 트리거에 의해 수정된 모든 행의 버전이 지정됩니다. 여기에는 트리거를 실행한 문에 의해 수정된 행과 트리거에 의해 수정된 모든 데이터가 포함됩니다.
MARS(Multiple Active Result Sets) 지원. 활성 결과 집합이 있을 때 MARS 세션에서 INSERT, UPDATE 또는 DELETE와 같은 데이터 수정 문을 실행하면 이 수정 문의 영향을 받는 행의 버전이 지정됩니다.
ONLINE 옵션을 지정하는 인덱스 작업 지원
행 버전 관리 기반 트랜잭션 격리 수준 지원
행 버전 관리를 사용하여 문 수준의 읽기 일관성을 유지하는 새로운 커밋된 읽기 격리 수준 구현
트랜잭션 수준의 읽기 일관성을 유지하는 새로운 스냅숏 격리 수준
tempdb 데이터베이스에는 버전 저장소로 사용할 공간이 충분해야 합니다. tempdb가 꽉 차면 업데이트 작업이 버전 생성을 중단하고 계속 진행되지만 필요한 특정 행 버전이 더 이상 존재하지 않으므로 읽기 작업이 실패할 수 있습니다. 이것은 트리거, MARS 및 온라인 인덱싱 등의 작업에 영향을 줍니다. 자세한 내용은 행 버전 관리 리소스 사용를 참조하십시오.
커밋된 읽기 및 스냅숏 트랜잭션에 행 버전 관리를 사용하는 과정은 다음 두 단계로 이루어집니다.
READ_COMMITTED_SNAPSHOT 및 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션 중 하나 또는 모두를 ON으로 설정합니다.
응용 프로그램에서 적절한 트랜잭션 격리 수준을 설정합니다.
READ_COMMITTED_SNAPSHOT 데이터베이스 옵션을 ON으로 설정하면 커밋된 읽기 격리 수준을 설정하는 트랜잭션에 행 버전 관리가 사용됩니다.
ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정하면 트랜잭션에서 스냅숏 격리 수준을 설정할 수 있습니다.
READ_COMMITTED_SNAPSHOT 또는 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정하면 SQL Server 데이터베이스 엔진에서 행 버전 관리를 사용하여 데이터를 조작하는 각 트랜잭션에 XSN(트랜잭션 시퀀스 번호)을 할당합니다. 트랜잭션은 BEGIN TRANSACTION 문이 실행될 때 시작합니다. 그러나 트랜잭션 시퀀스 번호는 BEGIN TRANSACTION 문 이후 첫 번째 읽기 또는 쓰기 작업이 실행될 때 시작합니다. 트랜잭션 시퀀스 번호는 할당될 때마다 1씩 증가합니다.
READ_COMMITTED_SNAPSHOT 또는 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정하면 데이터베이스에서 수행된 모든 데이터 수정 내용에 대한 논리적 복사본(버전)이 유지됩니다. 특정 트랜잭션에 의해 행이 수정될 때마다 데이터베이스 엔진 인스턴스는 행의 이전에 커밋된 이미지 버전을 tempdb에 저장합니다. 각 버전은 행을 변경한 트랜잭션의 트랜잭션 시퀀스 번호로 표시됩니다. 수정된 행의 여러 버전은 연결 목록을 통해 체인으로 연결됩니다. 최신 행 값은 항상 현재 데이터베이스에 저장되고 tempdb에 저장된 버전 지정된 행과 체인으로 연결되어 있습니다.
[!참고]
LOB(Large Object) 수정 내용의 경우 변경된 조각만 tempdb의 버전 저장소에 복사됩니다.
행 버전은 행 버전 관리 기반 격리 수준으로 실행되는 트랜잭션의 요구 사항을 만족할 때까지 유지됩니다. 데이터베이스 엔진은 가장 오래된 유용한 트랜잭션 시퀀스 번호를 추적하여 해당 번호보다 낮은 트랜잭션 시퀀스 번호로 표시된 모든 행 버전을 주기적으로 삭제합니다.
두 가지 데이터베이스 옵션을 모두 OFF로 설정하면 트리거 또는 MARS 세션에 의해 수정되었거나 ONLINE 인덱스 작업에서 읽은 행에만 버전이 지정됩니다. 이러한 행 버전은 더 이상 필요하지 않을 경우 해제됩니다. 백그라운드 스레드가 주기적으로 실행되어 오래되어 필요 없는 행 버전을 제거합니다.
[!참고]
짧게 실행되는 트랜잭션의 경우 수정된 행의 버전이 tempdb 데이터베이스의 디스크 파일에 작성되지 않고 버퍼 풀에 캐시될 수 있습니다. 버전이 지정된 행이 일시적으로 필요한 경우에는 버퍼 풀에서 삭제되며 이것이 반드시 I/O 오버헤드를 유발하는 것은 아닐 수도 있습니다.
데이터를 읽는 경우의 동작
행 버전 관리 기반 격리 데이터 읽기 수준으로 트랜잭션이 실행되는 경우에는 읽기 작업에서 읽고 있는 데이터에 대한 공유(S) 잠금을 획득하지 못하므로 데이터를 수정하는 트랜잭션을 차단하지 못합니다. 또한 획득한 잠금 수가 감소함에 따라 리소스 잠금으로 인한 오버헤드가 최소화됩니다. 행 버전 관리를 사용하는 커밋된 읽기 격리 및 스냅숏 격리는 버전이 지정된 데이터에 대해 문 수준 또는 트랜잭션 수준의 읽기 일관성을 유지하도록 디자인되었습니다.
행 버전 관리 기반 격리 수준에서 실행되는 트랜잭션을 포함하여 모든 쿼리는 컴파일 및 실행 중에 Sch-S(스키마 안정성) 잠금을 획득합니다. 이 때문에 동시 트랜잭션이 테이블에 대해 Sch-M(스키마 수정) 잠금을 유지하면 쿼리가 차단됩니다. 예를 들어 DDL(데이터 정의 언어) 작업은 테이블의 스키마 정보를 수정하기 전에 Sch-M 잠금을 획득합니다. 행 버전 관리 기반 격리 수준에서 실행되는 쿼리 트랜잭션을 포함하여 Sch-S 잠금을 획득하려고 시도하는 쿼리 트랜잭션은 차단됩니다. 반대로 Sch-S 잠금을 유지하는 쿼리는 Sch-M 잠금을 획득하려고 시도하는 동시 트랜잭션을 차단합니다. 잠금 동작에 대한 자세한 내용은 잠금 호환성(데이터베이스 엔진)을 참조하십시오.
스냅숏 격리 수준을 사용하는 트랜잭션이 시작되면 데이터베이스 엔진 인스턴스에서 현재 활성화된 모든 트랜잭션을 기록합니다. 스냅숏 트랜잭션에서 버전 체인이 있는 행을 읽으면 데이터베이스 엔진이 체인을 추적하여 다음과 같은 트랜잭션 시퀀스 번호가 있는 행을 검색합니다.
행을 읽는 스냅숏 트랜잭션의 시퀀스 번호보다 낮으면서 가장 근사한 번호
스냅숏 트랜잭션이 시작되었을 때 활성화된 트랜잭션의 목록에 없는 번호
스냅숏 트랜잭션에 따라 수행된 읽기 작업에서는 스냅숏 트랜잭션이 시작되었을 때 커밋된 각 행의 마지막 버전을 검색합니다. 따라서 트랜잭션의 시작 부분에 데이터가 위치하게 되므로 데이터의 스냅숏 트랜잭션이 일관되게 유지됩니다.
행 버전 관리가 사용된 커밋된 읽기 트랜잭션도 이와 비슷한 방식으로 작동합니다. 다만 커밋된 읽기 트랜잭션에서는 행 버전을 선택할 때 고유한 트랜잭션 시퀀스 번호가 사용되지 않는다는 점이 다릅니다. 문이 시작될 때마다 커밋된 읽기 트랜잭션에서는 데이터베이스 엔진 인스턴스에 대해 생성된 가장 최근의 트랜잭션 시퀀스 번호를 읽습니다. 이 트랜잭션 시퀀스 번호는 해당 문에 대해 올바른 행 버전을 선택하는 데 사용됩니다. 이러한 방법으로 커밋된 읽기 트랜잭션에서 각 문의 시작 부분에 있는 데이터의 스냅숏을 확인할 수 있습니다.
[!참고]
행 버전 관리를 사용하는 커밋된 읽기 트랜잭션은 문 수준에서 트랜잭션이 일관된 데이터 뷰를 제공하지만 이 유형의 트랜잭션에서 생성하거나 액세스한 행 버전은 해당 트랜잭션이 완료될 때까지 유지됩니다.
데이터를 수정하는 경우의 동작
행 버전 관리가 사용되는 커밋된 읽기 트랜잭션에서는 데이터 값을 읽을 때 데이터 행에 업데이트(U) 잠금이 적용되는 차단 검색을 사용하여 업데이트할 행을 선택합니다. 이것은 행 버전 관리를 사용하지 않는 커밋된 읽기 트랜잭션과 동일합니다. 데이터 행이 업데이트 조건에 맞지 않으면 해당 행의 업데이트 잠금이 해제되고 그 다음 행이 잠겨 검색됩니다.
스냅숏 격리 수준으로 실행되는 트랜잭션은 제약 조건을 적용하기 위해 수정 내용을 수행하기 전에 데이터에 대한 잠금을 획득하는 낙관적 데이터 수정 방법을 사용합니다. 그렇지 않으면 데이터가 수정될 때까지 데이터에 대한 잠금도 획득되지 않습니다. 스냅숏 트랜잭션은 데이터 행이 업데이트 조건에 맞으면 이 스냅숏 트랜잭션이 시작된 후 커밋된 동시 트랜잭션에 의해 해당 데이터 행이 수정되지 않았는지 확인합니다. 스냅숏 트랜잭션이 아닌 다른 트랜잭션에 의해 데이터 행이 수정된 경우에는 업데이트 충돌이 발생하고 스냅숏 트랜잭션이 종료됩니다. 데이터베이스 엔진은 업데이트 충돌을 처리합니다. 업데이트 충돌 검색 기능은 해제할 수 없습니다.
[!참고]
내부적으로 스냅숏 격리 수준으로 실행되는 업데이트 작업은 스냅숏 트랜잭션이 다음 항목에 액세스할 때는 커밋된 읽기 격리 수준으로 실행됩니다.
FOREIGN KEY 제약 조건이 있는 테이블
다른 테이블의 FOREIGN KEY 제약 조건에서 참조되는 테이블
둘 이상의 테이블을 참조하는 인덱싱된 뷰
그러나 이러한 조건에서도 업데이트 작업은 다른 트랜잭션에 의해 데이터가 수정되지 않았는지 확인합니다. 다른 트랜잭션에 의해 데이터가 수정된 경우 스냅숏 트랜잭션에서 업데이트 충돌이 발생하고 종료됩니다.
동작 요약
다음 표에서는 행 버전 관리를 사용하는 스냅숏 격리와 커밋된 읽기 격리의 차이점을 요약합니다.
속성 |
행 버전 관리를 사용하는 커밋된 읽기 격리 수준 |
스냅숏 격리 수준 |
|---|---|---|
지원 요구 사항에 따라 ON으로 설정되어야 하는 데이터베이스 옵션 |
READ_COMMITTED_SNAPSHOT |
ALLOW_SNAPSHOT_ISOLATION |
세션에서 특정한 유형의 행 버전 관리를 요청하는 방법 |
기본 커밋된 읽기 격리 수준을 사용하거나 SET TRANSACTION ISOLATION LEVEL 문을 실행하여 READ COMMITTED 격리 수준을 지정합니다. 이 작업은 트랜잭션이 시작된 후에 수행할 수 있습니다. |
트랜잭션이 시작되기 전에 SET TRANSACTION ISOLATION LEVEL 문을 실행하여 SNAPSHOT 격리 수준을 지정해야 합니다. |
문에서 읽는 데이터의 버전 |
각 문이 시작되기 전에 커밋된 모든 데이터 |
각 트랜잭션이 시작되기 전에 커밋된 모든 데이터 |
업데이트 처리 방법 |
행 버전을 실제 데이터로 변환하여 업데이트할 행을 선택하고 선택한 데이터 행에 업데이트 잠금을 사용합니다. 수정할 실제 데이터 행에 대해 배타적 잠금을 획득합니다. 업데이트 충돌 검색은 사용되지 않습니다. |
행 버전을 사용하여 업데이트할 행을 선택합니다. 수정할 실제 데이터 행에 대해 배타적 잠금을 획득하려고 시도합니다. 데이터가 다른 트랜잭션에 의해 이미 수정된 경우에는 업데이트 충돌이 발생하며 스냅숏 트랜잭션이 종료됩니다. |
업데이트 충돌 검색 |
없음 |
통합 지원되며 해제할 수 없습니다. |