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

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System(PDW)

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

트랜잭션 기본 사항

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

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

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

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

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

SQL 프로그래머는 데이터의 논리적 일관성을 적용하는 지점에서 트랜잭션을 시작하고 종료하는 역할을 담당합니다. 프로그래머가 조직의 비즈니스 규칙을 기준으로 데이터를 일관된 상태로 유지하는 데이터 수정 시퀀스를 정의해야 합니다. 프로그래머에는 SQL Server 데이터베이스 엔진 트랜잭션의 물리적 무결성을 적용할 수 있도록 이러한 수정 문이 단일 트랜잭션에 포함됩니다.

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

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

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

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

트랜잭션 제어

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

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

트랜잭션 시작

API 함수 및 Transact-SQL 문을 사용하여 명시적, 자동 커밋 또는 암시적 트랜잭션으로 SQL Server 데이터베이스 엔진 인스턴스에서 트랜잭션을 시작할 수 있습니다.

명시적 트랜잭션
명시적 트랜잭션은 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 수정하는 시스템 프로시저를 설정합니다.

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

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

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

• ALTER TABLE

• CREATE

• Delete

• DROP

• FETCH

• GRANT

• INSERT

• OPEN

• REVOKE

• SELECT

• TRUNCATE TABLE

• UPDATE

• MARS(여러 활성 결과 집합)에만 적용할 수 있는 일괄 처리 범위 트랜잭션 은 MARS 세션에서 시작되는 Transact-SQL 명시적 또는 암시적 트랜잭션이 일괄 처리 범위 트랜잭션이 됩니다. 일괄 처리가 완료될 때 커밋되거나 롤백되지 않는 일괄 처리 범위 트랜잭션은 SQL Server에서 자동으로 롤백됩니다.

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

  둘 이상의 데이터베이스에 걸쳐 있는 SQL Server 데이터베이스 엔진 단일 인스턴스 내의 트랜잭션은 실제로 분산 트랜잭션입니다. 인스턴스는 내부적으로 분산 트랜잭션을 관리합니다. 사용자에게 로컬 트랜잭션으로 작동합니다.

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

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

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

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

트랜잭션 종료

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

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

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

트랜잭션 처리 중 오류

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

런타임 문 오류(예: 제약 조건 위반)가 일괄 처리에서 발생하는 경우 SQL Server 데이터베이스 엔진 기본 동작은 오류를 생성한 문만 롤백하는 것입니다. 이 동작은 SET XACT_ABORT 문을 사용하여 변경할 수 있습니다. 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 예외를 발생시키고 실행을 구문 블록으로 CATCHTRY...CATCH 전송할 수 있습니다. 자세한 내용은 THROW(Transact-SQL)를 참조하세요.

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

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

SQL Server 데이터베이스 엔진 실행 시간까지 개체 이름이 확인되지 않는 지연된 이름 확인을 사용합니다. 다음 예제에서는 처음 두 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

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

SQL Server 데이터베이스 엔진 다음 메커니즘을 사용하여 트랜잭션의 무결성을 보장하고 여러 사용자가 동시에 데이터에 액세스할 때 데이터베이스의 일관성을 기본.

• 잠금

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

• 행 버전 관리

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

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

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

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

    사용하는 READ UNCOMMITTED경우 쿼리가 할당 순서 검색(IAM 페이지 사용)을 사용하여 행을 읽는 경우 다른 트랜잭션이 페이지 분할을 일으키는 경우 행을 놓칠 수 있습니다. 이 문제는 페이지 분할 중에 테이블 잠금이 유지되고 테이블에 클러스터형 인덱스가 없는 경우 업데이트로 인해 페이지 분할이 발생하지 않기 때문에 커밋된 읽기를 사용할 때 발생할 수 없습니다.

동시성 유형

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

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

• 비관적 동시성 제어

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

• 낙관적 동시성 제어

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

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

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

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

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

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

격리 수준이 낮을수록 동시에 데이터를 액세스할 수 있는 사용자가 많아지지만 동시성 부작용(예: 커밋되지 않은 읽기 또는 업데이트 손실) 횟수도 늘어납니다. 반대로 격리 수준이 높을수록 사용자가 발생할 수 있는 동시성 효과 유형이 줄어들지만 시스템 리소스가 더 많이 필요하며 한 트랜잭션이 다른 트랜잭션을 차단할 가능성이 높아질 수 있습니다. 적절한 격리 수준을 선택하는 것은 각 격리 수준의 오버헤드와 애플리케이션의 데이터 무결성 요구 사항의 균형을 맞추는 데 달려 있습니다. 직렬화 가능한 가장 높은 격리 수준은 트랜잭션이 읽기 작업을 반복할 때마다 정확히 동일한 데이터를 검색하도록 보장하지만 다중 사용자 시스템의 다른 사용자에게 영향을 줄 수 있는 잠금 수준을 수행하여 이 작업을 수행합니다. 커밋되지 않은 읽기의 가장 낮은 격리 수준은 수정되었지만 다른 트랜잭션에서 커밋되지 않은 데이터를 검색할 수 있습니다. 모든 동시성 부작용은 커밋되지 않은 읽기에서 발생할 수 있지만 읽기 잠금 또는 버전 관리가 없으므로 오버헤드가 최소화됩니다.

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

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

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

