트랜잭션 잠금 및 행 버전 관리 지침

적용 대상: Microsoft Fabric의 SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW) SQL 데이터베이스

모든 데이터베이스에서 트랜잭션을 잘못 관리하면 사용자가 많은 시스템에서 경합 및 성능 문제가 발생하는 경우가 많습니다. 데이터에 액세스하는 사용자 수가 증가함에 따라 트랜잭션을 효율적으로 사용하는 애플리케이션이 있어야 합니다. 이 가이드에서는 데이터베이스 엔진이 각 트랜잭션의 무결성을 유지하는 데 사용하는 잠금 및 행 버전 관리 메커니즘과 애플리케이션에서 트랜잭션을 효율적으로 제어할 수 있는 방법에 대해 설명합니다.

트랜잭션 기본 사항

트랜잭션은 하나의 논리적 작업 단위로 수행되는 일련의 작업입니다. 작업의 논리적 단위는 ACID(원자성, 일관성, 격리성 및 영속성) 속성이라고 하는 네 가지 속성을 통해 트랜잭션으로서의 자격을 부여합니다.

원자성
트랜잭션은 원자 단위여야 합니다. 모든 데이터 수정이 수행되거나 모두 수행되지 않습니다.

일관성
완료되면 트랜잭션은 모든 데이터를 일관된 상태로 유지해야 합니다. 관계형 데이터베이스에서는 트랜잭션 수정에 모든 규칙을 적용하여 모든 데이터 무결성을 유지해야 합니다. B-트리 인덱스 또는 이중으로 연결된 목록과 같은 모든 내부 데이터 구조는 트랜잭션이 끝날 때 정확해야 합니다.

격리
동시 트랜잭션에 의한 수정은 다른 동시 트랜잭션에 의한 수정과 격리되어야 합니다. 트랜잭션에서 다른 동시 트랜잭션이 수정하기 전 상태의 데이터를 보거나 두 번째 트랜잭션이 완료된 후의 데이터를 볼 수는 있지만 중간 상태는 볼 수 없습니다. 이는 시작 데이터를 다시 로드하고 일련의 트랜잭션을 재생하여 원래 트랜잭션이 수행된 후와 동일한 상태의 데이터로 끝날 수 있기 때문에 직렬화 기능이라고 합니다.

영속성
완전한 지속형 트랜잭션이 완료되면 그 영향은 영구적으로 시스템에 적용됩니다. 수정은 시스템에 오류가 발생한 경우에도 지속됩니다. SQL Server 2014(12.x) 이상에서는 지연된 지속형 트랜잭션을 지원합니다. 트랜잭션 로그 레코드가 디스크에 유지되기 전에 지연된 지속형 트랜잭션이 커밋합니다. 지연된 트랜잭션 내구성에 대한 자세한 내용은 트랜잭션 내구성 제어 문서를 참조하세요.

애플리케이션은 데이터의 논리적 일관성을 유지하는 지점에서 트랜잭션을 시작하고 종료해야 합니다. 애플리케이션은 조직의 비즈니스 규칙을 기준으로 데이터를 일관된 상태로 유지하는 데이터 수정 시퀀스를 정의해야 합니다. 애플리케이션은 데이터베이스 엔진이 트랜잭션의 무결성을 적용할 수 있도록 단일 트랜잭션에서 이러한 수정을 수행합니다.

각 트랜잭션의 무결성을 보장하는 메커니즘을 제공하는 것은 데이터베이스 엔진 인스턴스와 같은 엔터프라이즈 데이터베이스 시스템의 책임입니다. 데이터베이스 엔진은 다음을 제공합니다.

• 트랜잭션 격리를 유지하는 잠금 기능.

• 트랜잭션 내구성을 보장하는 로깅 기능. 완전 지속형 트랜잭션의 경우 트랜잭션이 커밋되기 전에 로그 레코드가 디스크에 기록됩니다. 따라서 서버 하드웨어, 운영 체제 또는 데이터베이스 엔진 자체의 인스턴스가 실패하더라도 인스턴스는 다시 시작할 때 트랜잭션 로그를 사용하여 불완전한 트랜잭션을 시스템 오류 지점으로 자동으로 롤백합니다. 트랜잭션 로그 레코드가 디스크에 기록되기 전에 지연된 지속형 트랜잭션이 커밋합니다. 로그 레코드가 디스크에 기록되기 전에 시스템 오류가 발생하면 이러한 트랜잭션이 손실될 수 있습니다. 지연된 트랜잭션 내구성에 대한 자세한 내용은 트랜잭션 내구성 제어 문서를 참조하세요.

• 트랜잭션의 원자성 및 일관성을 적용하는 트랜잭션 관리 기능입니다. 트랜잭션이 시작된 후에는 트랜잭션이 성공적으로 완료(커밋됨)되거나 데이터베이스 엔진에서 트랜잭션이 시작된 이후 해당 트랜잭션에 의해 수행된 모든 데이터 수정을 실행 취소해야 합니다. 이 작업은 변경 전의 상태를 데이터에 반환하기 때문에 트랜잭션 롤백이라고도 합니다.

트랜잭션 제어

애플리케이션은 주로 트랜잭션 시작 및 종료 시기를 지정하여 트랜잭션을 제어합니다. 트랜잭션 시작 및 종료 시기는 Transact-SQL 문 또는 데이터베이스 API(애플리케이션 프로그래밍 인터페이스) 함수를 사용하여 지정할 수 있습니다. 또한 시스템은 트랜잭션이 완료되기 전에 트랜잭션을 종료하는 오류를 올바르게 처리할 수 있어야 합니다. 자세한 내용은 트랜잭션, ODBC의 트랜잭션 수행 및 SQL Server Native Client의 트랜잭션을 참조하세요.

기본적으로 트랜잭션은 연결 수준에서 관리됩니다. 연결에서 트랜잭션이 시작되면 해당 연결에서 실행되는 모든 Transact-SQL 문은 트랜잭션이 종료될 때까지 트랜잭션의 일부입니다. MARS(Multiple Active Result Sets) 세션에만 해당되며, Transact-SQL 명시적 또는 암시적 트랜잭션이 일괄 처리 수준에서 관리되는 일괄 처리 범위 트랜잭션이 됩니다. 일괄 처리가 완료될 때 일괄 처리 범위의 트랜잭션이 커밋되거나 롤백되지 않으면 데이터베이스 엔진에서 해당 트랜잭션을 자동으로 롤백합니다. 자세한 내용은 MARS(Multiple Active Result Sets) 사용을 참조하세요.

트랜잭션 시작

API 함수 및 Transact-SQL 문을 사용하여 명시적, 자동 커밋 또는 암시적 트랜잭션으로 트랜잭션을 시작할 수 있습니다.

명시적 트랜잭션

명시적 트랜잭션은 API 함수를 통해 또는 Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION 및 ROLLBACK WORK Transact-SQL 문을 실행하여 트랜잭션의 시작과 끝을 명시적으로 정의하는 트랜잭션입니다. 트랜잭션이 끝나면 연결은 명시적 트랜잭션이 시작되기 전의 트랜잭션 모드로 되돌아가며, 이는 암시적 모드 또는 자동 커밋 모드일 수 있습니다.

다음 문을 제외하고 명시적 트랜잭션에서 모든 Transact-SQL 문을 사용할 수 있습니다.

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• 전체 텍스트 시스템 저장 프로시저
• 데이터베이스 옵션을 설정하는 sp_dboption 또는 명시적/암시적 트랜잭션 내에서 master 데이터베이스를 수정하는 시스템 프로시저

자동 커밋 트랜잭션

자동 커밋 모드는 데이터베이스 엔진의 기본 트랜잭션 관리 모드입니다. 모든 Transact-SQL 문은 완료 시 커밋되거나 롤백됩니다. 문이 성공적으로 완료되면 커밋됩니다. 오류가 발생하면 롤백됩니다. 이 기본 모드가 명시적 또는 암시적 트랜잭션에 의해 재정의되지 않은 경우 데이터베이스 엔진 인스턴스에 대한 연결은 자동 커밋 모드에서 작동합니다. 자동 커밋 모드는 SqlClient, ADO, OLE DB 및 ODBC의 기본 모드이기도 합니다.

암시적 트랜잭션

연결이 암시적 트랜잭션 모드에서 작동하는 경우 데이터베이스 엔진 인스턴스는 현재 트랜잭션이 커밋되거나 롤백된 후 자동으로 새 트랜잭션을 시작합니다. 트랜잭션의 시작을 표시하기 위해 아무 작업도 수행하지 않습니다. 각 트랜잭션만 커밋하거나 롤백합니다. 암시적 트랜잭션 모드는 트랜잭션의 연속 체인을 생성합니다. API 함수 또는 Transact-SQL SET IMPLICIT_TRANSACTIONS ON 문을 통해 암시적 트랜잭션 모드를 설정합니다. 이 모드를 Autocommit OFF라고도 합니다. setAutoCommit Method(SQLServerConnection)를 참조하세요.

연결에 대해 암시적 트랜잭션 모드를 설정하고 나면 이러한 문을 처음 실행할 때 데이터베이스 엔진 인스턴스가 자동으로 트랜잭션을 시작합니다.

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

일괄 처리 범위의 트랜잭션

MARS(Multiple Active Result Sets)에만 해당되며, MARS 세션에서 시작되는 Transact-SQL 명시적 또는 암시적 트랜잭션이 일괄 처리 범위 트랜잭션이 됩니다. 일괄 처리가 완료될 때 커밋되거나 롤백되지 않은 일괄 처리 범위의 트랜잭션은 데이터베이스 엔진에서 자동으로 롤백합니다.

분산 트랜잭션

분산 트랜잭션은 리소스 관리자라고 하는 둘 이상의 서버에 걸쳐 있습니다. 트랜잭션 관리는 트랜잭션 관리자라는 서버 구성 요소에 의해 리소스 관리자 간에 조정되어야 합니다. 데이터베이스 엔진의 각 인스턴스는 MS DTC(Microsoft Distributed Transaction Coordinator) 또는 분산 트랜잭션 처리를 위한 Open Group XA 사양을 지원하는 다른 트랜잭션 관리자와 같은 트랜잭션 관리자가 조정하는 분산 트랜잭션에서 리소스 관리자로 작동할 수 있습니다. 자세한 내용은 MSIX 설명서를 참조해 주세요.

데이터베이스 엔진의 단일 인스턴스에서 둘 이상의 데이터베이스를 사용하는 트랜잭션은 분산 트랜잭션이 됩니다. 인스턴스는 내부적으로 분산 트랜잭션을 관리하기 때문에; 사용자에게 로컬 트랜잭션으로 작동됩니다.

애플리케이션에서 분산 트랜잭션의 관리 방법은 로컬 트랜잭션과 많은 부분이 동일합니다. 트랜잭션이 끝나면 애플리케이션이 트랜잭션을 커밋 또는 롤백하도록 요청합니다. 트랜잭션 관리자는 분산 커밋을 다른 방법으로 관리하여 일부 리소스 관리자는 성공적으로 커밋하고 일부는 트랜잭션을 롤백하는 네트워크 오류의 발생 가능성을 최소화해야 합니다. 이는 2단계 커밋이라고 알려진 두 단계(준비 단계와 커밋 단계)로 커밋 프로세스를 관리하여 달성됩니다.

• 준비 단계

  트랜잭션 관리자가 커밋 요청을 수신하면 트랜잭션과 관련된 모든 리소스 관리자에게 준비 명령을 보냅니다. 그런 다음 각 리소스 관리자는 트랜잭션의 내구성을 유지하는데 필요한 모든 작업을 하고, 해당 트랜잭션의 모든 트랜잭션 로그 버퍼는 디스크로 플러시됩니다. 각 리소스 관리자가 준비 단계를 완료하면 준비 성공 또는 실패 여부를 트랜잭션 관리자에게 반환됩니다. SQL Server 2014(12.x)에서는 지연된 트랜잭션 내구성이 도입되었습니다. 지연된 지속형 트랜잭션은 각 리소스 관리자의 트랜잭션의 로그 버퍼가 디스크에 플러시되기 전에 커밋합니다. 지연된 트랜잭션 내구성에 대한 자세한 내용은 트랜잭션 내구성 제어 문서를 참조하세요.

• 커밋 단계

  트랜잭션 관리자가 모든 리소스 관리자로부터 준비 성공 알림을 받으면 각 리소스 관리자에게 커밋 명령을 보냅니다. 그런 다음에는 리소스 관리자가 커밋을 완료할 수 있습니다. 모든 리소스 관리자가 성공적인 커밋을 보고하면 트랜잭션 관리자가 애플리케이션에 성공을 알립니다. 준비 실패를 보고한 리소스 관리자가 있는 경우 트랜잭션 관리자가 각 리소스 관리자에게 롤백 명령을 보내서 애플리케이션에 커밋 실패를 알립니다.

  데이터베이스 엔진 애플리케이션은 Transact-SQL 또는 데이터베이스 API를 통해 분산 트랜잭션을 관리할 수 있습니다. 자세한 내용은 BEGIN DISTRIBUTED TRANSACTION(Transact-SQL)을 참조하세요.

트랜잭션 종료

COMMIT 또는 ROLLBACK 문을 사용하거나 해당 API 함수를 통해 트랜잭션을 종료할 수 있습니다.

• Commit

  트랜잭션이 성공하면 커밋합니다. COMMIT 문은 트랜잭션의 모든 수정 내용이 데이터베이스의 영구적인 부분이 되도록 보장합니다. Commit은 또한 트랜잭션에 사용된 잠금과 같은 리소스를 해제합니다.

• 롤백

  트랜잭션에서 오류가 발생하거나 사용자가 트랜잭션을 취소하려고 결정한 경우 트랜잭션을 롤백합니다. ROLLBACK 문은 트랜잭션 시작 시의 상태로 데이터를 반환하여 트랜잭션에서 수행된 모든 수정 내용을 백업합니다. 롤백은 또한 트랜잭션에서 보유한 리소스도 해제합니다.

트랜잭션 처리 중 오류

오류로 인해 트랜잭션이 성공적으로 완료되지 않은 경우 데이터베이스 엔진에서는 자동으로 트랜잭션을 롤백하고 해당 트랜잭션에 보유 중인 모든 리소스를 해제합니다. 데이터베이스 엔진 인스턴스에 대한 클라이언트 네트워크 연결이 끊어진 경우 네트워크에서 연결 중단 인스턴스에 알리면 연결에 대한 미해결 트랜잭션이 롤백됩니다. 클라이언트 애플리케이션이 실패하거나 클라이언트 컴퓨터가 중단되거나 다시 시작되면 연결이 끊어지고 네트워크에서 연결 중단을 알립니다. 그러면 데이터베이스 엔진 인스턴스가 미해결 트랜잭션을 롤백합니다. 클라이언트가 데이터베이스 엔진 연결을 끊으면 모든 미해결 트랜잭션이 롤백됩니다.

일괄 처리에서 제약 조건 위반 등 런타임 문 오류가 발생하면 데이터베이스 엔진에서는 기본적으로 오류를 발생시킨 문만 롤백합니다. 이 동작은 SET XACT_ABORT ON 문을 사용하여 변경할 수 있습니다. SET XACT_ABORT ON이 실행된 후에는 모든 런타임 문 오류 발생 시 자동으로 현재 트랜잭션이 롤백됩니다. 구문 오류와 같은 컴파일 오류는 SET XACT_ABORT의 영향을 받지 않습니다. 자세한 내용은 SET XACT_ABORT(Transact-SQL)를 참조하세요.

오류가 발생하면 적절한 동작(COMMIT 또는 ROLLBACK)을 애플리케이션 코드에 포함해야 합니다. 트랜잭션의 오류를 포함하여 오류를 처리하기 위한 한 가지 효과적인 도구는 Transact-SQL TRY...CATCH 구문입니다. 트랜잭션을 포함하는 예제에 대한 자세한 내용은 TRY...CATCH(Transact-SQL)를 참조하세요. SQL Server 2012(11.x)부터 THROW 문을 사용하여 예외를 발생시키고 실행을 TRY...CATCH 구문의 CATCH 블록으로 전송할 수 있습니다. 자세한 내용은 THROW(Transact-SQL)를 참조하세요.

자동 커밋 모드에서 컴파일 오류 및 런타임 오류

자동 커밋 모드에서는 데이터베이스 엔진 인스턴스가 하나의 SQL 문 대신 전체 일괄 처리를 롤백한 것처럼 표시되는 경우가 있습니다. 발생한 오류가 런타임 오류가 아니라 컴파일 오류인 경우 발생합니다. 컴파일 오류가 발생하면 데이터베이스 엔진에서 실행 계획을 작성할 수 없으므로 일괄 처리가 실행되지 않을 수 있습니다. 오류를 생성한 문 이전의 모든 문이 롤백되는 것처럼 보이지만 오류가 발생하면 일괄 처리의 모든 문이 실행되지 않습니다. 다음 예제에서는 컴파일 오류로 인해 세 번째 일괄 처리의 INSERT 문이 실행되지 않습니다. 처음 두 INSERT 문은 실행되지 않을 때 롤백되는 것으로 보입니다.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

다음 예제에서 세 번째 INSERT 문은 런타임 중복 기본 키 오류를 생성합니다. 처음 두 INSERT 문은 성공하고 커밋되므로 런타임 오류 후에도 유지됩니다.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

데이터베이스 엔진이 지연된 이름 확인을 사용하며, 이 경우 컴파일 시간이 아니라 실행 시간까지 개체 이름이 확인됩니다. 다음 예제에서는 처음 두 INSERT 문이 실행되고 커밋되며, 세 번째 INSERT 문이 존재하지 않는 테이블을 참조하여 런타임 오류를 생성한 후에도 해당 두 개의 행이 TestBatch 테이블에 남아 있습니다.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

잠금 및 행 버전 관리 기본 사항

데이터베이스 엔진에서는 여러 사용자가 동시에 데이터를 액세스하는 경우 다음 메커니즘을 사용하여 트랜잭션의 무결성을 확인하고 데이터베이스의 일관성을 유지합니다.

• 잠금

  각 트랜잭션은 해당 트랜잭션이 종속되는 행, 페이지 또는 테이블 등의 리소스에 대해 서로 다른 유형의 잠금을 요청합니다. 잠금은 다른 트랜잭션의 리소스 수정을 차단하여 잠금을 요청하는 트랜잭션에 문제가 발생하지 않도록 합니다. 각 트랜잭션은 잠긴 리소스에 대한 종속성이 더 이상 없을 때 잠금을 해제합니다.

• 행 버전 관리

  행 버전 관리 기반 격리 수준을 사용하면 데이터베이스 엔진은 수정된 각 행의 버전을 유지 관리합니다. 애플리케이션은 트랜잭션이 잠금으로 모든 읽기를 보호하는 대신 트랜잭션이 행 버전을 사용하여 트랜잭션 또는 문의 시작 부분에 있는 것처럼 데이터를 보도록 지정할 수 있습니다. 행 버전 관리를 사용하면 읽기 작업이 다른 트랜잭션을 차단할 가능성이 크게 줄어듭니다.

잠금 및 행 버전 관리는 사용자가 커밋되지 않은 데이터를 읽을 수 없도록 하고 여러 사용자가 동일한 데이터를 동시에 변경하지 못하도록 합니다. 잠금 또는 행 버전 관리 없이 해당 데이터에 대해 실행된 쿼리는 데이터베이스에서 아직 커밋되지 않은 데이터를 반환하여 예기치 않은 결과를 생성할 수 있습니다.

애플리케이션은 트랜잭션 격리 수준을 선택할 수 있습니다. 이 수준은 다른 트랜잭션에서 수정한 내용으로부터 트랜잭션에 대한 보호 수준을 정의합니다. 개별 Transact-SQL 문에 대해 테이블 수준 힌트를 지정하여 애플리케이션의 요구 사항에 맞게 동작을 추가로 조정할 수 있습니다.

동시 데이터 액세스 관리

같은 시간에 리소스에 액세스하는 사용자는 리소스에 동시에 액세스하고 있다고 합니다. 동시 데이터 액세스에는 여러 사용자가 다른 사용자가 적극적으로 사용하는 리소스를 수정하려고 할 때 부작용을 방지하는 메커니즘이 필요합니다.

동시성 효과

사용자가 데이터를 수정하면 동시에 같은 데이터를 읽거나 수정 중인 다른 사용자에게 영향을 미칠 수 있습니다. 이러한 사용자는 동시에 데이터에 액세스하고 있다고 합니다. 데이터베이스에 동시 실행 제어가 없는 경우 사용자에게 다음과 같은 부작용이 발생할 수 있습니다.

• 업데이트 손실

  두 개 이상의 트랜잭션이 동일한 행을 선택한 다음 원래 선택한 값에 따라 행을 업데이트할 때 업데이트 손실이 발생합니다. 각 트랜잭션은 다른 트랜잭션을 인식하지 못합니다. 마지막 업데이트는 다른 트랜잭션에서 수행한 업데이트를 덮어쓰며, 이로 인해 데이터가 손실됩니다.

  예를 들어 두 명의 편집자가 같은 문서를 복사한다고 가정합니다. 각 편집자가 각자 복사본을 변경한 다음 변경된 복사본을 저장하면 원본 문서를 덮어쓰게 됩니다. 마지막으로 변경된 복사본을 저장한 편집기는 다른 편집기에서 변경한 내용을 덮어씁니다. 다른 편집기가 트랜잭션을 완료하고 커밋할 때까지 한 편집기가 파일에 액세스할 수 없는 경우 이 문제를 방지할 수 있습니다.

• 커밋되지 않은 종속성(더티 읽기)

  커밋되지 않은 종속성은 다른 트랜잭션이 업데이트 중인 행을 두 번째 트랜잭션이 읽을 때 발생합니다. 두 번째 트랜잭션은 아직 커밋되지 않았으며 행을 업데이트하는 트랜잭션에 의해 변경될 수 있는 데이터를 읽는 것입니다.

  예를 들어 편집기는 전자 문서를 변경합니다. 변경하는 동안 다른 편집자가 그 시점까지 변경된 내용이 모두 포함된 문서를 복사한 다음 문서를 배포합니다. 그런 다음 첫 번째 편집기에서 지금까지 수행한 변경 내용이 잘못됨을 결정하고 편집 내용을 제거하고 문서를 저장합니다. 이 경우 배포된 문서에는 더 이상 존재하지 않으며 무시해야 하는 내용이 포함되어 있습니다. 첫 번째 편집기가 수정 내용을 최종 저장하고 트랜잭션을 커밋할 때까지 아무도 변경된 문서를 읽을 수 없는 경우 이 문제를 방지할 수 있습니다.

