최적화된 잠금

아티클
10/09/2023

이 문서에서는 잠금 메모리 사용량을 줄이고 동시 트랜잭션을 차단하는 향상된 트랜잭션 잠금 메커니즘을 제공하는 새로운 SQL Server 데이터베이스 엔진 기능인 최적화된 잠금 기능을 소개합니다.

최적화된 잠금이란?

최적화된 잠금은 대용량 트랜잭션에 대해 잠금이 거의 유지되지 않는 잠금 메모리를 줄이는 데 도움이 됩니다. 또한 최적화된 잠금은 잠금 에스컬레이션을 방지합니다. 이렇게 하면 테이블에 더 많은 동시 액세스를 허용합니다.

최적화된 잠금은 TID(트랜잭션 ID) 잠금 및 LAQ(한정 후 잠금)의 두 가지 기본 구성 요소로 구성됩니다.

TID(트랜잭션 ID)는 트랜잭션의 고유 식별자입니다. 각 행은 마지막으로 수정한 TID로 레이블이 지정됩니다. 잠재적으로 많은 키 또는 행 식별자 잠금 대신 TID에 대한 단일 잠금이 사용됩니다. 자세한 내용은 TID(트랜잭션 ID) 잠금 섹션 을 검토하세요.
LAQ(자격 증명 후 잠금)는 잠금을 획득하지 않고 행의 커밋된 최신 버전에 대한 쿼리 조건자를 평가하여 동시성을 향상시키는 최적화입니다. 자세한 내용은 LAQ(인증 후 잠금) 섹션을 검토하세요.

예시:

최적화된 잠금이 없으면 테이블에서 1백만 개의 행을 업데이트하려면 트랜잭션이 끝날 때까지 1백만 개의 배타적(X) 행 잠금이 필요할 수 있습니다.
최적화된 잠금을 사용하면 테이블에서 100만 개의 행을 업데이트하려면 100만 개의 행 잠금이 필요할 수 있지만 각 행이 업데이트되는 즉시 각 잠금이 해제되고 트랜잭션이 끝날 때까지 하나의 TID 잠금만 유지됩니다.

이 문서에서는 최적화된 잠금의 두 가지 핵심 개념을 자세히 설명합니다.

가용성

현재 최적화된 잠금은 Azure SQL Database에서만 사용할 수 있습니다. 자세한 내용은 현재 사용할 수 있는 최적화된 잠금 위치는 어디를 참조 하세요.

최적화된 잠금을 사용하도록 설정되었나요?

최적화된 잠금은 사용자 데이터베이스별로 사용하도록 설정됩니다. 데이터베이스에 커넥트 다음 쿼리를 사용하여 데이터베이스에서 최적화된 잠금을 사용하는 경우 검사.

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('testdb', 'IsOptimizedLockingOn');

지정된 DATABASEPROPERTYEX데이터베이스에 연결되지 않은 경우 결과는 다음과 같습니다 NULL. 수신 0 해야 합니다(최적화된 잠금이 비활성화됨) 또는 1 (사용).

다른 데이터베이스 기능에 최적화된 잠금 빌드:

최적화된 잠금을 사용하려면 데이터베이스에서 ADR(가속 데이터베이스 복구) 을 사용하도록 설정해야 합니다.
최적화된 잠금의 이점을 최대한 활용하려면 데이터베이스에 대해 커밋된 커밋된 스냅샷 격리(RCSI)를 사용하도록 설정해야 합니다.

ADR 및 RCSI는 Azure SQL Database에서 기본적으로 사용하도록 설정됩니다. 현재 데이터베이스에 대해 이러한 옵션이 사용하도록 설정되어 있는지 확인하려면 다음 T-SQL 쿼리를 사용합니다.

SELECT name
, is_read_committed_snapshot_on
, is_accelerated_database_recovery_on
FROM  sys.databases
WHERE name = db_name();

잠금 개요

최적화된 잠금을 사용하도록 설정하지 않은 경우의 동작에 대한 간단한 요약입니다. 자세한 내용은 트랜잭션 잠금 및 행 버전 관리 가이드를 검토하세요.