SQL Server는 행 버전 관리도 사용하는 두 개의 추가 트랜잭션 격리 수준을 지원합니다. 하나는 READ COMMITTED 격리의 구현이고, 하나는 트랜잭션 격리 수준인 스냅샷.

행 버전 관리 격리 수준	정의
커밋된 스냅샷 읽기(RCSI)	READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON으로 설정되면 READ COMMITTED 격리는 행 버전 관리 기능을 사용하여 문 수준 읽기 일관성을 제공합니다. 읽기 작업에는 SCH-S 테이블 수준 잠금만 필요하고 페이지 또는 행 잠금은 필요하지 않습니다. 즉, SQL Server 데이터베이스 엔진 행 버전 관리를 사용하여 문 시작 부분에 존재했던 데이터의 트랜잭션 일치 스냅샷 각 문을 제공합니다. 다른 트랜잭션에 의한 데이터 업데이트 차단을 위해 잠금이 사용되지는 않습니다. 사용자 정의 함수는 UDF를 포함하는 구문 시간이 시작된 후에 커밋된 데이터를 반환할 수 있습니다. READ_COMMITTED_SNAPSHOT 기본 설정인 데이터베이스 옵션이 OFF로 설정되면 READ COMMITTED 격리는 공유 잠금을 사용하여 현재 트랜잭션이 읽기 작업을 실행하는 동안 다른 트랜잭션이 행을 수정하지 못하도록 합니다. 또한 공유 잠금은 다른 트랜잭션이 완료될 때까지 해당 트랜잭션이 수정한 행을 문이 읽을 수 없도록 합니다. 두 구현 모두 READ COMMITTED 격리의 ISO 정의를 충족합니다.
스냅샷	스냅샷 격리 수준은 행 버전 관리 기능을 사용하여 트랜잭션 수준 읽기 일관성을 제공합니다. 읽기 작업은 페이지 또는 행 잠금을 획득하지 않습니다. SCH-S 테이블 잠금만 획득됩니다. 다른 트랜잭션에서 수정한 행을 읽을 때 트랜잭션이 시작될 때 존재했던 행의 버전을 검색합니다. 데이터베이스 옵션이 ON으로 설정된 경우에만 데이터베이스에 대한 스냅샷 격리를 ALLOW_SNAPSHOT_ISOLATION 사용할 수 있습니다. 기본적으로 이 옵션은 사용자 데이터베이스에 대해 OFF로 설정됩니다. 참고: SQL Server는 메타데이터의 버전 관리가 지원되지 않습니다. 이러한 이유로 스냅샷 격리에서 실행되는 명시적 트랜잭션에서 수행할 수 있는 DDL 작업에 대한 제한 사항이 있습니다. 다음 DDL 문은 BEGIN TRANSACTION 문 이후에 스냅샷 격리에서 허용되지 않습니다. ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME 또는 CLR(공용 언어 런타임) DDL 문. 이러한 문은 암시적 트랜잭션 내에서 스냅샷 격리를 사용하는 경우 허용됩니다. 암시적 트랜잭션은 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 COMMITTED	예	네	예
반복 가능한 읽기	아니요	없음	예
스냅샷	아니요	없음	아니요
직렬화	아니요	없음	아니요

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

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

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

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

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

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

자동 커밋 모드에서 트랜잭션 격리 수준을 지정할 때 OLE DB 애플리케이션은 속성을 DBPROP_SESS_AUTOCOMMITISOLEVELSDBPROPVAL_TI_CHAOS,, DBPROPVAL_TI_READUNCOMMITTEDDBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITYDBPROPVAL_TI_REPEATABLEREADDBPROPVAL_TI_ISOLATEDDBPROPVAL_TI_READCOMMITTEDDBPROPVAL_TI_SERIALIZABLE또는 DBPROPVAL_TI_SNAPSHOT.로 설정할 DBPROPSET_SESSION 수 있습니다.