• 일관성 없는 분석(반복하지 않는 읽기)

  일관성 없는 분석은 두 번째 트랜잭션이 같은 행에 여러 번 액세스하며 이때마다 다른 데이터를 읽을 경우 발생합니다. 일관성 없는 분석은 다른 트랜잭션이 두 번째 트랜잭션이 읽고 있는 데이터를 변경하고 있다는 측면에서 커밋되지 않은 종속성과 유사합니다. 그러나 일관성 없는 분석에서 두 번째 트랜잭션에서 읽은 데이터는 변경된 트랜잭션에 의해 커밋되었습니다. 또한 일관성 없는 분석에는 동일한 행의 여러 읽기(둘 이상)와 다른 트랜잭션에 의해 정보가 변경될 때마다 포함됩니다. 따라서 반복되지 않는 읽기라고 합니다.

  예를 들어 편집기는 동일한 문서를 두 번 읽지만 각 읽기 사이에는 작성기가 문서를 다시 작성합니다. 편집기가 문서를 두 번째로 읽는 경우 문서가 변경되었습니다. 원래의 읽기는 반복되지 않습니다. 편집기에서 마지막으로 문서를 읽을 때까지 작성자가 문서를 변경할 수 없는 경우 이 문제를 방지할 수 있습니다.

• 가상 읽기

  가상 읽기는 두 개의 동일한 쿼리가 실행되고 두 번째 쿼리에서 반환된 행의 집합이 다를 때 발생하는 상황입니다. 아래 예제에서는 이러한 상황이 어떻게 발생할 수 있는지 보여줍니다. 아래의 두 트랜잭션이 동시에 실행되고 있다고 가정합니다. 첫 번째 트랜잭션의 두 SELECT 문은 두 번째 트랜잭션의 INSERT 문이 둘 다 사용하는 데이터를 변경하기 때문에 다른 결과를 반환할 수 있습니다.

  --Transaction 1
  BEGIN TRAN;
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 AND ID < 10;
  
  --The INSERT statement from the second transaction occurs here.
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee (Id, Name)
  VALUES(6 ,'New');
  
  COMMIT;
  

• 행 업데이트로 인한 누락 및 이중 읽기

  • 업데이트된 행이 없거나 업데이트된 행이 여러 번 표시됨

    READ UNCOMMITTED 수준(또는 NOLOCK 테이블 힌트를 사용하는 문)에서 실행 중인 트랜잭션은 현재 트랜잭션에서 읽은 데이터를 다른 트랜잭션에서 수정하지 못하도록 하는 공유 잠금을 실행하지 않습니다. READ COMMITTED 수준에서 실행되는 트랜잭션은 공유 잠금을 실행하지만 행을 읽은 후 행 또는 페이지 잠금이 해제됩니다. 어떤 경우든 인덱스를 검색할 때 사용자가 읽기 작업을 수행하는 동안 다른 사용자가 행의 인덱스 키 열을 변경하면 키 변경으로 인해 사용자가 아직 검색하지 않은 위치로 행이 이동될 경우 해당 행이 다시 나타날 수 있습니다. 마찬가지로 키 변경으로 인해 사용자가 이미 읽은 인덱스 위치로 행이 이동될 경우 해당 행이 아예 읽히지 않을 수 있습니다. 이 문제를 방지하려면 SERIALIZABLE 또는 HOLDLOCK 힌트 또는 행 버전 관리를 사용합니다. 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하세요.

  • 업데이트 대상이 아닌 하나 이상의 행 누락

    READ UNCOMMITTED를 사용하는경우 쿼리가 할당 순서 검색(IAM 페이지 사용)을 사용하여 행을 읽는 경우 다른 트랜잭션이 페이지 분할을 일으키는 경우 행을 놓칠 수 있습니다. READ COMMITTED 격리 수준을 사용할 때는 이 문제가 발생하지 않습니다.

동시성 유형

여러 트랜잭션이 동시에 데이터베이스의 데이터를 수정하려고 할 때 한 트랜잭션이 수정한 내용이 다른 트랜잭션의 데이터에 부정적인 영향을 미치지 않도록 컨트롤 시스템을 구현해야 합니다. 이를 동시성 제어라고 합니다.

동시성 제어 이론에는 동시성 제어를 도입하는 방법에 대한 두 가지 분류가 있습니다.

• 비관적 동시성 제어

  잠금 시스템을 사용하면 트랜잭션이 다른 트랜잭션에게 영향을 주는 방식으로 데이터를 수정할 수 없습니다. 한 트랜잭션이 잠금을 유발하는 동작을 수행하면 다른 트랜잭션은 이 소유자가 잠금을 해제할 때까지 해당 잠금과 충돌하는 동작을 수행할 수 없습니다. 동시성 충돌이 발생하는 경우 트랜잭션 롤백에 필요한 비용보다 잠금을 통해 데이터를 보호하는 비용이 적게 드는 데이터 경합이 높은 환경에서 주로 사용되기 때문에 비관적 제어라고 합니다.

• 낙관적 동시 실행 제어

  낙관적 동시 실행 제어의 트랜잭션은 특정 데이터를 읽을 때 데이터를 잠그지 않습니다. 하지만 트랜잭션이 데이터를 업데이트할 때는 다른 트랜잭션이 해당 데이터를 읽은 후 변경하지 않았는지 검사가 진행됩니다. 다른 트랜잭션이 데이터를 업데이트하면 오류가 발생합니다. 일반적으로 오류를 수신하는 트랜잭션은 롤백하고 다시 시작합니다. 데이터에 대한 경합이 낮고 가끔 트랜잭션을 롤백하는 비용이 읽을 때 데이터 잠금 비용보다 낮은 환경에서 주로 사용되기 때문에 낙관적이라고 합니다.

데이터베이스 엔진 동시 실행 제어 메서드를 모두 지원합니다. 사용자는 연결에 대한 트랜잭션 격리 수준 또는 커서에 대한 동시성 옵션을 선택하여 동시성 제어 유형을 지정하게 됩니다. 이러한 특성은 Transact-SQL 문을 사용하거나 ADO, ADO.NET, OLE DB, ODBC 등의 데이터베이스 API(애플리케이션 프로그래밍 인터페이스)의 속성과 특성을 통해 정의할 수 있습니다.

데이터베이스 엔진의 격리 수준

트랜잭션은 한 트랜잭션이 다른 트랜잭션에 의해 수정되지 않도록 리소스 또는 데이터로부터 격리해야 하는 정도를 정의하는 격리 수준을 지정합니다. 격리 수준은 허용되는 동시성 부작용(예: 커밋되지 않은 읽기 또는 가상 읽기)의 관점에서 설명됩니다.

트랜잭션 격리 수준으로 제어할 수 있는 사항은 다음과 같습니다.

• 데이터를 읽을 때 잠금이 획득 여부, 요청되는 잠금 유형.
• 읽기 잠금이 유지되는 기간.
• 읽기 작업이 다른 트랜잭션에서 수정한 행을 참조할 경우 선택할 수 있는 옵션은 다음과 같습니다.
  • 행의 배타적 잠금이 해제될 때까지 차단
  • 문 또는 트랜잭션이 시작될 때 존재했던 행의 커밋된 버전 검색
  • 커밋되지 않은 데이터 수정 내용 읽기

격리 수준이 낮을수록 동시에 데이터를 액세스할 수 있는 트랜잭션이 많아지지만 동시 실행 부작용(예: 커밋되지 않은 읽기 또는 업데이트 손실) 트랜잭션 횟수도 늘어납니다. 반대로 격리 수준이 높을수록 트랜잭션이 마주칠 동시 실행 부작용 종류가 줄어들지만 시스템 리소스가 더 많이 필요하게 되고 한 트랜잭션이 다른 트랜잭션을 차단하게 될 확률도 높아집니다. 적절한 격리 수준을 선택하는 것은 각 격리 수준의 오버헤드와 응용 프로그램의 데이터 무결성 요구 사항 사이의 균형을 맞추는 데 달려 있습니다. 가능한 가장 높은 격리 수준인 SERIALIZABLE의 경우 트랜잭션이 읽기 작업을 반복할 때마다 정확히 동일한 데이터를 검색하도록 보장하지만 다중 사용자 시스템의 다른 트랜잭션에게 영향을 줄 수 있는 잠금 수준을 수행하여 이 작업을 수행합니다. 최하위 격리 수준인 READ UNCOMMITTED의 경우 다른 트랜잭션에서 수정했지만 커밋되지 않은 데이터를 검색할 수 있습니다. READ UNCOMMITTED에서는 모든 동시 실행 부작용이 발생할 수 있지만 읽기 잠금이나 버전 관리가 수행되지 않으므로 오버헤드가 최소화됩니다.

데이터베이스 엔진 격리 수준

ISO 표준은 데이터베이스 엔진이 지원하는 다음과 같은 격리 수준을 정의합니다.

격리 수준	정의
READ UNCOMMITTED	트랜잭션이 물리적으로 일치하지 않는 데이터를 읽지 않도록 충분히 격리되는 가장 낮은 격리 수준입니다. 이 수준에서는 더티 읽기가 허용되므로 한 트랜잭션에서 변경한 아직 커밋되지 않은 내용을 다른 트랜잭션에서 볼 수 있습니다.
READ COMMITTED	트랜잭션에서는 처음 트랜잭션이 완료될 때까지 기다리지 않고 다른 트랜잭션에서 이전에 읽은 수정되지 않은 데이터를 읽을 수 있습니다. 데이터베이스 엔진은 트랜잭션이 끝날 때까지 쓰기 잠금(선택한 데이터에 대해 획득)을 유지하지만 읽기 작업이 수행되는 즉시 읽기 잠금이 해제됩니다. 이 수준이 데이터베이스 엔진 기본 수준입니다.
REPEATABLE READ	데이터베이스 엔진에서는 트랜잭션이 끝날 때까지 일부 데이터에서 획득되는 읽기 잠금 및 쓰기 잠금이 유지됩니다. 그러나 범위 잠금은 관리되지 않으므로 가상 읽기가 발생할 수 있습니다.
SERIALIZABLE	트랜잭션이 서로 완전히 격리되는 가장 높은 수준입니다. 데이터베이스 엔진에서는 트랜잭션이 끝날 때까지 일부 데이터에서 획득되는 읽기 잠금 및 쓰기 잠금이 유지됩니다. 범위 잠금은 가상 읽기를 방지하기 위해 SELECT 작업에서 범위 WHERE 절을 사용할 때 획득됩니다. 참고: SERIALIZABLE 격리 수준이 요청되면 복제된 테이블의 DDL 작업 및 트랜잭션이 실패할 수 있습니다. 복제 쿼리는 SERIALIZABLE 격리 수준과 호환되지 않는 힌트를 사용하기 때문입니다.

또한 데이터베이스 엔진은 행 버전 관리를 사용하는 두 개의 추가 트랜잭션 격리 수준을 지원합니다. 하나는 READ COMMITTED 격리 수준의 구현이고, 하나는 SNAPSHOT 트랜잭션 격리 수준입니다.

행 버전 관리 격리 수준	정의
Read Committed Snapshot (RCSI)	Azure SQL 데이터베이스의 기본 설정인 READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON으로 설정되면 READ COMMITTED 격리 수준은 행 버전 관리 기능을 사용하여 문 수준 읽기 일관성을 제공합니다. 읽기 작업에는 스키마 안정성(Sch-S) 테이블 수준 잠금만 필요하고 페이지 또는 행 잠금은 필요하지 않습니다. 데이터베이스 엔진은 행 버전 관리를 사용하여 명령문 시작 시와 트랜잭션별로 데이터의 일관성이 유지된 스냅샷을 각 명령문에 제공합니다. 다른 트랜잭션에 의한 데이터 업데이트 차단을 위해 잠금이 사용되지는 않습니다. 사용자 정의 함수는 UDF를 포함하는 구문 시간이 시작된 후에 커밋된 데이터를 반환할 수 있습니다. READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 SQL Server 및 Azure SQL Managed Instance의 기본 설정인 OFF로 설정되어 있으면 READ COMMITTED 격리는 공유 잠금을 사용하여 현재 트랜잭션이 읽기 작업을 실행하는 동안 다른 트랜잭션이 행을 수정하지 못하도록 합니다. 또한 공유 잠금은 다른 트랜잭션이 완료될 때까지 해당 트랜잭션이 수정한 행을 문이 읽을 수 없도록 합니다. 두 구현 모두 READ COMMITTED 격리에 대한 ISO 정의를 충족합니다.
SNAPSHOT	스냅샷 격리 수준은 행 버전 관리를 통해 트랜잭션 수준의 읽기 일관성을 제공합니다. 읽기 작업은 페이지 또는 행 잠금을 획득하지 않습니다. Sch-S(스키마 안정성) 테이블 잠금만 획득됩니다. 다른 트랜잭션에서 수정한 행을 읽을 때 읽기 작업은 트랜잭션이 시작될 때 존재했던 행의 버전을 검색합니다. ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션이 ON으로 설정된 경우에만 SNAPSHOT 격리를 사용할 수 있습니다. 기본적으로 이 옵션은 SQL Server 및 Azure SQL Managed Instance의 사용자 데이터베이스에 대해 OFF로 설정되고 Azure SQL 데이터베이스의 데이터베이스에 대해 ON으로 설정됩니다. 참고: 데이터베이스 엔진은 메타데이터 버전 관리를 지원하지 않습니다. 따라서 스냅샷 격리에서 실행되는 명시적 트랜잭션 내에서 수행할 수 있는 DDL 작업에 대한 제한 사항이 있습니다. 다음 DDL 문, ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME 또는 CLR(공용 언어 런타임)은 BEGIN TRANSACTION 문 이후 스냅샷 격리에서 허용되지 않습니다. 이러한 명령문은 암시적 트랜잭션 내에서 스냅샷 격리를 사용하는 경우 허용됩니다. 암시적 트랜잭션은, 정의상, DDL 문과 함께 스냅샷 격리의 의미 체계를 적용할 수 있는 단일 명령문입니다. 이 원칙을 위반하면 오류 3961이 발생할 수 있습니다. Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

다음 테이블에서는 서로 다른 격리 수준에서 지원되는 동시성 부작용을 보여줍니다.

격리 수준	더티 읽기	반복되지 않는 읽기	팬텀
READ UNCOMMITTED	예	예	예
READ COMMITTED	아니요	예	예
REPEATABLE READ	아니요	아니요	예
SNAPSHOT	아니요	아니요	아니요
SERIALIZABLE	아니요	아니요	아니요

각 트랜잭션 격리 수준에서 제어하는 특정 종류의 잠금 또는 행 버전 관리에 대한 자세한 내용은 SET TRANSACTION ISOLATION LEVEL(Transact-SQL)을 참조하세요.

트랜잭션 격리 수준은 Transact-SQL이나 데이터베이스 API를 통해 설정할 수 있습니다.

Transact-SQL
Transact-SQL 스크립트는 SET TRANSACTION ISOLATION LEVEL 문을 사용합니다.

ADO
ADO 애플리케이션은 Connection 개체의 IsolationLevel 속성을 adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead 또는 adXactReadSerializable로 설정합니다.

ADO.NET
System.Data.SqlClient이라는 관리되는 네임스페이스를 사용하는 ADO.NET 애플리케이션은 SqlConnection.BeginTransaction 메서드를 호출하고 IsolationLevel 옵션을 Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable 또는 Snapshot로 설정할 수 있습니다.

OLE DB
트랜잭션을 시작할 때 OLE DB를 사용하는 애플리케이션은 isoLevel가 ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT 또는 ISOLATIONLEVEL_SERIALIZABLE로 설정된 상태에서 ITransactionLocal::StartTransaction을 호출합니다.

자동 커밋 모드에서 트랜잭션 격리 수준을 지정할 때 OLE DB 애플리케이션은 DBPROPSET_SESSION 속성 DBPROP_SESS_AUTOCOMMITISOLEVELS를 DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED 또는 DBPROPVAL_TI_SNAPSHOT로 설정할 수 있습니다.

ODBC
ODBC 애플리케이션이 Attribute가 SQL_ATTR_TXN_ISOLATION로 설정되고 ValuePtr가 SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ 또는 SQL_TXN_SERIALIZABLE로 설정된 상태에서 SQLSetConnectAttr을 호출합니다.

스냅샷 트랜잭션의 경우 애플리케이션은 특성이 SQL_COPT_SS_TXN_ISOLATION로 설정되고 ValuePtr이 SQL_TXN_SS_SNAPSHOT 로 설정된 상태에서 SQLSetConnectAttr을 호출합니다. 스냅샷 트랜잭션은 SQL_COPT_SS_TXN_ISOLATION 또는 SQL_ATTR_TXN_ISOLATION 중 하나를 사용하여 검색할 수 있습니다.

데이터베이스 엔진에서의 잠금

잠금은 데이터베이스 엔진에서 사용하는 메커니즘으로 동시에 여러 사용자가 동일한 데이터에 액세스하는 것을 동기화합니다.

특정 트랜잭션이 데이터 읽기나 수정 등을 통해 현재 데이터 상태에 종속되기 전에 동일한 데이터를 수정하는 다른 트랜잭션의 영향을 받지 못하도록 해당 트랜잭션을 보호해야 합니다. 트랜잭션은 데이터에 대한 잠금을 요청하여 자체 트랜잭션을 보호합니다. 잠금에는 공유(S) 또는 배타적(X)과 같은 다른 모드가 있습니다. 잠금 모드는 트랜잭션의 데이터에 대한 종속성 수준을 정의합니다. 해당 데이터에 대해 이미 다른 트랜잭션에 허용된 잠금 모드와 충돌되는 잠금은 이 트랜잭션에 허용될 수 없습니다. 트랜잭션이 동일한 데이터에 이미 부여된 잠금과 충돌하는 잠금 모드를 요청하는 경우 데이터베이스 엔진은 첫 번째 잠금이 해제될 때까지 요청 트랜잭션을 일시 중지합니다.

트랜잭션이 데이터 조각을 수정하는 경우 트랜잭션이 끝날 때까지 수정을 보호하는 특정 잠금이 유지됩니다. 읽기 작업을 보호하기 위해 획득한 잠금을 트랜잭션에서 지속하는 기간은 트랜잭션 격리 수준 설정과 최적화된 잠금의 사용 여부에 따라 달라집니다.

• 최적화된 잠금을 사용하도록 설정하지 않으면 쓰기에 필요한 행 및 페이지 잠금이 트랜잭션이 끝날 때까지 유지됩니다.

• 최적화된 잠금을 사용하도록 설정하면 트랜잭션 종료까지 TID(트랜잭션 ID) 잠금만 유지됩니다. 기본 READ COMMITTED 격리 수준에서 트랜잭션은 트랜잭션이 끝날 때까지 쓰기에 필요한 행 및 페이지 잠금을 보유하지 않습니다. 이렇게 하면 필요한 잠금 메모리가 줄어들고 잠금 에스컬레이션의 필요성이 줄어듭니다. 또한 최적화된 잠금을 사용할 경우 LAQ(자격 증명 후 잠금) 최적화는 잠금을 획득하지 않고 행의 커밋된 최신 버전에 대한 쿼리 조건자를 평가하여 동시 실행을 개선합니다.

트랜잭션을 통해 지속되는 모든 잠금은 트랜잭션이 완료되어 커밋되거나 롤백될 때 해제됩니다.

일반적으로 애플리케이션은 잠금을 직접 요청하지 않습니다. 잠금은 잠금 관리자라고 하는 데이터베이스 엔진의 일부를 통해 내부적으로 관리됩니다. 데이터베이스 엔진 인스턴스가 Transact-SQL 문을 처리하는 경우 데이터베이스 엔진 쿼리 프로세서는 액세스할 리소스를 결정합니다. 쿼리 프로세서는 액세스 유형과 트랜잭션 격리 수준 설정에 따라 각 리소스를 보호하는 데 필요한 잠금 유형을 결정합니다. 그런 다음 쿼리 프로세서는 잠금 관리자에서 적절한 잠금을 요청합니다. 잠금 관리자는 다른 트랜잭션에서 보유하는 충돌하는 잠금이 없는 경우 잠금을 부여합니다.

잠금 세분성 및 계층

데이터베이스 엔진에는 트랜잭션에 의해 다양한 유형의 리소스를 잠글 수 있는 다단계 잠금이 있습니다. 잠금 비용을 최소화하기 위해 데이터베이스 엔진은 작업에 적합한 수준에서 리소스를 자동으로 잠급니다. 행과 같은 더 작은 세분성으로 잠금하면 동시성이 증가하지만 많은 행이 잠겨 있는 경우 더 많은 잠금을 유지해야 하므로 오버헤드가 더 높습니다. 테이블과 같은 더 큰 세분성으로 잠그면 전체 테이블을 잠글 경우 테이블의 모든 부분에 대한 다른 트랜잭션의 액세스가 제한되기 때문에 동시성 측면에서 비용이 많이 듭니다. 그러나 유지 관리되는 잠금 수가 적기 때문에 오버헤드가 낮습니다.

데이터베이스 엔진에서는 리소스를 완전히 보호하기 위해 여러 수준의 세분성에서 잠금을 획득해야 하는 경우가 많습니다. 이러한 여러 수준의 세분성 잠금 그룹을 잠금 계층 구조라고 합니다. 예를 들어 인덱스의 읽기를 완전히 보호하려면 데이터베이스 엔진 인스턴스가 행에 대한 공유 잠금과 페이지 및 테이블의 의도 공유 잠금을 획득해야 할 수 있습니다.

다음 표에서는 데이터베이스 엔진이 잠글 수 있는 리소스를 보여줍니다.

리소스	설명
RID	힙 내에서 단일 행을 잠그는 데 사용되는 행 식별자입니다.
KEY	B-트리 인덱스에서 단일 행을 잠그는 행 잠금입니다.
PAGE	데이터 또는 인덱스 페이지와 같은 데이터베이스의 8KB 페이지입니다.
EXTENT	데이터 또는 인덱스 페이지와 같은 8페이지로 구성된 연속 그룹입니다.
HoBT 1	힙 또는 B-트리입니다. 클러스터형 인덱스가 없는 테이블에서 힙 데이터 페이지나 B-트리(인덱스)를 보호하는 잠금입니다.
TABLE 1	모든 데이터와 인덱스를 포함한 전체 테이블입니다.
FILE	데이터베이스 파일입니다.
APPLICATION	애플리케이션이 지정한 리소스입니다.
METADATA	메타데이터 잠금입니다.
ALLOCATION_UNIT	할당 단위입니다.
DATABASE	전체 데이터베이스입니다.
XACT 2	최적화된 잠금에서 사용하는 TID(트랜잭션 ID) 잠금입니다. 자세한 내용은 TID(트랜잭션 ID) 잠금을 참조하세요.

¹ HoBT 및 TABLE 잠금은 ALTER TABLE의 LOCK_ESCALATION 옵션에 의해 영향을 받을 수 있습니다.

