최적화된 잠금

적용 대상: Microsoft Fabric의 Azure SQL Database SQL 데이터베이스

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

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

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

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

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

예시:

• 최적화된 잠금이 없으면, 테이블에서 1천 개의 행을 업데이트하려면 트랜잭션이 끝날 때까지 1천 개의 배타적(X) 행 잠금이 필요할 수 있습니다.
• 최적화된 잠금을 사용하면 테이블에서 1천 개의 행을 업데이트하려는 경우 1천 개의 X 행 잠금이 필요할 수 있지만 각 행이 업데이트되는 즉시 각 잠금이 해제되고 트랜잭션이 끝날 때까지 하나의 TID 잠금만 유지됩니다. 잠금이 빠르게 해제되므로 잠금 메모리 사용량이 줄어들고 잠금 에스컬레이션 이 발생할 가능성이 훨씬 적어 워크로드 동시 실행이 향상됩니다.

참고 항목

최적화된 잠금을 사용하도록 설정하면 DML(데이터 수정 언어) 문(예: INSERT, UPDATE, DELETE, MERGE)에서 획득한 행 및 페이지 잠금이 감소하거나 제거됩니다. 스키마 잠금과 같은 다른 종류의 데이터베이스 및 개체 잠금에는 영향을 주지 않습니다.

가용성

최적화된 잠금은 모든 서비스 계층 및 컴퓨팅 크기에서 Azure SQL Database 및 패브릭 SQL 데이터베이스 에서만 사용할 수 있습니다.

최적화된 잠금은 현재 Azure SQL Managed Instance 또는 SQL Server에서 사용할 수 없습니다.

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

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

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

결과	설명
0	최적화된 잠금을 사용 안 하도록 설정되어 있습니다.
1	최적화된 잠금을 사용하도록 설정되어 있습니다.
NULL	최적화된 잠금을 사용할 수 없습니다.

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

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

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();

잠금 개요

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

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

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

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

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

격리 수준 기반의 행 버전 관리가 사용 중일 때 또는 ADR이 사용하도록 설정되어 있을 때 데이터베이스의 모든 행에는 내부적으로 TID(트랜잭션 ID)가 포함됩니다. 이 TID는 디스크에 유지됩니다. 행을 수정하는 모든 트랜잭션은 해당 행에 TID를 표시합니다.

TID 잠금을 사용하면 행의 키를 잠그는 대신 행의 TID에 대해 잠금이 수행됩니다. 수정 트랜잭션은 해당 TID에 X 잠금을 보유합니다. 다른 트랜잭션은 TID에 대한 S 잠금을 획득하여 첫 번째 트랜잭션이 완료될 때까지 기다립니다. TID 잠금을 사용하면 수정에 대해 페이지 및 행 잠금이 계속 수행되지만, 각 행이 수정되는 즉시 각 페이지 및 행 잠금이 해제됩니다. 트랜잭션이 끝날 때까지 유지되는 유일한 잠금은 TID 리소스에 대한 단일 X 잠금으로, 여러 페이지 및 행(키) 잠금을 대체합니다.

쓰기 트랜잭션이 활성 상태인 동안 현재 세션에 대한 잠금을 보여 주는 다음 예제를 살펴보겠습니다.

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

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

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

BEGIN TRANSACTION;

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 TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

최적화된 잠금을 사용하는 경우 요청은 XACT(트랜잭션) 리소스에 대한 단일 X 잠금만 보유합니다.

최적화된 잠금을 사용하도록 설정하지 않은 경우 동일한 요청은 각 행에 대해 3개의 X 키 잠금과 행이 포함된 페이지에 1개의 IX(의도 배타적) 잠금인 4개의 잠금을 보유합니다.

sys.dm_tran_locks DMV(동적 관리 뷰)는 작동 중인 최적화된 잠금 관찰과 같이 잠금 문제를 검사하거나 해결하는 데 유용합습니다.

최적화된 잠금과 LAQ(인증 후 잠금)

TID 인프라를 기반으로 하는 최적화된 잠금은 DML 문(예: INSERT, UPDATE, DELETE 및 MERGE)이 잠금을 획득하는 방법을 변경합니다.

최적화된 잠금이 없으면 먼저 업데이트(U) 행 잠금을 수행하여 쿼리 조건자가 행별로 검사됩니다. 조건자가 충족되면 행을 업데이트하기 전에 배타적(X) 행 잠금이 수행되고 트랜잭션 종료까지 유지됩니다.

최적화된 잠금을 사용하고 RCSI(READ COMMITTED 스냅샷 격리 수준)를 사용하도록 설정하면 잠금 없이 행의 커밋된 최신 버전에 조건자가 검사됩니다. 조건자가 충족되지 않으면 쿼리가 검사의 다음 행으로 이동합니다. 조건자가 충족되면 행을 업데이트하기 위해 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 TRANSACTION; UPDATE t1 SET b = b + 10 WHERE a = 1;
	BEGIN TRANSACTION; UPDATE t1 SET b = b + 10 WHERE a = 2;