ODBC
ODBC 애플리케이션이 설정되고 , 또는 SQL_TXN_READ_COMMITTEDSQL_TXN_REPEATABLE_READSQL_TXN_SERIALIZABLE로 설정된 SQL_TXN_READ_UNCOMMITTED상태에서 호출 SQLSetConnectAttrAttribute 합니다.ValuePtrSQL_ATTR_TXN_ISOLATION

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

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

잠금은 SQL Server 데이터베이스 엔진 여러 사용자의 액세스를 동일한 데이터 조각과 동시에 동기화하는 데 사용하는 메커니즘입니다.

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

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

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

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

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

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

잠금 세분성 및 계층

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

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

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

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

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

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

잠금 모드

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

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

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

공유 잠금

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

잠금 업데이트

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

UPDLOCK 테이블 힌트가 쿼리에 지정된 경우 UPDATE를 수행하지 않는 쿼리에서 업데이트(U) 잠금을 수행할 수도 있습니다. 애플리케이션은 일반적으로 읽기 및 쓰기가 트랜잭션 내에서 명시적으로 구분되는 "행 선택, 행 업데이트" 패턴을 사용합니다. 이 경우 격리 수준이 반복 읽기 또는 직렬화 가능인 경우 동시 업데이트가 교착 상태가 될 수 있습니다. 대신 애플리케이션은 "UPDLOCK 힌트가 있는 행 선택, 행 업데이트" 패턴을 따를 수 있습니다.

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

• 기본 읽기 커밋된 격리 수준에서 S 잠금은 짧은 기간이며 사용되는 즉시 해제됩니다. 짧은 기간 잠금으로 인해 교착 상태가 발생할 가능성은 낮습니다.

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

배타적 잠금

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

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

의도 잠금

SQL Server 데이터베이스 엔진 의도 잠금을 사용하여 잠금 계층 구조의 하위 리소스에 공유(S) 잠금 또는 배타적(X) 잠금을 배치하는 것을 보호합니다. 의도 잠금은 낮은 수준에서 잠금 전에 획득되므로 "의도 잠금"이라고 하며, 따라서 잠금을 낮은 수준에 배치하려는 의도를 나타냅니다.

의도 잠금은 다음 두 가지 용도로 사용됩니다.

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

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

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

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

스키마 잠금

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

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

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

대량 업데이트 잠금

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

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

키 범위 잠금

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

잠금 호환성

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

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

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

다음 표를 사용하여 SQL Server에서 사용할 수 있는 모든 잠금 모드의 호환성을 확인합니다.

키 범위 잠금

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

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

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

키 범위 잠금 모드

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

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

범위	Row	모드	설명
범위	S	RangeS-S	공유 범위, 공유 리소스 잠금; serialize할 수 있는 범위 검사입니다.
범위	U	RangeS-U	공유 범위, 업데이트 리소스 잠금, 직렬화 가능한 업데이트 검색입니다.
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

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

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

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

• 범위 검색 쿼리
• 존재하지 않는 행의 싱글톤 인출
• 삭제 작업
• 삽입 작업

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

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

예제

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

범위 검색 쿼리

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

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

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

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

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

SELECT name
FROM mytable
WHERE name = 'Bill';

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

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

트랜잭션 내에서 값을 삭제할 때 값이 속하는 범위는 삭제 작업을 수행하는 트랜잭션 기간 동안 잠글 필요가 없습니다. 트랜잭션이 끝날 때까지 삭제된 키 값을 잠그면 직렬화 가능성을 기본 수 있습니다. 다음과 같은 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) 잠금이 값 Dan에 배치됩니다. RangeI-N 모드 키 범위 잠금은 범위를 테스트하는 데만 필요하며 삽입 작업을 수행하는 트랜잭션 기간 동안 유지되지 않습니다. 다른 트랜잭션은 삽입된 Dan값 앞이나 뒤에 값을 삽입하거나 삭제할 수 있습니다. 그러나 값 Dan을 읽거나 삽입하거나 삭제하려는 트랜잭션은 삽입 트랜잭션이 커밋되거나 롤백될 때까지 차단됩니다.

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

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

INSERT mytable VALUES ('Dan');

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

잠금 에스컬레이션

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

