Memory-Optimized 테이블을 사용하여 트랜잭션 격리 수준에 대한 지침

많은 시나리오에서 트랜잭션 격리 수준을 지정해야 합니다. 메모리 최적화 테이블에 대한 트랜잭션 격리는 디스크 기반 테이블과 다릅니다.

트랜잭션 격리 수준을 지정하기 위한 요구 사항:

TRANSACTION ISOLATION LEVEL은 고유하게 컴파일된 저장 프로시저의 콘텐츠를 구성하는 ATOMIC 블록에 필요한 옵션입니다.
컨테이너 간 트랜잭션에서 격리 수준 사용에 대한 제한으로 인해 해석된 Transact-SQL 메모리 최적화 테이블의 사용은 종종 테이블에 액세스하는 데 사용되는 격리 수준을 지정하는 테이블 힌트와 함께 제공되어야 합니다. 격리 수준 힌트 및 컨테이너 간 트랜잭션에 대한 자세한 내용은 트랜잭션 격리 수준을 참조하세요.
원하는 트랜잭션 격리 수준을 명시적으로 선언해야 합니다. 잠금 힌트(예: XLOCK)를 사용하여 트랜잭션의 특정 행 또는 테이블 격리를 보장할 수 없습니다.
데이터베이스에 액세스하는 애플리케이션은 트랜잭션 종료 충돌, 유효성 검사 실패 및 커밋 종속성 오류로 인한 오류를 처리하는 재시도 논리를 구현해야 합니다. 커밋 종속성 오류는 읽기 전용 트랜잭션에서도 발생할 수 있습니다.
메모리 최적화 테이블을 사용하여 장기 실행 트랜잭션을 피해야 합니다. 이러한 트랜잭션은 충돌 및 후속 트랜잭션 종료 가능성을 높입니다. 또한 장기 실행 트랜잭션은 가비지 수집을 지연합니다. 트랜잭션 실행 시간이 길수록 In-Memory OLTP가 최근에 삭제된 행 버전을 유지하므로 새 트랜잭션에 대한 조회 성능이 저하될 수 있습니다.

디스크 기반 테이블은 일반적으로 트랜잭션 격리를 위해 잠금 및 차단을 사용합니다. 메모리 최적화 테이블은 다중 버전 관리 및 충돌 검색을 사용하여 격리를 보장합니다. 자세한 내용은 Memory-Optimized 테이블의 트랜잭션에서 충돌 검색, 유효성 검사 및 커밋 종속성 검사 섹션을 참조하세요.

디스크 기반 테이블은 다중 버전 관리 기능을 격리 수준 SNAPSHOT 및 READ_COMMITTED_SNAPSHOT과 함께 사용할 수 있도록 허용합니다. 메모리 최적화 테이블의 경우 모든 격리 수준은 REPEATABLE READ 및 SERIALIZABLE을 비롯한 다중 버전 기반입니다.

트랜잭션 유형

SQL Server의 모든 쿼리는 트랜잭션의 컨텍스트에서 실행됩니다.

SQL Server에는 세 가지 유형의 트랜잭션이 있습니다.

자동 커밋 트랜잭션. 활성 트랜잭션 컨텍스트가 없고 암시적 트랜잭션이 세션에서 ON으로 설정되지 않은 경우 각 쿼리에는 자체 트랜잭션 컨텍스트가 있습니다. 트랜잭션은 문이 실행을 시작할 때 시작되고 문이 완료되면 완료됩니다.
명시적 트랜잭션. 사용자는 명시적 BEGIN TRAN 또는 BEGIN ATOMIC을 통해 트랜잭션을 시작합니다. 트랜잭션은 해당 COMMIT 및 ROLLBACK 또는 END(원자성 블록의 경우)에 따라 완료됩니다.
암시적 트랜잭션. IMPLICIT_TRANSACTIONS 옵션이 ON으로 설정되면 사용자가 문을 실행하고 활성 트랜잭션 컨텍스트가 없을 때마다 트랜잭션이 암시적으로 시작됩니다. 트랜잭션은 명시적 COMMIT 및 ROLLBACK을 통해 완료됩니다.

