SQL Server 잠금 에스컬레이션으로 인한 차단 문제 해결

아티클
11/22/2024

요약

잠금 에스컬레이션은 여러 세분화된 잠금(예: 행 또는 페이지 잠금)을 테이블 잠금으로 변환하는 프로세스입니다. Microsoft SQL Server 잠금 에스컬레이션을 수행할 시기를 동적으로 결정합니다. 이 결정을 내릴 때 SQL Server 특정 검사에 보관된 잠금 수, 전체 트랜잭션에서 보유하는 잠금 수 및 시스템 전체의 잠금에 사용되는 메모리를 고려합니다. 일반적으로 SQL Server 기본 동작으로 인해 성능이 향상되거나 과도한 시스템 잠금 메모리를 보다 합리적인 수준으로 줄여야 하는 경우에만 잠금 에스컬레이션이 발생합니다. 그러나 일부 애플리케이션 또는 쿼리 디자인은 이 작업이 바람직하지 않을 때 잠금 에스컬레이션을 트리거할 수 있으며 에스컬레이션된 테이블 잠금은 다른 사용자를 차단할 수 있습니다. 이 문서에서는 잠금 에스컬레이션으로 인해 차단이 발생하는지 여부를 확인하는 방법과 원치 않는 잠금 에스컬레이션을 처리하는 방법에 대해 설명합니다.

원래 제품 버전: SQL Server
원래 KB 번호: 323630

잠금 에스컬레이션으로 인해 차단이 발생하는지 확인

잠금 에스컬레이션은 대부분의 차단 문제를 일으키지 않습니다. 차단 문제가 발생하는 시점 또는 그 근처에 잠금 에스컬레이션이 발생하는지 여부를 확인하려면 이벤트를 포함하는 lock_escalation 확장 이벤트 세션을 시작합니다. 이벤트가 표시되지 lock_escalation 않으면 서버에서 잠금 에스컬레이션이 발생하지 않으며 이 문서의 정보는 상황에 적용되지 않습니다.

잠금 에스컬레이션이 발생하는 경우 에스컬레이션된 테이블 잠금이 다른 사용자를 차단하고 있는지 확인합니다.

헤드 차단기 및 헤드 차단기가 보유하고 다른 SPID(서버 프로세스 ID)를 차단하는 잠금 리소스를 식별하는 방법에 대한 자세한 내용은 INF: SQL Server 차단 문제 이해 및 해결을 참조하세요.

다른 사용자를 차단하는 잠금이 잠금 모드가 S(공유) 또는 X(배타적)인 TAB(테이블 수준) 잠금 이외의 항목인 경우 잠금 에스컬레이션은 문제가 되지 않습니다. 특히 TAB 잠금이 의도 잠금(예: IS, IU 또는 IX의 잠금 모드)인 경우 잠금 에스컬레이션으로 인한 것이 아닙니다. 잠금 에스컬레이션으로 인해 차단 문제가 발생하지 않는 경우 INF: SQL Server 차단 문제 해결 단계 이해 및 해결을 참조하세요.

잠금 에스컬레이션 방지

잠금 에스컬레이션을 방지하는 가장 간단하고 안전한 방법은 트랜잭션을 짧게 유지하고 잠금 에스컬레이션 임계값을 초과하지 않도록 비용이 많이 드는 쿼리의 잠금 공간을 줄이는 것입니다. 다음 전략을 포함하여 이 목표를 달성하는 방법에는 여러 가지가 있습니다.

대규모 일괄 처리 작업을 여러 개의 더 작은 작업으로 분할합니다. 예를 들어 다음 쿼리를 실행하여 감사 테이블에서 100,000개 이상의 이전 레코드를 제거한 다음 쿼리로 인해 다른 사용자를 차단하는 잠금 에스컬레이션이 발생했음을 확인합니다.
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
이러한 레코드를 한 번에 수백 개 제거하면 트랜잭션당 누적되는 잠금 수를 크게 줄일 수 있습니다. 이렇게 하면 잠금 에스컬레이션이 방지됩니다. 예를 들어 다음 쿼리를 실행합니다.
```
DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;
```
쿼리를 최대한 효율적으로 만들어 쿼리의 잠금 공간을 줄입니다. 대용량 검사 또는 많은 책갈피 조회는 잠금 에스컬레이션 가능성을 높일 수 있습니다. 또한 교착 상태의 가능성이 증가하고 동시성 및 성능에 부정적인 영향을 미칩니다. 잠금 에스컬레이션을 유발하는 쿼리를 식별한 후 새 인덱스를 만들거나 기존 인덱스에 열을 추가하여 인덱스 또는 테이블 검사를 제거하고 인덱스 검색의 효율성을 최대화할 기회를 찾습니다. 실행 계획을 검토하고 잠재적으로 비클러스터형 인덱스를 만들어 쿼리 성능을 향상시킵니다. 자세한 내용은 SQL Server 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