² XACT 잠금 리소스에 대해 추가 잠금 리소스를 사용할 수 있습니다. 최적화된 잠금에 대한 진단 추가를 참조하세요.

잠금 모드

데이터베이스 엔진은 동시 트랜잭션을 통해 리소스에 액세스하는 방법을 결정하는 다양한 잠금 모드를 사용하여 리소스를 잠급니다.

다음 표에서는 데이터베이스 엔진에서 사용하는 리소스 잠금 모드를 보여줍니다.

잠금 모드	설명
공유(S)	SELECT 문과 같이 데이터를 변경하거나 업데이트하지 않는 읽기 작업에 사용됩니다.
업데이트(U)	업데이트할 수 있는 리소스에 사용됩니다. 여러 세션이 나중에 리소스를 읽고 잠그고 잠재적으로 업데이트할 때 발생하는 일반적인 형태의 교착 상태를 방지합니다.
배타적(X)	INSERT, UPDATE 또는 DELETE와 같은 데이터 수정 작업에 사용합니다. 여러 개의 업데이트 작업이 같은 리소스에 대해 동시에 이루어지지 못하게 합니다.
의도	잠금 계층 구조를 설정하는 데 사용됩니다. 내재된 잠금의 종류에는 의도 공유(IS), 의도 배타적(IX), 의도 배타적 공유(SIX)이 있습니다.
스키마	테이블의 스키마에 종속된 작업이 실행 중일 때 사용됩니다. 스키마 잠금에는 스키마 수정(Sch-M)과 스키마 안정성(Sch-S) 잠금이 있습니다.
대량 업데이트(BU)	TABLOCK 힌트를 사용하여 데이터를 테이블로 대량 복사하는 경우 사용합니다.
키 범위	SERIALIZABLE 트랜잭션 격리 수준을 사용할 때 쿼리가 읽는 행 범위를 보호합니다. 쿼리가 다시 실행된 경우 다른 트랜잭션에서 SERIALIZABLE 트랜잭션의 쿼리에 적합한 행을 삽입할 수 없게 합니다.

공유 잠금

공유(S) 잠금을 사용하면 동시 트랜잭션이 비관적 동시성 제어 하에서 리소스를 읽을 수 있습니다. 공유(S) 잠금이 리소스에 존재하는 동안 다른 트랜잭션은 데이터를 수정할 수 없습니다. 트랜잭션 격리 수준을 REPEATABLE READ 이상으로 설정하거나 잠금 힌트를 사용하여 트랜잭션 기간에 대한 공유(S) 잠금을 보유하지 않는 한, 리소스에 대한 공유(S) 잠금은 읽기 작업이 완료되면 바로 해제됩니다.

업데이트 잠금

데이터베이스 엔진이 업데이트 실행을 준비할 때 업데이트(U) 잠금을 배치합니다. U 잠금은 S 잠금과 호환되지만 지정된 리소스에서 한 번에 하나의 트랜잭션만 U 잠금을 보유할 수 있습니다. 이것이 핵심입니다. 많은 동시 트랜잭션은 S 잠금을 보유할 수 있지만 하나의 트랜잭션만 리소스에 대한 U 잠금을 보유할 수 있습니다. 업데이트(U) 잠금은 결국 배타적(X) 잠금으로 업그레이드되어 행을 업데이트합니다.

UPDLOCK 테이블 힌트가 문에 지정된 경우 업데이트(U) 잠금은 UPDATE 명령문 이외의 문에서 수행할 수도 있습니다.

• 일부 애플리케이션은 읽기 및 쓰기가 트랜잭션 내에서 명시적으로 구분되는 "행 선택, 행 업데이트" 패턴을 사용합니다. 이 경우 격리 수준이 REPEATABLE READ이거나 SERIALIZABLE 경우 동시 업데이트로 인해 다음과 같이 교착 상태가 발생할 수 있습니다.

  트랜잭션은 데이터를 읽고, 리소스에 대한 공유(S) 잠금을 획득한 다음, 배타적(X) 잠금으로 잠금 변환이 필요한 데이터를 수정합니다. 두 트랜잭션이 리소스에 대해 공유(S) 잠금을 얻은 다음 데이터를 동시에 업데이트하려고 하면 한 트랜잭션이 배타적(X) 잠금으로 잠금을 변변환하려고 합니다. 한 트랜잭션의 배타적(X) 잠금은 다른 트랜잭션의 공유(S) 잠금과 호환되지 않으므로 공유-배타 잠금 변환에서 잠금 대기가 발생합니다. 두 번째 트랜잭션은 해당 업데이트에 대한 배타적(X) 잠금을 획득하려고 시도합니다. 두 트랜잭션 모두 배타적(X) 잠금으로 변환되고 서로 다른 트랜잭션이 공유(S) 잠금을 해제하기를 기다리고 있기 때문에 교착 상태가 발생합니다.

  기본 READ COMMITTED 격리 수준에서 S 잠금은 짧은 기간이며 사용되는 즉시 해제됩니다. 위에서 설명한 교착 상태는 여전히 가능하지만 짧은 기간 잠금으로는 가능성이 훨씬 적습니다.

  이러한 유형의 교착 상태를 방지하기 위해 애플리케이션은 "UPDLOCK 힌트가 있는 행 선택, 행 업데이트 패턴"을 따를 수 있습니다.

• UPDLOCK 힌트를 SNAPSHOT 격리가 사용 중일 때 쓰기에 사용하는 경우 트랜잭션은 최신 버전의 행에 액세스할 수 있어야 합니다. 최신 버전이 더 이상 표시되지 않는 경우 Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict를 수신할 수 있습니다. 예를 들어 스냅샷 격리 작업을 참조하세요.

배타적 잠금

배타적(X) 잠금은 동시 트랜잭션을 통해 리소스에 대한 액세스를 차단합니다. 배타적(X) 잠금을 사용하면 다른 트랜잭션이 잠금으로 보호되는 해당 데이터를 수정할 수 없습니다. 읽기 작업은 NOLOCK 힌트 또는 READ UNCOMMITTED 격리 수준을 사용해서만 수행할 수 있습니다.

INSERT, UPDATE 및 DELETE와 같은 데이터 수정 문은 읽기와 수정 작업을 모두 결합합니다. 이 문은 먼저 필요한 수정 작업을 수행하기 전에 데이터를 가져오기 위해 읽기 작업을 수행합니다. 따라서 데이터 수정 문은 일반적으로 공유 잠금과 배타적 잠금을 모두 요청합니다. 예를 들어 UPDATE 문은 다른 테이블과의 조인을 기반으로 한 테이블의 행을 수정할 수 있습니다. 이 경우 UPDATE 문은 업데이트된 행에 대한 배타적 잠금을 요청하는 것 외에도 조인 테이블에서 읽은 행에 대한 공유 잠금을 요청합니다.

내재된 잠금

데이터베이스 진에서는 내재된 잠금을 사용하여 잠금 계층 구조 아래쪽에 있는 하위 수준 리소스에 설정되는 공유(S) 잠금 또는 배타적(X) 잠금을 보호합니다. 하위 수준의 잠금보다 먼저 확보되어 하위 수준에 잠금을 설정하려고 하는 의도를 나타내므로 "내재된 잠금"이라고 합니다.

내재된 잠금은 다음 두 가지 용도로 사용됩니다.

• 다른 트랜잭션이 상위 수준 리소스를 수정하여 하위 수준 잠금을 무효화하는 것을 방지합니다.
• 더 높은 수준의 세분성에서 잠금 충돌을 감지하는 데이터베이스 엔진의 효율성을 향상합니다.

예를 들어 공유 의도 잠금은 해당 테이블 내의 페이지 또는 행에서 공유(S) 잠금을 요청하기 전에 테이블 수준에서 요청됩니다. 테이블 수준에서 내재된 잠금을 설정하면 이후에 다른 트랜잭션이 해당 페이지를 포함하는 테이블에 대해 배타적(X) 잠금을 얻을 수 없습니다. 데이터베이스 엔진은 테이블 수준에서만 내재된 잠금을 검사하여 트랜잭션이 해당 테이블에 대한 잠금을 안전하게 획득할 수 있는지 확인하므로 내재된 잠금이 성능을 향상시킵니다. 이렇게 하면 트랜잭션이 전체 테이블을 잠글 수 있는지 확인하기 위해 테이블의 모든 행 또는 페이지 잠금을 검사해야 하는 요구 사항이 제거됩니다.

내재된 잠금에는 의도 공유(IS), 의도 배타적(IX), 의도 배타적 공유(SIX)이 있습니다.

잠금 모드	설명
의도 공유(IS)	계층 구조의 아래쪽에 있는 일부 리소스에 대해 요청되거나 확보된 공유 잠금을 보호합니다.
의도 배타적(IX)	계층 구조의 아래쪽에 있는 일부 리소스에 대해 요청되거나 확보된 배타적 잠금을 보호합니다. IX는 IS의 상위 집합이며 하위 수준 리소스에 대한 공유 잠금 요청도 보호합니다.
의도 배타적 공유(SIX)	계층 구조의 아래쪽에 있는 모든 리소스에 대해 요청되거나 확보된 공유 잠금과 하위 수준 리소스의 일부에 대한 의도 배타 잠금을 보호합니다. 최상위 리소스에서 동시 IS 잠금이 허용됩니다. 예를 들어 테이블에 대한 SIX 잠금을 확보하면 수정되는 페이지에 대한 의도 배타적 잠금 및 수정되는 행에 대한 배타적 잠금도 동시에 확보됩니다. 리소스당 한 번에 하나의 SIX 잠금을 설정할 수 있으므로 다른 트랜잭션이 테이블 수준에서 IS 잠금을 얻어 계층 구조 아래쪽에 있는 리소스를 읽을 수는 있어도 다른 트랜잭션이 리소스를 업데이트할 수는 없습니다.
의도 업데이트(IU)	계층 구조의 아래쪽에 있는 모든 리소스에 대해 요청되거나 확보된 업데이트 잠금을 보호합니다. IU 잠금은 페이지 리소스에만 사용됩니다. 업데이트 작업이 수행되면 IU 잠금이 IX 잠금으로 변환됩니다.
공유 의도 업데이트(SIU)	이러한 잠금을 개별적으로 획득하고 동시에 두 잠금을 모두 보유한 결과인 S 및 IU 잠금의 조합입니다. 예를 들어 트랜잭션은 PAGLOCK 힌트를 사용하여 쿼리를 실행한 다음 업데이트 작업을 실행합니다. PAGLOCK 힌트가 있는 쿼리는 S 잠금을 획득하고 업데이트 작업은 IU 잠금을 획득합니다.
업데이트 의도 배타적(UIX)	이러한 잠금을 개별적으로 획득하고 동시에 두 잠금을 모두 보유한 결과인 U 및 IX 잠금의 조합입니다.

스키마 잠금

데이터베이스 엔진에서는 열 추가 또는 테이블 삭제와 같은 테이블 DDL(데이터 정의 언어) 작업 중에 스키마 수정(Sch-M) 잠금을 사용합니다. 이 잠금이 유지되는 동안 Sch-M 잠금은 테이블에 대한 동시 액세스를 차단합니다. 즉, Sch-M 잠금은 잠금이 해제될 때까지 모든 외부 작업을 차단합니다.

테이블 잘림과 같은 일부 DML(데이터 조작 언어) 작업은 Sch-M 잠금을 사용하여 동시 작업으로 영향을 받는 테이블에 대한 액세스를 방지합니다.

데이터베이스 엔진 쿼리를 컴파일하고 실행할 때 스키마 안정성(Sch-S) 잠금을 사용합니다. Sch-S 잠금은 배타적(X) 잠금 등의 트랜잭션 잠금을 차단하지 않습니다. 따라서 테이블에 X 잠금이 있는 트랜잭션을 비롯한 다른 트랜잭션은 쿼리가 컴파일되는 동안 계속 실행됩니다. 그러나 Sch-M 잠금을 획득하는 동시 DDL 작업과 동시 DML 작업은Sch-S 잠금에 의해 차단됩니다.

대량 업데이트 잠금

대량 업데이트(BU) 잠금을 사용하면 여러 스레드가 데이터를 동시에 같은 테이블로 대량 로드하는 것은 허용하고, 데이터를 대량 로드하지 않는 다른 프로세스가 테이블에 액세스하는 것은 방지할 수 있습니다. 데이터베이스 엔진에서는 다음 조건이 모두 충족되면 대량 업데이트(BU) 잠금을 사용합니다.

• Transact-SQL BULK INSERT 문 또는 OPENROWSET(BULK) 함수를 사용하거나 .NETSqlBulkCopy, OLEDB 빠른 로드 API 또는 ODBC 대량 복사 API와 같은 Bulk Insert API 명령 중 하나를 사용하여 데이터를 테이블에 대량 복사합니다.
• TABLOCK 힌트가 지정되거나 sp_tableoption을 사용하여 table lock on bulk load 테이블 옵션이 설정됩니다.

키 범위 잠금

키 범위 잠금은 SERIALIZABLE 트랜잭션 격리 수준을 사용하는 동안 Transact-SQL 문에서 읽는 레코드 집합에 암시적으로 포함된 행 범위를 보호합니다. 키 범위 잠금은 가상 읽기를 방지합니다. 행 간의 키 범위를 보호하여 트랜잭션에서 액세스하는 레코드 집합에 대한 가상 삽입 또는 삭제를 방지합니다.

잠금 호환성

잠금 호환성에 따라 여러 트랜잭션이 동시에 같은 리소스에 대한 잠금을 획득할 수 있는지 여부가 결정됩니다. 리소스가 다른 트랜잭션에 의해 이미 잠겨 있는 경우 요청된 잠금 모드가 기존 잠금 모드와 호환되는 경우에만 새 잠금 요청을 부여할 수 있습니다. 요청된 잠금의 모드가 기존 잠금과 호환되지 않을 경우 새 잠금을 요청하는 트랜잭션은 기존 잠금이 해제되거나 잠금 시간 초과 간격이 만료될 때까지 기다립니다. 예를 들어 잠금 모드는 배타적 잠금과 호환되지 않습니다. 배타적(X) 잠금이 유지되는 동안 배타적(X) 잠금이 해제될 때까지 다른 트랜잭션은 해당 리소스에 대한 모든 종류의 잠금(공유, 업데이트 또는 배타적)을 획득할 수 없습니다. 반대로 리소스에 공유(S) 잠금이 적용된 경우에는 첫 번째 트랜잭션이 완료되지 않아도 다른 트랜잭션이 해당 리소스에 대해 공유 잠금 또는 업데이트(U) 잠금을 획득할 수 있습니다. 그러나 다른 트랜잭션은 공유 잠금이 해제될 때까지 배타적 잠금을 획득할 수 없습니다.

다음 표에서는 가장 일반적인 잠금 모드의 호환성을 보여줍니다.

기존 부여 모드	IS	S	U	IX	SIX	X
요청된 모드
의도 공유(IS)	예	예	예	예	예	아니요
공유(S)	예	예	예	아니요	아니요	아니요
업데이트(U)	예	예	아니요	아니요	아니요	아니요
의도 배타적(IX)	예	아니요	아니요	예	아니요	아니요
의도 배타적 공유(SIX)	예	아니요	아니요	아니요	아니요	아니요
배타적(X)	아니요	아니요	아니요	아니요	아니요	없음

다음 표를 사용하여 데이터베이스 엔진에서 사용할 수 있는 모든 잠금 모드의 호환성을 확인합니다.

키	설명
N	충돌 없음
I	Illegal
C	충돌
NL	잠금 없음
SCH-S	스키마 안정성 잠금
SCH-M	스키마 수정 잠금
S	공유
U	엽데이트
X	전용
IS	의도 공유
IU	의도 업데이트
IX	의도 배타적
SIU	의도 업데이트와 공유
SIX	의도 배타적 공유
UIX	의도 전용으로 업데이트
BU	대량 업데이트
RS-S	공유 범위 공유
RS-U	공유 범위 업데이트
RI-N	range-null 삽입
RI-S	범위 공유 삽입
RI-U	범위 업데이트 삽입
RI-X	범위 전용 삽입
RX-S	전용 범위 공유
RX-U	전용 범위 업데이트
RX-X	전용 범위 전용

키 범위 잠금

키 범위 잠금은 SERIALIZABLE 트랜잭션 격리 수준을 사용하는 동안 Transact-SQL 문에서 읽는 레코드 집합에 암시적으로 포함된 행 범위를 보호합니다. SERIALIZABLE 격리 수준에서는 트랜잭션 중 실행되는 모든 쿼리가 트랜잭션 중 실행될 때마다 동일한 행 집합을 가져와야 합니다. 키 범위 잠금은 키가 SERIALIZABLE 트랜잭션에서 읽은 키 범위에 속하는 새 행을 다른 트랜잭션이 삽입하지 못하도록 하여 이 요구 사항을 충족합니다.

키 범위 잠금은 가상 읽기를 방지합니다. 행 간의 키 범위를 보호하여 트랜잭션에서 액세스하는 레코드 집합에 대한 가상 삽입도 방지합니다.

키 범위 잠금은 시작 및 끝 키 값을 지정하여 인덱스 위에 배치됩니다. 이 잠금은 키 값이 해당 범위에 속하는 모든 행의 삽입, 업데이트 또는 삭제 시도를 차단합니다. 이는 이러한 작업을 수행하려면 먼저 인덱스에 대한 잠금을 획득해야 하기 때문입니다. 예를 들어 SERIALIZABLE 트랜잭션은 키 값이 BETWEEN 'AAA' AND 'CZZ' 조건과 일치하는 모든 행을 읽는 SELECT 문을 발행할 수 있습니다. 'AAA'에서 'CZZ'에 이르는 범위의 키 값에 대한 키 범위 잠금을 사용하면 다른 트랜잭션이 'ADG', 'BBD' 또는 'CAL'과 같은 해당 범위의 아무 곳에나 키 값이 있는 행을 삽입할 수 없습니다.

키 범위 잠금 모드

키 범위 잠금에는 범위 행 형식으로 지정된 범위 및 행 구성 요소가 모두 포함됩니다.

• 범위는 두 개의 연속 인덱스 항목 사이의 범위를 보호하는 잠금 모드를 나타냅니다.
• 행은 인덱스 항목을 보호하는 잠금 모드를 나타냅니다.
• 모드는 사용된 결합 잠금 모드를 나타냅니다. 키 범위 잠금 모드는 두 부분으로 구성됩니다. 첫 번째는 인덱스 범위(RangeT)를 잠그는 데 사용되는 잠금 유형을 나타내고 두 번째는 특정 키(K)를 잠그는 데 사용되는 잠금 형식을 나타냅니다. 두 부분은 T-K와 같이 하이픈(-)으로 연결됩니다.

범위	Row	모드	설명
RangeS	S	RangeS-S	공유 범위, 공유 리소스 잠금, SERIALIZABLE 범위 검색입니다.
RangeS	U	RangeS-U	공유 범위, 업데이트 리소스 잠금, SERIALIZABLE 업데이트 검색입니다.
RangeI	Null	RangeI-N	삽입 범위, null 리소스 잠금, 인덱스에 새 키를 삽입하기 전에 범위를 테스트하는 데 사용됩니다.
RangeX	X	RangeX-X	제외 범위, 배타적 리소스 잠금, 범위에서 키를 업데이트할 때 사용됩니다.

키 범위 잠금 모드에는 겹치는 키 및 범위에서 가져온 다른 잠금과 호환되는 잠금을 보여 주는 호환성 매트릭스가 있습니다.

기존 부여 모드	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
요청된 모드
공유(S)	예	예	아니요	예	예	예	아니요
업데이트(U)	예	아니요	아니요	예	아니요	예	아니요
배타적(X)	아니요	아니요	아니요	아니요	아니요	예	아니요
RangeS-S	예	예	아니요	예	예	아니요	아니요
RangeS-U	예	아니요	아니요	예	아니요	아니요	아니요
RangeI-N	예	예	예	아니요	아니요	예	아니요
RangeX-X	아니요	아니요	아니요	아니요	아니요	아니요	아니요

변환 잠금

변환 잠금은 키 범위 잠금이 다른 잠금과 겹칠 때 생성됩니다.

잠금 1	잠금 2	변환 잠금
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

변환 잠금은 다양한 복합 환경에서 짧은 시간 동안 나타날 수 있으며 때로는 동시 프로세스를 실행하는 동안에 나타납니다.

직렬화 가능 범위 검색, 싱글톤 페치, 삭제 및 삽입

키 범위 잠금을 통해 다음 작업을 직렬화할 수 있습니다.

• 범위 검색 쿼리
• 존재하지 않는 행의 singleton 페치
• 삭제 작업
• 삽입 작업

키 범위 잠금이 발생하기 전에 다음 조건을 충족해야 합니다.

• 트랜잭션 격리 수준은 SERIALIZABLE로 설정해야 합니다.
• 쿼리 프로세서가 인덱스를 사용하여 범위 필터 조건자를 구현해야 합니다. 예를 들어 SELECT 문의 WHERE 절은 다음 ColumnX BETWEEN N'AAA' AND N'CZZ' 조건자를 사용하여 범위 조건을 설정할 수 있습니다. 키 범위 잠금은 ColumnX가 인덱스 키로 적용되는 경우에만 획득할 수 있습니다.

예제

다음 표와 인덱스는 다음 키 범위 잠금 예제의 기초로 사용됩니다.

범위 검색 쿼리

범위 검색 쿼리를 직렬화하려면 동일한 쿼리가 동일한 트랜잭션 내에서 실행될 때마다 동일한 결과를 반환해야 합니다. 새 행은 다른 트랜잭션에 의해 범위 검색 쿼리 내에 삽입되어서는 안 됩니다. 그렇지 않으면 가상 삽입이 됩니다. 예를 들어 다음 쿼리는 이전 그림의 테이블과 인덱스를 사용합니다.

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

키 범위 잠금은 이름이 Adam과 Dale 값 사이에 있는 행 범위에 해당하는 인덱스 항목에 설정되어 앞의 쿼리에서 한정하는 새 행의 추가 또는 삭제를 방지합니다. 이 범위의 첫 번째 이름은 Adam이지만 이 인덱스 항목에 RangeS-S 모드 키 범위 잠금을 사용하면 Abigail과 같이 A로 시작하는 새 이름을 Adam 앞에 추가할 수 없습니다. 마찬가지로 Dale의 인덱스 항목에 RangeS-S 키 범위 잠금을 사용하면 Clive와 같이 C로 시작하는 새 이름을 Carlos 뒤에 추가할 수 없습니다.

존재하지 않는 데이터의 싱글톤 페치