데이터베이스 엔진 잠금은 데이터 무결성 및 일관성을 보호하기 위해 여러 트랜잭션이 동일한 데이터를 동시에 업데이트하지 못하도록 하는 메커니즘입니다.

트랜잭션이 데이터를 수정해야 하는 경우 데이터에 대한 잠금을 요청할 수 있습니다. 데이터에 충돌하는 다른 잠금이 없으면 잠금이 부여되고 트랜잭션이 수정을 진행할 수 있습니다. 다른 충돌 잠금이 데이터에 유지되는 경우 트랜잭션은 잠금이 해제될 때까지 기다렸다가 계속 진행해야 합니다.

여러 트랜잭션이 동일한 데이터에 동시에 액세스할 수 있는 경우 데이터베이스 엔진 동시 읽기 및 쓰기와 잠재적으로 복잡한 충돌을 해결해야 합니다. 잠금은 데이터베이스 엔진이 ANSI SQL 트랜잭션 격리 수준에 대한 의미 체계를 제공할 수 있는 메커니즘 중 하나입니다. 데이터베이스 잠금은 필수이지만 동시성, 교착 상태, 복잡성 및 잠금 오버헤드가 감소하면 성능 및 확장성에 영향을 줄 수 있습니다.

최적화된 잠금 및 TID(트랜잭션 ID) 잠금

행 버전 관리가 사용 중일 때 데이터베이스 엔진 모든 행에는 내부적으로 TID(트랜잭션 ID)가 포함됩니다. 이 TID는 디스크에 유지됩니다. 행을 수정하는 모든 트랜잭션은 해당 행에 TID를 스탬프합니다.

TID 잠금을 사용하면 행의 키를 잠그는 대신 행의 TID에서 잠금이 수행됩니다. 수정 트랜잭션은 해당 TID에 X 잠금을 유지합니다. 다른 트랜잭션은 첫 번째 트랜잭션이 여전히 활성 상태인 경우 검사 위해 TID에 대한 S 잠금을 획득합니다. TID 잠금을 사용하면 업데이트에 대해 페이지 및 행 잠금이 계속 수행되지만 각 행이 업데이트되는 즉시 각 페이지 및 행 잠금이 해제됩니다. 트랜잭션이 끝날 때까지 유지되는 유일한 잠금은 TID 리소스에 대한 X 잠금으로, 다음 데모에 설명된 대로 페이지 및 행(키) 잠금을 대체합니다. (다른 표준 데이터베이스 및 개체 잠금은 최적화된 잠금의 영향을 받지 않습니다.)

최적화된 잠금은 대용량 트랜잭션에 대해 잠금이 거의 유지되지 않는 잠금 메모리를 줄이는 데 도움이 됩니다. 또한 최적화된 잠금은 잠금 에스컬레이션을 방지합니다. 이렇게 하면 다른 동시 트랜잭션이 테이블에 액세스할 수 있습니다.

사용자의 현재 세션에 대한 잠금을 찾는 다음 T-SQL 샘플 시나리오를 고려합니다.

CREATE TABLE t0
(a int PRIMARY KEY not null
,b int null);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRAN
UPDATE t0
SET b=b+10;

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
AND resource_type in ('PAGE','RID','KEY','XACT');

COMMIT TRAN
GO
DROP TABLE IF EXISTS t0;

최적화된 잠금의 이점이 없는 동일한 쿼리는 다음 네 개의 잠금을 만듭니다.

sys.dm_tran_locks DMV(동적 관리 뷰)는 작동 중인 최적화된 잠금 관찰을 포함하여 잠금 문제를 검사하거나 해결하는 데 유용할 수 있습니다.

정규화 후 최적화된 잠금 및 잠금(LAQ)

TID 인프라를 기반으로 하는 최적화된 잠금은 쿼리 조건자의 보안 잠금 방식을 변경합니다.