최적화된 잠금을 사용하도록 설정하고 기본 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세 테이블에 액세스하는 조인TableBTableC이라고 가정합니다. 이 문은 클러스터형 인덱스에서 3,000개의 행 잠금을 TableA 획득하고 클러스터형 인덱스에서 5,000개 이상의 행 잠금을 TableB획득하지만 아직 액세스 TableC하지 않았습니다. 데이터베이스 엔진 문이 5,000개 이상의 행 잠금TableB을 획득했음을 감지하면 현재 트랜잭션에서 TableB보유한 모든 잠금을 에스컬레이션하려고 시도합니다. 또한 현재 트랜잭션에서 TableA보유한 모든 잠금을 에스컬레이션하려고 시도하지만 잠금 TableA 수가 5,000개 미만이므로 에스컬레이션이 성공하지 못합니다. 에스컬레이션이 발생했을 때 아직 액세스하지 않았기 때문에 잠금 에스컬레이션이 시도 TableC 되지 않습니다.

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

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

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

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

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

혼합 잠금 유형 에스컬레이션

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

예를 들어 세션을 가정합니다.

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

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

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

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

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

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

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

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

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

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

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

• 읽기 작업에 대한 공유 잠금을 생성하지 않는 격리 수준을 사용합니다.

  • READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON인 경우 READ COMMITTED 격리 수준입니다.

  • SNAPSHOT 격리 수준입니다.

  • READ UNCOMMITTED 격리 수준입니다. 이는 더티 읽기로 작동할 수 있는 시스템에만 사용할 수 있습니다.

    참고 항목

    격리 수준을 변경하면 데이터베이스 엔진 인스턴스의 모든 테이블에 영향을 줍니다.

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

• 최적화된 잠금을 사용하도록 설정하지 않은 경우 분할된 테이블에 대해 ALTER TABLE의 LOCK_ESCALATION 옵션을 사용하여 잠금을 테이블 대신 HoBT 수준으로 에스컬레이션하거나 잠금 에스컬레이션을 사용하지 않도록 설정합니다.

• 대규모 일괄 처리 작업을 여러 개의 더 작은 작업으로 분할합니다. 예를 들어 다음 쿼리를 실행하여 감사 테이블에서 수십만 개의 이전 레코드를 제거한 다음 다른 사용자를 차단하는 잠금 에스컬레이션이 발생했음을 발견했다고 가정합니다.

  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  

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

  SET ROWCOUNT 500
  delete_more:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  IF @@ROWCOUNT > 0 GOTO delete_more
  SET ROWCOUNT 0
  

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

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

• 다른 SPID가 현재 호환되지 않는 테이블 잠금을 보유하고 있는 경우에는 잠금 에스컬레이션이 발생할 수 없습니다. 잠금 에스컬레이션은 항상 테이블 잠금으로 에스컬레이션되고 페이지 잠금에는 에스컬레이션되지 않습니다. 또한 다른 SPID가 호환되지 않는 TAB 잠금을 보유하여 잠금 에스컬레이션 시도가 실패하는 경우 에스컬레이션을 시도한 쿼리는 TAB 잠금을 기다리는 동안 차단되지 않습니다. 대신, 원래의 보다 세분화된 수준(행, 키 또는 페이지)에서 잠금을 계속 획득하여 주기적으로 추가 에스컬레이션을 시도합니다. 따라서 특정 테이블에 대한 잠금 에스컬레이션을 방지하는 한 가지 방법은 에스컬레이션된 잠금 유형과 호환되지 않는 다른 연결에서 잠금을 획득하고 유지하는 것입니다. 테이블 수준의 IX(의도 배타적) 잠금은 행이나 페이지를 잠그지 않지만 에스컬레이션된 S(공유) 또는 X(배타적) TAB 잠금과 호환되지 않습니다. 예를 들어 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 힌트로 테이블 잠금을 강제 적용하거나 관리자가 저장 프로시저를 사용하여 sp_indexoption 페이지 또는 행 잠금을 사용하지 않도록 설정한 경우 제외).

• 추적 플래그 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과 같은 잠금 힌트를 사용하면 초기 잠금 계획만 변경됩니다. 잠금 힌트는 잠금 에스컬레이션을 방지하지 않습니다.

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

다음 예제와 같이 확장 이벤트(xEvent)를 사용하여 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

동적 잠금

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

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

동적 잠금에는 다음과 같은 이점이 있습니다.

• 간소화된 데이터베이스 관리. 데이터베이스 관리자는 잠금 에스컬레이션 임계값을 조정할 필요가 없습니다.
• 성능이 향상됩니다. SQL Server 데이터베이스 엔진 작업에 적합한 잠금을 사용하여 시스템 오버헤드를 최소화합니다.
• 애플리케이션 개발자는 개발에 집중할 수 있습니다. SQL Server 데이터베이스 엔진 잠금을 자동으로 조정합니다.

SQL Server 2008(10.0.x)부터 잠금 에스컬레이션 동작이 옵션 도입 LOCK_ESCALATION 과 함께 변경되었습니다. 자세한 내용은 ALTER TABLE 옵션을 참조 LOCK_ESCALATION 하세요.

