키 범위 잠금
키 범위 잠금은 직렬화 가능 트랜잭션 격리 수준을 사용하는 동안 Transact-SQL 문에서 읽는 레코드 집합에 포함된 행 범위를 암시적으로 보호합니다. 직렬화 가능 격리 수준에서는 트랜잭션 중 실행되는 모든 쿼리가 트랜잭션 중 실행될 때마다 동일한 행 집합을 가져와야 합니다. 키 범위 잠금은 다른 트랜잭션에서 해당 키가 직렬화 가능 트랜잭션에서 읽은 키 범위에 속하는 새 행을 삽입하지 못하도록 하여 이 요구 사항을 보호합니다.
키 범위 잠금은 팬텀 읽기를 방지합니다. 또한 행 사이에서 키 범위를 보호하여 트랜잭션에서 액세스하는 레코드 집합에 대한 팬텀 삽입을 방지합니다.
키 범위 잠금은 인덱스에 배치되어 시작 키 값과 종료 키 값을 지정합니다. 이 잠금은 키 값이 해당 범위에 속하는 모든 행의 삽입, 업데이트 또는 삭제 시도를 차단합니다. 이는 이러한 작업을 수행하려면 먼저 인덱스에 대한 잠금을 획득해야 하기 때문입니다. 예를 들어 직렬화 가능 트랜잭션은 해당 키 값이 **'AAA'**부터 **'CZZ'**까지인 모든 행을 읽는 SELECT 문을 실행할 수 있습니다. **'AAA'**부터 **'CZZ'**까지의 범위에 있는 키 값에 대한 키 범위 잠금은 다른 트랜잭션에서 해당 키 값이 'ADG', 'BBD' 또는 **'CAL'**과 같이 해당 범위에 있는 행을 삽입하지 못하도록 합니다.
키 범위 잠금 모드
키 범위 잠금에는 범위-행 형식으로 지정된 범위 및 행 구성 요소가 모두 포함됩니다.
범위는 두 개의 연속되는 인덱스 항목 간의 범위를 보호하는 잠금 모드를 나타냅니다.
행은 인덱스 항목을 보호하는 잠금 모드를 나타냅니다.
모드는 사용된 혼합 잠금 모드를 나타냅니다. 키 범위 잠금 모드는 두 부분으로 구성됩니다. 첫 번째는 인덱스 범위(RangeT)를 잠그는 데 사용하는 잠금 유형을 나타내고 두 번째는 특정 키(K)를 잠그는 데 사용하는 잠금 유형을 나타냅니다. 두 부분은 T-K와 같이 하이픈(-)으로 연결됩니다.
범위
행
모드
설명
RangeS
S
RangeS-S
공유 범위, 공유 리소스 잠금. 직렬화 가능한 범위 검색입니다.
RangeS
U
RangeS-U
공유 범위, 업데이트 리소스 잠금. 직렬화 가능한 업데이트 검색입니다.
RangeI
Null
RangeI-N
삽입 범위, null 리소스 잠금. 인덱스에 새 키를 삽입하기 전에 범위를 테스트하는 데 사용됩니다.
RangeX
X
RangeX-X
배타적 범위, 배타적 리소스 잠금. 범위 내의 키를 업데이트할 때 사용됩니다.
[!참고]
내부적 Null 잠금 모드는 다른 모든 잠금 모드와 호환됩니다.
키 범위 잠금 모드에는 겹치는 키 및 범위에서 얻은 다른 잠금과 호환되는 잠금을 보여 주는 호환성 행렬이 있습니다. 전체 잠금 호환성 행렬은 잠금 호환성을 참조하십시오.
|
기존의 허가 모드 |
|
|
|
|
|
|
|---|---|---|---|---|---|---|---|
요청 모드 |
S |
U |
X |
RangeS-S |
RangeS-U |
RangeI-N |
RangeX-X |
공유(S) |
예 |
예 |
아니요 |
예 |
예 |
예 |
아니요 |
업데이트(U) |
예 |
아니요 |
아니요 |
예 |
아니요 |
예 |
아니요 |
배타적(X) |
아니요 |
아니요 |
아니요 |
아니요 |
아니요 |
예 |
아니요 |
RangeS-S |
예 |
예 |
아니요 |
예 |
예 |
아니요 |
아니요 |
RangeS-U |
예 |
아니요 |
아니요 |
예 |
아니요 |
아니요 |
아니요 |
RangeI-N |
예 |
예 |
예 |
아니요 |
아니요 |
예 |
아니요 |
RangeX-X |
아니요 |
아니요 |
아니요 |
아니요 |
아니요 |
아니요 |
아니요 |
변환 잠금
변환 잠금은 키 범위 잠금이 다른 잠금과 겹칠 때 만들어집니다.
잠금 1 |
잠금 2 |
변환 잠금 |
|---|---|---|
S |
RangeI-N |
RangeI-S |
U |
RangeI-N |
RangeI-U |
X |
RangeI-N |
RangeI-X |
RangeI-N |
RangeS-S |
RangeX-S |
RangeI-N |
RangeS-U |
RangeX-U |
변환 잠금은 다양한 복합 환경에서 짧은 시간 동안 나타날 수 있으며 때로는 동시 프로세스를 실행하는 동안에 나타납니다.
직렬화 가능 범위 검색, 단일 인출, 삭제 및 삽입
키 범위 잠금을 사용하면 다음 작업을 직렬화할 수 있습니다.
범위 검색 쿼리
존재하지 않는 행의 단일 인출
삭제 작업
삽입 작업
키 범위 잠금이 발생하려면 다음 조건을 만족해야 합니다.
트랜잭션 격리 수준을 SERIALIZABLE로 설정해야 합니다.
쿼리 프로세서가 인덱스를 사용하여 범위 필터 조건자를 구현해야 합니다. 예를 들어 SELECT 문의 WHERE 절은 다음 조건자를 사용하여 범위 조건을 설정할 수 있습니다. ColumnX BETWEEN N**'AAA'** AND N**'CZZ'**. 키 범위 잠금은 ColumnX가 인덱스 키 내에 있는 경우에만 얻을 수 있습니다.
예
다음 테이블 및 인덱스는 이어지는 키 범위 잠금 예의 기준으로 사용됩니다.
.gif "인덱스 B-트리가 있는 데이터베이스 테이블 그림")
범위 검색 쿼리
범위 검색 쿼리가 직렬화 가능인지 확인하려면 같은 트랜잭션 내에서 같은 쿼리를 실행할 때마다 같은 결과를 반환해야 합니다. 다른 트랜잭션에서는 범위 스캔 쿼리 내에 새 행을 추가하면 안됩니다. 그렇지 않으면 이러한 행은 팬텀 삽입이 됩니다. 예를 들어 다음 쿼리는 앞 그림의 테이블과 인덱스를 사용합니다.
SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';
키 범위 잠금은 이름이 Adam과 Dale 값 사이에 있는 데이터 행 범위에 해당하는 인덱스 항목에 설정되어 앞의 쿼리에서 한정하는 새 행의 추가 또는 삭제를 방지합니다. 이 범위의 첫 번째 이름은 Adam이지만 이 인덱스 항목에 대해 RangeS-S 모드 키 범위 잠금을 사용하면 Abigail과 같이 A로 시작하는 새 이름을 Adam 앞에 추가할 수 없습니다. 마찬가지로 Dale의 인덱스 항목에 대해 RangeS-S 키 범위 잠금을 사용하면 Clive와 같이 C로 시작하는 새 이름을 Carlos 뒤에 추가할 수 없습니다.
[!참고]
보유한 RangeS-S 잠금 수는 n+1입니다. 여기서 n은 쿼리를 만족하는 행 수입니다.
존재하지 않는 데이터의 단일 인출
트랜잭션 내의 쿼리가 존재하지 않는 행을 선택하려고 하면 같은 트랜잭션 내에서 나중에 쿼리를 실행해도 같은 결과를 반환해야 합니다. 다른 트랜잭션도 존재하지 않는 행을 삽입할 수 없습니다. 다음과 같은 쿼리를 예로 들 수 있습니다.
SELECT name
FROM mytable
WHERE name = 'Bill';
이 경우 Bill이라는 이름이 인접한 두 인덱스 항목 사이에 삽입되므로 키 범위 잠금은 Ben부터 Bing까지의 이름 범위에 해당하는 인덱스 항목에 적용됩니다. RangeS-S 모드 키 범위 잠금은 인덱스 항목 Bing에 적용됩니다. 이렇게 되면 다른 모든 트랜잭션이 Bill 등의 값을 인덱스 항목 Ben과 Bing 사이에 삽입할 수 없습니다.
삭제 작업
트랜잭션 내에서 값을 삭제할 때는 트랜잭션이 삭제 작업을 수행하는 동안 값이 속하는 범위를 잠글 필요가 없습니다. 삭제된 키 값을 트랜잭션이 끝날 때까지 잠그기만 해도 직렬화 기능이 유지됩니다. 다음과 같은 DELETE 문을 예로 들 수 있습니다.
DELETE mytable
WHERE name = 'Bob';
배타적(X) 잠금이 Bob이라는 이름에 해당하는 인덱스 항목에 설정되어 있습니다. 다른 트랜잭션은 삭제된 값인 Bob 전후에 값을 삽입하거나 삭제할 수 있습니다. 그러나 값 Bob을 읽거나 삽입하거나 삭제하려는 트랜잭션은 삭제 트랜잭션이 커밋되거나 롤백될 때까지 차단됩니다.
범위 삭제는 세 가지 기본 잠금 모드인 행 잠금, 페이지 잠금 또는 테이블 잠금을 사용하여 실행될 수 있습니다. 행, 페이지 또는 테이블 잠금 전략은 쿼리 최적화 프로그램에 의해 결정되거나 ROWLOCK, PAGLOCK 또는 TABLOCK 등의 최적화 프로그램 힌트를 통해 사용자가 지정할 수 있습니다. PAGLOCK 또는 TABLOCK을 사용하는 경우 이 페이지에서 모든 행이 삭제되면 데이터베이스 엔진은 즉시 인덱스 페이지 할당을 해제합니다. 반대로 ROWLOCK을 사용하면 삭제된 모든 행이 삭제된 것으로 표시만 되고 나중에 백그라운드 태스크를 사용하여 인덱스 페이지에서 제거됩니다.
삽입 작업
트랜잭션 내에서 값을 삽입할 때는 트랜잭션이 삽입 작업을 수행하는 동안 값이 속하는 범위를 잠글 필요가 없습니다. 삽입된 키 값을 트랜잭션이 끝날 때까지 잠그기만 해도 직렬화 기능이 유지됩니다. 다음과 같은 INSERT 문을 예로 들 수 있습니다.
INSERT mytable VALUES ('Dan');
범위를 테스트하기 위해 RangeI-N 모드 키 범위 잠금이 David 이름에 해당하는 인덱스 항목에 적용됩니다. 잠금이 허용되면 Dan이 삽입되고 Dan 값에 배타적(X) 잠금이 적용됩니다. Range-N 모드 키 범위 잠금은 범위를 테스트하는 데만 필요하며 트랜잭션이 삽입 작업을 수행하는 동안에는 보유되지 않습니다. 다른 트랜잭션은 삽입된 값 Dan 전후에 값을 삽입하거나 삭제할 수 있습니다. 그러나 값 Dan을 읽거나 삽입하거나 삭제하려는 트랜잭션은 삽입 트랜잭션이 커밋되거나 롤백될 때까지 차단됩니다.