최적화된 잠금 없이 쿼리의 조건자는 먼저 업데이트(U) 행 잠금을 수행하여 검색에서 행별로 검사. 조건자가 충족되면 행을 업데이트하기 전에 X 행 잠금이 수행됩니다.

최적화된 잠금을 사용하고 커밋된 읽기 스냅샷 격리 수준(RCSI)을 사용하도록 설정하면 행 잠금 없이 커밋된 최신 버전에 조건자가 적용됩니다. 조건자가 충족되지 않으면 쿼리가 검사의 다음 행으로 이동합니다. 조건자가 충족되면 행을 실제로 업데이트하기 위해 X 행 잠금이 수행됩니다. X 행 잠금은 트랜잭션이 종료되기 전에 행 업데이트가 완료되는 즉시 해제됩니다.

조건자 평가는 잠금을 획득하지 않고 수행되므로 서로 다른 행을 수정하는 동시 쿼리는 서로 차단되지 않습니다.

예시:

CREATE TABLE t1
(a int not null
,b int null);

INSERT INTO t1 VALUES (1,10),(2,20),(3,30);
GO

세션 1	세션 2
`BEGIN TRAN` `UPDATE t1` `SET b=b+10` `WHERE a=1;`
	`BEGIN TRAN` `UPDATE t1` `SET b=b+10` `WHERE a=2;`
`COMMIT TRAN`
	`COMMIT TRAN`

이전 예제에서 최적화된 잠금을 사용하여 변경 내용을 차단하는 동작입니다. 최적화된 잠금이 없으면 세션 2가 차단됩니다.

그러나 최적화된 잠금을 사용하면 세션 2의 조건자를 충족하지 않는 a=1이 포함된 행 1의 최신 커밋된 버전으로 인해 세션 2가 차단되지 않습니다.

조건자가 충족되면 행의 활성 트랜잭션이 완료될 때까지 기다립니다. S TID 잠금을 기다려야 하는 경우 행이 변경되었을 수 있으며 커밋된 최신 버전이 변경되었을 수 있습니다. 이 경우 업데이트 충돌로 인해 트랜잭션을 중단하는 대신 데이터베이스 엔진 동일한 행에서 조건자 평가를 다시 시도합니다. 조건자가 재시도 시 자격이 있으면 행이 업데이트됩니다.

조건자 변경이 자동으로 다시 시도되는 경우 다음 예제를 고려합니다.

CREATE TABLE t2
(a int not null
,b int null);

INSERT INTO t2 VALUES (1,10),(2,20),(3,30);
GO

세션 1	세션 2
`BEGIN TRAN` `UPDATE t2` `SET b=b+10` `WHERE a=1;`
	`BEGIN TRAN` `UPDATE t2` `SET b=b+10` `WHERE a=1;`
`COMMIT TRAN`
	`COMMIT TRAN`

최적화된 잠금 및 RCSI를 사용하여 쿼리 동작 변경

엄격한 트랜잭션 실행 순서를 사용하는 워크로드가 있는 읽기 커밋된 스냅샷 격리 수준(RCSI)의 동시 시스템은 최적화된 잠금을 사용하도록 설정할 때 다른 쿼리 동작을 경험할 수 있습니다.

트랜잭션 T2가 트랜잭션 T1 중에 업데이트된 열을 b 기반으로 테이블을 t1 업데이트하는 다음 예제를 고려해 보세요.

CREATE TABLE t1 (a int not null, b int null);

INSERT INTO t1 VALUES (1,1);
GO

세션 1	세션 2
BEGIN TRAN T1 UPDATE t1 SET b=2 WHERE a=1;
	BEGIN TRAN T2 UPDATE t1 SET b=3 WHERE b=2;
COMMIT TRAN
	COMMIT TRAN

최적화된 잠금의 필수적인 부분인 LAQ(한정 후 잠금)를 사용 및 사용하지 않고 위의 시나리오의 결과를 평가해 보겠습니다.

LAQ가 없는 경우

LAQ가 없으면 트랜잭션 T2가 차단되고 트랜잭션 T1이 완료될 때까지 기다립니다.

