[SQL Server] Lock Escalation

Lock Escalation은 좁은 범위의 락이 다수 잡힐 경우 DBMS에서 해당 락의 범위를 상승시키는 현상이다.

DBMS에서 좁은 범위로 락이 여러개 잡히는 것 보다 큰 범위로 적은 수의 락이 잡히는 것이 메모리 리소스에서 유리하기 때문이다. 주로 레코드, 페이지 단위의 락이 테이블 단위의 락으로 상승하는 현상이 일어난다.

EX) 하나의 테이블에 대량의 업데이트를 실행할 때 각 레코드별로 락이 잡히는 것이 아닌 테이블에 락이 잡힌다. 이로인한 문제점은 해당 테이블이 서비스에서 데이터가 입력이 되는 경우라면 입력 프로세스가 블락이 잡히며 이는 서비스 지연으로 이어질 수 있다.

SQL Server docs에서는 락 에스컬레이션의 임계값을 5000개의 lock 또는 메모리 임계값 (잠금 메모리의 40%에 달하는 임계값으로 버퍼풀의 24%라고 한다)에 도달하면 발생한다고 한다.

즉 테이블의 전체 건수의 몇% 이상일 때 발생하는 것이 아니라고 판단된다.

 

또한 락 에스컬레이션을 제어할 수 있는 옵션 방법은 2가지로 나뉠 수 있다.

첫번째는 추적 플래그 설정을 통한 제어로 추적 플래그의 특성상 세션, 글로벌 단위로 제어가 가능하다

1211과 1224 이다.

1211은 에스컬레이션을 없애는 옵션으로 하드한 설정이며

1224는 잠금 수로 인한 락 에스컬레이션을 해제하며 메모리로 인한 에스컬레이션만 허용하는 옵션으로 비교적 소프트하게 사용이 가능할 것으로 확인된다.

(

- 40% of the memory that is used by Database Engine. This is applicable only when the locks parameter of sp_configure is set to 0.
- 40% of the lock memory that is configured by using the locks parameter of sp_configure. For more information, see Server configuration options (SQL Server).

)

 

또한 테이블 단위의 옵션 지정으로 설정이 가능하다.

ALTER [TABLENAME] SET (LOCK_EXCALATION = [설정 값 (DISABLE, TABLE, AUTO 中 1]

 

 

그렇다면 논리적으로는 Lock Escalation을 어떻게 피할 수 있을까?

 

대량의 데이터를 처리할 필요가 있을 경우 작은 범위로 나누어서 반복하여 처리하도록 하자.

ex)

UPDATE TABLE_1 SET COLUMN_1 = 1000 WHERE COLUMN_2 < 50000

-->
//일부 수동처리를 하여 적절한 성능이 나온는 데이터의 범위를 찾아주는 과정이 필요하다.
DECLARE @NUM_BEFORE INT = 0, @NUM_AFTER INT = 100

WHILE @NUM_BEFORE > 50000
BEGIN
	UPDATE TABLE_1 SET COLUMN_1 = 1 WHERE COLUMN_2 >= @NUM_BEFORE  AND COLUMN_2 < @NUM_AFTER
    SET @NUM_BEFORE = @NUM_BEFORE + 100
    SET @NUM_AFTER = @NUM_AFTER + 100
END

대량 데이터를 필요한 경우 나누어서 처리하는건 락 에스컬레이션 방지 외에도 특히 서비스 중인 DB에서는 자주 고려해야할 방법이라고 생각한다.

 

 

참조:

https://learn.microsoft.com/ko-kr/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver16#tf1224

https://learn.microsoft.com/ko-kr/troubleshoot/sql/database-engine/performance/resolve-blocking-problems-caused-lock-escalation