초기 계획 READ COMMITTED 격리

READ COMMITTED는 SQL Server의 기본 격리 수준입니다.

격리 수준 READ COMMITTED는 트랜잭션에 현재 트랜잭션 외부의 변경 내용에서 커밋되지 않은 데이터가 표시되지 않도록 보장합니다. 즉, 트랜잭션은 데이터베이스에 커밋되었거나 현재 트랜잭션에 의해 변경된 데이터만 읽습니다.

메모리 최적화 테이블에 대해 지원되는 모든 격리 수준은 커밋된 읽기 보장을 제공합니다. 따라서 트랜잭션에 더 강력한 보장이 필요하지 않은 경우 메모리 최적화 테이블에 지원되는 격리 수준을 사용할 수 있습니다. SNAPSHOT은 다른 격리 수준에 비해 가장 적은 시스템 리소스를 사용합니다.

SNAPSHOT 격리 수준(메모리 최적화 테이블에 대해 지원되는 가장 낮은 격리 수준)에서 제공하는 보장에는 READ COMMITTED의 보장이 포함됩니다. 트랜잭션의 모든 문장은 동일한 일관된 버전의 데이터베이스를 읽습니다. 트랜잭션에서 읽은 모든 행이 데이터베이스에 커밋되는 것뿐만 아니라 모든 읽기 작업에서도 동일한 트랜잭션 집합에 의해 변경된 집합을 볼 수 있습니다.

지침: READ COMMITTED 격리 보장만 필요한 경우 고유하게 컴파일된 저장 프로시저와 함께 SNAPSHOT 격리를 사용하고 해석된 Transact-SQL을 통해 메모리 최적화 테이블에 액세스합니다.

자동 커밋 트랜잭션의 경우 격리 수준 READ COMMITTED는 메모리 최적화 테이블을 위해 SNAPSHOT에 암시적으로 매핑됩니다. 따라서 TRANSACTION ISOLATION LEVEL 세션 설정이 READ COMMITTED로 설정된 경우 메모리 최적화 테이블에 액세스할 때 테이블 힌트를 통해 격리 수준을 지정할 필요가 없습니다.

다음 자동 커밋 트랜잭션 예제에서는 임시 일괄 처리의 일부로 메모리 최적화 테이블 Customers와 일반 테이블 [주문 기록] 간의 조인을 보여 줍니다.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  
SELECT *   
FROM dbo.Customers AS c   
LEFT JOIN dbo.[Order History] AS oh   
    ON c.customer_id = oh.customer_id;

다음 명시적 또는 암시적 트랜잭션 예제는 동일한 조인을 보여 주지만 이번에는 명시적 사용자 트랜잭션에서 보여 줍니다. 메모리 최적화된 테이블인 Customers는 테이블 힌트 WITH (SNAPSHOT)를 사용하여 스냅샷 격리 하에 액세스되며, 일반 테이블인 [Order History]는 읽기 커밋 격리 하에 액세스됩니다.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
BEGIN TRAN  
SELECT * FROM dbo.Customers c with (SNAPSHOT)   
LEFT JOIN dbo.[Order History] oh   
    ON c.customer_id=oh.customer_id  
...  
COMMIT

운영 차이점

커밋된 읽기 보장 외에도 디스크 기반 테이블을 사용하는 애플리케이션이 사용할 수 있는 두 가지 주요 구현 세부 정보도 있습니다. READ COMMITTED 격리를 사용하여 액세스하는 디스크 기반 테이블을 SNAPSHOT 격리를 사용하여 액세스하는 메모리 최적화 테이블로 변환할 때 다음 사항에 유의하세요.

디스크 기반 테이블에 대한 READ COMMITTED 격리 수준 구현(READ_COMMITTED_SNAPSHOT OFF라고 가정)은 잠금을 사용하여 판독기와 기록기 간의 충돌을 방지합니다. 작성자가 행 업데이트를 시작하면 잠금을 설정하고 트랜잭션이 커밋될 때까지 잠금을 해제하지 않습니다. 모든 읽기 작업은 차단되며 쓰기 트랜잭션이 커밋될 때까지 기다립니다.