두 트랜잭션이 모두 커밋되면 테이블에 t1 는 다음 행이 포함됩니다.

 a | b
 1 | 3

LAQ를 사용하여

LAQ를 사용하면 트랜잭션 T2는 b행의 커밋된 최신 버전(b버전 저장소의 =1)을 사용하여 조건자(b=2)를 평가합니다. 이 행은 한정되지 않습니다. 따라서 T2는 건너뛰고 T2는 트랜잭션 T1에 의해 차단되지 않고 다음 행으로 이동합니다. 이 예제에서 LAQ는 차단을 제거하지만 결과가 달라집니다.

두 트랜잭션이 모두 커밋되면 테이블에 t1 는 다음 행이 포함됩니다.

 a | b
 1 | 2

Important

LAQ가 없더라도 애플리케이션은 잠금 힌트를 사용하지 않고 SQL Server(버전 관리 격리 수준)가 엄격한 순서를 보장한다고 가정해서는 안 됩니다. 이전 연습과 같이 엄격한 트랜잭션 실행 순서를 사용하는 워크로드가 있는 RCSI 아래의 동시 시스템에 있는 고객을 위한 일반적인 권장 사항은 더 엄격한 격리 수준을 사용하는 것입니다.

최적화된 잠금에 대한 진단 추가

최적화된 잠금으로 차단 및 교착 상태의 모니터링 및 문제 해결을 지원하려면 다음 추가 사항을 찾습니다.

최적화된 잠금을 위한 대기 유형
- XACTsys.dm_os_wait_stats(Transact-SQL)의 대기 유형 및 리소스 설명:
  - LCK_M_S_XACT_READ - 태스크가 읽을 의도를 사용하여 XACT wait_resource 형식의 공유 잠금을 대기할 때 발생합니다.
  - LCK_M_S_XACT_MODIFY - 수정할 의도를 사용하여 태스크가 XACT wait_resource 형식의 공유 잠금을 대기할 때 발생합니다.
  - LCK_M_S_XACT - 작업이 의도를 유추할 수 없는 XACT wait_resource 형식의 공유 잠금을 기다리는 경우에 발생합니다. 매우 드물게 발생합니다.
리소스 잠금 표시 유형
- XACT 리소스를 잠급니다. 자세한 내용은 sys.dm_tran_locks(Transact-SQL)를 참조 resource_description하세요.
리소스 표시 대기
- XACT 리소스를 기다립니다. 자세한 내용은 sys.dm_exec_requests(Transact-SQL)를 참조 wait_resource하세요.
교착 상태 그래프
- 교착 상태 보고서의 각 리소스에서 각 <xactlock> 요소는 교착 상태의 <resource-list>각 멤버의 잠금에 대한 기본 리소스 및 특정 정보를 보고합니다. 자세한 내용과 예제는 최적화된 잠금 및 교착 상태를 참조 하세요.

최적화된 잠금을 사용하는 모범 사례

커밋된 읽기 스냅샷 격리 사용(RCSI)

최적화된 잠금의 이점을 최대화하려면 데이터베이스에서 커밋된 읽기 스냅샷 격리(RCSI)를 사용하도록 설정하고 커밋된 읽기 격리를 기본 격리 수준으로 사용하는 것이 좋습니다. 사용하도록 설정하지 않은 경우 다음 샘플을 사용하여 RCSI를 사용하도록 설정합니다.

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

Azure SQL Database에서 RCSI는 기본적으로 사용하도록 설정되며 커밋된 읽기는 기본 격리 수준입니다. RCSI를 사용하도록 설정하고 커밋된 읽기 격리 수준을 사용하는 경우 판독기는 기록기를 차단하지 않으며 기록기는 판독기를 차단하지 않습니다. 읽기 권한자는 쿼리 시작 시 수행된 스냅샷 행 버전을 읽습니다. LAQ를 사용하면 기록기는 U 잠금을 획득하지 않고도 최신 커밋된 행 버전을 기반으로 조건자당 행을 한정합니다. LAQ를 사용하면 행이 자격이 있고 해당 행에 활성 쓰기 트랜잭션이 있는 경우에만 쿼리가 대기합니다. 커밋된 최신 버전을 기준으로 한정하고 정규화된 행만 잠그면 차단이 줄어들고 동시성이 증가합니다.

