다음을 통해 공유

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

  • 아티클

요약

잠금 에스컬레이션은 여러 세분화된 잠금(예: 행 또는 페이지 잠금)을 테이블 잠금으로 변환하는 프로세스입니다. 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 절이 에스컬레이션을 일으키는 경우 인덱스 검색에 표시되는 비클러스터형 인덱스에 열을 추가하거나 쿼리 계획의 책갈피 조회 논리 연산자 아래에 인덱스 검색 논리 연산자를 추가하는 것이 좋습니다. 포함 인덱스(쿼리에 사용된 테이블의 모든 열이 포함된 인덱스) 또는 조인 조건에 사용된 열을 포함하는 인덱스 또는 "열 선택" 목록에 모든 항목을 포함하는 것이 비현실적인 경우 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 힌트를 사용하여 테이블 잠금을 강제로 적용하거나 관리자가 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 사용하여 열을 검사합니다.
  • 인스턴스 수준: 인스턴스에 대해 추적 플래그 1211 또는 1224 또는 둘 다를 사용하도록 설정하여 잠금 에스컬레이션을 사용하지 않도록 설정할 수 있습니다. 그러나 이러한 추적 플래그는 SQL Server 인스턴스에서 전역적으로 모든 잠금 에스컬레이션을 사용하지 않도록 설정합니다. 잠금 에스컬레이션은 수천 개의 잠금을 획득하고 해제하는 오버헤드로 인해 속도가 느려지는 쿼리의 효율성을 극대화하여 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 합니다.

권장 사항

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

참고 항목