SET TRANSACTION ISOLATION LEVEL: 격리 수준

SET TRANSACTION ISOLATION LEVEL은 SQL Server에 연결된 세션이 발행하는 T-SQL 문장의 잠금과 행 버전 관리 동작을 제어하는 문장이에요. 다시 말해 트랜잭션의 격리 수준을 정해서, 동시성(얼마나 많은 작업을 한꺼번에 허용할지)과 일관성(읽는 데이터가 얼마나 안정적인지) 사이의 균형을 조절해요. SQL Server의 기본 격리 수준은 READ COMMITTED예요. 격리 수준을 선택하는 것 자체는 데이터 수정을 보호하는 잠금과는 별개라는 점을 기억해야 해요.

출처: SET TRANSACTION ISOLATION LEVEL (Transact-SQL) - Microsoft Learn

문법

SQL Server, Azure SQL Database, SQL database in Microsoft Fabric용 문법은 다음과 같아요.

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

Azure Synapse Analytics와 병렬 데이터 웨어하우스(PDW)에서는 READ UNCOMMITTED만 지원해요.

각 격리 수준

READ UNCOMMITTED

다른 트랜잭션이 수정했지만 아직 커밋하지 않은 행까지 읽을 수 있어요. 공유 잠금을 걸지 않아 다른 트랜잭션의 수정을 막지 않고, 배타 잠금에도 막히지 않아 커밋되지 않은 수정(dirty read)을 읽을 수 있어요. 트랜잭션이 끝나기 전에 데이터 값이 바뀌거나 행이 나타나고 사라질 수 있어요. 트랜잭션 안의 모든 SELECT에 NOLOCK 힌트를 단 것과 같은 효과로, 가장 제약이 느슨한 수준이에요.

READ COMMITTED (기본값)

다른 트랜잭션이 수정했지만 커밋하지 않은 데이터는 읽지 못해서 dirty read를 막아줘요. 다만 트랜잭션 안의 문장 사이에 다른 트랜잭션이 데이터를 바꿀 수 있어 nonrepeatable read나 팬텀 데이터가 발생할 수 있어요.

READ_COMMITTED_SNAPSHOT 데이터베이스 옵션 값에 따라 동작이 달라져요.

OFF(기본): 공유 잠금을 사용해서 읽기 연산 중에 다른 트랜잭션이 해당 행을 수정하지 못하게 해요. 행 잠금은 다음 행 처리 전에, 페이지 잠금은 다음 페이지 읽을 때, 테이블 잠금은 문장이 끝날 때 해제돼요.
ON: 행 버전 관리를 사용해서 각 문장이 시작된 시점의 일관된 스냅샷을 보여줘요. 잠금으로 보호하지 않아요. Azure SQL Database와 SQL database in Microsoft Fabric에서는 ON이 기본이에요.

REPEATABLE READ

커밋되지 않은 데이터를 읽지 못하고, 트랜잭션이 끝날 때까지 다른 트랜잭션이 현재 트랜잭션이 읽은 데이터를 수정하지도 못해요. 읽은 모든 데이터에 공유 잠금을 걸고 트랜잭션이 끝날 때까지 보유해요. 다만 다른 트랜잭션이 현재 트랜잭션의 검색 조건에 맞는 새 행을 삽입할 수 있고, 문장을 다시 실행하면 그 새 행이 보여서 팬텀 리드가 발생할 수 있어요. 공유 잠금을 문장 끝이 아니라 트랜잭션 끝까지 유지하므로 기본값인 READ COMMITTED보다 동시성이 낮아요. 꼭 필요할 때만 쓰는 게 좋아요.

SNAPSHOT

트랜잭션 안의 어떤 문장이 읽는 데이터든, 트랜잭션이 시작된 시점에 존재했던 커밋된 데이터의 일관된 버전을 보여줘요. 트랜잭션 시작 후 다른 트랜잭션이 커밋한 수정은 보이지 않아요. 마치 트랜잭션이 시작된 시점의 커밋된 데이터 스냅샷을 찍어두는 것과 같은 효과예요.

SNAPSHOT 트랜잭션은 읽을 때 잠금을 요청하지 않아서, 읽기가 쓰기를 막지 않고 쓰기도 읽기를 막지 않아요. 다만 이 수준을 쓰려면 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 먼저 ON으로 켜야 해요. 다른 격리 수준으로 시작한 트랜잭션을 SNAPSHOT으로 바꾸면 트랜잭션이 중단되니 주의해야 해요.

SERIALIZABLE

가장 강한 제약을 주는 수준이라 조건이 세 가지예요.

커밋되지 않은 데이터를 읽지 못해요.
트랜잭션이 읽은 데이터를 다른 트랜잭션이 트랜잭션 끝까지 수정하지 못해요.
현재 트랜잭션이 읽은 키 값 범위에 들어오는 새 행을 다른 트랜잭션이 트랜잭션 끝까지 삽입하지 못해요.

각 문장의 검색 조건에 맞는 키 범위에 범위 잠금을 걸고 트랜잭션이 끝날 때까지 보유해요. 그래서 문장을 두 번 실행해도 같은 행 집합을 읽어요. 트랜잭션 안의 모든 SELECT에 HOLDLOCK 힌트를 단 것과 같은 효과로, 키 범위 전체를 잠그고 끝까지 유지하므로 동시성이 낮아요. 필요할 때만 쓰는 게 좋아요.

사용할 때 알아둘 점

격리 수준을 도중에 바꾸면, 변경 후 읽는 리소스는 새 수준의 규칙으로, 변경 전에 이미 읽은 리소스는 이전 수준의 규칙으로 계속 보호돼요.
저장 프로시저나 트리거 안에서 이 문장을 실행하면, 그 객체가 제어권을 반환할 때 격리 수준이 호출 시점의 수준으로 되돌아가요.
이 문장은 파싱 시점이 아니라 실행·런타임 시점에 적용돼요.
격리 수준을 정한다고 데이터 수정을 보호하는 잠금이 바뀌지는 않아요. 트랜잭션은 수정한 데이터에 항상 배타 잠금을 걸고 트랜잭션이 끝날 때까지 보유해요.

REPEATABLE READ로 세션 격리 수준을 설정하고 트랜잭션을 여는 예시는 다음과 같아요.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
SELECT * FROM HumanResources.EmployeePayHistory;
SELECT * FROM HumanResources.Department;
COMMIT TRANSACTION;
GO

이 예시처럼 설정하면 트랜잭션 안의 모든 SELECT가 끝날 때까지 공유 잠금을 유지해요.

SET TRANSACTION ISOLATION LEVEL: 격리 수준