차단을 줄이는 것 외에도 필요한 잠금 메모리가 줄어듭니다. 이는 판독기가 잠금을 사용하지 않고 기록기는 트랜잭션이 끝날 때 만료되는 잠금 대신 짧은 기간 잠금만 취하기 때문입니다. 반복 가능한 읽기 또는 직렬화 가능과 같은 더 엄격한 격리 수준을 사용하는 경우 데이터베이스 엔진 읽기 권한자와 기록기 모두에 대해 트랜잭션이 끝날 때까지 행 및 페이지 잠금을 유지해야 하므로 차단 및 잠금 메모리가 증가합니다.

힌트 잠금 방지

테이블 및 쿼리 힌트는 적용되지만 최적화된 잠금의 이점을 줄입니다. 쿼리에서 UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK 등과 같은 잠금 힌트는 최적화된 잠금의 모든 이점을 줄입니다. 쿼리에 이러한 잠금 힌트가 있으면 데이터베이스 엔진 행/페이지 잠금을 가져와 트랜잭션이 끝날 때까지 잠금 힌트의 의도를 적용하도록 합니다. 일부 애플리케이션에는 잠금 힌트가 필요한 논리가 있습니다. 예를 들어 UPDLOCK을 사용하여 선택한 행을 읽은 다음 나중에 업데이트하는 경우와 같습니다. 필요한 경우에만 잠금 힌트를 사용하는 것이 좋습니다.

최적화된 잠금을 사용하면 기존 쿼리에 대한 제한이 없으며 쿼리를 다시 작성할 필요가 없습니다. 힌트를 사용하지 않는 쿼리는 최적화된 잠금을 통해 가장 많은 이점을 얻을 수 있습니다.

쿼리의 한 테이블에 대한 테이블 힌트는 동일한 쿼리의 다른 테이블에 대해 최적화된 잠금을 사용하지 않도록 설정하지 않습니다. 또한 최적화된 잠금은 UPDATE 문으로 업데이트되는 테이블의 잠금 동작에만 영향을 줍니다. 예시:

CREATE TABLE t3
(a int not null
, b int not null);

CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;

이전 쿼리 예제에서는 테이블 t4 만 잠금 힌트의 영향을 받지만 t3 최적화된 잠금의 이점을 활용할 수 있습니다.

UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;

이전 쿼리 예제에서는 테이블 t3 만 반복 가능한 읽기 격리 수준을 사용하고 트랜잭션이 끝날 때까지 잠금을 유지합니다. 최적화된 잠금을 활용할 수 있는 t3 다른 업데이트도 있습니다. HOLDLOCK 힌트도 마찬가지입니다.

질문과 대답(FAQ)

최적화된 잠금은 현재 어디에서 사용할 수 있나요?

현재 최적화된 잠금은 Azure SQL Database에서 사용할 수 있습니다.

최적화된 잠금은 다음 서비스 계층에서 사용할 수 있습니다.

모든 DTU 서비스 계층
프로비전 및 서버리스를 포함한 모든 vCore 서비스 계층

최적화된 잠금은 현재 다음에서 사용할 수 없습니다.

Azure SQL Managed Instance
SQL Server 2022(16.x)

최적화된 잠금은 기본적으로 새 데이터베이스와 기존 데이터베이스 모두에서 사용 중입니까?

Azure SQL Database에서는 그렇습니다.

최적화된 잠금이 사용되는지 어떻게 감지할 수 있나요?

최적화된 잠금이 사용하도록 설정되어 있는지 확인하세요 .

내 데이터베이스에서 ADR(가속 데이터베이스 복구)을 사용하도록 설정하지 않으면 어떻게 되나요?

ADR을 사용하지 않도록 설정하면 최적화된 잠금도 자동으로 비활성화됩니다.

