데이터베이스 엔진의 격리 수준
한 트랜잭션을 리소스 또는 다른 트랜잭션에서 수정한 데이터 내용으로부터 격리하는 정도를 정의하는 격리 수준을 트랜잭션에 지정할 수 있습니다. 격리 수준은 허용되는 동시성 부작용(예: 커밋되지 않은 읽기 또는 가상 읽기)의 관점에서 설명됩니다.
트랜잭션 격리 수준으로 제어할 수 있는 사항은 다음과 같습니다.
데이터를 읽을 때 잠금을 확보할지 여부 및 요청되는 잠금의 종류
읽기 잠금의 보유 기간
읽기 작업이 다른 트랜잭션에서 수정한 행을 참조할 경우 선택할 수 있는 다음과 같은 옵션
행에 대한 배타적 잠금이 해제될 때까지 차단
문 또는 트랜잭션 시작 당시 커밋된 행 버전 검색
커밋되지 않은 데이터 수정 내용 읽기
트랜잭션 격리 수준을 선택해도 데이터 수정 내용을 보호하기 위해 획득된 잠금에는 영향을 주지 않습니다. 설정된 격리 수준에 관계없이 트랜잭션은 항상 수정하는 데이터에 대해 배타적 잠금을 얻고 해당 트랜잭션이 완료될 때까지 이 잠금을 보유합니다. 읽기 작업의 경우 트랜잭션 격리 수준은 대개 다른 트랜잭션에서 수정한 내용의 영향을 받지 않도록 보호 수준을 정의합니다.
격리 수준이 낮을수록 동시에 데이터를 액세스할 수 있는 사용자가 많아지지만 동시성 부작용(예: 커밋되지 않은 읽기 또는 업데이트 손실) 횟수도 늘어납니다. 반대로 격리 수준이 높을수록 동시성 부작용 종류가 줄어들지만 시스템 리소스가 더 많이 필요하게 되고 한 트랜잭션이 다른 트랜잭션을 차단하게 될 확률도 높아집니다. 적절한 격리 수준을 선택하려면 응용 프로그램의 데이터 무결성 요구 사항과 각 격리 수준에 의해 야기되는 오버헤드를 신중하게 평가해야 합니다. 최상위 격리 수준인 직렬화 가능의 경우 트랜잭션이 읽기 작업을 반복할 때마다 정확히 동일한 데이터를 검색하지만 다중 사용자 시스템에서 다른 사용자에게 영향을 줄 수 있는 수준의 잠금을 수행함으로써 이를 달성합니다. 최하위 격리 수준인 커밋되지 않은 읽기의 경우 다른 트랜잭션에서 수정했지만 커밋되지 않은 데이터를 검색할 수 있습니다. 커밋되지 않은 읽기에서는 모든 동시성 부작용이 발생할 수 있지만 읽기 잠금이나 버전 관리가 수행되지 않으므로 오버헤드가 최소화됩니다.
데이터베이스 엔진 격리 수준
ISO 표준은 다음 격리 수준을 정의합니다. 이 격리 수준은 모두 SQL Server 데이터베이스 엔진에서 지원됩니다.
커밋되지 않은 읽기(물리적으로 손상된 데이터만 읽지 않도록 트랜잭션을 격리하는 최하위 수준)
커밋된 읽기(데이터베이스 엔진의 기본 수준)
반복 읽기
직렬화 가능(트랜잭션이 서로 완전히 격리되는 최상위 수준)
.gif "중요 정보") 중요 |
|---|
직렬화 가능 격리 수준이 요청된 경우 복제된 테이블에 대한 DDL 작업 및 트랜잭션이 실패할 수 있는데 이는 복제 쿼리가 직렬화 가능 격리 수준과 호환되지 않을 수 있는 힌트를 사용하기 때문입니다. |
SQL Server에서는 행 버전 관리를 사용하는 두 트랜잭션 격리 수준을 지원합니다. 하나는 커밋된 읽기 격리를 새롭게 구현한 것이고 다른 하나는 새로 추가된 트랜잭션 격리 수준인 스냅숏입니다.
READ_COMMITTED_SNAPSHOT 데이터베이스 옵션을 ON으로 설정하면 커밋된 읽기 격리가 행 버전 관리를 통해 문 수준의 읽기 일관성을 제공합니다. 읽기 작업에 SCH-S 테이블 수준 잠금만 필요하고 페이지 또는 행 잠금은 필요하지 않습니다. READ_COMMITTED_SNAPSHOT 데이터베이스 옵션을 기본값인 OFF로 설정하면 커밋된 읽기 격리가 이전 버전의 SQL Server에서처럼 작동합니다. 두 구현 모두 커밋된 읽기 격리에 대한 ANSI 정의를 충족합니다.
스냅숏 격리 수준은 행 버전 관리를 통해 트랜잭션 수준의 읽기 일관성을 제공합니다. 읽기 작업에 SCH-S 테이블 잠금만 필요하고 페이지 또는 행 잠금은 필요하지 않습니다. 다른 트랜잭션에서 수정한 행을 읽을 때 트랜잭션 시작 당시의 행 버전을 검색합니다. ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정하면 데이터베이스에 대해 스냅숏 격리만 사용할 수 있습니다. 기본적으로 사용자 데이터베이스에 대해서는 이 옵션이 OFF로 설정되어 있습니다.
[!참고]
SQL Server에서는 메타데이터의 버전 관리를 지원하지 않습니다. 따라서 스냅숏 격리에서 실행하는 명시적 트랜잭션에서 수행할 수 있는 DDL 작업에 대한 제한 사항이 있습니다. ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME 또는 CLR(공용 언어 런타임) DDL 문과 같은 DDL 문은 BEGIN TRANSACTION 문 다음에 스냅숏 격리에서 허용되지 않습니다. 이러한 문은 암시적 트랜잭션 내에서 스냅숏 격리를 사용할 때 허용됩니다. 기본적으로 암시적 트랜잭션은 DDL 문에서도 스냅숏 격리의 의미 체계를 적용할 수 있게 하는 단일 문입니다. 이 원칙을 위반하면 오류 3961이 발생할 수 있습니다. 오류 3961: "문에서 액세스한 개체가 이 트랜잭션이 시작된 후 다른 동시 트랜잭션의 DDL 문에 의해 수정되어 데이터베이스 '%.*ls'에서 스냅숏 격리 트랜잭션이 실패했습니다. 메타데이터에 버전이 지정되지 않았으므로 이 트랜잭션은 허용되지 않습니다. 스냅숏 격리를 함께 사용하여 메타데이터에 대해 동시 업데이트를 수행하면 일관되지 않은 결과가 발생할 수 있습니다."
다음 표에서는 각 격리 수준에서 사용되는 동시성 부작용을 보여 줍니다.
격리 수준 |
커밋되지 않은 읽기 |
반복되지 않는 읽기 |
가상 |
|---|---|---|---|
커밋되지 않은 읽기 |
예 |
예 |
예 |
커밋된 읽기 |
아니요 |
예 |
예 |
반복 읽기 |
아니요 |
아니요 |
예 |
스냅숏 |
아니요 |
아니요 |
아니요 |
직렬화 가능 |
아니요 |
아니요 |
아니요 |
각 트랜잭션 격리 수준에서 제어하는 특정 종류의 잠금 또는 행 버전 관리에 대한 자세한 내용은 SET TRANSACTION ISOLATION LEVEL(Transact-SQL)을 참조하십시오.