일부 애플리케이션에서는 특히 애플리케이션 계층에서 두 트랜잭션 간에 동기화가 있는 경우 판독기가 항상 기록기가 커밋되기를 기다린다고 가정할 수 있습니다.

지침: 애플리케이션은 차단 동작에 의존할 수 없습니다. 애플리케이션에서 동시 트랜잭션 간의 동기화가 필요한 경우 sp_getapplock (Transact-SQL)를 통해 애플리케이션 계층 또는 데이터베이스 계층에서 이러한 논리를 구현할 수 있습니다.
READ COMMITTED 격리를 사용하는 트랜잭션에서 각 명령문은 데이터베이스에 있는 행의 최신 버전을 확인합니다. 따라서 후속 문은 데이터베이스 상태의 변경 내용을 확인합니다.

새 행이 발견될 때까지 WHILE 루프를 사용하여 테이블을 폴링하는 것은 이 가정을 사용하는 애플리케이션 패턴의 예입니다. 루프를 반복할 때마다 쿼리에 데이터베이스의 최신 업데이트가 표시됩니다.

지침: 애플리케이션이 메모리 최적화 테이블을 폴링하여 테이블에 기록된 가장 최근 행을 가져와야 하는 경우 폴링 루프를 트랜잭션 범위 밖으로 이동합니다.

다음은 이 가정을 사용하는 애플리케이션 패턴의 예입니다. 새 행이 발견될 때까지 WHILE 루프를 사용하여 테이블을 반복적으로 조회합니다. 각 루프 반복에서 쿼리는 데이터베이스의 최신 업데이트에 액세스합니다.

다음 예제 스크립트는 t1 테이블에 행이 생길 때까지 폴링합니다. 그런 다음 추가 처리를 위해 테이블에서 단일 행을 제거합니다.

테이블 t1에 액세스하기 위해 스냅샷 격리를 사용하므로 폴링 논리는 트랜잭션 범위를 벗어나야 합니다. 트랜잭션 범위 내에서 폴링 논리를 사용하면 장기 실행 트랜잭션이 만들어지는데 이는 잘못된 방법입니다.

-- poll table  
WHILE NOT EXISTS (SELECT 1 FROM dbo.t1)  
BEGIN   
  -- if empty, wait and poll again  
  WAITFOR DELAY '00:00:01'  
END  
  
BEGIN TRANSACTION  
  DECLARE @id int  
  SELECT TOP 1 @id=id FROM dbo.t1 WITH (SNAPSHOT)  
  DELETE FROM dbo.t1 WITH (SNAPSHOT) WHERE id=@id  
  
  -- insert processing based on @id  
COMMIT

잠금 테이블 힌트

HOLDLOCK 및 XLOCK과 같은 잠금 힌트(테이블 힌트(Transact-SQL)는 디스크 기반 테이블과 함께 사용하여 SQL Server가 지정된 격리 수준에 필요한 것보다 더 많은 잠금을 갖도록 할 수 있습니다.

메모리 최적화 테이블은 잠금을 사용하지 않습니다. REPEATABLE READ 및 SERIALIZABLE과 같은 높은 격리 수준을 사용하여 원하는 보증을 선언할 수 있습니다.

잠금 힌트는 지원되지 않습니다. 대신 트랜잭션 격리 수준을 통해 필요한 보증을 선언합니다. (SQL Server가 메모리 최적화 테이블에 잠금을 설정하지 않으므로 NOLOCK이 지원됩니다. 디스크 기반 테이블과 달리 NOLOCK은 메모리 최적화 테이블에 대한 READ UNCOMMITTED 동작을 의미하지 않습니다.)

또한 참조하십시오

Memory-Optimized 테이블의 트랜잭션 이해
Memory-Optimized 테이블의 트랜잭션에 대한 재시도 논리에 대한 지침
트랜잭션 격리 수준

Last updated on 2017-03-06