[MSSQL] Table Lock Escalations이란?

이번시간에는 테이블 잠금 에스컬레이션(Table Lock Escalations)에 대해서 알아보도록 하겠습니다.

 

Table Lock Escalations이란?

잠금 에스컬레이션은 행, 페이지 잠금과 같은 많은 세분화된 잠금을 "테이블 잠금으로 변환"하는 프로세스입니다.

SQL Server는 서버는 잠금 에스컬레이션을 수행할 시기를 동적으로 결정합니다. 

 

일반적으로 잠금을 유지하려면 메모리 리소스가 필요합니다. 따라서 SQL Server는 기본적으로 행 수준 잠금을 사용하지만 SQL 엔진이 많은 수의 행 잠금을 단일 테이블 잠금으로 변환하고 프로세스에서 행 잠금이 보유한 메모리를 해제하는 것이 훨씬 더 효과적이라 생각합니다.

"잠금 에스컬레이션"은 SQL Server에서 사용하는 최적화 기술이며 기본적으로 대규모 업데이트에 대한 잠금을 처리하는 방법입니다. SQL Server가 많은 수의 행을 수정할 때 데이터베이스 엔진이 많은 수의 개별 잠금(행 잠금)을 처리하는 대신 더 적은 수의 더 큰 잠금(테이블 잠금)을 취하는 것이 더 효율적입니다.

SQL Server의 잠금 계층 구조 는 데이터베이스 수준에서 시작하여 행 수준으로 내려갑니다.
데이터베이스 -> 테이블 -> 페이지 -> 행

 

잠금 에스컬레이션 임계값

MSDN 설명서에 따르면 잠금 에스컬레이션은 Transact-SQL 문이 테이블의 단일 참조에 대해 5,000개 이상의 잠금을 획득할 때 트리거됩니다. 

 

SQL Server의 잠금 에스컬레이션 모드확인

데이터베이스의 테이블에 대한 잠금 에스컬레이션 모드는 3가지이며 아래와 같이 확인할 수 있습니다.

select name, lock_escalation_desc
from sys.tables

 

Lock Escalation Modes  종류

1.Table (default)
2.Auto
3.Disable

 

Lock Escalation Modes 변경방법

잠금 모드는 기본값이 Table 이지만 변경이 가능합니다. 하지만 메모리 문제가 발생할 수 있으니 주의가 필요합니다.

ALTER TABLE tablename
SET (LOCK_ESCALATION = DISABLE) — or TABLE or AUTO

 

잠금 에스컬레이션을 방지하는 방법

1.트랜잭션을 더 짧게 유지.
2.성능 조정을 수행하고 효율적으로 만들어 값비싼 쿼리의 잠금 공간 줄임
3.대규모 작업을 일괄 처리로 나눕니다.

/**** 레코드 분산 쿼리 ****/
대규모 일괄 처리 작업을 여러 개의 작은 작업으로 나분합니다. 

예를 들어 다음 쿼리를 실행하여 감사 테이블에서 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;