트랜잭션 내의 쿼리가 존재하지 않는 행을 선택하려고 하는 경우 동일한 트랜잭션 내의 이후 지점에서 쿼리를 실행하려면 동일한 결과를 반환해야 합니다. 존재하지 않는 행을 삽입할 수 있는 다른 트랜잭션은 없습니다. 예를 들어 쿼리를 다음과 같이 지정합니다.

SELECT name
FROM mytable
WHERE name = 'Bill';

키 범위 잠금은 이름 Bill이 인접한 두 인덱스 항목 사이에 삽입되기 때문에 이름 범위 Ben ~ Bing에 해당하는 인덱스 항목에 배치됩니다. RangeS-S 모드 키 범위 잠금은 인덱스 항목 Bing에 적용됩니다. 이렇게 하면 다른 모든 트랜잭션이 인덱스 항목 Ben과 Bing 사이에서 Bill과 같은 값을 삽입할 수 없습니다.

최적화된 잠금 없이 작업 삭제

트랜잭션 내에서 행을 삭제할 때 행이 속하는 범위는 삭제 작업을 수행하는 트랜잭션 기간 동안 잠글 필요가 없습니다. 트랜잭션이 끝날 때까지 삭제된 키 값을 잠그면 직렬화 가능성을 유지하기에 충분합니다. 다음과 같은 DELETE 문을 예로 들 수 있습니다.

DELETE mytable
WHERE name = 'Bob';

배타적(X) 잠금은 이름 Bob에 해당하는 인덱스 항목에 배치됩니다. 다른 트랜잭션은 삭제하려는 Bob 값을 가진 행의 앞이나 뒤에 값을 삽입하거나 삭제할 수 있습니다. 그러나 값 Bob과 일치하는 행을 읽거나 삽입하거나 삭제하려는 트랜잭션은 삭제 트랜잭션이 커밋되거나 롤백될 때까지 차단됩니다. (READ_COMMITTED_SNAPSHOT 데이터베이스 옵션 및 SNAPSHOT 격리 수준은 이전에 커밋된 상태의 행 버전에서 읽기를 허용합니다.)

행, 페이지 또는 테이블 잠금의 세 가지 기본 잠금 모드를 사용하여 범위 삭제를 실행할 수 있습니다. 행, 페이지 또는 테이블 잠금 전략은 쿼리 최적화 프로그램에서 결정하거나 ROWLOCK, PAGLOCK 또는 TABLOCK과 같은 쿼리 최적화 프로그램 힌트를 통해 사용자가 지정할 수 있습니다. PAGLOCK 또는 TABLOCK을 사용하는 경우 이 페이지에서 모든 행이 삭제되면 SQL Server 데이터베이스 엔진은 즉시 인덱스 페이지 할당을 해제합니다. 반면 ROWLOCK을 사용하는 경우 삭제된 모든 행은 삭제된 행으로만 표시됩니다. 나중에 백그라운드 작업을 사용하여 인덱스 페이지에서 제거됩니다.

최적화된 잠금을 사용하여 작업 삭제

트랜잭션 내에서 행을 삭제하면 행 및 페이지 잠금이 증분 방식으로 획득 및 해제되며 트랜잭션 기간 동안 유지되지 않습니다. 다음과 같은 DELETE 문을 예로 들 수 있습니다.

DELETE mytable
WHERE name = 'Bob';

TID 잠금은 트랜잭션 기간 동안 수정된 모든 행에 배치됩니다. 값 Bob에 해당하는 인덱스 행의 TID에서 잠금을 획득합니다. 최적화된 잠금을 사용하면 업데이트에 대해 페이지 및 행 잠금이 계속 획득되지만 각 행이 업데이트되는 즉시 각 페이지 및 행 잠금이 해제됩니다. TID 잠금은 트랜잭션이 완료될 때까지 행이 업데이트되지 않도록 보호합니다. 값 Bob을 사용하는 행을 읽거나 삽입하거나 삭제하려는 트랜잭션은 삭제 트랜잭션이 커밋되거나 롤백될 때까지 차단됩니다. (READ_COMMITTED_SNAPSHOT 데이터베이스 옵션 및 SNAPSHOT 격리 수준은 이전에 커밋된 상태의 행 버전에서 읽기를 허용합니다.)

그렇지 않으면 삭제 작업의 잠금 메커니즘은 최적화된 잠금 없이도 동일합니다.

최적화된 잠금 없이 작업 삽입

트랜잭션 내에서 행을 삽입할 때 행이 속하는 범위는 삽입 작업을 수행하는 트랜잭션 기간 동안 잠글 필요가 없습니다. 트랜잭션이 끝날 때까지 삽입된 키 값을 잠그면 직렬화 가능성을 유지하기에 충분합니다. 다음과 같은 INSERT 문을 예로 들 수 있습니다.

INSERT mytable VALUES ('Dan');

RangeI-N 모드 키 범위 잠금은 범위를 테스트하기 위해 이름 David에 해당하는 인덱스 행에 배치됩니다. 잠금이 부여되면 Dan 값을 사용하는 행이 삽입되고 배타적(X) 잠금이 삽입된 행에 배치됩니다. RangeI-N 모드 키 범위 잠금은 범위를 테스트하는 데만 필요하며 삽입 작업을 수행하는 트랜잭션 기간 동안 유지되지 않습니다. 다른 트랜잭션은 Dan 값을 사용하는 삽입된 행 앞이나 뒤에 값을 삽입하거나 삭제할 수 있습니다. 그러나 Dan 값을 사용하는 행을 읽거나 삽입하거나 삭제하려는 트랜잭션은 삽입하는 트랜잭션이 커밋되거나 롤백될 때까지 차단됩니다.

최적화된 잠금을 사용하여 작업 삽입

트랜잭션 내에서 행을 삽입할 때 행이 속하는 범위는 삽입 작업을 수행하는 트랜잭션 기간 동안 잠글 필요가 없습니다. 온라인 인덱스 다시 작성이 진행 중이거나 동시 SERIALIZABLE 트랜잭션이 있는 경우에만 행 및 페이지 잠금을 거의 획득하지 않습니다. 행 및 페이지 잠금을 획득하면 신속하게 해제되고 트랜잭션 기간 동안 유지되지 않습니다. 트랜잭션이 끝날 때까지 삽입된 키 값에 배타적 TID 잠금을 배치하면 직렬화 가능성을 유지하기에 충분합니다. 다음과 같은 INSERT 문을 예로 들 수 있습니다.

INSERT mytable VALUES ('Dan');

최적화된 잠금을 사용하면 인스턴스에서 SERIALIZABLE 격리 수준을 사용하는 트랜잭션이 하나 이상 있는 경우에만 RangeI-N 잠금이 획득됩니다. RangeI-N 모드 키 범위 잠금은 범위를 테스트하기 위해 이름 David에 해당하는 인덱스 행에 배치됩니다. 잠금이 부여되면 Dan 값을 사용하는 행이 삽입되고 배타적(X) 잠금이 삽입된 행에 배치됩니다. RangeI-N 모드 키 범위 잠금은 범위를 테스트하는 데만 필요하며 삽입 작업을 수행하는 트랜잭션 기간 동안 유지되지 않습니다. 다른 트랜잭션은 Dan 값을 사용하는 삽입된 행 앞이나 뒤에 값을 삽입하거나 삭제할 수 있습니다. 그러나 Dan 값을 사용하는 행을 읽거나 삽입하거나 삭제하려는 트랜잭션은 삽입하는 트랜잭션이 커밋되거나 롤백될 때까지 차단됩니다.

잠금 에스컬레이션

잠금 에스컬레이션은 많은 세분화된 잠금을 더 적은 성긴 잠금으로 변환하여 동시성 경합의 가능성을 높이면서 시스템 오버헤드를 줄이는 프로세스입니다.

잠금 에스컬레이션은 최적화된 잠금이 사용되는지 여부에 따라 다르게 동작합니다.

최적화된 잠금 없이 잠금 에스컬레이션

데이터베이스 엔진에서 하위 수준 잠금을 획득하면 하위 수준 개체가 포함된 개체에 내재된 잠금도 배치됩니다.

• 행 또는 인덱스 키 범위를 잠그면 데이터베이스 엔진이 행 또는 키가 포함된 페이지에 내재된 잠금을 배치합니다.
• 페이지를 잠그면 데이터베이스 엔진이 페이지를 포함하는 상위 수준 개체에 내재된 잠금을 배치합니다. 개체에 대한 내재된 잠금 외에도 다음 개체에 대해 의도 페이지 잠금이 요청됩니다.
  • 비클러스터형 인덱스의 리프 수준 페이지
  • 클러스터형 인덱스의 데이터 페이지
  • 힙 데이터 페이지

데이터베이스 엔진에서 동일한 문에 대해 행 및 페이지 잠금을 모두 수행하여 잠금 수를 최소화하고 잠금 에스컬레이션이 필요할 가능성을 줄일 수 있습니다. 예를 들어 데이터베이스 엔진은 비클러스터형 인덱스에는 페이지 잠금을 배치(쿼리를 만족시키기 위해 인덱스 노드에서 충분히 인접한 키가 선택된 경우)하고 클러스터형 인덱스 또는 힙에는 행 잠금을 배치할 수 있습니다.

잠금을 에스컬레이션하기 위해 데이터베이스 엔진에서 테이블의 내재된 잠금을 해당하는 전체 잠금으로 변경하려고 시도합니다. 예를 들어 의도 배타적(IX) 잠금을 배타적(X) 잠금으로 변경하거나 의도 공유(IS) 잠금을 공유(S) 잠금으로 변경합니다. 잠금 에스컬레이션 시도가 성공하고 전체 테이블 잠금을 획득하면 힙 또는 인덱스의 트랜잭션에서 보유하는 모든 HoBT, 페이지(PAGE) 또는 행 수준(RID, KEY) 잠금이 해제됩니다. 전체 잠금을 획득할 수 없는 경우 해당 시간에 잠금 에스컬레이션이 발생하지 않으며 데이터베이스 엔진 행, 키 또는 페이지 잠금을 계속 획득합니다.

데이터베이스 엔진은 행 또는 키 범위 잠금을 페이지 잠금으로 에스컬레이션하지 않고 테이블 잠금으로 직접 에스컬레이션합니다. 마찬가지로 페이지 잠금은 항상 테이블 잠금으로 에스컬레이션됩니다. 분할된 테이블의 잠금은 테이블 잠금 대신 연결된 파티션에 대한 HoBT 수준으로 에스컬레이션될 수 있습니다. HoBT 수준 잠금이 파티션에 대해 정렬된 HoBT를 반드시 잠그는 것은 아닙니다.

동시 트랜잭션이 보유한 잠금의 충돌로 인해 잠금 에스컬레이션 시도가 실패하면 데이터베이스 엔진은 트랜잭션이 획득한 추가 1,250개의 잠금 각각에 대해 잠금 에스컬레이션을 다시 시도합니다.

각 에스컬레이션 이벤트는 주로 단일 Transact-SQL 문의 수준에서 작동합니다. 이벤트가 시작되면 데이터베이스 엔진이 에스컬레이션 임계값 요구 사항을 충족하는 경우 활성 문에서 참조한 테이블에서 현재 트랜잭션이 소유한 모든 잠금을 에스컬레이션하려고 시도합니다. 문이 테이블에 액세스하기 전에 에스컬레이션 이벤트가 시작되면 해당 테이블의 잠금을 에스컬레이션하려고 시도하지 않습니다. 잠금 에스컬레이션이 성공하면 테이블이 현재 문에서 참조되고 에스컬레이션 이벤트에 포함되는 경우 이전 문에서 트랜잭션이 획득하고 이벤트가 시작될 때 계속 유지되는 모든 잠금이 에스컬레이션됩니다.

예를 들어 세션에서 다음 작업을 수행한다고 가정합니다.

• 트랜잭션을 시작합니다.
• TableA를 업데이트합니다. 그러면 트랜잭션이 완료될 때까지 유지되는 TableA에서 배타적 행 잠금이 생성됩니다.
• TableB를 업데이트합니다. 그러면 트랜잭션이 완료될 때까지 유지되는 TableB에서 배타적 행 잠금이 생성됩니다.
• TableA에 TableC를 조인하는 SELECT를 수행합니다. 쿼리 실행 계획은 TableC에서 행을 검색하기 전에 TableA에서 검색할 행을 호출합니다.
• SELECT 문은 TableA에서 행을 검색하는 동안 TableC에 액세스하기 전에 잠금 에스컬레이션을 트리거합니다.

잠금 에스컬레이션이 성공하면 TableA의 세션에서 보유한 잠금만 에스컬레이션됩니다. 여기에는 SELECT 문의 공유 잠금과 이전 UPDATE 문의 배타적 잠금이 모두 포함됩니다. SELECT 문에 대해 TableA에서 획득한 세션의 잠금만 계산되어 잠금 에스컬레이션이 수행되어야 하는지 여부를 결정하지만, 에스컬레이션이 성공하면 TableA의 세션이 보유한 모든 잠금이 테이블의 배타적 잠금으로 에스컬레이션되고 TableA에서 내재된 잠금을 포함한 다른 모든 하위 세분성 잠금이 해제됩니다.

SELECT 문에서 TableB에 대한 활성 참조가 없으므로 TableB의 잠금을 에스컬레이션하려고 시도하지 않습니다. 마찬가지로 에스컬레이션이 발생했을 때 아직 액세스되지 않았기 때문에 에스컬레이션되지 않은 TableC의 잠금을 에스컬레이션하려고 시도하지 않습니다.

최적화된 잠금을 사용하여 잠금 에스컬레이션

최적화된 잠금은 트랜잭션 기간 동안 잠금이 거의 유지되지 않는 잠금 메모리를 줄이는 데 도움이 됩니다. 데이터베이스 엔진에서 행 및 페이지 잠금을 획득할 때 잠금 에스컬레이션이 비슷하게 발생할 수 있지만 빈도가 훨씬 낮습니다. 최적화된 잠금은 일반적으로 잠금 에스컬레이션을 방지하여 잠금 수와 필요한 잠금 메모리 양을 줄이는 데 성공합니다.

최적화된 잠금을 사용하도록 설정한 경우 기본 READ COMMITTED 격리 수준에서 데이터베이스 엔진은 행이 수정되는 즉시 행 및 페이지 잠금을 해제합니다. 단일 TID(트랜잭션 ID) 잠금을 제외하고 트랜잭션 기간 동안 행 및 페이지 잠금이 유지되지 않습니다. 이렇게 하면 잠금 에스컬레이션 가능성이 줄어듭니다.

잠금 에스컬레이션 임계값

잠금 에스컬레이션은 ALTER TABLE SET LOCK_ESCALATION 옵션을 사용하여 테이블에서 잠금 에스컬레이션을 사용하지 않도록 설정하지 않은 경우와 다음 조건 중 하나가 있는 경우에 트리거됩니다.

• 단일 Transact-SQL 문은 분할되지 않은 단일 테이블 또는 인덱스에 대해 5,000개 이상의 잠금을 획득합니다.
• 단일 Transact-SQL 문은 분할된 테이블의 단일 파티션에 대해 5,000개 이상의 잠금을 획득하고 ALTER TABLE SET LOCK_ESCALATION 옵션은 AUTO로 설정됩니다.
• 데이터베이스 엔진 인스턴스의 잠금 수가 메모리 또는 구성 임계값을 초과합니다.

잠금 충돌로 인해 잠금을 에스컬레이션할 수 없는 경우 데이터베이스 엔진은 1,250개의 새 잠금을 획득할 때마다 주기적으로 잠금 에스컬레이션을 트리거합니다.

Transact-SQL 문의 에스컬레이션 임계값

데이터베이스 엔진에서 새로 획득한 잠금 1,250개마다 가능한 에스컬레이션을 확인할 때 Transact-SQL 문이 테이블의 단일 참조에 대해 5,000개 이상의 잠금을 획득한 경우에만 잠금 에스컬레이션이 발생합니다. Transact-SQL 문이 테이블의 단일 참조에 대해 5,000개 이상의 잠금을 획득하면 잠금 에스컬레이션이 트리거됩니다. 예를 들어 문이 한 인덱스에서 3,000개의 잠금을 획득하고 동일한 테이블의 다른 인덱스에서 3,000개의 잠금을 획득하는 경우 잠금 에스컬레이션이 트리거되지 않습니다. 마찬가지로 문에 테이블에 대한 자체 조인이 있고 테이블에 대한 각 참조가 테이블에서 3,000개의 잠금만 획득하는 경우 잠금 에스컬레이션이 트리거되지 않습니다.

잠금 에스컬레이션은 에스컬레이션이 트리거될 때 액세스된 테이블에 대해서만 발생합니다. 단일 SELECT 문이 여기 TableA, TableB 및 TableC 시퀀스의 세 테이블에 액세스하는 조인이라고 가정합니다. 이 문은 TableA에 대한 클러스터형 인덱스에서 3,000개의 행 잠금을 획득하고 TableB에 대한 클러스터형 인덱스에서 5,000개 이상의 행 잠금을 획득했지만 아직 TableC에 액세스 하지 않았습니다. 데이터베이스 엔진에서 문이 TableB에서 5,000개 이상의 행 잠금을 획득했음을 감지하면 현재 TableB에 대해 트랜잭션에서 보유한 모든 잠금을 에스컬레이션하려고 시도합니다. 또한 현재 TableA에 대해 트랜잭션이 보유한 모든 잠금을 에스컬레이션하려고 시도하지만 TableA에 대한 잠금 수가 5,000개 미만이므로 에스컬레이션이 성공하지 못합니다. 에스컬레이션이 발생했을 때 아직 액세스하지 않았기 때문에 TableC에 대해 잠금 에스컬레이션이 시도되지 않습니다.

데이터베이스 엔진 인스턴스의 에스컬레이션 임계값

잠금 수가 잠금 에스컬레이션에 대한 메모리 임계값보다 커지면 데이터베이스 엔진에서 잠금 에스컬레이션을 트리거합니다. 메모리 임계값은 다음과 같은 잠금 구성 옵션의 설정에 따라 다릅니다.

• locks 옵션이 기본값 0으로 설정된 경우 잠금 개체에서 사용하는 메모리가 AWE 메모리를 제외하고 데이터베이스 엔진에서 사용하는 메모리의 24%가 되면 잠금 에스컬레이션 임계값에 도달합니다. 잠금을 나타내는 데 사용되는 데이터 구조는 약 100바이트 길이입니다. 이 임계값은 데이터베이스 엔진이 동적으로 메모리를 획득하고 해제하여 다양한 워크로드에 맞게 조정하기 때문입니다.

• locks 옵션이 0이 아닌 값인 경우 잠금 에스컬레이션 임계값은 잠금 옵션 값의 40%(메모리 압력이 있는 경우 40% 미만)입니다.

데이터베이스 엔진에서 에스컬레이션을 위해 모든 세션에서 활성 문을 선택할 수 있으며, 1,250개의 새 잠금마다 인스턴스에 사용된 잠금 메모리가 임계값 이상으로 유지되는 한 에스컬레이션 문을 선택합니다.

혼합 잠금 유형을 사용하여 잠금 에스컬레이션

잠금 에스컬레이션이 발생하면 힙 또는 인덱스에 대해 선택한 잠금이 가장 제한적인 하위 수준 잠금의 요구 사항을 충족할 만큼 충분히 강력합니다.

예를 들어 세션을 다음과 같이 가정합니다.

• 트랜잭션을 시작합니다.
• 클러스터형 인덱스가 포함된 테이블을 업데이트합니다.
• 동일한 테이블을 참조하는 SELECT 문을 실행합니다.

UPDATE 문은 다음과 같은 잠금을 획득합니다.

• 업데이트된 데이터 행에 대한 배타적(X) 잠금입니다.
• 해당 행을 포함하는 클러스터형 인덱스 페이지에 대한 의도 배타적(IX) 잠금입니다.
• 클러스터형 인덱스에 대한 IX 잠금 및 테이블의 다른 잠금입니다.

SELECT 문은 다음과 같은 잠금을 획득합니다.

• UPDATE 문에서 X 잠금으로 행을 이미 보호하지 않는 한 공유(S)는 읽는 모든 데이터 행에 대해 잠급니다.
• IX 잠금으로 페이지가 이미 보호되지 않는 한 해당 행이 포함된 모든 클러스터형 인덱스 페이지에서 내재된 공유(IS) 잠금이 적용됩니다.
• 클러스터형 인덱스 또는 테이블이 이미 IX 잠금으로 보호되어 있으므로 잠금이 없습니다.

SELECT 문이 잠금 에스컬레이션을 트리거하기에 충분한 잠금을 획득하고 에스컬레이션이 성공하면 테이블의 IX 잠금이 X 잠금으로 변환되고 모든 행, 페이지 및 인덱스 잠금이 해제됩니다. 업데이트와 읽기는 모두 테이블의 X 잠금으로 보호됩니다.

잠금 및 잠금 에스컬레이션 줄이기

대부분의 경우 데이터베이스 엔진이 잠금 및 잠금 에스컬레이션에 대한 기본 설정으로 작동할 때 최상의 성능을 제공합니다.

• 최적화된 잠금을 활용합니다.

  • 최적화된 잠금은 잠금 메모리 사용을 줄이고 동시 트랜잭션 간 차단을 줄이는 향상된 잠금 메커니즘을 제공합니다. 잠금 에스컬레이션은 최적화된 잠금을 사용할 때 발생할 가능성이 훨씬 적습니다.
  • 최적화된 잠금과 함께 테이블 힌트를 사용하지 않습니다. 테이블 힌트는 최적화된 잠금의 효율성을 줄일 수 있습니다.
  • 최적화된 잠금의 이점을 최대한 활용하려면 데이터베이스에서 READ_COMMITTED_SNAPSHOT 옵션을 사용하도록 설정합니다. Azure SQL 데이터베이스의 기본값입니다.
  • 최적화된 잠금을 사용하려면 데이터베이스에서 ADR(가속 데이터베이스 복구)을 사용하도록 설정해야 합니다.

데이터베이스 엔진 인스턴스가 많은 잠금을 생성하고 자주 잠금 에스컬레이션이 발생하는 경우 다음 전략을 사용하여 잠금의 양을 줄이는 것이 좋습니다.

• 읽기 작업에서 공유 잠금을 생성하지 않는 격리 수준 사용:

  • READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON인 경우 READ COMMITTED 격리 수준.
  • SNAPSHOT 격리 수준.
  • READ UNCOMMITTED 격리 수준. 이는 더티 읽기로 작동 가능한 시스템에만 사용할 수 있습니다.

• PAGLOCK 또는 TABLOCK 테이블 힌트를 사용하여 하위 수준 잠금 대신 데이터베이스 엔진 사용 페이지, 힙 또는 인덱스 잠금을 사용합니다. 그러나 이 옵션을 사용하면 사용자가 동일한 데이터에 액세스하려고 시도하는 다른 사용자를 차단하는 문제가 증가하며, 여러 동시 사용자가 있는 시스템에서는 사용하지 않아야 합니다.

