SET TRANSACTION ISOLATION LEVEL(Transact-SQL)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics분석 플랫폼 시스템(PDW)Microsoft Fabric 미리 보기의 SQL 데이터베이스

SQL Server에 연결하여 실행되는 Transact-SQL 문의 잠금 및 행 버전 관리 동작을 제어합니다.

Transact-SQL 구문 표기 규칙

Syntax

Microsoft Fabric Preview의 SQL Server, Azure SQL Database 및 SQL 데이터베이스에 대한 구문입니다.

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Azure Synapse Analytics 및 병렬 데이터 웨어하우스에 대한 구문입니다.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

인수

커밋되지 않은 읽기

문이 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 행을 읽을 수 있도록 지정합니다.

수준에서 실행되는 READ UNCOMMITTED 트랜잭션은 다른 트랜잭션이 현재 트랜잭션에서 읽은 데이터를 수정하지 못하도록 공유 잠금을 발급하지 않습니다. READ UNCOMMITTED 트랜잭션은 현재 트랜잭션이 수정되었지만 다른 트랜잭션에 의해 커밋되지 않은 행을 읽지 못하게 하는 배타적 잠금에 의해 차단되지 않습니다. 이 옵션을 설정하면 커밋되지 않은 수정 내용을 읽을 수 있으며, 이 수정 내용을 더티 읽기라고 합니다. 트랜잭션이 종료되기 전에 데이터의 값을 변경하고 데이터 집합에 행을 표시하거나 표시하지 않을 수 있습니다. 이 옵션은 트랜잭션의 모든 문에 있는 모든 SELECT 테이블에 대해 설정하는 NOLOCK 것과 동일한 효과를 줍니다. 또한 격리 수준 중에서 제한이 가장 적습니다.

SQL Server에서는 다음 중 하나를 사용하여 트랜잭션에서 커밋되지 않은 데이터 수정 내용에 대해 더티 읽기를 수행할 수 없도록 하여 잠금 경합을 최소화할 수도 있습니다.

• READ COMMITTED 데이터베이스 옵션이 .로 READ_COMMITTED_SNAPSHOT 설정된 ON격리 수준입니다.

• SNAPSHOT 격리 수준입니다. 스냅샷 격리에 대한 자세한 내용은 SQL Server에서의 스냅샷 격리를 참조하세요.

커밋된 읽기

문이 수정되었지만 다른 트랜잭션에서 커밋되지 않은 데이터를 읽을 수 없게 지정합니다. 이렇게 하면 더티 읽기를 방지할 수 있습니다. 현재 트랜잭션 내에 있는 개별 문 간에 다른 트랜잭션에서 데이터를 변경하면 반복할 수 없는 읽기가 발생하거나 가상 데이터가 될 수 있습니다. 이 옵션은 SQL Server 기본값입니다.

동작 READ COMMITTED 은 데이터베이스 옵션의 설정에 READ_COMMITTED_SNAPSHOT 따라 달라집니다.

• SQL Server의 기본값으로 OFF 설정된 경우 READ_COMMITTED_SNAPSHOT 데이터베이스 엔진은 공유 잠금을 사용하여 현재 트랜잭션이 읽기 작업을 실행하는 동안 다른 트랜잭션이 행을 수정하지 못하도록 합니다. 또한 공유 잠금은 다른 트랜잭션이 완료될 때까지 해당 트랜잭션이 수정한 행을 문이 읽을 수 없도록 합니다. 공유 잠금 유형은 해제 시기를 결정합니다. 행 잠금은 다음 행이 처리되기 전에 해제되고, 페이지 잠금은 다음 페이지를 읽을 때 해제되고 테이블 잠금은 명령문이 끝나면 해제됩니다.

• 설정된 ON경우 READ_COMMITTED_SNAPSHOT 데이터베이스 엔진은 행 버전 관리를 사용하여 각 문에 문의 시작 부분에 존재했던 데이터의 트랜잭션 일치 스냅샷을 제공합니다. 잠금은 다른 트랜잭션의 업데이트로부터 데이터를 보호하는 데 사용되지 않습니다.

  • READ_COMMITTED_SNAPSHOT ON 는 Microsoft Fabric Preview의 Azure SQL Database 및 SQL 데이터베이스의 기본값입니다.

스냅샷 격리는 FILESTREAM 데이터를 지원합니다. 스냅샷 격리 모드에서 트랜잭션의 문에서 읽은 FILESTREAM 데이터는 트랜잭션 시작 시 존재했던 데이터의 트랜잭션 일치 버전입니다.

READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 있는 경우 ON테이블 힌트를 사용하여 READCOMMITTEDLOCK 격리 수준에서 실행되는 트랜잭션의 개별 문에 대한 행 버전 관리 대신 공유 잠금을 READ COMMITTED 요청할 수 있습니다.

반복 가능한 읽기