잠금 분할

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

잠금 분할 이해

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

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

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

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

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

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

잠금 분할 구현 및 모니터링

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

분할된 리소스에 대한 잠금을 획득하는 경우:

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

• NL, SCH-S, IS, IU 및 IX 이외의 모드에서 공유(S), 배타적(X) 및 기타 잠금은 파티션 ID 0부터 시작하여 파티션 ID 순서에 따라 모든 파티션에서 획득되어야 합니다. 분할된 리소스에 대한 이러한 잠금은 각 파티션이 사실상 별도의 잠금이므로 분할되지 않은 리소스에서 동일한 모드의 잠금보다 더 많은 메모리를 사용합니다. 메모리 증가는 파티션의 수에 따라 결정됩니다. Windows 성능 모니터 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 데이터베이스 엔진 행 버전 관리 기반 격리 수준

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

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

• 트리거에 삽입되고 삭제된 테이블을 빌드합니다. 트리거에 의해 수정된 모든 행의 버전이 지정됩니다. 여기에는 트리거를 실행한 문에 의해 수정된 행과 트리거에 의해 수정된 모든 데이터가 포함됩니다.
• MARS(다중 활성 결과 집합)를 지원합니다. MARS 세션이 활성 결과 집합이 있을 때 데이터 수정 문(예: INSERTUPDATEDELETE또는)을 발행하는 경우 수정 문의 영향을 받는 행의 버전이 지정됩니다.
• ONLINE 옵션을 지정하는 인덱스 작업을 지원합니다.
• 행 버전 관리 기반 트랜잭션 격리 수준을 지원합니다.
  • 행 버전 관리 기능을 사용하여 문 수준 읽기 일관성을 제공하는 READ COMMITTED 격리 수준의 새로운 구현입니다.
  • 트랜잭션 수준 읽기 일관성을 제공하기 위한 새로운 격리 수준인 스냅샷.

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

커밋된 읽기 및 스냅샷 트랜잭션에 행 버전 관리를 사용하는 과정은 다음 두 단계로 이루어집니다.

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

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

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

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

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

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

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

데이터를 읽을 때의 동작

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

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

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

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

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

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

행 버전 관리를 사용하는 커밋된 읽기 트랜잭션은 거의 동일한 방식으로 작동합니다. 차이점은 행 버전을 선택할 때 커밋된 읽기 트랜잭션이 자체 트랜잭션 시퀀스 번호를 사용하지 않는다는 것입니다. 문이 시작될 때마다 커밋된 읽기 트랜잭션은 SQL Server 데이터베이스 엔진 해당 인스턴스에 대해 발급된 최신 트랜잭션 시퀀스 번호를 읽습니다. 해당 문의 올바른 행 버전을 선택하는 데 사용되는 트랜잭션 시퀀스 번호입니다. 이렇게 하면 커밋된 읽기 트랜잭션이 각 문의 시작 부분에 있는 데이터의 스냅샷 볼 수 있습니다.

데이터를 수정할 때의 동작

데이터 쓰기의 동작은 최적화된 잠금이 존재할 때와 없는 경우와 크게 다릅니다.

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

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

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

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

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

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

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

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

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

동작 요약

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

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

행 버전 관리 리소스 사용

행 버전 관리 프레임워크는 SQL Server에서 사용할 수 있는 다음 기능을 지원합니다.

• 트리거
• MARS(다중 활성 결과 집합)
• 온라인 인덱싱

또한 행 버전 관리 프레임워크는 다음 행 버전 관리 기반 트랜잭션 격리 수준을 지원합니다. 이러한 격리 수준은 기본적으로 설정되지 않습니다.

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

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

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

tempdb의 사용된 공간

SQL Server 데이터베이스 엔진 tempdb 각 인스턴스에 대해 인스턴스의 모든 데이터베이스에 대해 생성된 행 버전을 저장할 충분한 공간이 있어야 합니다. 데이터베이스 관리자는 버전 저장소를 지원할 충분한 공간이 있는지 확인해야 tempdb 합니다. 에는 두 개의 버전 저장소가 있습니다.tempdb

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

활성 트랜잭션이 액세스해야 하는 한 행 버전을 저장해야 합니다. 1분마다 백그라운드 스레드는 더 이상 필요하지 tempdb않은 행 버전을 제거하고 . 다음 중 하나에 해당될 경우 장기 실행 트랜잭션은 버전 저장소의 공간이 해제되지 않도록 합니다.

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

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

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

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