• 최적화된 잠금을 사용할 수 없는 경우 분할된 테이블에서 ALTER TABLE의 LOCK_ESCALATION 옵션을 사용하면 테이블 수준이 아니라 파티션 수준으로 잠금을 에스컬레이션하거나 테이블의 잠금 에스컬레이션을 사용하지 않도록 설정할 수 있습니다.

• 큰 배치 작업을 몇 개의 작은 작업으로 분할할 수 있습니다. 예를 들어 다음 쿼리를 실행하여 감사 테이블에서 수십만 개의 이전 행을 제거한 다음 다른 사용자를 차단하는 잠금 에스컬레이션이 발생했음을 발견했다고 가정합니다.

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

  이러한 행을 한 번에 수백 개 제거하면 트랜잭션당 누적되는 잠금 수를 크게 줄이고 잠금 에스컬레이션을 방지할 수 있습니다. 예시:

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• 쿼리를 최대한 효율적으로 만들어 쿼리 잠금 범위를 좁힙니다. 검색이 많거나 키 조회 수가 많으면 잠금 에스컬레이션 가능성이 증가할 수 있습니다. 또한 교착 상태의 가능성이 증가하고 일반적으로 동시 실행 및 성능에 부정적인 영향을 줍니다. 잠금 에스컬레이션을 유발한 쿼리를 찾은 후 새 인덱스를 만들거나 기존 인덱스에 열을 추가하여 전체 인덱스 또는 테이블 검색을 제거하고 인덱스 검색의 효율성을 극대화하는 기회를 찾습니다. 데이터베이스 엔진 튜닝 관리자를 사용하여 쿼리에 대한 자동 인덱스 분석을 수행하는 것이 좋습니다. 자세한 내용은 자습서: 데이터베이스 엔진 튜닝 관리자를 참조하세요. 이 최적화의 한 가지 목표는 인덱스가 키 조회 비용을 최소화하기 위해 가능한 한 적은 수의 행을 반환하도록 하는 것입니다(특정 쿼리에 대한 인덱스의 선택성을 최대화). 데이터베이스 엔진에서 키 조회 논리 연산자가 많은 행을 반환할 수 있다고 예상하는 경우 프리페치 최적화를 사용하여 조회를 수행할 수 있습니다. 데이터베이스 엔진에서 조회에 프리페치를 사용하는 경우 쿼리 부분의 트랜잭션 격리 수준을 REPEATABLE READ로 늘려야 합니다. 즉, READ COMMITTED 격리 수준에서 SELECT 문과 유사하게 보일 수 있는 항목은 클러스터형 인덱스와 비클러스터형 인덱스 모두에서 수천 개의 키 잠금을 획득할 수 있으며, 이로 인해 이러한 쿼리가 잠금 에스컬레이션 임계값을 초과할 수 있습니다. 에스컬레이션된 잠금이 공유 테이블 잠금인 경우 특히 중요합니다. 이 잠금은 기본 READ COMMITTED 격리 수준에서 일반적으로 표시되지 않습니다.

  프리페치 최적화를 사용한 키 조회가 잠금 에스컬레이션을 일으키는 경우 쿼리 계획의 키 조회 논리 연산자 아래에 있는 Index Seek 또는 Index Scan 논리 연산자에 나타나는 비클러스터형 인덱스에 열을 추가하는 것이 좋습니다. 포함 인덱스(쿼리에 사용된 테이블의 모든 열을 포함하는 인덱스) 또는 SELECT 열 목록에 있는 모든 항목을 포함하는 것이 비현실적인 경우 조인 조건 또는 WHERE 절에 사용된 열을 포함하는 인덱스를 만들 수 있습니다. 중첩 루프 조인은 프리페치 최적화를 사용할 수 있으며 이로 인해 동일한 잠금 동작이 발생합니다.

• 다른 SPID가 현재 호환되지 않는 테이블 잠금을 보유하고 있는 경우에는 잠금 에스컬레이션이 발생할 수 없습니다. 잠금 에스컬레이션은 항상 테이블 잠금으로 에스컬레이션되고 페이지 잠금에는 에스컬레이션되지 않습니다. 또한 다른 SPID가 호환되지 않는 테이블 잠금을 보유하여 잠금 에스컬레이션 시도가 실패하는 경우 에스컬레이션을 시도한 쿼리는 테이블 잠금을 기다리는 동안 차단되지 않습니다. 대신 원래의 보다 세분화된 수준(행, 키 또는 페이지)에서 잠금을 계속 획득하여 주기적으로 추가 에스컬레이션을 시도합니다. 따라서 특정 테이블에 대한 잠금 에스컬레이션을 방지하는 한 가지 방법은 에스컬레이션된 잠금 유형과 호환되지 않는 다른 연결에 대한 잠금을 획득하고 유지하는 것입니다. 테이블 수준의 의도 배타적(IX) 잠금은 행이나 페이지를 잠그지 않지만 에스컬레이션된 공유(S) 또는 배타적(X) 테이블 잠금과 호환되지 않습니다. 예를 들어 mytable 테이블에서 다수의 행을 수정하고 잠금 에스컬레이션으로 인해 발생하는 차단을 유발한 일괄 작업을 실행해야 한다고 가정합니다. 이 작업이 항상 1시간 이내에 완료되는 경우 다음 코드가 포함된 Transact-SQL 작업을 만들고 일괄 처리 작업의 시작 시간 몇 분 전에 새 작업을 시작하도록 예약할 수 있습니다.

  BEGIN TRAN;
  
  SELECT *
  FROM mytable WITH (UPDLOCK, HOLDLOCK)
  WHERE 1 = 0;
  
  WAITFOR DELAY '1:00:00';
  
  COMMIT TRAN;
  

  이 쿼리는 1시간 동안 mytable에 대한 IX 잠금을 획득하고 보유하므로 해당 시간 동안 테이블에 대한 잠금 에스컬레이션을 방지합니다. 이 일괄 처리는 데이터를 수정하거나 다른 쿼리를 차단하지 않습니다(다른 쿼리가 TABLOCK 힌트로 테이블 잠금을 강제 적용하거나 관리자가 mytable의 인덱스에 페이지 또는 행 잠금을 사용하지 않도록 설정한 경우 제외).

• 추적 플래그 1211 및 1224를 사용하여 모든 또는 일부 잠금 에스컬레이션을 사용하지 않도록 설정할 수도 있습니다. 그러나 이러한 추적 플래그는 전체 데이터베이스 엔진 인스턴스에 대해 전역적으로 모든 잠금 에스컬레이션을 사용하지 않도록 설정합니다. 잠금 에스컬레이션은 수천 개의 잠금을 획득하고 해제하는 오버헤드로 인해 느려지는 쿼리의 효율성을 극대화하여 데이터베이스 엔진에서 매우 유용한 용도로 사용됩니다. 잠금 에스컬레이션은 잠금을 추적하는 데 필요한 메모리를 최소화하는 데도 도움이 됩니다. 데이터베이스 엔진 잠금 구조에 동적으로 할당할 수 있는 메모리는 유한하므로 잠금 에스컬레이션을 사용하지 않도록 설정하고 잠금 메모리가 충분히 커지면 쿼리에 대한 추가 잠금 할당 시도가 실패할 수 있으며 다음 오류가 발생할 수 있습니다. Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  참고 항목

  MSSQLSERVER_1204 오류가 발생하면 현재 문의 처리를 중지하고 활성 트랜잭션을 롤백합니다. 롤백 자체는 사용자를 차단하거나 데이터베이스 서비스를 다시 시작하는 경우 데이터베이스 복구 시간이 길어질 수 있습니다.

  참고 항목

  ROWLOCK과 같은 잠금 힌트를 사용하면 초기 잠금 취득만 변경됩니다. 잠금 힌트는 잠금 에스컬레이션을 방지하지 않습니다.

SQL Server 2008(10.0.x)이상 버전에서 잠금 에스컬레이션의 동작이 LOCK_ESCALATION 테이블 옵션의 도입으로 변경되었습니다. 자세한 내용은 ALTER TABLE의 LOCK_ESCALATION 옵션을 참조하세요.

잠금 에스컬레이션 모니터링

다음 예제와 같이 lock_escalation 확장 이벤트를 사용하여 잠금 에스컬레이션을 모니터링합니다.

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

동적 잠금

행 잠금과 같은 하위 수준 잠금을 사용하면 두 트랜잭션이 동일한 데이터 조각에 대한 잠금을 동시에 요청할 가능성을 줄여 동시성이 높아집니다. 또한 잠금 수 및 잠금 관리에 필요한 리소스 수도 늘어납니다. 테이블 또는 페이지 잠금과 같이 높은 수준의 잠금을 사용하면 오버헤드는 줄어들지만 동시성이 감소합니다.

데이터베이스 엔진은 동적 잠금 전략을 사용하여 가장 효율적인 잠금을 결정합니다. 데이터베이스 엔진은 스키마 및 쿼리의 특성에 따라 쿼리가 실행될 때 가장 적합한 잠금을 자동으로 결정합니다. 예를 들어 잠금 오버헤드를 줄이기 위해 최적화 프로그램은 인덱스 검색을 수행할 때 인덱스에서 페이지 잠금을 선택할 수 있습니다.

잠금 분할

잠금을 확보하고 해제하는 과정에서는 내부 잠금 리소스에 대한 경합이 발생하기 때문에 대규모 컴퓨터 시스템의 경우 자주 참조되는 개체를 잠그면 성능이 저하될 수 있습니다. 잠금 분할은 단일 잠금 리소스를 여러 잠금 리소스로 분할하여 잠금 성능을 향상시킵니다. 이 기능은 논리적 CPU가 16개 이상인 시스템에서만 사용할 수 있으며 자동으로 사용하도록 설정되며 사용하지 않도록 설정할 수 없습니다. 개체 잠금만 분할할 수 있습니다. 하위 형식이 있는 개체 잠금은 분할되지 않습니다. 자세한 내용은 sys.dm_tran_locks(Transact-SQL)를 참조하세요.

잠금 분할 이해

잠금 작업은 여러 공유 리소스에 액세스하며, 그 중 두 가지는 잠금 분할에 의해 최적화됩니다.

• Spinlock

  행 또는 테이블과 같은 잠금 리소스에 대한 액세스를 제어합니다.

  잠금 분할 없이 하나의 스핀 잠금은 단일 잠금 리소스에 대한 모든 잠금 요청을 관리합니다. 많은 양의 작업이 발생하는 시스템에서 잠금 요청이 스핀 잠금을 사용할 수 있게 될 때까지 기다리면 경합이 발생할 수 있습니다. 이러한 상황에서 잠금을 획득하면 병목 현상이 발생할 수 있으며 성능에 부정적인 영향을 미칠 수 있습니다.

  단일 잠금 리소스에 대한 경합을 줄이기 위해 잠금 분할은 단일 잠금 리소스를 여러 잠금 리소스로 분할하여 부하를 여러 스핀 잠금에 분산합니다.

• 메모리

  잠금 리소스 구조를 저장하는 데 사용됩니다.

  스핀 잠금을 획득하면 잠금 구조가 메모리에 저장 및 액세스되고 수정할 수 있습니다. 여러 리소스에 잠금 액세스를 분산하면 CPU 간에 메모리 블록을 전송할 필요가 없으므로 성능 향상에 도움이 됩니다.

잠금 분할 구현 및 모니터링

CPU가 16개 이상인 시스템의 경우 잠금 분할이 기본적으로 설정됩니다. 잠금 분할을 사용하도록 설정하면 SQL Server 오류 로그에 정보 메시지가 기록됩니다.

분할된 리소스에 대한 잠금을 획득하는 경우는 다음과 같습니다.

• NL, Sch-S, IS, IU 및 IX 잠금 모드만 단일 파티션에 대해 확보됩니다.

• NL, Sch-S, IS, IU 및 IX 이외의 모드에서 공유(S), 배타적(X) 및 기타 잠금은 파티션 ID 0부터 시작하여 파티션 ID 순서에 따라 모든 파티션에서 획득되어야 합니다. 분할된 리소스에 대한 이러한 잠금은 각 파티션이 사실상 별도의 잠금이므로 분할되지 않은 리소스에서 동일한 모드의 잠금보다 더 많은 메모리를 사용합니다. 메모리 증가는 파티션의 수에 따라 결정됩니다. SQL Server 잠금 성능 카운터에는 분할된 잠금과 분할되지 않은 잠금에서 사용하는 메모리에 대한 정보가 표시됩니다.

트랜잭션이 시작될 때 파티션에 트랜잭션이 할당됩니다. 트랜잭션의 경우 분할할 수 있는 모든 잠금 요청은 해당 트랜잭션에 할당된 파티션을 사용합니다. 이 메서드를 통해 서로 다른 트랜잭션에 의해 동일한 개체의 리소스를 잠그는 액세스가 서로 다른 파티션에 분산됩니다.

sys.dm_tran_locks 동적 관리 뷰의 resource_lock_partition 열은 잠금 분할된 리소스에 대한 잠금 파티션 ID를 제공합니다. 자세한 내용은 sys.dm_tran_locks(Transact-SQL)를 참조하세요.

잠금 분할 작업

다음은 잠금 분할을 보여 주는 코드 예제입니다. 이 예제에서는 서로 다른 두 세션에서 실행되는 두 가지 트랜잭션을 통해 CPU가 16개인 시스템의 잠금 분할 동작을 보여 줍니다.

이러한 Transact-SQL 문은 다음 예제에서 사용되는 테스트 개체를 만듭니다.

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

예 1

세션 1:

트랜잭션에서 SELECT 문이 실행됩니다. HOLDLOCK 잠금 힌트로 인해 이 문은 테이블에 대한 내재된 공유(IS) 잠금을 획득하고 유지합니다(이 그림의 경우 행 및 페이지 잠금은 무시됨). IS 잠금은 트랜잭션에 할당된 파티션에서만 획득됩니다. 이 예에서는 파티션 ID 7에 대해 IS 잠금을 획득했다고 가정합니다.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

세션 2:

트랜잭션이 시작되고 이 트랜잭션에서 실행되는 SELECT 문이 테이블에 대한 공유(S) 잠금을 획득 및 유지합니다. S 잠금은 모든 파티션에 대해 확보되므로 각 파티션에 대해 하나씩 잠금이 생성되어 여러 테이블이 잠기게 됩니다. 예를 들어 16-CPU 시스템에서는 잠금 파티션 ID 0-15에서 16개의 S 잠금이 발급됩니다. S 잠금은 세션 1의 트랜잭션에 의해 파티션 ID 7에 확보된 IS 잠금과 호환되므로 트랜잭션 간에 차단이 발생하지 않습니다.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

세션 1:

다음 SELECT 문은 세션 1에서 여전히 활성 상태인 트랜잭션에서 실행됩니다. 배타적(X) 테이블 잠금 힌트로 인해 트랜잭션은 테이블에 대한 X 잠금을 획득하려고 시도합니다. 그러나 세션 2에서 트랜잭션에 의해 유지되는 S 잠금은 파티션 ID 0에서 X 잠금을 차단합니다.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

예 2

세션 1:

트랜잭션에서 SELECT 문이 실행됩니다. HOLDLOCK 잠금 힌트로 인해 이 문은 테이블에 대한 내재된 공유(IS) 잠금을 획득하고 유지합니다(이 그림의 경우 행 및 페이지 잠금은 무시됨). IS 잠금은 트랜잭션에 할당된 파티션에서만 획득됩니다. 이 예에서는 파티션 ID 6에 대해 IS 잠금을 획득했다고 가정합니다.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

세션 2:

트랜잭션에서 SELECT 문이 실행됩니다. TABLOCKX 잠금 힌트로 인해 트랜잭션은 테이블에서 배타적(X) 잠금을 획득하려고 시도합니다. 파티션 ID 0부터 시작하는 모든 파티션에서 X 잠금을 획득해야 합니다. X 잠금은 모든 파티션 ID 0-5에서 획득되지만 파티션 ID 6에서 획득한 IS 잠금에 의해 차단됩니다.

X 잠금에 아직 도달하지 않은 파티션 ID 7-15에서 다른 트랜잭션은 잠금을 계속 획득할 수 있습니다.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

데이터베이스 엔진의 행 버전 관리 기반 격리 수준

SQL Server 2005(9.x)부터 데이터베이스 엔진에서는 기존 격리 수준을 구현한 READ COMMITTED를 제공하여 행 버전 관리를 사용하는 문 수준 스냅샷을 제공합니다. 또한 데이터베이스 엔진은 행 버전 관리를 사용하여 트랜잭션 수준 스냅샷을 제공하는 트랜잭션 격리 수준 SNAPSHOT을 제공합니다.

행 버전 관리란 행을 수정하거나 삭제할 때 쓰기에 복사 메커니즘을 호출하는 SQL Server의 일반적인 프레임워크입니다. 이렇게 하려면 트랜잭션이 실행되는 동안 이전 버전의 행을 이전 트랜잭션 일치 상태가 필요한 트랜잭션에 사용할 수 있어야 합니다. 행 버전 관리는 다음 기능을 이행하는 용도로 사용됩니다.

• 트리거에서 inserted 테이블 및 deleted 테이블을 빌드합니다. 트리거에 의해 수정된 모든 행의 버전이 지정됩니다. 여기에는 트리거를 실행한 문에 의해 수정된 행과 트리거에 의해 수정된 모든 데이터가 포함됩니다.
• MARS(Multiple Active Result Sets)를 지원합니다. MARS 세션이 활성 결과 집합이 있는 시기에 데이터 수정 문(예: INSERT, UPDATE 또는 DELETE)을 발행하는 경우 수정 문의 영향을 받는 행의 버전이 지정됩니다.
• ONLINE 옵션을 지정하는 인덱스 작업을 지원합니다.
• 행 버전 관리 기반 트랜잭션 격리 수준을 지원합니다.
  • 행 버전 관리 기능을 사용하여 문 수준 읽기 일관성을 제공하는 READ COMMITTED 격리 수준의 새로운 구현입니다.
  • 트랜잭션 수준 읽기 일관성을 제공하는 새로운 격리 수준 SNAPSHOT입니다.

행 버전은 버전 저장소에 저장됩니다. 데이터베이스에서 가속 데이터베이스 복구를 사용하도록 설정하면 해당 데이터베이스에 버전 저장소가 만들어집니다. 그렇지 않으면 버전 저장소가 tempdb 데이터베이스에 만들어집니다.

데이터베이스는 버전 저장소에 충분한 공간이 있어야 합니다. 버전 저장소가 tempdb에 있고 tempdb 데이터베이스가 가득 차면, 업데이트 작업이 버전 생성을 중단하고 계속 진행되지만 필요한 특정 행 버전이 더 이상 존재하지 않으므로 읽기 작업이 실패할 수 있습니다. 트리거, MARS, 온라인 인덱싱과 같은 작업에 영향을 줍니다.

가속 데이터베이스 복구를 사용하고 버전 저장소가 가득 차면 읽기 작업은 계속 성공하지만 버전(예: UPDATE 및 DELETE)을 생성하는 쓰기 작업은 실패합니다. 데이터베이스에 충분한 공간이 있는 경우 INSERT 작업이 계속 성공합니다.

READ COMMITTED 및 SNAPSHOT 트랜잭션에 행 버전 관리를 사용하는 과정은 다음 두 단계로 이루어집니다.

1. READ_COMMITTED_SNAPSHOT 및 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션 중 하나 또는 둘 다를 ON으로 설정합니다.

2. 애플리케이션에서 적절한 트랜잭션 격리 수준을 설정합니다.

   • READ_COMMITTED_SNAPSHOT 데이터베이스 옵션을 ON으로 설정하면 READ COMMITTED 격리 수준을 설정하는 트랜잭션에 행 버전 관리가 사용됩니다.
   • ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정하면 트랜잭션에서 SNAPSHOT 격리 수준을 설정할 수 있습니다.

READ_COMMITTED_SNAPSHOT 또는 ON 데이터베이스 옵션을 ALLOW_SNAPSHOT_ISOLATION으로 설정하면 데이터베이스 엔진에서 행 버전 관리를 사용하여 데이터를 조작하는 각 트랜잭션에 XSN(트랜잭션 시퀀스 번호)을 할당합니다. 트랜잭션은 BEGIN TRANSACTION 문이 실행될 때 시작됩니다. 그러나 트랜잭션 시퀀스 번호는 BEGIN TRANSACTION 문 이후 첫 번째 읽기 또는 쓰기 작업이 실행될 때 시작합니다. 트랜잭션 시퀀스 번호는 할당될 때마다 하나씩 증가합니다.

READ_COMMITTED_SNAPSHOT 또는 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션이 ON이면 데이터베이스에서 수행되는 모든 데이터 수정에 대해 논리 복사본(버전)이 유지 관리됩니다. 특정 트랜잭션에 의해 행이 수정될 때마다 데이터베이스 엔진 인스턴스는 이전에 커밋된 행 이미지의 버전을 버전 저장소에 저장합니다. 각 버전은 변경된 트랜잭션의 트랜잭션 시퀀스 번호로 표시됩니다. 수정된 행의 여러 버전은 연결 목록을 통해 체인으로 연결됩니다. 최신 행 값은 항상 현재 데이터베이스에 저장되고 버전 저장소에 저장된 버전이 지정된 행에 연결됩니다.

행 버전은 행 버전 관리 기반 격리 수준에서 실행되는 트랜잭션의 요구 사항을 충족할 만큼 충분히 오래 유지됩니다. 데이터베이스 엔진은 가장 오래된 유용한 트랜잭션 시퀀스 번호를 추적하여 해당 번호보다 낮은 트랜잭션 시퀀스 번호로 표시된 모든 행 버전을 주기적으로 삭제합니다.

두 데이터베이스 옵션을 모두 OFF로 설정하면 트리거 또는 MARS 세션에서 수정하거나 온라인 인덱스 작업에서 읽은 행만 버전이 지정됩니다. 이러한 행 버전은 더 이상 필요하지 않을 경우 해제됩니다. 백그라운드 프로세스는 부실 행 버전을 제거합니다.

데이터를 읽는 경우의 동작

행 버전 관리 기반 격리 데이터 읽기 수준으로 트랜잭션이 실행되는 경우에는 읽기 작업에서 읽고 있는 데이터에 대한 공유(S) 잠금을 획득하지 못하므로 데이터를 수정하는 트랜잭션을 차단하지 못합니다. 또한 획득한 잠금 수가 감소함에 따라 리소스 잠금으로 인한 오버헤드가 최소화됩니다. 행 버전 관리 및 SNAPSHOT 격리를 사용하여 READ COMMITTED 격리는 버전이 지정된 데이터의 문 수준 또는 트랜잭션 수준 읽기 일관성을 제공하도록 설계되었습니다.