문은 수정되었지만 다른 트랜잭션에서 아직 커밋되지 않은 데이터를 읽을 수 없으며, 현재 트랜잭션이 완료될 때까지 다른 트랜잭션이 현재 트랜잭션에서 읽은 데이터를 수정할 수 없게 지정합니다.

공유 잠금은 트랜잭션의 각 문이 읽은 모든 데이터에 적용되며 트랜잭션이 완료될 때까지 유지됩니다. 이렇게 하면 다른 트랜잭션이 현재 트랜잭션에서 읽은 행을 수정할 수 없습니다. 다른 트랜잭션은 현재 트랜잭션이 실행한 문의 검색 조건과 일치하는 새 행을 삽입할 수 있습니다. 현재 트랜잭션이 문을 다시 시도하면 새 행이 검색되어 가상 읽기가 발생합니다. 공유 잠금은 각 문의 끝에서 해제되는 대신 트랜잭션의 끝에 유지되므로 동시성은 기본 READ COMMITTED 격리 수준보다 낮습니다. 이 옵션은 필요한 경우에만 사용하세요.

SNAPSHOT

트랜잭션의 문에서 읽은 데이터가 트랜잭션의 시작 부분에 존재했던 데이터의 트랜잭션 일치 버전이 되도록 지정합니다. 트랜잭션은 시작되기 전에 커밋된 데이터 수정 내용만 인식할 수 있습니다. 현재 트랜잭션이 시작된 후 다른 트랜잭션에서 수정한 데이터는 현재 트랜잭션에서 실행되는 문에 표시되지 않습니다. 따라서 트랜잭션의 문이 트랜잭션 시작 당시 커밋된 데이터의 스냅샷을 가져오는 것처럼 보입니다.

데이터베이스가 복구 SNAPSHOT 되는 경우를 제외하고 트랜잭션은 데이터를 읽을 때 잠금을 요청하지 않습니다. SNAPSHOT 데이터를 읽는 트랜잭션은 다른 트랜잭션이 데이터를 쓰는 것을 차단하지 않습니다. 데이터를 쓰는 트랜잭션은 트랜잭션이 데이터를 읽는 것을 차단 SNAPSHOT 하지 않습니다.

데이터베이스 복구 SNAPSHOT 의 롤백 단계에서는 롤백 중인 다른 트랜잭션에 의해 잠긴 데이터를 읽으려고 하면 트랜잭션이 잠금을 요청합니다. SNAPSHOT 트랜잭션이 롤백될 때까지 트랜잭션이 차단됩니다. 잠금이 부여된 직후 해제됩니다.

ALLOW_SNAPSHOT_ISOLATION 격리 수준을 사용하는 SNAPSHOT 트랜잭션을 ON 시작하려면 먼저 데이터베이스 옵션을 설정해야 합니다. 격리 수준을 사용하는 트랜잭션이 SNAPSHOT 여러 데이터베이스의 데이터에 액세스하는 경우 각 데이터베이스 ALLOW_SNAPSHOT_ISOLATION 에서 설정 ON 해야 합니다.

트랜잭션을 다른 격리 수준으로 시작한 격리 수준으로 설정할 SNAPSHOT 수 없습니다. 이렇게 하면 트랜잭션이 중단됩니다. 트랜잭션이 격리 수준에서 시작되는 SNAPSHOT 경우 다른 격리 수준으로 변경한 다음 다시 .로 SNAPSHOT변경할 수 있습니다. 트랜잭션은 데이터에 처음 액세스할 때 시작됩니다.

격리 수준에서 실행되는 SNAPSHOT 트랜잭션은 해당 트랜잭션의 변경 내용을 볼 수 있습니다. 예를 들어 트랜잭션이 테이블에서 작업을 수행한 UPDATE 다음 동일한 테이블에 대해 문을 실행 SELECT 하면 수정된 데이터가 결과 집합에 포함됩니다.

직렬화 가능

다음 조건을 지정합니다.

• 문은 수정되었지만 다른 트랜잭션에서 아직 커밋되지 않은 데이터를 읽을 수 없습니다.

• 다른 트랜잭션은 현재 트랜잭션이 완료될 때까지 현재 트랜잭션에서 읽은 데이터를 수정할 수 없습니다.

• 다른 트랜잭션은 현재 트랜잭션이 완료될 때까지 현재 트랜잭션의 문에서 읽은 키 범위에 속하는 키 값이 있는 새 행을 삽입할 수 없습니다.

범위 잠금은 트랜잭션에서 실행된 각 문의 검색 조건과 일치하는 키 값의 범위에 적용됩니다. 따라서 다른 트랜잭션은 현재 트랜잭션에서 실행한 문에 한정되는 행을 업데이트하거나 삽입할 수 없습니다. 즉, 트랜잭션의 문이 두 번째로 실행되는 경우 동일한 행 집합을 읽습니다. 범위 잠금은 트랜잭션이 완료될 때까지 유지됩니다. 범위 잠금은 전체 키 범위를 잠그고 트랜잭션이 완료될 때까지 해당 잠금을 보유하므로 격리 수준 중에서 가장 제한적입니다. 동시성이 더 낮기 때문에 필요할 때만 이 옵션을 사용하도록 하세요. 이 옵션은 트랜잭션의 모든 문에 있는 모든 SELECT 테이블에 대해 설정하는 HOLDLOCK 것과 동일한 효과를 줍니다.