데이터 행에 사용되는 공간

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

• READ_COMMITTED_SNAPSHOT 또는 ALLOW_SNAPSHOT_ISOLATION 옵션은 ON입니다.
• 테이블에 트리거가 있습니다.
• MARS(여러 활성 결과 집합)가 사용되고 있습니다.
• 테이블에서 현재 온라인 인덱스 작성 작업이 실행되고 있습니다.

이러한 14바이트는 이러한 모든 조건에서 행이 처음 수정될 때 데이터베이스 행에서 제거됩니다.

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

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

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

큰 개체의 공간 사용량

SQL Server 데이터베이스 엔진 최대 2GB(기가바이트) 길이의 nvarchar(max)imagevarchar(max)varbinary(max)ntexttext큰 문자열을 포함할 수 있는 6가지 데이터 형식을 지원합니다. 이러한 데이터 형식을 사용하여 저장된 큰 문자열은 데이터 행에 연결된 일련의 데이터 조각에 저장됩니다. 행 버전 관리 정보는 이러한 큰 문자열을 저장하는 데 사용되는 각 조각에 저장됩니다. 데이터 조각은 테이블의 큰 개체에만 사용되는 페이지의 모음입니다.

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

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

nvarchar(max)이전 버전의 SQL Server에서는 , varchar(max)및 varbinary(max) 데이터 형식을 사용할 수 없습니다. 따라서 업그레이드 문제가 없습니다.

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

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

성능 및 문제에 대한 행 버전 관리, 버전 저장소 및 스냅샷 격리 프로세스를 모니터링하기 위해 SQL Server는 Windows 시스템 모니터의 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 그룹화합니다. 이 함수를 사용하여 버전 저장소의 가장 큰 소비자를 찾습니다. 자세한 내용은 sys.dm_tran_top_version_generators(Transact-SQL)를 참조 하세요.

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

• sys.dm_tran_version_store_space_usage. 각 데이터베이스에 대한 버전 저장소 레코드에서 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_스냅샷_database_transactions(Transact-SQL)를 참조하세요.

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

성능 카운터

SQL Server 성능 카운터는 SQL Server 프로세스의 영향을 받는 시스템 성능에 대한 정보를 제공합니다. 다음 성능 카운터는 행 버전 관리를 사용하는 트랜잭션뿐만 아니라 버전 저장소를 모니터링 tempdb 합니다. 이러한 성능 카운터는 SQLServer:Transactions 성능 개체에 포함되어 있습니다.

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

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

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

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

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

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

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

  참고 항목

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

• 버전 저장소 단위 수입니다. 버전 저장소 단위 수를 모니터링합니다.

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

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

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

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

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

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

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

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

  참고 항목

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

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

다음 예에서는 스냅샷 격리 트랜잭션과 행 버전 관리를 사용하는 커밋된 읽기 트랜잭션 동작의 차이를 보여 줍니다.

A. 스냅샷 격리 작업

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

세션 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 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. 행 버전 관리를 사용하여 커밋된 읽기 작업

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

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

세션 1:

USE AdventureWorks2022;  -- Or any earlier version of the AdventureWorks database.
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_SNAPSHOTALLOW_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으로 설정되면 데이터베이스에서 데이터를 수정한 모든 활성 트랜잭션이 완료될 때까지 SQL Server 데이터베이스 엔진 인스턴스가 수정된 데이터에 대한 행 버전을 생성하지 않습니다. 활성 수정 트랜잭션이 있는 경우 SQL Server는 옵션 PENDING_ON의 상태를 .로 설정합니다. 모든 수정 트랜잭션이 완료되면 옵션의 상태가 ON으로 변경됩니다. 사용자는 옵션이 완전히 ON이 될 때까지 해당 데이터베이스에서 스냅샷 트랜잭션을 시작할 수 없습니다. 데이터베이스 관리자가 ALLOW_SNAPSHOT_ISOLATION 옵션을 OFF로 설정하면 데이터베이스의 상태가 먼저 PENDING_OFF가 된 후 OFF로 변경됩니다.

다음 Transact-SQL 문은 ALLOW_SNAPSHOT_ISOLATION 사용하도록 설정합니다.

ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

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