COMMIT TRANSACTION;
	COMMIT TRANSACTION;

최적화된 잠금이 없으면 세션 1이 행 세션 2에 대한 U 잠금을 유지하므로 세션 2가 차단됩니다. 하지만 최적화된 잠금을 사용하면 U 잠금이 수행되지 않고 행 1의 커밋된 최신 버전에서 열 a가 1과 같은데 이는 세션 2의 조건자를 충족하지 않으므로 세션 2가 차단되지 않습니다.

LAQ U 잠금이 수행되지 않으므로 조건자가 평가된 후 동시 트랜잭션이 행을 수정할 수 있습니다. 조건자가 충족되고 행에 다른 활성 트랜잭션(X TID 잠금 없음)이 없으면 행이 수정됩니다. 활성 트랜잭션이 있는 경우 다른 트랜잭션이 행을 수정했을 수 있으므로 데이터베이스 엔진 트랜잭션이 완료될 때까지 대기하고 수정 시 조건자를 다시 평가합니다. 조건자가 여전히 충족되면 행이 수정됩니다.

다른 트랜잭션이 행을 변경했기 때문에 조건자 평가가 자동으로 다시 시도되는 다음 예제를 살펴보세요.

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

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

세션 1	세션 2
BEGIN TRANSACTION; UPDATE t3 SET b = b + 10 WHERE a = 1;
	BEGIN TRANSACTION; UPDATE t3 SET b = b + 10 WHERE a = 1;
COMMIT TRANSACTION;
	COMMIT TRANSACTION;

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

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

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

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

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

LAQ(인증 후 잠금)를 사용 및 사용하지 않고 위의 시나리오의 결과를 평가해 보겠습니다.

LAQ를 사용하지 않는 경우

LAQ가 없으면 트랜잭션 T2의 UPDATE 문이 차단되고 트랜잭션 T1이 완료될 때까지 기다립니다. T1이 완료되면 T2는 조건자가 충족되므로 행 설정 열 b을 3로 업데이트합니다.

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

 a | b
 1 | 3

LAQ를 사용하는 경우

LAQ를 사용하면 트랜잭션 T2는 b 열이 1과 같은 행의 커밋된 최신 버전을 사용하여 조건자(b = 2)를 평가합니다. 해당 행은 자격이 없으므로 건너뛰고 문은 트랜잭션 T1에 의해 차단되지 않고 완료합니다. 이 예제에서 LAQ는 차단을 제거하지만 결과가 달라집니다.

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

 a | b
 1 | 2

Important

LAQ가 없더라도 애플리케이션은 행 버전 기반의 격리 수준이 사용될 때 잠금 힌트를 사용하지 않고 데이터베이스 엔진이 엄격한 순서를 보장한다고 가정해서는 안 됩니다. 이전 예제와 같이 엄격한 트랜잭션 실행 순서를 사용하는 RCSI 아래의 동시 워크로드에 있는 고객을 위한 일반적인 권장 사항은 REPEATABLE READ와 SERIALIZABLE처럼 더 엄격한 격리 수준을 사용하는 것입니다.

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

다음 개선 사항은 최적화된 잠금을 사용하는 경우 차단 및 교착 상태를 모니터링하고 문제를 해결하는 데 도움이 됩니다.

• 최적화된 잠금을 위한 대기 유형
  • TID의 S 잠금에 대한 XACT 대기 유형 및 sys.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를 참조하세요.
• 교착 상태 그래프
  • 교착 상태 보고서 <resource-list>의 각 리소스에서 각 <xactlock> 요소는 교착 상태의 각 멤버의 잠금에 대한 기본 리소스 및 특정 정보를 보고합니다. 자세한 내용과 예제는 최적화된 잠금 및 교착 상태를 참조하세요.

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

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

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

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

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

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

힌트 잠금 방지

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

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

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

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

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

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

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

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

이전 쿼리 예제에서는 t5 테이블만 REPEATABLE READ 격리 수준을 사용하고 트랜잭션이 끝날 때까지 잠금을 유지합니다. t5에 대한 다른 업데이트는 최적화된 잠금의 이점을 활용할 수 있습니다. HOLDLOCK 힌트의 경우도 마찬가지입니다.

질문과 대답(FAQ)

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

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

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

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

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

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

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

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

읽기 전용 보조 복제본에서 최적화된 잠금이 사용되나요?

아니요, DML 문은 읽기 전용 복제본에서 실행할 수 없으므로 해당 행 및 페이지 잠금은 수행되지 않습니다.

tempdb 및 임시 테이블에서 데이터를 수정할 때 최적화된 잠금이 사용되나요?

현재는 불가능합니다.

관련 콘텐츠

• 트랜잭션 잠금 및 행 버전 관리 지침
• RCSI(커밋된 스냅샷 격리 읽기)
• sys.dm_tran_locks (Transact-SQL)
• Azure SQL 내 가속 데이터베이스 복구
• 가속 데이터베이스 복구