최적화된 잠금에도 불구하고 쿼리를 강제로 차단하려면 어떻게 해야 하나요?

RCSI를 사용하는 고객의 경우 최적화된 잠금을 사용할 때 두 쿼리 간에 강제로 차단하려면 READCOMMITTEDLOCK 쿼리 힌트를 사용합니다.

최적화된 잠금을 사용하지 않도록 설정할 수 있나요?

현재 고객은 최적화된 잠금을 사용하지 않도록 설정하는 지원 요청을 만들 수 있습니다.

다음 단계를 사용하여 Azure SQL Database용 Azure Portal에서 새 지원 요청을 만듭니다.

먼저 데이터베이스에 대해 최적화된 잠금이 사용하도록 설정되어 있는지 확인합니다.
Azure Portal 메뉴에서 도움말 + 지원을 선택합니다.
도움말 + 지원에서 지원 요청 만들기를 선택합니다.
문제 유형에서 기술을 선택합니다.
구독, 서비스 및 리소스의 경우 원하는 SQL Database를 선택합니다.
요약하면 "최적화된 잠금 사용 안 함"을 입력합니다.
문제 유형에 대해 성능 및 쿼리 실행을 선택합니다.
문제 하위 형식의 경우 차단 및 교착 상태를 선택합니다.
추가 세부 정보에서 최적화된 잠금을 사용하지 않도록 설정하려는 이유에 대해 가능한 한 많은 정보를 제공합니다. 최적화된 잠금을 사용하지 않도록 설정하는 이유와 사용 사례를 검토하는 데 관심이 있습니다.

최적화된 잠금

최적화된 잠금이란?

가용성

최적화된 잠금을 사용하도록 설정되었나요?

잠금 개요

최적화된 잠금 및 TID(트랜잭션 ID) 잠금

정규화 후 최적화된 잠금 및 잠금(LAQ)

최적화된 잠금 및 RCSI를 사용하여 쿼리 동작 변경

최적화된 잠금에 대한 진단 추가

최적화된 잠금을 사용하는 모범 사례

커밋된 읽기 스냅샷 격리 사용(RCSI)

힌트 잠금 방지

질문과 대답(FAQ)

최적화된 잠금은 현재 어디에서 사용할 수 있나요?

최적화된 잠금은 기본적으로 새 데이터베이스와 기존 데이터베이스 모두에서 사용 중입니까?

최적화된 잠금이 사용되는지 어떻게 감지할 수 있나요?

내 데이터베이스에서 ADR(가속 데이터베이스 복구)을 사용하도록 설정하지 않으면 어떻게 되나요?

최적화된 잠금에도 불구하고 쿼리를 강제로 차단하려면 어떻게 해야 하나요?

최적화된 잠금을 사용하지 않도록 설정할 수 있나요?

피드백

피드백

추가 리소스

최적화된 잠금

최적화된 잠금이란?

가용성

최적화된 잠금을 사용하도록 설정되었나요?

잠금 개요

최적화된 잠금 및 TID(트랜잭션 ID) 잠금

정규화 후 최적화된 잠금 및 잠금(LAQ)

최적화된 잠금 및 RCSI를 사용하여 쿼리 동작 변경

최적화된 잠금에 대한 진단 추가

최적화된 잠금을 사용하는 모범 사례

커밋된 읽기 스냅샷 격리 사용(RCSI)

힌트 잠금 방지

질문과 대답(FAQ)

최적화된 잠금은 현재 어디에서 사용할 수 있나요?

최적화된 잠금은 기본적으로 새 데이터베이스와 기존 데이터베이스 모두에서 사용 중입니까?

최적화된 잠금이 사용되는지 어떻게 감지할 수 있나요?

내 데이터베이스에서 ADR(가속 데이터베이스 복구)을 사용하도록 설정하지 않으면 어떻게 되나요?

최적화된 잠금에도 불구하고 쿼리를 강제로 차단하려면 어떻게 해야 하나요?

최적화된 잠금을 사용하지 않도록 설정할 수 있나요?

관련 콘텐츠

피드백

피드백

추가 리소스