이 최적화의 한 가지 목표는 인덱스 검색이 책갈피 조회 비용을 최소화하기 위해 가능한 한 적은 수의 행을 반환하도록 하는 것입니다(쿼리에 대한 인덱스의 선택성을 최대화). SQL Server 책갈피 조회 논리 연산자가 많은 행을 반환할 것으로 예상하는 경우 절을 PREFETCH 사용하여 책갈피 조회를 수행할 수 있습니다. SQL Server 책갈피 조회에 를 사용하는 PREFETCH 경우 쿼리 부분의 트랜잭션 격리 수준을 쿼리의 일부에 대해 "반복 가능한 읽기"로 늘려야 합니다. 즉, "읽기 커밋된" 격리 수준에서 문처럼 SELECT 보이는 것은 클러스터형 인덱스와 비클러스터형 인덱스 모두에서 수천 개의 키 잠금을 획득할 수 있습니다. 이로 인해 이러한 쿼리가 잠금 에스컬레이션 임계값을 초과할 수 있습니다. 이는 일반적으로 기본 "읽기 커밋된" 격리 수준에서는 볼 수 없지만 에스컬레이션된 잠금이 공유 테이블 잠금인 경우 특히 중요합니다. Bookmark Lookup WITH PREFETCH 절이 에스컬레이션을 일으키는 경우 Index Seek에 표시되는 비클러스터형 인덱스에 열을 추가하거나 쿼리 계획의 책갈피 조회 논리 연산자 아래에 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;
```
이 쿼리는 mytable에 대한 IX 잠금을 1시간 동안 획득하고 보유합니다. 이렇게 하면 해당 시간 동안 테이블의 잠금 에스컬레이션이 방지됩니다. 이 일괄 처리는 데이터를 수정하거나 다른 쿼리를 차단하지 않습니다(다른 쿼리가 TABLOCK 힌트를 사용하여 테이블 잠금을 강제하거나 관리자가 ALTER INDEX를 사용하여 페이지 또는 행 잠금을 사용하지 않도록 설정한 경우).
쿼리가 조건자 및 조인 열에 인덱스를 사용할 수 있는지 여부를 설명하는 데 사용되는 관계형 데이터베이스 용어인 SARGability 부족으로 인한 잠금 에스컬레이션을 제거합니다. SARGability에 대한 자세한 내용은 내부 디자인 가이드 쿼리 고려 사항을 참조하세요. 예를 들어 많은 행(또는 단일 행)을 요청하는 것으로 보이지 않는 매우 간단한 쿼리는 여전히 전체 테이블/인덱스를 검사하게 될 수 있습니다. WHERE 절의 왼쪽에 함수 또는 계산이 있는 경우 이 문제가 발생할 수 있습니다. SARGability가 부족한 예제에는 암시적 또는 명시적 데이터 형식 변환, ISNULL() 시스템 함수, 매개 변수로 전달된 열이 있는 사용자 정의 함수 또는 열의 계산(예: WHERE CONVERT(INT, column1) = @a 또는 WHERE Column1*Column2 = 5)이 포함됩니다. 이러한 경우 쿼리는 모든 열 값을 먼저 검색하고 함수에 전달해야 하므로 적절한 열이 포함된 경우에도 기존 인덱스를 검색할 수 없습니다. 이렇게 하면 전체 테이블 또는 인덱스가 검색되고 많은 수의 잠금이 획득됩니다. 이러한 상황에서 SQL Server 잠금 수 에스컬레이션 임계값에 도달할 수 있습니다. 해결 방법은 WHERE 절의 열에 대해 함수를 사용하지 않도록 하여 SARGable 조건을 보장하는 것입니다.

잠금 에스컬레이션 사용 안 함

SQL Server 잠금 에스컬레이션을 사용하지 않도록 설정할 수 있지만 권장하지는 않습니다. 대신 잠금 에스컬레이션 방지 섹션에 설명된 방지 전략을 사용합니다.

테이블 수준: 테이블 수준에서 잠금 에스컬레이션을 사용하지 않도록 설정할 수 있습니다. ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE)를 참조하세요. 대상으로 지정할 테이블을 확인하려면 T-SQL 쿼리를 검사합니다. 가능하지 않은 경우 확장 이벤트를 사용하고 , lock_escalation 이벤트를 사용하도록 설정하고, object_id 열을 검사합니다. 또는 Lock:Escalation 이벤트를 사용하고 SQL Profiler를 ObjectID2 사용하여 열을 검사합니다.
인스턴스 수준: instance 대해 추적 플래그 1211 또는 1224 또는 둘 다를 사용하도록 설정하여 잠금 에스컬레이션을 사용하지 않도록 설정할 수 있습니다. 그러나 이러한 추적 플래그는 SQL Server instance 모든 잠금 에스컬레이션을 전역적으로 사용하지 않도록 설정합니다. 잠금 에스컬레이션은 수천 개의 잠금을 획득하고 해제하는 오버헤드로 인해 느려지는 쿼리의 효율성을 극대화하여 SQL Server 유용한 목적을 제공합니다. 잠금 에스컬레이션은 잠금을 추적하는 데 필요한 메모리를 최소화하는 데도 도움이 됩니다. SQL Server 잠금 구조에 동적으로 할당할 수 있는 메모리는 유한합니다. 따라서 잠금 에스컬레이션을 사용하지 않도록 설정하고 잠금 메모리가 충분히 커지면 쿼리에 대한 추가 잠금을 할당하려는 시도가 실패하고 다음 오류 항목이 생성될 수 있습니다.

오류: 1204, 심각도: 19, 상태: 1
SQL Server 현재 LOCK 리소스를 가져올 수 없습니다. 활성 사용자가 적으면 문을 다시 실행하거나 시스템 관리자에게 SQL Server 잠금 및 메모리 구성을 검사 요청합니다.

참고

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

SQL Server 구성 관리자 사용하여 이러한 추적 플래그(-T1211 또는 -T1224)를 추가할 수 있습니다. 새 시작 매개 변수가 적용되려면 SQL Server 서비스를 다시 시작해야 합니다. 또는 DBCC TRACEON (1224, -1) 쿼리를 DBCC TRACEON (1211, -1) 실행하면 추적 플래그가 즉시 적용됩니다.
그러나 -T1211 또는 -T1224를 시작 매개 변수로 추가하지 않으면 SQL Server 서비스를 다시 시작할 때 명령의 DBCC TRACEON 효과가 손실됩니다. 추적 플래그를 켜면 향후 잠금 에스컬레이션이 방지되지만 활성 트랜잭션에서 이미 발생한 잠금 에스컬레이션은 되돌릴 수 없습니다.

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

잠금 에스컬레이션 임계값

잠금 에스컬레이션은 다음 조건 중 하나에서 발생할 수 있습니다.

메모리 임계값에 도달했습니다 . 잠금 메모리의 40%에 달하는 메모리 임계값에 도달했습니다. 잠금 메모리가 버퍼 풀의 24%를 초과하면 잠금 에스컬레이션을 트리거할 수 있습니다. 잠금 메모리는 표시되는 버퍼 풀의 60%로 제한됩니다. 잠금 에스컬레이션 임계값은 잠금 메모리의 40%로 설정됩니다. 이는 버퍼 풀의 60%의 40%, 즉 24%입니다. 잠금 메모리가 60% 제한을 초과하는 경우(잠금 에스컬레이션을 사용하지 않도록 설정한 경우 훨씬 더 가능성이 높기 때문에) 추가 잠금을 할당하려는 모든 시도가 실패하고 1204 오류가 생성됩니다.
잠금 임계값에 도달 - 메모리 임계값을 검사한 후 현재 테이블 또는 인덱스에서 획득한 잠금 수가 평가됩니다. 숫자가 5,000을 초과하면 잠금 에스컬레이션이 트리거됩니다.

도달한 임계값을 이해하려면 확장 이벤트를 사용하고, lock_escalation 이벤트를 사용하도록 설정하고, escalated_lock_count 및 escalation_cause 열을 검사합니다. 또는 Lock:Escalation 이벤트를 사용하고 값을 검사 EventSubClass 합니다. 여기서 "0 - LOCK_THRESHOLD"은 문이 잠금 임계값을 초과했음을 나타내고 "1 - MEMORY_THRESHOLD"는 문이 메모리 임계값을 초과했음을 나타냅니다. 또한 및 IntegerData2 열을 검사 IntegerData 합니다.

권장 사항

잠금 에스컬레이션 방지 섹션에서 설명하는 메서드는 테이블 또는 instance 수준에서 에스컬레이션을 사용하지 않도록 설정하는 것보다 더 나은 옵션입니다. 또한 예방 메서드는 일반적으로 잠금 에스컬레이션을 사용하지 않도록 설정하는 것보다 쿼리에 대해 더 나은 성능을 생성합니다. 이 문서에서 설명하는 것과 같은 다른 옵션을 조사하는 동안 잠금 에스컬레이션으로 인해 발생하는 심각한 차단을 완화하기 위해 이 추적 플래그만 사용하도록 설정하는 것이 좋습니다.

다음을 통해 공유