설명

격리 수준 옵션 중 하나만 한 번에 설정할 수 있으며 명시적으로 변경될 때까지 해당 연결에 대해 설정된 상태로 유지됩니다. 문 절의 테이블 힌트 FROM 가 테이블에 대해 다른 잠금 또는 버전 관리 동작을 지정하지 않는 한 트랜잭션 내에서 수행되는 모든 읽기 작업은 지정된 격리 수준에 대한 규칙에 따라 작동합니다.

트랜잭션 격리 수준은 읽기 작업 시 획득되는 잠금 유형을 정의합니다. 페이지 또는 테이블의 많은 행이 읽기에서 참조되는 경우 행 잠금을 페이지 또는 테이블 잠금으로 에스컬레이션할 수 있지만 일반적으로는 공유 잠금을 획득 READ COMMITTED 하거나 REPEATABLE READ 행 잠금입니다. 트랜잭션이 읽은 후 행을 수정하는 경우 트랜잭션은 해당 행을 보호하기 위해 배타적 잠금을 획득하고 트랜잭션이 완료될 때까지 배타적 잠금이 유지됩니다. 예를 들어 트랜잭션에 REPEATABLE READ 행에 대한 공유 잠금이 있고 트랜잭션이 행을 수정하면 공유 행 잠금이 배타적 행 잠금으로 변환됩니다.

한 가지 경우를 제외하고 트랜잭션 중 언제든지 다른 격리 수준으로 전환할 수 있습니다. 격리 수준에서 격리로 변경할 때 예외가 SNAPSHOT 발생합니다. 이렇게 하면 트랜잭션이 실패하고 롤백됩니다. 그러나 격리에서 시작된 트랜잭션을 SNAPSHOT 다른 격리 수준으로 변경할 수 있습니다.

트랜잭션의 격리 수준을 변경하는 경우 변경 후에 읽은 리소스는 새 수준의 규칙에 따라 보호됩니다. 변경 전에 읽은 리소스는 이전 수준의 규칙에 따라 계속 보호됩니다. 예를 들어 트랜잭션이 변경된 READ COMMITTEDSERIALIZABLE경우 변경 후 획득한 공유 잠금은 이제 트랜잭션이 끝날 때까지 유지됩니다.

저장 프로시저 또는 트리거에서 발급 SET TRANSACTION ISOLATION LEVEL 하는 경우 개체가 반환될 때 격리 수준이 개체가 호출될 때 적용되는 수준으로 다시 설정됩니다. 예를 들어 일괄 처리에서 설정한 REPEATABLE READ 다음 일괄 처리에서 격리 수준을 설정하는 저장 프로시저를 SERIALIZABLE호출하는 경우 격리 수준 설정은 저장 프로시저가 컨트롤을 일괄 처리로 반환할 때로 되돌아 REPEATABLE READ 갑니다.

두 세션을 바인딩하는 데 사용하는 sp_bindsession 경우 각 세션은 격리 수준 설정을 유지합니다. 한 세션의 격리 수준 설정을 변경하는 데 사용하는 SET TRANSACTION ISOLATION LEVEL 것은 세션에 바인딩된 다른 세션의 설정에 영향을 주지 않습니다.

SET TRANSACTION ISOLATION LEVEL 는 구문 분석 시간이 아니라 실행 또는 런타임에 적용됩니다.

힙의 최적화된 대량 로드 작업은 다음 격리 수준에서 실행되는 쿼리를 차단합니다.

• SNAPSHOT
• READ UNCOMMITTED
• READ COMMITTED 행 버전 관리 사용

반대로 이러한 격리 수준에서 실행된 쿼리는 힙의 최적화된 대량 로드 작업을 차단합니다. 대량 로드 작업에 대한 자세한 내용은 데이터 대량 가져오기 및 내보내기(SQL Server)를 참조하세요.

FILESTREAM 사용 데이터베이스는 다음 트랜잭션 격리 수준을 지원합니다.

예

다음 예에서는 세션에 대한 TRANSACTION ISOLATION LEVEL을 설정합니다. 이어지는 각 Transact-SQL 문에 대해 SQL Server는 트랜잭션이 종료될 때까지 모든 공유 잠금을 유지합니다.

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

관련 콘텐츠

• ALTER DATABASE(Transact-SQL)
• DBCC USEROPTIONS(Transact-SQL)
• SELECT(Transact-SQL)
• SET 문(Transact-SQL)
• 테이블 힌트(Transact-SQL)