잠금 에스컬레이션(데이터베이스 엔진)
잠금 에스컬레이션은 많은 수의 미세 잠금을 더 적은 수의 성긴 잠금으로 변환하여 동시성 경합 가능성은 높이고 시스템 오버헤드는 줄이는 프로세스입니다.
SQL Server 데이터베이스 엔진은 하위 수준의 잠금을 획득할 때 더 하위 수준의 개체를 포함하는 개체에 의도 잠금도 배치합니다.
행 또는 인덱스 키 범위를 잠그는 경우 데이터베이스 엔진은 행 또는 키를 포함하는 페이지에 의도 잠금을 배치합니다.
페이지를 잠그는 경우 데이터베이스 엔진은 페이지를 포함하는 더 상위 수준의 개체에 의도 잠금을 배치합니다. 개체에 배치된 의도 잠금 외에도 다음 개체에 대해 의도 페이지 잠금이 요청됩니다.
비클러스터형 인덱스의 리프 수준 페이지
클러스터형 인덱스의 데이터 페이지
힙 데이터 페이지
데이터베이스 엔진은 동일한 문에 대해 행 잠금과 페이지 잠금을 모두 수행하여 잠금 수를 최소화하고 잠금 에스컬레이션이 필요할 가능성을 낮출 수 있습니다. 예를 들어 데이터베이스 엔진은 비클러스터형 인덱스에는 페이지 잠금을 배치(쿼리를 만족시키기 위해 인덱스 노드에서 충분히 인접한 키가 선택된 경우)하고 데이터에는 행 잠금을 배치할 수 있습니다.
데이터베이스 엔진은 잠금을 에스컬레이션하기 위해 테이블의 의도 잠금을 해당 전체 잠금으로 변경하려고 시도합니다. 예를 들어 의도 배타(IX) 잠금을 배타(X) 잠금으로 변경하거나 내재된 공유(IS) 잠금을 공유(S) 잠금으로 변경하려고 시도합니다. 잠금 에스컬레이션 시도가 성공하여 전체 테이블 잠금을 획득하면 힙이나 인덱스에서 트랜잭션이 보유한 모든 힙 또는 B-트리, 페이지(PAGE) 또는 행 수준(RID) 잠금이 해제됩니다. 전체 잠금을 획득할 수 없는 경우에는 해당 시점에서 잠금 에스컬레이션이 발생하지 않고 데이터베이스 엔진은 행, 키 또는 페이지 잠금 획득을 계속 시도합니다.
데이터베이스 엔진은 행 또는 키 범위 잠금을 페이지 잠금으로 에스컬레이션하지 않고 곧바로 테이블 잠금으로 에스컬레이션합니다. 마찬가지로 페이지 잠금도 항상 테이블 잠금으로 에스컬레이션됩니다. SQL Server 2008에서는 분할된 테이블의 잠금이 테이블 잠금이 아니라 관련된 파티션에 대한 HoBT 수준으로 에스컬레이션될 수 있습니다. HoBT 수준 잠금이 해당 파티션에 대해 정렬된 HoBT를 반드시 잠그는 것은 아닙니다.
[!참고]
HoBT 수준 잠금은 일반적으로 동시성을 증가시키지만 각각 다른 파티션을 잠그는 트랜잭션이 배타적 잠금을 다른 파티션으로 확장하려는 경우 교착 상태가 발생할 수도 있습니다. 경우에 따라 TABLE 잠금 세분성이 향상될 수도 있습니다.
동시 트랜잭션이 보유한 잠금의 충돌로 인해 잠금 에스컬레이션 시도가 실패하면 데이터베이스 엔진은 트랜잭션이 획득한 추가 1,250개의 잠금 각각에 대해 잠금 에스컬레이션을 다시 시도합니다.
각 에스컬레이션 이벤트는 주로 단일 Transact-SQL 문의 수준에서 작동합니다. 이벤트가 시작되면 데이터베이스 엔진은 에스컬레이션 임계값 요구 사항이 충족되는 경우 활성 문에서 참조한 테이블 중에서 현재 트랜잭션이 소유한 모든 잠금을 에스컬레이션하려고 시도합니다. 문이 테이블에 액세스하기 전에 에스컬레이션 이벤트가 시작되면 해당 테이블의 잠금에 대한 에스컬레이션은 시도되지 않습니다. 잠금 에스컬레이션이 성공한 경우 테이블이 현재 문에서 참조되고 에스컬레이션 이벤트에 포함되어 있다면 이전 문에서 트랜잭션이 획득하여 이벤트 시작 시에도 여전히 보유하는 모든 잠금이 에스컬레이션됩니다.
예를 들어 다음 작업을 수행하는 세션이 있다고 가정합니다.
트랜잭션을 시작합니다.
TableA를 업데이트합니다. 그러면 TableA에 트랜잭션이 완료될 때까지 보유되는 배타 행 잠금이 생성됩니다.
TableB를 업데이트합니다. 그러면 TableB에 트랜잭션이 완료될 때까지 보유되는 배타 행 잠금이 생성됩니다.
TableA를 TableC와 조인하는 SELECT를 수행합니다. 쿼리 실행 계획에 따라 행은 TableC에서 검색되기 전에 TableA에서 검색되어야 합니다.
SELECT 문은 TableC에 액세스하기 전에 TableA에서 행을 검색하는 동안 잠금 에스컬레이션을 트리거합니다.
잠금 에스컬레이션이 성공하면 TableA에서 세션이 보유한 잠금만 에스컬레이션됩니다. 여기에는 SELECT 문의 공유 잠금과 이전 UPDATE 문의 배타 잠금이 모두 포함됩니다. 잠금 에스컬레이션을 수행할지 결정하기 위해 세션이 SELECT 문에 대해 TableA에서 획득한 잠금 수가 계산되는 동안 에스컬레이션이 성공하면 TableA에서 세션이 보유한 모든 잠금은 테이블의 배타 잠금으로 에스컬레이션되고 TableA에서 의도 잠금을 포함하여 세분성이 더 낮은 다른 모든 잠금은 해제됩니다.
SELECT 문에 TableB에 대한 활성 참조가 없기 때문에 TableB의 잠금 에스컬레이션은 시도되지 않습니다. 마찬가지로 에스컬레이션이 발생할 때 아직 액세스되지 않았기 때문에 에스컬레이션되지 않은 TableC의 잠금에는 에스컬레이션이 시도되지 않습니다.
잠금 에스컬레이션 임계값
잠금 에스컬레이션은 ALTER TABLE SET LOCK_ESCALATION 옵션을 사용하여 테이블에서 잠금 에스컬레이션이 해제되지 않은 경우와 다음 조건 중 하나에 해당하는 경우 트리거됩니다.
단일 Transact-SQL 문이 분할되지 않은 단일 테이블이나 인덱스에 대해 5,000개 이상의 잠금을 획득한 경우
단일 Transact-SQL 문이 분할된 테이블의 단일 파티션에 대해 5,000개 이상의 잠금을 획득하고 ALTER TABLE SET LOCK_ESCALATION 옵션이 AUTO로 설정된 경우
데이터베이스 엔진 인스턴스의 잠금 수가 메모리 또는 구성 임계값을 초과한 경우
잠금 충돌로 인해 잠금을 에스컬레이션할 수 없는 경우 데이터베이스 엔진은 1,250개의 잠금이 새로 획득될 때마다 주기적으로 잠금 에스컬레이션을 트리거합니다.
Transact-SQL 문의 에스컬레이션 임계값
잠금 에스컬레이션은 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의 잠금 수가 5000보다 크므로 에스컬레이션은 발생하지 않습니다. 에스컬레이션이 발생할 때 아직 액세스되지 않았으므로 TableC에 대해서는 잠금 에스컬레이션이 시도되지 않습니다.
데이터베이스 엔진 인스턴스의 에스컬레이션 임계값
잠금 수가 잠금 에스컬레이션에 대한 메모리 임계값보다 커지면 데이터베이스 엔진에서 잠금 에스컬레이션을 트리거합니다. 메모리 임계값은 다음과 같은 locks 구성 옵션의 설정에 따라 다릅니다.
locks 옵션을 기본값인 0으로 설정하면 잠금 개체에서 사용하는 메모리가 AWE 메모리를 제외하고 데이터베이스 엔진에서 사용하는 메모리의 24%일 때 잠금 에스컬레이션 임계값에 도달합니다. 잠금을 나타내는 데 사용되는 데이터 구조의 길이는 약 100바이트입니다. 데이터베이스 엔진이 다양한 작업에 맞추어 동적으로 메모리를 획득하거나 해제하기 때문에 이 임계값은 동적입니다.
locks 옵션을 0 이외의 값으로 설정하면 잠금 에스컬레이션 임계값은 locks 옵션 값의 40%이고 메모리가 가중되는 경우에는 40% 미만입니다.
데이터베이스 엔진은 에스컬레이션을 수행하기 위해 모든 세션에서 모든 활성 문을 선택할 수 있으며 인스턴스에 사용된 잠금 메모리가 임계값보다 높게 유지되는 경우에는 1,250개의 새 잠금마다 에스컬레이션을 수행할 문을 선택합니다.
혼합 잠금 유형 에스컬레이션
잠금 에스컬레이션이 발생하면 힙이나 인덱스에 대해 선택한 잠금은 더 하위 수준의 가장 제한적인 잠금에 대한 요구 사항도 충분히 만족시킬 수 있습니다.
예를 들어 하나의 세션이 있다고 가정합니다.
트랜잭션을 시작합니다.
클러스터형 인덱스를 포함하는 테이블을 업데이트합니다.
동일한 테이블을 참조하는 SELECT 문을 실행합니다.
UPDATE 문은 다음 잠금을 획득합니다.
업데이트된 데이터 행에 대한 배타(X) 잠금
이러한 행을 포함하는 클러스터형 인덱스 페이지에 대한 의도 배타(IX) 잠금
클러스터형 인덱스에 대한 IX 잠금 및 테이블에 대한 IX 잠금
SELECT 문은 다음 잠금을 획득합니다.
행이 UPDATE 문에서 X 잠금으로 아직 보호되지 않은 경우 SELECT 문이 읽는 모든 데이터 행에 대한 공유(S) 잠금
페이지가 IX 잠금으로 아직 보호되지 않은 경우 해당 행을 포함하는 모든 클러스터형 인덱스 페이지에 대한 내재된 공유 잠금
이미 IX 잠금으로 보호되기 때문에 클러스터형 인덱스나 테이블에 대한 잠금은 없습니다.
SELECT 문이 잠금 에스컬레이션을 트리거하기에 충분한 잠금을 획득하고 에스컬레이션이 성공하면 테이블에 대한 IX 잠금이 X 잠금으로 변환되고 모든 행, 페이지 및 인덱스 잠금이 해제됩니다. 업데이트와 읽기는 모두 테이블에 대한 X 잠금으로 보호됩니다.
잠금 및 에스컬레이션 줄이기
대부분의 경우 데이터베이스 엔진은 잠금 및 잠금 에스컬레이션에 대한 기본 설정으로 작동할 때 최상의 성능을 발휘합니다. 데이터베이스 엔진 인스턴스에서 많은 수의 잠금이 생성되고 잠금 에스컬레이션이 자주 발생하면 다음과 같이 잠금 수를 줄일 것을 고려하십시오.
읽기 작업에 대해 공유 잠금을 만들지 않는 격리 수준 사용
READ_COMMITTED_SNAPSHOT 데이터베이스 옵션이 ON으로 설정된 READ COMMITTED 격리 수준
SNAPSHOT 격리 수준
READ UNCOMMITTED 격리 수준. 이 수준은 커밋되지 않은 읽기로 작동할 수 있는 시스템에서만 사용할 수 있습니다.
[!참고]
격리 수준 변경은 데이터베이스 엔진 인스턴스의 모든 테이블에 영향을 미칩니다.
데이터베이스 엔진이 행 잠금 대신 페이지, 힙 또는 인덱스 잠금을 사용하도록 PAGLOCK 또는 TABLOCK 테이블 힌트 사용. 그러나 이 옵션을 사용하면 한 사용자가 동일한 데이터에 액세스하려는 다른 사용자를 차단하는 문제가 증가하므로 동시 사용자가 많은 시스템에서 이 옵션을 사용하면 안 됩니다.
분할된 테이블의 경우 ALTER TABLE의 LOCK_ESCALATION 옵션을 사용하면 테이블 수준이 아니라 HoBT 수준으로 잠금을 에스컬레이션하거나 잠금 에스컬레이션을 해제할 수 있습니다.
추적 플래그 1211 및 1224를 사용하여 잠금 에스컬레이션의 전부 또는 일부를 해제할 수도 있습니다. 자세한 내용은 추적 플래그(Transact-SQL)를 참조하십시오. 또한 SQL Server Profiler Lock:Escalation 이벤트를 사용하여 잠금 에스컬레이션을 모니터링할 수 있습니다. 자세한 내용은 SQL Server 프로파일러 사용을 참조하십시오.