현재 데이터베이스에 대한 스냅샷 격리 프레임워크의 상태	설명
OFF	스냅샷 격리 트랜잭션에 대한 지원은 활성화되지 않습니다. 스냅샷 격리 트랜잭션은 허용되지 않습니다.
PENDING_ON	스냅샷 격리 트랜잭션에 대한 지원이 OFF에서 ON으로 전환되는 중입니다. 열린 트랜잭션을 완료해야 합니다. 스냅샷 격리 트랜잭션은 허용되지 않습니다.
ON	스냅샷 격리 트랜잭션에 대한 지원이 활성화됩니다. 스냅샷 트랜잭션이 허용됩니다.
PENDING_OFF	스냅샷 격리 트랜잭션에 대한 지원은 전환 상태(ON에서 OFF로)입니다. 이 시간 이후에 시작된 스냅샷 트랜잭션은 이 데이터베이스에 액세스할 수 없습니다. 업데이트 트랜잭션은 이 데이터베이스에서 계속해서 버전 관리를 수행합니다. 기존 스냅샷 트랜잭션은 문제 없이 이 데이터베이스에 계속 액세스할 수 있습니다. 데이터베이스 스냅샷 격리 상태가 ON일 때 활성 상태였던 모든 스냅샷 트랜잭션이 완료될 때까지 상태 PENDING_OFF OFF가 되지 않습니다.

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

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

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

사용자는 ON 또는 tempdbmsdb.에서 master옵션을 설정할 READ_COMMITTED_SNAPSHOT 수 없습니다.

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

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

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

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

  ALTER DATABASE AdventureWorks2022
      SET READ_COMMITTED_SNAPSHOT ON;
  

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

• 다음 코드 예제와 같이 데이터베이스 옵션을 설정 ALLOW_SNAPSHOT_ISOLATION 하여 ON 스냅샷 격리:

  ALTER DATABASE AdventureWorks2022
      SET ALLOW_SNAPSHOT_ISOLATION ON;
  

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

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

  다음 코드 예제에서는 트랜잭션 격리 수준을 커밋된 읽기로 변경하도록 수정된 동일한 SELECT 문을 보여 줍니다. 이 변경으로 SELECT 인해 문이 성공적으로 실행됩니다.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  BEGIN TRAN
      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은 ( 또는 msdbmaster.)에서 tempdb사용할 수 없습니다.

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

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

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

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

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

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

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

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

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

  참고 항목

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

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

잠금 시간 제한 사용자 지정

다른 트랜잭션이 리소스에 대해 충돌하는 잠금을 이미 소유하고 있기 때문에 Microsoft SQL Server 데이터베이스 엔진 인스턴스가 트랜잭션에 대한 잠금을 부여할 수 없는 경우 첫 번째 트랜잭션은 기존 잠금이 해제될 때까지 대기하는 것이 차단됩니다. 기본적으로 정해진 제한 시간은 없으며 리소스를 잠그기 전에 해당 리소스가 잠겨 있는지 여부를 확인할 수 없습니다. 단, 데이터에 대한 액세스를 시도할 수는 있으나 이로 인해 무기한으로 차단될 수 있습니다.

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

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

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

SELECT @@lock_timeout;
GO

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

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

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

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

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

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID
    FROM HumanResources.Employee;
GO

필요한 경우 테이블 수준 힌트를 지정하여 개별 쿼리 또는 DML 문에 대해 격리 수준을 재정의할 수 있습니다. 테이블 수준 힌트를 지정해도 세션의 다른 문에는 영향을 주지 않습니다. 테이블 수준 힌트는 반드시 필요한 경우에만 기본 동작을 변경하는 데 사용하는 것이 좋습니다.

격리 수준이 데이터를 읽을 때 공유 잠금이 요청되지 않는 수준으로 설정된 경우에도 SQL Server 데이터베이스 엔진 메타데이터를 읽을 때 잠금을 획득해야 할 수 있습니다. 예를 들어 커밋되지 않은 읽기 격리 수준에서 실행되는 트랜잭션은 데이터를 읽을 때 공유 잠금을 획득하지 않지만 시스템 카탈로그 뷰를 읽을 때 잠금을 요청할 수 있습니다. 즉, 동시 트랜잭션이 해당 테이블의 메타데이터를 수정할 때 커밋되지 않은 읽기 트랜잭션으로 인해 테이블을 쿼리할 때 차단이 발생할 수 있습니다.

현재 설정된 트랜잭션 격리 수준을 확인하려면 다음 예제와 같이 문을 사용합니다 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 문의 개별 테이블 참조에 대해 잠금 힌트를 지정할 수 있습니다. 힌트는 테이블 데이터에 사용되는 SQL Server 데이터베이스 엔진 인스턴스의 잠금 또는 행 버전 관리 유형을 지정합니다. 테이블 수준 잠금 힌트는 개체에서 획득한 잠금 유형을 더 세밀하게 제어해야 하는 경우에 사용할 수 있습니다. 이러한 잠금 힌트는 세션의 현재 트랜잭션 격리 수준을 재정의합니다.

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

