다음을 통해 공유

최적화된 잠금

  • 아티클

적용 대상: AAzure SQL 데이터베이스

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

최적화된 잠금이란 무엇인가요?

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

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

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

예시:

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

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

가용성

현재 최적화된 잠금은 Azure SQL 데이터베이스에서만 사용할 수 있습니다. 자세한 내용은 최적화된 잠금은 현재 어디에서 사용할 수 있나요?를 참조하세요.

최적화된 잠금이 사용하도록 설정되어 있나요?

최적화된 잠금은 사용자 데이터베이스별로 사용하도록 설정됩니다. 데이터베이스에 연결한 후, 다음 쿼리를 사용하여 데이터베이스에서 최적화된 잠금이 사용하도록 설정되어 있는지 확인합니다.

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

DATABASEPROPERTYEX에 지정된 데이터베이스에 연결되지 않은 경우 결과는 NULL입니다 . 0(최적화된 잠금 사용 안 함) 또는 1(사용)을 수신해야 합니다.

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

ADR 및 RCSI는 Azure SQL 데이터베이스에서 기본적으로 사용하도록 설정됩니다. 현재 데이터베이스에 대해 이 옵션이 사용하도록 설정되어 있는지 확인하려면 다음 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 쿼리의 결과 집합 스크린샷.

최적화된 잠금을 활용하지 않는 동일한 쿼리는 다음 네 개의 잠금을 만듭니다.

최적화된 잠금을 사용하도록 설정한 경우 3개의 잠금을 보여 주는 단일 세션에 대한 sys.dm_tran_locks 쿼리의 결과 집합 스크린샷.

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 아래의 동시 시스템에 있는 고객을 위한 일반적인 권장 사항은 더 엄격한 격리 수준을 사용하는 것입니다.

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

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

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

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

RCSI(읽기 커밋된 읽기 스냅샷 격리)를 사용하도록 설정

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

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

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

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

힌트 잠금 방지

테이블 및 쿼리 힌트는 적용되지만 최적화된 잠금의 이점이 감소합니다. 쿼리에서 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 데이터베이스에서 사용할 수 있습니다.

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

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

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

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

최적화된 잠금은 기본적으로 새 데이터베이스와 기존 데이터베이스 모두에서 활성화되나요?

Azure SQL 데이터베이스에서는 그렇습니다.

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

최적화된 잠금이 사용하도록 설정되어 있나요?를 참조하세요.

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

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

최적화된 잠금이 활성화되어 있더라도 쿼리를 강제로 차단하려면 어떻게 해야 하나요?

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

관련 콘텐츠