행 버전 관리 기반 격리 수준에서 실행되는 트랜잭션을 포함하여 모든 쿼리는 컴파일 및 실행 중에 스키마 안정성(Sch-S) 잠금을 획득합니다. 이 때문에 동시 트랜잭션이 테이블에 대해 스키마 수정(Sch-M) 잠금을 유지하면 쿼리가 차단됩니다. 예를 들어 DDL(데이터 정의 언어) 작업은 테이블의 스키마 정보를 수정하기 전에 Sch-M 잠금을 획득합니다. 행 버전 관리 기반 격리 수준에서 실행되는 트랜잭션을 포함하여 트랜잭션은 Sch-S 잠금을 획득하려고 할 때 차단됩니다. 반대로 Sch-S 잠금을 유지하는 쿼리는 Sch-M 잠금을 획득하려고 시도하는 동시 트랜잭션을 차단합니다.

SNAPSHOT 격리 수준을 사용하는 트랜잭션이 시작되면 데이터베이스 엔진 인스턴스에서 현재 활성화된 모든 트랜잭션을 기록합니다. SNAPSHOT 트랜잭션에서 버전 체인이 있는 행을 읽는 경우 데이터베이스 엔진은 체인을 따르고 트랜잭션 시퀀스 번호가 있는 행을 검색합니다.

• 행을 읽는 스냅샷 트랜잭션의 시퀀스 번호와 가장 가깝지만 이보다 낮습니다.

• 스냅샷 트랜잭션이 시작되었을 때 활성화된 트랜잭션의 목록에 없는 번호

SNAPSHOT 트랜잭션에 따라 수행된 읽기 작업에서는 SNAPSHOT 트랜잭션이 시작되었을 때 커밋된 각 행의 마지막 버전을 검색합니다. 트랜잭션 시작 시 존재했던 데이터의 트랜잭션 일치 스냅샷을 제공합니다.

행 버전 관리를 사용하는 READ COMMITTED 트랜잭션은 거의 동일한 방식으로 작동합니다. 다만 READ COMMITTED 트랜잭션에서는 행 버전을 선택할 때 고유한 트랜잭션 시퀀스 번호가 사용되지 않는다는 점이 다릅니다. 문이 시작될 때마다 READ COMMITTED 트랜잭션은 데이터베이스 엔진의 해당 인스턴스에 대해 발급된 최신 트랜잭션 시퀀스 번호를 읽습니다. 해당 문의 행 버전을 선택하는 데 사용되는 트랜잭션 시퀀스 번호입니다. 이렇게 하면 READ COMMITTED 트랜잭션이 각 문의 시작 부분에 있는 데이터의 스냅샷을 볼 수 있습니다.

데이터를 수정할 때의 동작

데이터 쓰기 동작은 최적화된 잠금이 사용 가능할 때와 아닌 경우가 크게 다릅니다.

최적화된 잠금 없이 데이터 수정

행 버전 관리를 사용하는 READ COMMITTED 트랜잭션에서 업데이트할 행 선택은 데이터 값을 읽을 때 데이터 행에서 업데이트(U) 잠금을 획득하는 차단 검사를 사용하여 수행됩니다. 이는 행 버전 관리가 사용되지 않는 READ COMMITTED 트랜잭션과 동일합니다. 데이터 행이 업데이트 기준을 충족하지 않으면 해당 행의 업데이트 잠금이 해제되고 다음 행이 잠기고 검사됩니다.

SNAPSHOT 격리에서 실행되는 트랜잭션은 제약 조건을 적용하기 위해 수정을 수행하기 전에 데이터에 대한 잠금을 획득하여 데이터 수정에 대해 낙관적인 접근 방식을 적용합니다. 그렇지 않으면 데이터를 수정할 때까지 데이터에 대한 잠금이 획득되지 않습니다. 데이터 행이 업데이트 조건을 충족하는 경우 SNAPSHOT 트랜잭션은 SNAPSHOT 트랜잭션이 시작된 후 커밋된 동시 트랜잭션에 의해 데이터 행이 수정되지 않았는지 확인합니다. SNAPSHOT 트랜잭션 외에서 데이터 행이 수정된 경우 업데이트 충돌이 발생하고 SNAPSHOT 트랜잭션이 종료됩니다. 업데이트 충돌은 데이터베이스 엔진에서 처리되며 업데이트 충돌 검색을 사용하지 않도록 설정할 방법이 없습니다.

최적화된 잠금을 사용하여 데이터 수정

최적화된 잠금을 사용하도록 설정하고, READ_COMMITTED_SNAPSHOT(RCSI) 데이터베이스 옵션을 사용하도록 설정하고, 기본 READ COMMITTED 격리 수준을 사용하면 판독기는 잠금을 획득하지 않으며 기록기는 트랜잭션이 끝날 때 만료되는 잠금 대신 짧은 기간의 하위 수준 잠금을 획득합니다.

최적화된 잠금을 사용하여 효율성 극대화를 위해 RCSI를 사용하도록 설정하는 것이 좋습니다. REPEATABLE READ 또는 SERIALIZABLE과 같은 더 엄격한 격리 수준을 사용하는 경우 데이터베이스 엔진은 읽기 권한자와 기록기 모두에 대해 트랜잭션이 끝날 때까지 행 및 페이지 잠금을 유지하므로 차단 및 잠금 메모리가 증가합니다.

RCSI를 사용하도록 설정하고 기본 READ COMMITTED 격리 수준을 사용하는 경우 기록기는 U 잠금을 획득하지 않고도 커밋된 최신 버전의 행을 기반으로 조건자당 행을 한정합니다. 쿼리는 행이 자격이 있고 해당 행 또는 페이지에 또 다른 활성 쓰기 트랜잭션이 있는 경우에만 대기합니다. 커밋된 최신 버전을 기준으로 한정하고 정규화된 행만 잠그면 차단이 줄어들고 동시성이 증가합니다.

업데이트 충돌이 RCSI 및 기본 READ COMMITTED 격리 수준에서 감지되면 고객 워크로드에 영향을 주지 않으면서 자동으로 처리되고 다시 시도됩니다.

최적화된 잠금을 사용하도록 설정하고 SNAPSHOT 격리 수준을 사용하는 경우 업데이트 충돌의 동작이 최적화된 잠금이 없는 것과 동일합니다. 업데이트 충돌은 애플리케이션에서 처리하고 다시 시도해야 합니다.

동작 요약

다음 표에서는 행 버전 관리를 사용하는 SNAPSHOT 격리와 READ COMMITTED 격리의 차이점을 요약합니다.

속성	행 버전 관리를 사용하는 READ COMMITTED 격리 수준	SNAPSHOT 격리 수준
필요한 지원을 활성화하기 위해 ON으로 설정해야 하는 데이터베이스 옵션입니다.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
세션이 특정 유형의 행 버전 관리를 요청하는 방법입니다.	기본 READ COMMITTED 격리 수준을 사용하거나 READ COMMITTED 문을 실행하여 SET TRANSACTION ISOLATION LEVEL 격리 수준을 지정합니다. 트랜잭션이 시작된 후에 이 작업을 수행할 수 있습니다.	트랜잭션이 시작되기 전에 SNAPSHOT 격리 수준을 지정하려면 SET TRANSACTION ISOLATION LEVEL을 실행해야 합니다.
문에서 읽는 데이터의 버전	각 문이 시작되기 전에 커밋된 모든 데이터	각 트랜잭션이 시작되기 전에 커밋된 모든 데이터
업데이트 처리 방법	최적화된 잠금 사용 안 함: 행 버전에서 실제 데이터로 되돌리고 업데이트할 행을 선택한 다음 선택한 데이터 행에 업데이트 잠금을 사용합니다. 수정할 실제 데이터 행에 대해 배타적 잠금을 획득합니다. 업데이트 충돌 검색이 없습니다. 최적화된 잠금 사용: 잠금을 획득하지 않고 마지막으로 커밋된 버전에 따라 행이 선택됩니다. 행이 업데이트 자격이 되는 경우 전용 행 또는 페이지 잠금이 획득됩니다. 업데이트 충돌이 감지되면 자동으로 처리되고 다시 시도됩니다.	행 버전을 사용하여 업데이트할 행을 선택합니다. 수정할 실제 데이터 행에 대한 배타적 잠금을 획득하려고 시도하고, 데이터가 다른 트랜잭션에 의해 수정된 경우 업데이트 충돌이 발생하고 스냅샷 트랜잭션이 종료됩니다.
충돌 검색 업데이트	최적화된 잠금 사용 안 함: 없음. 최적화된 잠금 사용: 업데이트 충돌이 감지되면 자동으로 처리되고 다시 시도됩니다.	통합 지원. 사용하지 않도록 설정할 수 없음.

행 버전 관리 리소스 사용

행 버전 관리 프레임워크는 다음 데이터베이스 엔진 기능을 지원합니다.

• 트리거
• MARS(Multiple Active Result Sets)
• 온라인 인덱싱

또한 행 버전 관리 프레임워크는 다음 행 버전 관리 기반 트랜잭션 격리 수준을 지원합니다.

• READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON이면 READ_COMMITTED 트랜잭션이 행 버전 관리를 사용하여 문 수준 읽기 일관성을 제공합니다.
• ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션이 ON이면 SNAPSHOT 트랜잭션이 행 버전 관리를 사용하여 트랜잭션 수준 읽기 일관성을 제공합니다.

행 버전 관리 기반 격리 수준은 읽기 작업에 공유 잠금을 사용하지 않으므로 트랜잭션에서 획득하는 잠금 수를 줄입니다. 이렇게 하면 잠금을 관리하는 데 사용되는 리소스가 줄어 시스템 성능이 향상됩니다. 또한 다른 트랜잭션에서 획득한 잠금으로 인해 트랜잭션이 차단되는 횟수가 줄어 성능이 향상됩니다.

행 버전 관리 기반 격리 수준은 데이터 수정에 필요한 리소스를 증가시킵니다. 이 옵션을 설정하면 데이터베이스의 모든 데이터 수정에 대해 버전이 지정됩니다. 행 버전 관리 기반 격리를 사용하는 활성 트랜잭션이 없는 경우에도 수정 전에 데이터의 복사본이 버전 저장소에 저장됩니다. 수정 후의 데이터에는 버전 저장소에 저장된 버전이 지정된 데이터에 대한 포인터가 포함됩니다. 큰 개체의 경우 변경된 개체의 일부만 버전 저장소에 저장됩니다.

tempdb의 사용된 공간

데이터베이스 엔진의 각 인스턴스에 대해 버전 저장소에는행 버전을 저장할 충분한 공간이 있어야 합니다. 데이터베이스 관리자는 tempdb 및 다른 데이터베이스(가속 데이터베이스 복구를 사용하는 경우)에 버전 저장소를 지원할 충분한 공간이 있는지 확인해야 합니다. 두 가지 형식의 버전 저장소가 있습니다.

• 온라인 인덱스 빌드 버전 저장소는 온라인 인덱스 빌드에 사용됩니다.
• 공통 버전 저장소는 다른 모든 데이터 수정 작업에 사용됩니다.

활성 트랜잭션이 액세스해야 하는 한 행 버전을 저장해야 합니다. 주기적으로 백그라운드 스레드가 필요 없게 된 행 버전을 제거하여 버전 저장소의 공간을 비웁니다. 다음 중 하나에 해당될 경우 장기 실행 트랜잭션은 버전 저장소의 공간이 해제되지 않도록 합니다.

• 행 버전 관리 기반 격리를 사용합니다.
• 트리거, MARS 또는 온라인 인덱스 빌드 작업을 사용합니다.
• 행 버전을 생성합니다.

버전 저장소가 tempdb에 있고 tempdb가 공간이 부족한 경우 데이터베이스 엔진에서 버전 저장소를 강제로 축소합니다. 축소하는 동안, 아직 행 버전을 생성하지 않은 트랜잭션 중 장기 실행 트랜잭션은 교착 상태가 발생한 것으로 표시됩니다. 각 희생자 트랜잭션에 대한 오류 로그에 메시지 3967이 생성됩니다. 트랜잭션이 희생자로 표시되면 버전 저장소에서 더 이상 행 버전을 읽을 수 없습니다. 행 버전을 읽으려고 하면 메시지 3966이 생성되고 트랜잭션이 롤백됩니다. 축소 프로세스가 성공하면 tempdb에서 공간을 사용할 수 있게 됩니다. 그렇지 않으면 tempdb의 공간이 부족하고 다음이 발생합니다.

• 쓰기 작업은 계속 실행되지만 버전을 생성하지는 않습니다. 정보 메시지(3959)가 오류 로그에 표시되지만 데이터를 쓰는 트랜잭션은 영향을 받지 않습니다.

• tempdb 전체 롤백으로 인해 생성되지 않고 오류 3958로 종료한 행 버전에 액세스하려고 시도하는 트랜잭션.

데이터 행에 사용되는 공간

각 데이터베이스 행은 행 버전 관리 정보에 대해 행 끝에 최대 14바이트를 사용할 수 있습니다. 행 버전 관리 정보에는 버전을 커밋한 트랜잭션의 트랜잭션 시퀀스 번호와 버전이 지정된 행에 대한 포인터가 포함됩니다. 이러한 14바이트는 행이 처음 수정되거나 새 행이 삽입될 때 다음 조건 중에서 추가됩니다.

• READ_COMMITTED_SNAPSHOT 또는 ALLOW_SNAPSHOT_ISOLATION 옵션이 ON으로 설정됩니다.
• 테이블에 트리거가 있습니다.
• MARS(Multiple Active Result Sets)를 사용합니다.
• 테이블에서 현재 온라인 인덱스 작성 작업이 실행되고 있습니다.

버전 저장소가 tempdb에 있는 경우 이러한 14바이트는 이러한 모든 조건에서 행이 처음 수정될 때 데이터베이스 행에서 제거됩니다.

• READ_COMMITTED_SNAPSHOT 및 ALLOW_SNAPSHOT_ISOLATION 옵션이 OFF으로 설정됩니다.
• 트리거가 테이블에 더 이상 존재하지 않습니다.
• MARS는 사용되지 않습니다.
• 온라인 인덱스 빌드 작업이 현재 실행되지 않고 있습니다.

가속 데이터베이스 복구를 더 이상 사용하도록 설정하지 않고 위의 조건이 충족되는 경우 행이 수정될 때도 14바이트가 제거됩니다.

행 버전 관리 기능을 사용하는 경우 데이터베이스 행당 14바이트를 수용할 수 있도록 데이터베이스에 추가 디스크 공간을 할당해야 할 수 있습니다. 행 버전 관리 정보를 추가하면 현재 페이지에서 사용할 수 있는 공간이 부족한 경우 인덱스 페이지 분할 또는 새 데이터 페이지 할당이 발생할 수 있습니다. 예를 들어 평균 행 길이가 100바이트이면 14바이트를 더 추가할 경우 기존 테이블이 최대 14%까지 증가합니다.

채우기 비율을 낮추면 인덱스 페이지의 조각화를 방지하거나 줄일 수 있습니다. 테이블 또는 뷰의 데이터 및 인덱스에 대한 현재 페이지 밀도 정보를 보려면 sys.dm_db_index_physical_stats를 사용할 수 있습니다.

큰 개체의 공간 사용량

데이터베이스 엔진에서는 최대 2GB의 큰 문자열을 저장할 수 있는 몇 개의 데이터 형식 nvarchar(max), varchar(max), varbinary(max), ntext, text 및 image를 지원합니다. 이러한 데이터 형식을 사용하여 저장된 큰 데이터는 데이터 행에 연결된 일련의 데이터 조각에 저장됩니다. 행 버전 관리 정보는 이러한 큰 문자열을 저장하는 데 사용되는 각 조각에 저장됩니다. 데이터 조각은 테이블의 큰 개체에만 사용되는 페이지 집합에 저장됩니다.

새로운 큰 값이 데이터베이스에 추가되면 조각당 최대 8040바이트의 데이터를 사용하여 할당됩니다. 이전 버전의 데이터베이스 엔진이 조각당 최대 8080바이트의 ntext, text 또는 image 데이터를 저장했습니다.

데이터베이스를 이전 버전의 SQL Server에서 SQL Server로 업그레이드할 때 행 버전 관리 정보를 위한 공간을 만들기 위해 기존 ntext, text 및 image LOB(큰 개체) 데이터가 업데이트되지 않습니다. 그러나 LOB 데이터가 처음 수정되면 버전 관리 정보의 스토리지를 사용하도록 동적으로 업그레이드됩니다. 이는 행 버전이 생성되지 않은 경우에도 마찬가지입니다. LOB 데이터가 업그레이드되면 조각당 저장된 최대 바이트 수가 8080바이트에서 8040바이트로 줄어듭니다. 업그레이드 프로세스는 LOB 값을 삭제하고 동일한 값을 다시 삽입하는 것과 같습니다. LOB 데이터는 1바이트만 수정된 경우에도 업그레이드됩니다. 이 작업은 각 ntext, text 또는 image 열에 대해 한 번 수행되지만 LOB 데이터의 크기에 따라 각 작업 수행 시 대량의 페이지 할당 및 I/O 작업이 발생할 수 있습니다. 수정이 완전히 기록되는 경우 많은 양의 로깅 작업이 생성될 수도 있습니다. 데이터베이스 복구 모델이 FULL로 설정되지 않은 경우 WRITETEXT 및 UPDATETEXT 작업은 최소 로깅됩니다.

이 요구 사항을 충족하는 충분한 디스크 공간을 할당해야 합니다.

행 버전 관리 및 버전 저장소 모니터링

성능 및 문제에 대한 행 버전 관리, 버전 저장소 및 스냅샷 격리 프로세스를 모니터링하기 위해 데이터베이스 엔진은 DMV(동적 관리 뷰) 및 성능 카운터 형식의 도구를 제공합니다.

DMV

다음 DMV는 행 버전 관리를 사용하는 트랜잭션뿐만 아니라 tempdb의 현재 시스템 상태 및 버전 저장소에 대한 정보를 제공합니다.

• sys.dm_db_file_space_usage. 데이터베이스의 각 파일에 대한 공간 사용 정보를 반환합니다. 자세한 내용은 sys.dm_db_file_space_usage(Transact-SQL)를 참조하세요.

• sys.dm_db_session_space_usage. 데이터베이스에서 발생하는 세션별로 페이지 할당 및 할당 취소 작업을 반환합니다. 자세한 내용은 sys.dm_db_session_space_usage(Transact-SQL)를 참조하세요.

• sys.dm_db_task_space_usage. 데이터베이스에서 발생하는 태스크별로 페이지 할당 및 할당 취소 작업을 반환합니다. 자세한 내용은 sys.dm_db_task_space_usage(Transact-SQL)를 참조하세요.

• sys.dm_tran_top_version_generators. 버전 저장소에서 가장 많은 버전을 생성하는 개체에 대한 가상 테이블을 반환합니다. 상위 256개의 집계 레코드 길이를 database_id 및 rowset_id 별로 그룹화합니다. 이 함수를 사용하여 버전 저장소의 최대 소비자를 찾습니다. tempdb의 버전 저장소에만 적용됩니다. 자세한 내용은 sys.dm_tran_top_version_generators(Transact-SQL)를 참조하세요.

• sys.dm_tran_version_store. 공용 버전 저장소의 모든 버전 레코드를 표시하는 가상 테이블을 반환합니다. tempdb의 버전 저장소에만 적용됩니다. 자세한 내용은 sys.dm_tran_version_store(Transact-SQL)를 참조하세요.

• sys.dm_tran_version_store_space_usage. 각 데이터베이스에 대한 버전 저장소 레코드에서 사용하는 tempdb의 총 공간을 표시하는 가상 테이블을 반환합니다. tempdb의 버전 저장소에만 적용됩니다. 자세한 내용은 sys.dm_tran_version_store_space_usage(Transact-SQL)를 참조하세요.

  참고 항목

  시스템 개체 sys.dm_tran_top_version_generators와 sys.dm_tran_version_store는 실행 비용이 매우 많이 들며 두 함수는 모두 전체 버전 저장소를 쿼리하므로 매우 클 수 있습니다. sys.dm_tran_version_store_space_usage는 개별 버전 저장소 레코드를 탐색하지 않고 데이터베이스 당 tempdb에서 사용되는 집계된 버전 저장소 공간을 반환하므로 실행하기 효율적이고 비용이 많이 들지 않습니다.

• sys.dm_tran_active_snapshot_database_transactions. 행 버전 관리가 사용하는 SQL Server 인스턴스 내의 모든 데이터베이스에 있는 모든 활성 트랜잭션에 대한 가상 테이블을 반환합니다. 시스템 트랜잭션은 이 DMV에 나타나지 않습니다. 자세한 내용은 sys.dm_tran_active_snapshot_database_transactions(Transact-SQL)를 참조하세요.

• sys.dm_tran_transactions_snapshot. 각 트랜잭션에서 수행한 스냅샷을 표시하는 가상 테이블을 반환합니다. 스냅샷에는 행 버전 관리가 사용하는 활성 트랜잭션의 시퀀스 번호가 포함됩니다. 자세한 내용은 sys.dm_tran_transactions_snapshot(Transact-SQL)를 참조하세요.

• sys.dm_tran_current_transaction. 현재 세션에서 트랜잭션의 행 버전 관리 관련 상태 정보를 표시하는 단일 행을 반환합니다. 자세한 내용은 sys.dm_tran_current_transaction(Transact-SQL)을 참조하세요.

• sys.dm_tran_current_snapshot. 현재 스냅샷 격리 트랜잭션이 시작될 때의 모든 활성 트랜잭션을 표시하는 가상 테이블을 반환합니다. 현재 트랜잭션에서 스냅샷 격리를 사용하는 경우 이 함수는 행을 반환하지 않습니다. DMV sys.dm_tran_current_snapshot는 현재 스냅샷에 대한 활성 트랜잭션만 반환한다는 점을 제외하고 sys.dm_tran_transactions_snapshot와 유사합니다. 자세한 내용은 sys.dm_tran_current_snapshot(Transact-SQL)을 참조하세요.

• sys.dm_tran_persistent_version_store_stats. 가속 데이터베이스 복구를 사용할 때 사용되는 각 데이터베이스의 영구 버전 저장소에 대한 통계를 반환합니다. 자세한 내용은 sys.dm_tran_persistent_version_store_stats(Transact-SQL)를 참조하세요.

성능 카운터

다음 성능 카운터는 tempdb의 버전 저장소 및 행 버전 관리를 사용하는 트랜잭션을 모니터링합니다. 이러한 성능 카운터는 SQLServer:Transactions 성능 개체에 포함되어 있습니다.