SQL Server 데이터베이스 엔진 데이터를 읽을 때 공유 잠금 요청을 방지하는 잠금 힌트를 사용하여 선택을 처리하는 경우에도 메타데이터를 읽을 때 잠금을 획득해야 할 수 있습니다. 예를 들어 힌트를 SELECT 사용하면 NOLOCK 데이터를 읽을 때 공유 잠금을 획득하지 않지만 시스템 카탈로그 뷰를 읽을 때 잠금을 요청할 수 있습니다. 즉 SELECT , 문을 사용하여 NOLOCK 차단할 수 있습니다.

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

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) 잠금입니다. 이 경우 순차성은 더 이상 보장되지 않습니다.

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

인덱스 잠금 사용자 지정

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

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

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

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

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

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

고급 트랜잭션 정보

중첩 트랜잭션

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

다음 예제에서는 중첩된 트랜잭션의 의도된 사용을 보여 줍니다. TransProc 프로시저는 프로시저를 실행하는 프로세스의 트랜잭션 모드에 관계없이 트랜잭션을 강제 실행합니다. 트랜잭션이 활성 상태일 때 TransProc이 호출되면 TransProc의 중첩된 트랜잭션은 대부분 무시되며 INSERT 해당 문은 외부 트랜잭션에 대해 획득한 최종 작업에 따라 커밋되거나 롤백됩니다. 처리 중인 트랜잭션 COMMIT TRANSACTION 이 없는 프로세스에 의해 실행되는 경우 TransProc 프로시저의 끝에 있는 문이 효과적으로 커밋 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 rollback. */
SELECT * FROM TestTrans;
GO

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

각 호출은 COMMIT TRANSACTION 마지막으로 실행된 BEGIN TRANSACTION호출에 적용 COMMIT WORK 됩니다. BEGIN TRANSACTION 문이 중첩된 COMMIT 경우 문은 가장 안쪽 트랜잭션인 마지막 중첩 트랜잭션에만 적용됩니다. 중첩된 트랜잭션 내의 문이 외부 트랜잭션의 트랜잭션 이름을 참조하더라도 COMMIT TRANSACTION transaction_name 커밋은 가장 안쪽 트랜잭션에만 적용됩니다.

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

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

바운드 세션 사용

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

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

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

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

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

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

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

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

바운드 세션 유형

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

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

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

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

바운드 세션 사용 시기

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

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

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

코드 효율적인 트랜잭션

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

코드 지침

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

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

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

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

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

• 낮은 트랜잭션 격리 수준을 지능적으로 사용합니다. 대부분의 애플리케이션은 커밋된 읽기 트랜잭션 격리 수준을 사용하도록 코딩할 수 있습니다. 모든 트랜잭션에 직렬화 가능한 트랜잭션 격리 수준이 필요한 것은 아닙니다.

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

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

• 가능하면 홀드락과 같은 비관적 잠금 힌트를 피합니다. HOLDLOCK 또는 SERIALIZABLE 격리 수준과 같은 힌트로 인해 프로세스가 공유 잠금에서도 대기하고 동시성을 줄일 수 있습니다.

• 암시적 트랜잭션의 특성으로 인해 예측할 수 없는 동작이 발생할 수 있는 경우 암시적 트랜잭션을 사용하지 마세요. 암시적 트랜잭션 및 동시성 문제 참조

• 채우기 비율 감소로 인덱스를 디자인하면 인덱스 페이지의 조각화를 방지하거나 줄일 수 있으므로 특히 디스크에서 검색할 때 인덱스 검색 시간을 줄일 수 있습니다. 테이블 또는 뷰의 데이터 및 인덱스에 대한 조각화 정보를 보려면 .sys.dm_db_index_physical_stats

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

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

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

장기 실행 트랜잭션 관리

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

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

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

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

장기 실행 트랜잭션 검색

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

• sys.dm_tran_database_transactions

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

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

• DBCC OPENTRAN

  이 문을 사용하면 트랜잭션 소유자의 사용자 ID를 식별할 수 있으므로 트랜잭션을 롤백하는 대신 커밋하여 보다 질서 정연한 종료를 위해 트랜잭션의 원본을 추적할 수 있습니다. 자세한 내용은 DBCC OPENTRAN(Transact-SQL)을 참조하세요.

트랜잭션 중지

KILL 문을 사용해야 할 수도 있습니다. 그러나 특히 중요한 프로세스가 실행 중일 때는 이 문을 신중하게 사용하십시오. 자세한 내용은 KILL(Transact-SQL)을 참조 하세요.

교착 상태

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

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

관련 콘텐츠

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