• tempdb의 사용 가능한 공간(KB) tempdb 데이터베이스에서 사용 가능한 공간의 양(KB)을 모니터링합니다. 스냅샷 격리를 지원하는 버전 저장소를 처리하기에 tempdb에 충분한 여유 공간이 있어야 합니다.

  다음 수식은 버전 저장소의 대략적인 예상 크기를 제공합니다. 장기 트랜잭션의 경우 생성 및 정리 속도를 모니터링하여 버전 저장소의 최대 크기를 예측하는 것이 유용할 수 있습니다.

  [일반 버전 저장소의 크기] = 2 * [분당 생성된 버전 저장소 데이터] * [트랜잭션의 가장 긴 실행 시간(분)]

  트랜잭션의 가장 긴 실행 시간에는 온라인 인덱스 빌드가 포함되지 않아야 합니다. 이러한 작업은 매우 큰 테이블에서 시간이 오래 걸릴 수 있으므로 온라인 인덱스 빌드는 별도의 버전 저장소를 사용합니다. 온라인 인덱스 작성 버전 저장소의 크기는 온라인 인덱스 작성을 수행하는 동안 모든 인덱스를 포함하여 테이블에서 수정된 전체 데이터 양과 거의 같습니다.

• Version Store Size(KB). tempdb의 모든 버전 저장소의 크기(KB)를 모니터링합니다. 이 정보를 통해 tempdb 데이터베이스의 버전 저장소에 필요한 공간을 결정할 수 있습니다. 일정 기간 동안 이 카운터를 모니터링하면 tempdb에 필요한 추가 공간을 예상할 수 있습니다.

• Version Generation rate (KB/s). tempdb의 모든 버전 저장소에서 초당 KB로 버전 생성 속도를 모니터링합니다.

• Version Cleanup rate (KB/s). tempdb의 모든 버전 저장소에서 초당 KB로 버전 정리 속도를 모니터링합니다.

  참고 항목

  버전 생성 속도(KB/s) 및 버전 정리 속도(KB/s)의 정보를 사용하여 tempdb 공간 요구 사항을 예측할 수 있습니다.

• Version Store unit count. 버전 저장소 단위 수를 모니터링합니다.

• Version Store unit creation. 인스턴스가 시작된 이후 행 버전 저장을 위해 생성된 버전 저장소 단위의 총 수를 모니터링합니다.

• Version Store unit truncation. 인스턴스가 시작된 이후 잘린 버전 저장소 단위의 총 수를 모니터링합니다. SQL Server에서 버전 저장소 단위에 저장된 버전 행이 활성 트랜잭션을 실행하는 데 불필요하다고 결정하면 버전 저장소 단위가 잘립니다.

• Update conflict ratio. 총 업데이트 스냅샷 트랜잭션 중 업데이트 충돌이 있는 업데이트 스냅샷 트랜잭션의 비율을 모니터링합니다.

• Longest Transaction Running Time. 행 버전 관리를 사용하여 트랜잭션의 가장 긴 실행 시간(초)을 모니터링합니다. 이 정보를 사용하면 예기치 않게 오래 실행되는 트랜잭션이 있는지를 확인할 수 있습니다.

• 트랜잭션. 활성 트랜잭션의 총 수를 모니터링합니다. 여기에는 시스템 트랜잭션이 포함되지 않습니다.

• Snapshot Transactions. 활성 스냅샷 트랜잭션의 총 수를 모니터링합니다.

• Update Snapshot Transactions. 업데이트 작업을 수행하는 활성 스냅샷 트랜잭션의 총 수를 모니터링합니다.

• NonSnapshot Version Transactions. 버전 레코드를 생성하는 스냅샷이 아닌 활성 트랜잭션의 총 수를 모니터링합니다.

  참고 항목

  Update Snapshot Transactions과 NonSnapshot Version Transactions의 합계는 버전 생성에 참여하는 총 트랜잭션 수를 나타냅니다. Snapshot Transactions와 Update Snapshot Transactions 값의 차이를 보고 읽기 전용 스냅샷 트랜잭션의 수를 알 수 있습니다.

행 버전 관리 기반 격리 수준 예

다음 예에서는 SNAPSHOT 격리 트랜잭션과 행 버전 관리를 사용하는 READ COMMITTED 트랜잭션 동작의 차이를 보여 줍니다.

A. 스냅샷 격리 작업

이 예제에서는 SNAPSHOT 격리에서 실행되는 트랜잭션이 다른 트랜잭션에 의해 수정된 데이터를 읽습니다. SNAPSHOT 트랜잭션은 다른 트랜잭션에서 실행하는 업데이트 작업을 차단하지 않으며 데이터 수정을 무시하고 계속 버전이 지정된 행에서 데이터를 읽습니다. 그러나 SNAPSHOT 트랜잭션이 다른 트랜잭션에 의해 이미 수정된 데이터를 수정하려고 하면 SNAPSHOT 트랜잭션이 오류를 생성하고 종료됩니다.

세션 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

세션 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

세션 1:

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

세션 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

세션 1:

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B. 행 버전 관리를 사용하는 읽기 커밋 격리 작업

이 예제에서는 행 버전 관리를 사용하는 READ COMMITTED 트랜잭션이 다른 트랜잭션과 동시에 실행됩니다. READ COMMITTED 트랜잭션은 SNAPSHOT 트랜잭션과 다르게 동작합니다. SNAPSHOT 트랜잭션과 마찬가지로 READ COMMITTED 트랜잭션은 다른 트랜잭션이 데이터를 수정한 후에도 버전이 지정된 행을 읽습니다. 그러나 SNAPSHOT 트랜잭션과 달리 READ COMMITTED 트랜잭션은 다음을 수행합니다.

• 다른 트랜잭션이 데이터 변경 내용을 커밋한 후 수정된 데이터를 읽습니다.
• SNAPSHOT 트랜잭션이 할 수 없는 다른 트랜잭션에서 수정한 데이터를 업데이트할 수 있습니다.

세션 1:

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

세션 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

세션 1:

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

세션 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

세션 1:

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

행 버전 관리 기반 격리 수준 활성화

데이터베이스 관리자는 ALTER DATABASE 문의 READ_COMMITTED_SNAPSHOT 및 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 사용하여 행 버전 관리에 대한 데이터베이스 수준 설정을 제어합니다.

READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON으로 설정되면 옵션을 지원하는 데 사용되는 메커니즘이 즉시 활성화됩니다. READ_COMMITTED_SNAPSHOT 옵션을 설정할 때는 ALTER DATABASE 명령을 실행하는 연결만 데이터베이스에서 허용됩니다. ALTER DATABASE 명령 실행이 완료될 때까지 데이터베이스에서 다른 열린 연결이 없어야 합니다. 데이터베이스가 단일 사용자 모드에 있을 필요는 없습니다.

다음 Transact-SQL 문이 READ_COMMITTED_SNAPSHOT을 지원합니다.

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션이 ON으로 설정되면 데이터베이스에서 데이터를 수정한 모든 활성 트랜잭션이 완료될 때까지 데이터베이스 엔진 인스턴스가 수정된 데이터에 대한 행 버전 생성을 시작하지 않습니다. 활성 수정 트랜잭션이 있으면 데이터베이스 엔진에서 이 옵션의 상태를 PENDING_ON으로 설정합니다. 모든 수정 트랜잭션이 완료되면 옵션의 상태가 ON으로 변경됩니다. 사용자는 옵션이 완전히 ON이 될 때까지 해당 데이터베이스에서 SNAPSHOT 트랜잭션을 시작할 수 없습니다. 마찬가지로 데이터베이스 관리자가 ALLOW_SNAPSHOT_ISOLATION 옵션을 OFF로 설정하는 경우 데이터베이스가 PENDING_OFF 상태를 통과합니다.

다음 Transact-SQL 문이 ALLOW_SNAPSHOT_ISOLATION을 지원할 것입니다.

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

다음 표에서는 ALLOW_SNAPSHOT_ISOLATION 옵션의 상태를 나열하고 설명합니다. ALLOW_SNAPSHOT_ISOLATION 옵션과 함께 ALTER DATABASE를 사용하면 현재 데이터베이스 데이터에 액세스하는 사용자를 차단하지 않습니다.

현재 데이터베이스에 대한 SNAPSHOT 격리 상태	설명
OFF	SNAPSHOT 격리 트랜잭션에 대한 지원은 활성화되지 않습니다. SNAPSHOT 격리 트랜잭션은 허용되지 않습니다.
PENDING_ON	SNAPSHOT 격리 트랜잭션에 대한 지원이 OFF에서ON로 전환되는 중입니다. 열린 트랜잭션을 완료해야 합니다. SNAPSHOT 격리 트랜잭션은 허용되지 않습니다.
ON	SNAPSHOT 격리 트랜잭션에 대한 지원이 활성화됩니다. SNAPSHOT트랜잭션이 허용됩니다.
PENDING_OFF	SNAPSHOT 격리 트랜잭션에 대한 지원이 ON에서OFF로 전환되는 중입니다. 이 시간 이후에 시작된 SNAPSHOT 트랜잭션은 이 데이터베이스에 액세스할 수 없습니다. 기존 SNAPSHOT 트랜잭션은 이 데이터베이스에 계속 액세스할 수 있습니다. 기존 쓰기 트랜잭션은 여전히 이 데이터베이스에서 버전 관리 작업을 사용합니다. 데이터베이스 SNAPSHOT 격리 상태가 ON 완료될 때 시작된 모든 SNAPSHOT 트랜잭션까지 PENDING_OFF 상태가 OFF되지는 않습니다.

두 행 버전 관리 데이터베이스 옵션의 상태를 확인하려면 sys.databases 카탈로그 뷰를 사용합니다.

모든 사용자 테이블과 master 및 msdb에 저장된 일부 시스템 테이블에 대한 모든 업데이트는 행 버전을 생성합니다.

ALLOW_SNAPSHOT_ISOLATION 옵션은 데이터베이스 master 및 msdb 데이터베이스 에서 자동으로 ON으로 설정되며 사용하지 않도록 설정할 수 없습니다.

사용자는 master, tempdb 또는 msdb에서 READ_COMMITTED_SNAPSHOT 옵션을 ON으로 설정할 수 없습니다.

행 버전 관리 기반 격리 수준 사용

행 버전 관리 프레임워크는 항상 사용하도록 설정되며 여러 기능에서 사용됩니다. 행 버전 관리 기반 격리 수준을 제공하는 것 외에도 트리거 및 MARS(여러 활성 결과 집합) 세션에서 수정한 내용을 지원하고 온라인 인덱스 작업에 대한 데이터 읽기를 지원하는 데 사용됩니다.

행 버전 관리 기반 격리 수준은 데이터베이스 수준에서 설정됩니다. 설정된 데이터베이스의 개체에 액세스하는 애플리케이션은 모두 다음과 같은 격리 수준을 사용하여 쿼리를 실행할 수 있습니다.

• 다음 코드 예제와 같이 READ_COMMITTED_SNAPSHOT 데이터베이스 옵션을 ON으로 설정하여 행 버전 관리 작업을 사용하는 READ COMMITTED 전용입니다.

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  READ_COMMITTED_SNAPSHOT에서 데이터베이스를 사용하도록 설정하면 READ COMMITTED 격리 수준에서 실행되는 모든 쿼리는 행 버전 관리를 사용합니다. 즉, 읽기 작업이 업데이트 작업을 차단하지 않습니다.

• 다음 코드 예제와 같이 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정하여 SNAPSHOT 격리를 수행합니다.

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  데이터베이스 간 쿼리를 사용하는 경우 SNAPSHOT 격리 상태에서 실행되는 트랜잭션은 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션이 ON으로 설정된 데이터베이스의 테이블에 액세스할 수 있습니다. ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션이 ON으로 설정되지 않은 데이터베이스의 테이블에 액세스하려면 격리 수준을 변경해야 합니다. 예를 들어 다음 코드 예제에서는 SNAPSHOT 트랜잭션에서 실행되는 동안 두 테이블을 조인하는 SELECT 문을 보여줍니다. 하나의 테이블은 SNAPSHOT 격리를 사용하도록 설정되지 않은 데이터베이스에 속합니다. SELECT 문이 SNAPSHOT 격리 상태에서 실행되면 성공적으로 실행되지 않습니다.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

  다음 코드 예제에서는 특정 테이블에 액세스하고 있을 때 트랜잭션 격리 수준을 READ COMMITTED로 변경하도록 수정된 동일한 SELECT 문을 보여 줍니다. 이 변경으로 인해 SELECT 문이 성공적으로 실행됩니다.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1 WITH (READCOMMITTED)
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

행 버전 관리 기반 격리 수준을 사용하는 트랜잭션의 제한 사항

행 버전 관리 기반 격리 수준으로 작업할 때는 다음과 같은 제한 사항을 고려합니다.

• READ_COMMITTED_SNAPSHOT은 tempdb, msdb 또는 master에서 사용할 수 없습니다.

• 전역 임시 테이블은 tempdb에 저장됩니다. SNAPSHOT 트랜잭션 내의 전역 임시 테이블에 액세스할 때 다음 중 하나가 발생해야 합니다.

  • tempdb에서 ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션을 ON으로 설정합니다.
  • 격리 힌트를 사용하여 문에 대한 격리 수준을 변경합니다.

• SNAPSHOT 트랜잭션은 다음과 같은 경우 실패합니다.

  • SNAPSHOT 트랜잭션이 시작된 후 SNAPSHOT 트랜잭션이 데이터베이스에 액세스하기 전에 데이터베이스가 읽기 전용으로 만들어집니다.
  • 여러 데이터베이스에서 개체에 액세스하는 경우 SNAPSHOT 트랜잭션이 시작된 후 SNAPSHOT 트랜잭션이 데이터베이스에 액세스하기 전에 데이터베이스 복구가 발생하는 방식으로 데이터베이스 상태가 변경되었습니다. 예를 들어 데이터베이스가 OFFLINE 그 다음 ONLINE으로 설정된 경우, AUTO_CLOSE 옵션 집합이 ON로 설정됨으로 인해 데이터베이스가 자동으로 닫히고 다시 열리는 경우, 또는 데이터베이스가 분리되어 다시 연결되는 경우입니다.

• 분산 분할된 데이터베이스의 쿼리를 포함한 분산 트랜잭션은 SNAPSHOT 격리에서 지원되지 않습니다.

• 데이터베이스 엔진은 여러 버전의 시스템 메타데이터를 유지하지 않습니다. 테이블 및 기타 데이터베이스 개체(인덱스, 뷰, 데이터 형식, 저장 프로시저 및 공용 언어 런타임 함수)의 DDL(데이터 정의 언어) 문은 메타데이터를 변경합니다. DDL 문이 개체를 수정하면 SNAPSHOT 격리의 개체에 대한 동시 참조로 인해 SNAPSHOT 트랜잭션이 실패합니다. READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON으로 설정된 경우 READ COMMITTED 트랜잭션에는 이 제한이 없습니다.

  예를 들어 데이터베이스 관리자가 다음 ALTER INDEX 문을 실행합니다.

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD;
  GO
  

  ALTER INDEX 문이 실행될 때 활성 상태인 스냅샷 트랜잭션은 ALTER INDEX 문이 실행된 후 HumanResources.Employee 테이블을 참조하려고 하면 오류를 받습니다. 행 버전 관리를 사용하는 READ COMMITTED 트랜잭션은 영향을 받지 않습니다.

  참고 항목

  BULK INSERT 작업을 수행할 때 대상 테이블 메타데이터가 변경될 수 있습니다. 제약 조건 검사를 해제한 경우를 예로 들 수 있습니다. 이 경우 대량 삽입 테이블에 액세스하는 동시 SNAPSHOT 격리 트랜잭션이 실패합니다.

잠금 및 행 버전 관리 사용자 지정

잠금 제한 시간 사용자 지정

다른 트랜잭션에서 이미 리소스에 대해 충돌되는 잠금을 소유하고 있어 데이터베이스 엔진의 인스턴스에서 트랜잭션에 잠금을 허가할 수 없는 경우 이 트랜잭션은 기존 잠금이 해제되기를 기다리면서 차단됩니다. 기본적으로 잠금 대기에 대한 제한 시간은 없으므로 트랜잭션이 무기한 차단될 가능성이 있습니다.

LOCK_TIMEOUT 설정을 통해 애플리케이션은 문이 차단된 리소스를 기다리는 최대 시간을 설정할 수 있습니다. 문이 LOCK_TIMEOUT 설정보다 오래 기다린 경우 차단된 문은 자동으로 취소되고 오류 메시지 1222(Lock request time-out period exceeded)가 반환됩니다. 그러나 문을 포함하는 트랜잭션은 롤백되지 않습니다. 따라서 애플리케이션에는 오류 메시지 1222를 트래핑할 수 있는 오류 처리기가 있어야 합니다. 애플리케이션이 오류를 트래핑하지 않으면 트랜잭션 내의 개별 문이 취소되었지만 트랜잭션이 활성 상태로 유지된다는 사실을 모르고 애플리케이션이 계속 진행할 수 있습니다. 트랜잭션의 뒷부분에 있는 문이 실행되지 않은 문에 따라 달라질 수 있으므로 오류가 발생할 수 있습니다.

오류 메시지 1222를 트래핑하는 오류 처리기를 구현하면 애플리케이션에서 시간 초과 상황을 처리하고 차단된 문을 자동으로 다시 전송하거나 전체 트랜잭션을 롤백하는 등의 해결 동작을 취할 수 있습니다.

현재 LOCK_TIMEOUT 설정을 확인하려면 @@LOCK_TIMEOUT 함수를 실행합니다.

SELECT @@LOCK_TIMEOUT;
GO

트랜잭션 격리 수준 사용자 정의

READ COMMITTED는 데이터베이스 엔진에 대한 기본 격리 수준입니다. 애플리케이션을 다른 격리 수준에서 실행해야 하는 경우 다음과 같은 방법으로 격리 수준을 설정할 수 있습니다.

• SET TRANSACTION ISOLATION LEVEL 문을 실행합니다.
• System.Data.SqlClient이라는 네임스페이스를 사용하는 ADO.NET 애플리케이션은 SqlConnection.BeginTransaction 메서드를 사용하여 IsolationLevel 옵션을 지정할 수 있습니다.
• ADO를 사용하는 애플리케이션은 Autocommit Isolation Levels 속성을 설정할 수 있습니다.
• 트랜잭션을 시작할 때 OLE DB를 사용하는 애플리케이션은 isoLevel가 원하는 트랜잭션 격리 수준으로 설정된 상태에서 ITransactionLocal::StartTransaction을 호출할 수 있습니다. 자동 커밋 모드에서 격리 수준을 지정할 때 OLE DB를 사용하는 애플리케이션에서는 DBPROPSET_SESSION 속성 DBPROP_SESS_AUTOCOMMITISOLEVELS를 원하는 트랜잭션 격리 수준으로 설정할 수 있습니다.
• ODBC를 사용하는 애플리케이션은 SQLSetConnectAttr를 사용하여 SQL_COPT_SS_TXN_ISOLATION 특성을 설정할 수 있습니다.

격리 수준을 지정하면 세션의 모든 쿼리 및 DML(데이터 조작 언어) 문에 대한 잠금 동작이 해당 격리 수준에서 작동합니다. 세션이 종료되거나 격리 수준을 다른 수준으로 설정할 때까지 해당 격리 수준이 적용됩니다.

다음 예제에서는 SERIALIZABLE 격리 수준을 설정합니다.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

필요한 경우 테이블 수준 힌트를 지정하여 개별 쿼리 또는 DML 문에 대해 격리 수준을 재정의할 수 있습니다. 테이블 수준 힌트를 지정해도 세션의 다른 문에는 영향을 주지 않습니다.

현재 설정된 트랜잭션 격리 수준을 확인하려면 다음 예제와 같이 DBCC USEROPTIONS 문을 사용합니다. 결과 집합은 시스템의 결과 집합과 다를 수 있습니다.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

결과 집합은 다음과 같습니다.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

힌트 잠금

SELECT, INSERT, UPDATE, DELETE 및 MERGE 문의 개별 테이블 참조에 대해 잠금 힌트를 지정할 수 있습니다. 힌트는 테이블 데이터에 사용되는 데이터베이스 엔진 인스턴스의 잠금 또는 행 버전 관리 유형을 지정합니다. 테이블 수준 잠금 힌트는 개체에서 획득한 잠금 유형을 더 세밀하게 제어해야 하는 경우에 사용할 수 있습니다. 이러한 잠금 힌트는 세션의 현재 트랜잭션 격리 수준을 재정의합니다.

특정 잠금 힌트 및 해당 동작에 대한 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하세요.

데이터베이스 엔진에서는 데이터를 읽을 때 공유 잠금 요청을 방지하는 잠금 힌트를 사용하여 문을 처리하는 경우에도 메타데이터를 읽을 때 잠금을 획득해야 할 수 있습니다. 예를 들어 READ UNCOMMITTED 격리 수준에서 실행되거나 NOLOCK 힌트를 사용하는 SELECT 문은 데이터를 읽을 때 공유 잠금을 획득하지 않지만 시스템 카탈로그 뷰를 읽을 때 잠금을 요청할 수 있습니다. 즉, 동시 트랜잭션이 테이블의 메타데이터를 수정할 때 이러한 SELECT 문이 차단될 수 있습니다.

다음 예제와 같이 트랜잭션 격리 수준이 SERIALIZABLE로 설정되고 테이블 수준 잠금 힌트 NOLOCK이 SELECT 문과 함께 사용되는 경우 SERIALIZABLE 트랜잭션을 유지하는 데 일반적으로 사용되는 키 범위 잠금은 획득되지 않습니다.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

HumanResources.Employee를 참조하는 유일한 잠금은 스키마 안정성(Sch-S) 잠금입니다. 이 경우 순차성은 더 이상 보장되지 않습니다.

ALTER TABLE의 LOCK_ESCALATION 옵션은 잠금 에스컬레이션 중에 테이블 잠금을 방지하고 분할된 테이블에서 HoBT(파티션) 잠금을 사용하도록 설정합니다. 이 옵션은 잠금 힌트가 아니지만 잠금 에스컬레이션을 줄이는 데 사용할 수 있습니다. 자세한 내용은 ALTER TABLE(Transact-SQL)을 참조하세요.

인덱스 잠금 사용자 지정

데이터베이스 엔진은 대부분의 경우 쿼리에 가장 적합한 잠금 세분성을 자동으로 선택하는 동적 잠금 전략을 사용합니다. 기본 잠금 수준은 잘 알려져 있으며 일관적인 테이블 또는 인덱스 액세스 패턴이 아닌 경우 및 리소스 충돌 문제를 해결해야 하는 경우 재정의하지 않는 것이 좋습니다. 잠금 수준을 재정의하면 테이블 또는 인덱스에 대한 동시 액세스를 상당히 방해할 수 있습니다. 예를 들어 사용자가 많이 액세스하는 큰 테이블에 테이블 수준 잠금만 지정하면 테이블에 액세스하기 전에 테이블 수준 잠금이 해제될 때까지 기다려야 하기 때문에 병목 현상이 발생할 수 있습니다.

액세스 패턴이 잘 이해되고 일관된 경우 페이지 또는 행 잠금을 허용하지 않는 것이 도움이 되는 몇 가지 경우가 있습니다. 예를 들어 데이터베이스 애플리케이션은 일괄 처리 프로세스에서 매주 업데이트되는 조회 테이블을 사용합니다. 동시 판독기는 공유(S) 잠금을 사용하여 테이블에 액세스하고 주간 일괄 업데이트는 배타적(X) 잠금을 사용하여 테이블에 액세스합니다. 테이블에서 페이지 및 행 잠금을 해제하면 판독기가 공유 테이블 잠금을 통해 동시에 테이블에 액세스할 수 있어 주중 잠금 오버헤드가 줄어듭니다. 일괄 처리 작업이 실행되면 배타적 테이블 잠금을 가져오기 때문에 업데이트를 효율적으로 완료할 수 있습니다.

주간 일괄 업데이트는 업데이트가 실행되는 동안 동시 판독기가 테이블에 액세스하지 못하도록 차단하므로 페이지 및 행 잠금을 해제하는 것이 허용 가능하거나 허용되지 않을 수 있습니다. 일괄 처리 작업이 몇 개의 행 또는 페이지만 변경하는 경우 행 또는 페이지 수준 잠금을 허용하도록 잠금 수준을 변경할 수 있습니다. 그러면 다른 세션이 차단 없이 테이블에서 읽을 수 있습니다. 업데이트가 다수 포함된 일괄 작업의 경우, 테이블에 대한 배타적 잠금이 효율적인 일괄 작업 실행을 위한 최상의 방법일 수 있습니다.

일부 워크로드에서는 두 개의 동시 작업이 동일한 테이블에서 행 잠금을 획득한 다음 둘 다 페이지를 잠가야 하기 때문에 서로를 차단할 때 교착 상태 유형이 발생할 수 있습니다. 행 잠금을 허용하지 않으면 작업 중 하나가 대기하여 교착 상태를 방지합니다. 교착 상태에 대한 자세한 내용은 교착 상태 가이드를 참조하세요.

인덱스에 사용되는 잠금의 세분성은 CREATE INDEX 및 ALTER INDEX 문을 사용하여 설정할 수 있습니다. 또한 및 CREATE TABLE 및 ALTER TABLE 문을 사용하여 PRIMARY KEY 및 UNIQUE 제약 조건에 대한 잠금 세분성을 설정할 수 있습니다. 이전 버전과의 호환성을 위해 sp_indexoption 시스템 저장 프로시저도 세분성을 설정할 수 있습니다. 지정된 인덱스 현재 잠금 옵션을 표시하려면 INDEXPROPERTY 함수를 사용합니다. 페이지 수준 잠금, 행 수준 잠금 또는 페이지 수준과 행 수준 잠금을 모두 지정된 인덱스에서 허용지 않을 수 있습니다.

허용되지 않는 잠금	인덱스에 액세스하는 항목
페이지 수준	행 수준 및 테이블 수준 잠금
행 수준	페이지 수준 및 테이블 수준 잠금
페이지 수준 및 행 수준	테이블 수준 잠금

고급 트랜잭션 정보

중첩된 트랜잭션

명시적 트랜잭션은 중첩될 수 있습니다. 이는 주로 트랜잭션에 이미 있는 프로세스 또는 활성 트랜잭션이 없는 프로세스에서 호출할 수 있는 저장 프로시저의 트랜잭션을 지원하기 위한 것입니다.

다음 예는 중첩된 트랜잭션의 용도를 보여줍니다. 트랜잭션이 활성화될 때 TransProc가가 호출되는 경우 TransProc에 중첩된 트랜잭션의 결과는 외부 트랜잭션에 의해 제어되며 해당 INSERT 문은 외부 트랜잭션의 커밋 또는 롤백에 따라 커밋되거나 롤백됩니다. 처리 중인 트랜잭션이 없는 프로세스에서 TransProc를 실행하면 프로시저 마지막에서 COMMIT TRANSACTION이 INSERT 문을 커밋합니다.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

내부 트랜잭션 커밋은 외부 트랜잭션이 활성 상태일 때 데이터베이스 엔진이 무시합니다. 트랜잭션은 가장 바깥쪽 트랜잭션이 끝날 때 커밋이나 롤백에 따라 커밋되거나 롤백됩니다. 외부 트랜잭션이 커밋되면 내부 중첩 트랜잭션도 커밋됩니다. 외부 트랜잭션이 롤백되면 내부 트랜잭션이 개별적으로 커밋되었는지 여부에 관계없이 모든 내부 트랜잭션도 롤백됩니다.

COMMIT TRANSACTION 또는 COMMIT WORK에 대한 각 호출은 마지막으로 실행된 BEGIN TRANSACTION에 적용됩니다. BEGIN TRANSACTION 문을 중첩하면 COMMIT 문이 마지막으로 중첩된 트랜잭션, 즉 가장 안쪽의 트랜잭션에만 적용됩니다. 중첩된 트랜잭션 내의 COMMIT TRANSACTION transaction_name 문이 바깥쪽 트랜잭션의 트랜잭션 이름을 참조해도 커밋은 가장 안쪽의 트랜잭션에만 적용됩니다.

ROLLBACK TRANSACTION 문의 transaction_name 매개 변수가 명명된 중첩 트랜잭션 집합에서 내부 트랜잭션을 참조하는 것은 허용되지 않습니다. transaction_name은 가장 바깥쪽 트랜잭션의 트랜잭션 이름만 참조할 수 있습니다. 외부 트랜잭션의 이름을 사용하는 ROLLBACK TRANSACTION transaction_name 문이 중첩된 트랜잭션 집합의 모든 수준에서 실행되면 모든 중첩 트랜잭션이 롤백됩니다. ROLLBACK WORK 또는 ROLLBACK TRANSACTION 문이 transaction_name 매개 변수 없이 중첩 트랜잭션 집합의 특정 수준에서 실행되면 가장 바깥쪽 트랜잭션을 포함하여 모든 중첩 트랜잭션이 롤백됩니다.

@@TRANCOUNT 함수는 현재 트랜잭션 중첩 수준을 기록합니다. 각 BEGIN TRANSACTION 문은 @@TRANCOUNT를 하나씩 증가시킵니다. COMMIT TRANSACTION 또는 COMMIT WORK 문은 각기 @@TRANCOUNT을 1씩 감소시킵니다. 트랜잭션 이름이 없는 ROLLBACK WORK 또는 ROLLBACK TRANSACTION 문은 중첩된 모든 트랜잭션을 롤백하고 @@TRANCOUNT을 0으로 감소시킵니다. 중첩된 트랜잭션 집합에서 가장 바깥쪽 트랜잭션의 트랜잭션 이름을 사용하는 ROLLBACK TRANSACTION는 중첩된 모든 트랜잭션을 롤백하고 @@TRANCOUNT을 0으로 감소시킵니다. 트랜잭션 안에 있는지 결정하려면 SELECT @@TRANCOUNT로 1 이상인지 확인합니다. @@TRANCOUNT이 0이면 트랜잭션에 있지 않습니다.

바운드 세션 사용

바운드 세션은 동일한 서버의 여러 세션에서 작업을 쉽게 조정할 수 있습니다. 바운드 세션을 사용하면 두 개 이상의 세션이 동일한 트랜잭션과 잠금을 공유할 수 있으며 잠금 충돌 없이 동일한 데이터에서 작업할 수 있습니다. 바운드 세션은 같은 애플리케이션 내의 여러 세션에서 생성되거나 개별 세션의 여러 애플리케이션에서 생성될 수 있습니다.

바운드 세션에 참여하려면 세션에서 개방형 Data Services를 통한 srv_getbindtoken이나 sp_getbindtoken을 호출하여 바인드 토큰을 가져와야 합니다. 바인딩 토큰은 바인딩된 각 트랜잭션을 고유하게 식별하는 문자열입니다. 그러면 바인딩 토큰이 현재 세션과 바인딩될 다른 세션으로 전송됩니다. 다른 세션은 첫 번째 세션에서 받은 바인딩 토큰을 사용하여 sp_bindsession를 호출하고 트랜잭션에 바인딩합니다.

바인딩 토큰은 첫 번째 세션을 만드는 애플리케이션 코드에서 이후에 세션을 첫 번째 세션에 바인딩하는 애플리케이션 코드로 전송되어야 합니다. 애플리케이션이 다른 프로세스에서 시작하는 트랜잭션에 대한 바인딩 토큰을 가져오는 데 사용할 수 있는 Transact-SQL 문 또는 API 함수는 없습니다. 바인딩 토큰을 전송하는 데 사용할 수 있는 몇 가지 메서드는 다음과 같습니다.

• 세션이 모두 동일한 애플리케이션 프로세스에서 시작되는 경우 바인딩 토큰을 전역 메모리에 저장하거나 매개 변수로 함수에 전달할 수 있습니다.

• 세션이 별도의 애플리케이션 프로세스에서 수행되는 경우 RPC(원격 프로시저 호출) 또는 DDE(동적 데이터 교환)와 같은 IPC(프로세스 간 통신)를 사용하여 바인딩 토큰을 전송할 수 있습니다.

• 바인딩 토큰은 첫 번째 세션에 바인딩하려는 프로세스에서 읽을 수 있는 데이터베이스 엔진 인스턴스의 테이블에 저장할 수 있습니다.

바인딩된 세션 집합의 세션 하나만 언제든지 활성화할 수 있습니다. 세션이 인스턴스에서 문을 실행하고 있거나 인스턴스로부터 보류 중인 결과를 받으면 동일한 토큰에 연결된 다른 세션은 현재 세션이 처리를 마치거나 현재 문을 취소할 때까지 해당 인스턴스에 액세스할 수 없습니다. 인스턴스가 다른 바인딩된 세션의 문을 처리하는 중이면 트랜잭션 공간이 사용 중이며 세션이 나중에 다시 시도해야 함을 나타내는 오류가 발생합니다.

세션을 바인딩하는 경우 각 세션은 해당 격리 수준 설정을 유지합니다. SET TRANSACTION ISOLATION LEVEL을 사용하여 한 세션의 격리 수준 설정을 변경해도 동일한 토큰에 바인딩된 다른 세션의 설정에는 영향을 주지 않습니다.

바운드 세션 유형

두 가지 유형의 바인딩된 세션은 로컬이며 분산됩니다.

• 로컬 바인딩된 세션 바인딩된 세션이 데이터베이스 엔진의 단일 인스턴스에서 단일 트랜잭션의 트랜잭션 공간을 공유할 수 있도록 허용합니다.

• 분산 바인딩된 세션 MS DTC(Microsoft Distributed Transaction Coordinator)를 사용하여 전체 트랜잭션이 커밋되거나 롤백될 때까지 바운드 세션이 둘 이상의 인스턴스에서 동일한 트랜잭션을 공유할 수 있도록 허용합니다.

분산 바운드 세션은 문자열 바인드 토큰으로 식별되지 않고 분산 트랜잭션 식별 번호로 식별됩니다. 바운드 세션이 로컬 트랜잭션에 관여하고 SET REMOTE_PROC_TRANSACTIONS ON인 원격 서버에서 RPC를 실행하는 경우 로컬 바인딩된 트랜잭션은 MS DTC에 의해 분산 바인딩된 트랜잭션으로 자동으로 승격되고 MS DTC 세션이 시작됩니다.

바운드 세션 사용 시기

이전 버전의 SQL Server에서는 바인딩된 세션이 호출하는 프로세스를 대신하여 Transact-SQL 문을 실행해야 하는 확장 저장 프로시저를 개발하는 데 주로 사용되었습니다. 호출 프로세스에서 바인드 토큰을 확장 저장 프로시저의 한 매개 변수로 전달하도록 설정하면 프로시저가 호출 프로세스의 트랜잭션 공간에 참여하여 호출 프로시저와 확장 저장 프로시저가 통합됩니다.

데이터베이스 엔진에서 CLR을 사용하여 작성된 저장 프로시저는 확장 저장 프로시저보다 더 안전하고 확장 가능하며 안정적입니다. CLR 저장 프로시저는 SqlContext 개체를 사용하여 sp_bindsession이 아닌 호출 세션의 컨텍스트에 조인합니다.

바운드 세션은 비즈니스 논리가 단일 비즈니스 트랜잭션에서 협조적으로 작동하는 별도의 프로그램에 통합되는 3계층 애플리케이션을 개발하는 데 사용할 수 있습니다. 이러한 프로그램의 경우 데이터베이스 액세스를 잘 조정하도록 코드를 작성해야 합니다. 두 세션이 동일한 잠금을 공유하므로 두 프로그램에서 동시에 동일한 데이터를 수정하려고 하면 안 됩니다. 언제든지 하나의 세션만 트랜잭션의 일부로 작업을 수행할 수 있습니다. 병렬 실행이 있을 수 없습니다. 트랜잭션은 모든 DML 문이 완료되고 결과가 검색된 경우와 같이 잘 정의된 yield 지점에서만 세션 간에 전환할 수 있습니다.

코드 효율적인 트랜잭션

트랜잭션을 가능한 한 짧게 유지하는 것이 중요합니다. 트랜잭션이 시작되면 DBMS(데이터베이스 관리 시스템)가 트랜잭션이 끝날 때까지 많은 리소스를 보유하여 트랜잭션의 ACID(원자성, 일관성, 격리성, 영속성) 속성을 보호합니다. 데이터를 수정하는 경우 수정된 행은 다른 트랜잭션이 행을 읽지 못하도록 하는 배타적 잠금으로 보호되어야 하며 트랜잭션이 커밋되거나 롤백될 때까지 배타적 잠금을 유지해야 합니다. 트랜잭션 격리 수준 설정에 따라 SELECT 문에서 트랜잭션이 커밋 또는 롤백될 때까지 보유해야 하는 잠금을 획득할 수 있습니다. 특히 사용자가 많은 시스템에서는 동시 연결 간의 리소스에 대한 잠금 경합을 줄이기 위해 트랜잭션을 최대한 짧게 유지해야 합니다. 오래 실행되고 비효율적인 트랜잭션은 적은 수의 사용자에게는 문제가 되지 않을 수 있지만 수천 명의 사용자가 있는 시스템에서는 매우 문제가 됩니다. SQL Server 2014(12.x)부터 데이터베이스 엔진은 지연된 지속형 트랜잭션을 지원합니다. 지연된 지속성 트랜잭션은 확장성 및 성능을 향상시킬 수 있지만 내구성을 보장하지는 않습니다. 자세한 내용은 트랜잭션 내구성 제어를 참조하세요.

코드 지침

효율적인 트랜잭션을 코딩하기 위한 지침은 다음과 같습니다.

• 트랜잭션 중 사용자로부터 입력을 요청하지 마십시오. 트랜잭션이 시작되기 전에 사용자로부터 필요한 모든 입력을 가져옵니다. 트랜잭션 중에 추가 사용자 입력이 필요한 경우 현재 트랜잭션을 롤백하고 사용자 입력이 제공된 후 트랜잭션을 다시 시작합니다. 사용자가 즉시 응답하더라도 인간의 반응 시간은 컴퓨터 속도보다 훨씬 느립니다. 트랜잭션에 의해 보유된 모든 리소스는 꽤 긴 시간 동안 보유되므로 차단 문제가 발생할 수 있습니다. 사용자가 응답하지 않으면 트랜잭션이 활성 상태로 유지되어 응답할 때까지 중요한 리소스가 잠기며 몇 분 또는 몇 시간 동안 발생하지 않을 수 있습니다.

• 가능한 경우 데이터를 탐색하는 동안 트랜잭션을 열지 마세요. 모든 예비 데이터 분석이 완료될 때까지 트랜잭션을 시작하지 말아야 합니다.

• 트랜잭션을 최대한 짧게 유지합니다. 수정해야 하는 사항을 알고 나면 트랜잭션을 시작하고 수정 문을 실행한 다음 즉시 커밋하거나 롤백합니다. 트랜잭션이 필요해지기 전에 열지 마세요.

• 차단을 줄이려면 읽기 전용 쿼리에 행 버전 관리 기반 격리 수준을 사용하는 것이 좋습니다.

• 낮은 트랜잭션 격리 수준을 지능적으로 사용합니다. READ COMMITTED 트랜잭션 격리 수준을 사용하도록 많은 애플리케이션을 코딩할 수 있습니다. SERIALIZABLE 트랜잭션 격리 수준이 필요한 트랜잭션은 거의 없습니다.

• 낙관적 동시 실행 옵션을 지능적으로 사용하세요. 동시 업데이트 가능성이 적은 시스템에서는 한 사용자가 데이터를 읽은 후 다른 사용자가 해당 데이터를 변경하여 발생하는 오류를 처리하는 오버헤드가 데이터를 읽을 때마다 행을 잠그는 오버헤드보다 훨씬 적을 수 있습니다.

• 트랜잭션 중에 가능한 최소 데이터 양에 액세스합니다. 이렇게 하면 잠긴 행 수가 줄어들어 트랜잭션 간의 경합이 줄어듭니다.

• 가능하면 HOLDLOCK과 같은 최악 잠금 힌트를 피합니다. HOLDLOCK 또는 SERIALIZABLE 격리 수준과 같은 힌트로 프로세스가 공유 잠금에서도 대기하고 동시 실행을 줄일 수 있습니다.

• 가능하면 암시적 트랜잭션을 사용하지 마세요. 암시적 트랜잭션의 특성으로 인해 예측 불가능한 동작이 발생할 수 있습니다. 암시적 트랜잭션 및 동시 실행 문제를 참조하세요.

암시적 트랜잭션과 동시성 및 리소스 문제 방지

동시성 및 리소스 문제를 방지하려면 암시적 트랜잭션을 신중하게 관리합니다. 암시적 트랜잭션을 사용하는 경우 COMMIT 또는 ROLLBACK 이후의 다음 Transact-SQL 문이 새 트랜잭션을 자동으로 시작합니다. 이로 인해 애플리케이션이 데이터를 탐색하는 동안 또는 사용자의 입력이 필요한 경우에도 새 트랜잭션이 열릴 수 있습니다. 데이터 수정을 보호하는 데 필요한 마지막 트랜잭션을 완료한 다음 데이터 수정을 보호하기 위해 트랜잭션이 다시 한 번 필요할 때까지 암시적 트랜잭션을 해제합니다. 이 프로세스를 통해 애플리케이션이 데이터를 검색하고 사용자로부터 입력을 받는 동안 데이터베이스 엔진이 자동 커밋 모드를 사용합니다.

또한 SNAPSHOT 격리 수준을 사용하도록 설정하면 새 트랜잭션이 잠금을 보유하지 않지만 장기 실행 트랜잭션은 이전 버전이 버전 저장소에서 제거되지 않도록 합니다.

장기 실행 트랜잭션 관리

장기 실행 트랜잭션은 적시에 커밋하거나 롤백하지 않은 활성 트랜잭션입니다. 예를 들어 트랜잭션의 시작과 끝을 사용자가 제어하는 경우 장기 실행 트랜잭션의 일반적인 원인은 트랜잭션을 시작한 다음 트랜잭션이 사용자의 응답을 기다리는 동안 나가는 것입니다.

장기 실행 트랜잭션은 다음과 같이 데이터베이스에 심각한 문제를 일으킬 수 있습니다.

• 활성 트랜잭션이 커밋되지 않은 많은 수정 작업을 수행한 후에 서버 인스턴스가 종료되면 서버 인스턴스가 다시 시작된 후의 복구 단계 수행 시 recovery interval 서버 구성 옵션 또는 ALTER DATABASE ... SET TARGET_RECOVERY_TIME 옵션에 의해 지정된 시간보다 오래 걸릴 수 있습니다. 이러한 옵션은 각각 활성 검사점과 간접 검사점을 제어합니다. 검사점 유형에 대한 자세한 내용은 데이터베이스 검사점(SQL Server)을 참조하세요.

• 더 중요한 것은 대기 중인 트랜잭션이 로그를 거의 생성하지 않을 수 있지만 로그 잘림이 무기한 유지되어 트랜잭션 로그가 증가하고 채워질 수 있다는 것입니다. 트랜잭션 로그가 채워지면 데이터베이스는 더 이상 쓰기를 수행할 수 없습니다. 자세한 내용은 SQL Server 트랜잭션 로그 아키텍처 및 관리 가이드, 전체 트랜잭션 로그 문제 해결(SQL Server Error 9002), 트랜잭션 로그를 참조하세요.

장기 실행 트랜잭션 검색

장기 실행 트랜잭션을 찾으려면 다음 중 하나를 사용합니다.

• sys.dm_tran_database_transactions

  이 동적 관리 뷰는 데이터베이스 수준에서 트랜잭션 정보를 반환합니다. 장기 실행 트랜잭션의 경우 특히 관심 있는 열에는 첫 번째 로그 레코드 시간(database_transaction_begin_time,) 현재 트랜잭션 상태(database_transaction_state), 트랜잭션 로그(database_transaction_begin_lsn)에 시작 레코드의 LSN(로그 시퀀스 번호)이 있습니다.

  자세한 내용은 sys.dm_tran_database_transactions(Transact-SQL)를 참조하세요.

• DBCC OPENTRAN

  이 문을 사용하면 트랜잭션 소유자의 사용자 ID를 식별할 수 있으므로 잠재적으로 트랜잭션의 출처를 추적하여 보다 적절하게 종료할 수 있습니다(커밋 또는 롤백). 자세한 내용은 DBCC OPENTRAN(Transact-SQL)을 참조하세요

트랜잭션 종료

특정 세션에서 트랜잭션을 종료하려면 KILL 문을 사용합니다. 그러나 특히 중요한 프로세스가 실행 중일 때는 이 문을 신중하게 사용하십시오. 자세한 내용은 KILL(Transact SQL)을 참조하세요.

교착 상태

교착 상태는 잠금과 관련된 복잡한 항목이지만 차단과는 다릅니다.

• 모니터링, 진단, 샘플을 포함하여 교착 상태에 대한 자세한 내용은 교착 상태 가이드를 참조하세요.
• Azure SQL 데이터베이스의 교착 상태에 대한 자세한 내용은 Azure SQL 데이터베이스의 교착 상태 분석 및 방지를 참조하세요.

관련 콘텐츠

• SQL Server 차단 문제 이해 및 해결을 참조하세요
• Azure SQL 데이터베이스 차단 문제의 이해 및 해결
• 트랜잭션 관련 동적 관리 뷰 및 함수(Transact-SQL)
• 행 버전 관리 오버헤드
• sys.dm_tran_locks (Transact-SQL)