SQL Server Lock 매커니즘

2021-11-25 Database

SQL Server는 잠금 관리자라고도 부르는 쿼리 프로세서가 트랜잭션의 액세스 유형과 격리 수준에 따라 리소스에 잠금(Lock)을 설정합니다. 잠금(Lock)은 행, 페이지, 테이블 등과 같은 리소스의 잠금 유형(잠금 단위)과 제약 사항에 따라 공유(Shared), 배타적(Exclusive) 등의 잠금 모드로 구분됩니다. 그리고 잠금 관리자는 부모-자식 관계를 가지는 리소스의 계층 구조에서 잠금을 획득할 때, 요청 리소스 뿐만 아니라 부모 리소스들에도 잠금을 표시하고 관리합니다.

리소스 계층 구조는 파일 탐색기에서 디렉토리 트리와 비슷한 개념입니다.

SQL Server의 리소스 계층 구조

페이지 및 익스텐트 아키텍처

데이터를 액세스하는 기본 단위는 페이지(PAGE)입니다. (오라클과 같은 DBMS는 블럭 단위를 사용합니다. )

하나의 페이지는 헤더, 데이터 행, 오프셋으로 구성되며 8kb 공간을 차지합니다.

데이터 행을 포함하고 있는 데이터 페이지, 인덱스 참조가 포함된 인덱스 페이지, 메타 데이터를 포함하는 메타 페이지가 있습니다.

익스텐트(Extents)는 연속되는 8페이지(64kb)의 공간 관리 단위입니다.

힙(HEAP)이란 Clurster Index가 없는 테이블을 말하며 데이터를 정렬하지 않고 저장하는 구조를 말합니다.

IAM(Index Allocation Map)은 힙 테이블에서 찾고자하는 데이터가 어떤 익스텐트와 페이지에 속하는지 분석하고 스캔해야 하는 범위를 찾습니다.

잠금 유형

잠금이 설정될 수 있는 행, 페이지, 테이블 등과 같은 리소스의 잠금 유형을 말하며 잠금 단위라고도 부릅니다.

잠금 모드는 아래쪽에서 상세하게 다루겠지만 잠금의 단위를 설명하기 위해 간략하게만 설명합니다.

X (Exclusive Lock)는 쓰기 전용 잠금이며, 다른 잠금과 호환되지 않습니다.

S (Shared Lock)는 읽기 전용 잠금이며, S, U와 호환되지만 X와는 호환되지 않습니다.

U (Update Lock)은 업데이트 전용 잠금이며, S 이외 잠금과 호환되지 않습니다.

RID

데이터 페이지의 행 잠김을 의미하며, 일반적으로 힙(HEAP) 테이블의 행을 액세스할 때 표시 됩니다.

CASE 1) 인덱스 없는 테이블에 3개의 행을 INSERT 했을때 잠금 상태

RID 타입과 X 모드로 잠금이 INSERT 행의 수 만큼 생성 되었습니다.
저장된 각 행은 서로 다른 fileid:pagenumber:rid 형식의 Resource 식별자 값을 가집니다.
- fileid : 페이지가 포함된 파일 식별 값.
- pagenumber : 페이지 식별 값.
- rid : 페이지에 포함된 행의 식별 값.
sp_lock 컬럼 설명
- SPID : SQL을 실행한 프로세스 세션 ID
- DBID : 데이터베이스 식별번호
- OBJID : 개체 식별 번호
- INDID : 인덱스 식별번호
- TYPE : 잠금 유형 (TAB: 테이블, PAG: 페이지, KEY: 인덱스, RID: 행)
- RESOURCE : 리소스 식별값
- MODE : 잠금모드
- STATUS : 잠금요청상태 (WAIT인 경우 세션이 블로킹 상태)

CASE 2) INSERT 된 3개 행 중에서 1, 3번만 UPDATE하거나 DELETE 했을 때 잠금 상태

CASE 3) 하나의 세션에서 1번 행을 업데이트하고, 다른 세션에서 2번 행을 SELECT 하거나 UPDATE 했을 때 블로킹 발생

두 개의 세션이 서로 다른 행을 참조 했음에도 불구하고 블로킹(WAIT)이 발생합니다. 그 이유는 SQL Server가 리소스를 액세스(I/O)하는 기본단위가 페이지이고, Clurster Index가 없는 힙(HEAP) 테이블에서 참조하는 행을 찾기 위해는 IAM(Index Allocation Map)을 이용해서 테이블에 포함된 모든 페이지를 탐색해야 되기 때문입니다. 리소스 계층 구조에서 페이지는 페이지의 유형과 페이지 내에 포함된 행들의 데이터 크기에 따라 동적으로 확장될 수 있어서 하나의 행 데이터가 여러 페이지에 존재할 수 있습니다. 따라서 IAM은 특정 행을 찾기 위해서 테이블에 포함된 모든 페이지를 탐색해야 합니다. 이러한 매커니즘 때문에 RID 타입은 행의 잠금 상태를 표시 하지만 실제 잠금의 범위는 행 단위가 아니라 테이블 단위로 잠금 됩니다.

SQL Server Storage Engine: Heap Tables

KEY

인덱스 페이지의 키 잠금을 의미하며, Clurster Index가 생성된 테이블에 잠금 요청되면 행과 연결된 인덱스 키를 잠급니다.

인덱스 페이지란? 하나 이상의 키 값과 실제 데이터가 저장된 데이터 페이지를 가르키는 포인터를 가지는 정렬된 목록을 B-tree 구조로 저장한 페이지를 말합니다. 즉, Clurster Index 정보가 저장되는 페이지입니다.

KEY 타입은 인덱스 페이지에서 잠금을 획득하기 때문에 행 단위 잠금이 가능 합니다.

행 단위 잠금은 동시성이 향상되지만 많은 행을 잠글 경우 더 많은 잠금을 보유해야 하므로 오버헤드가 늘어납니다. 테이블 단위 잠금은 다른 트랜잭션이 테이블에 액세스하지 못하게 제한되므로 동시성은 떨어지지만 유지 관리할 잠금 수가 적으므로 오버헤드가 줄어듭니다.
Clurster Index 테이블은 조건절과 관계 없이 KEY 타입으로 잠금을 획득하지만, 조건절에 Clurster Index나 Non clustered Index 컬럼을 사용하지 않으면 동일한 Resource 값을 바라보면서 블로킹 상태가 됩니다.
Cluster index, Nonclustered Index 소개

PAGE

잠금 상태의 행이 어떤 페이지를 참조하고 있는지 의미하며, PAG 타입으로 표시 됩니다.

TABLE

잠금 상태의 행이 어떤 테이블을 참조하고 있는지 의미하며, TAB 타입으로 표시 됩니다.

DATABASE

특정 프로세스가 master 및 tempdb 이외의 데이터베이스를 사용할 때 공유 잠금(Shard Lock)을 설정하여 다른 프로세스가 사용 중인 데이터베이스를 변경하지 못하도록 방지합니다. DB 타입으로 표시 됩니다.

EXTENT

extent 공간 할당 및 해제 될 때 extent의 잠금을 의미하며, EXT 타입으로 표시 됩니다.

HoBT (Heap/BTree)

데이터 페이지의 힙 또는 인덱스의 BTree 구조에 대한 잠금을 의미하며, HBT 타입으로 표시 됩니다.

FILE

데이터베이스 파일의 잠금을 의미하며, FIL 타입으로 표시 됩니다.

APPLICATION

애플리케이션이 지정한 리소스의 잠금을 의미하며, APP 타입으로 표시 됩니다.

METADATA

메타데이터의 잠금을 의미하며, MD 타입으로 표시 됩니다.

ALLOCATION_UNIT

할당 단위에 대한 잠금을 의미하며, AU 타입으로 표시 됩니다.

잠금 모드

잠금 모드는 Shared(S), Update(U), Exclusive(X) 등이 있으며, 각 잠금은 잠금의 호환 여부에 따라 블로킹될 수 있습니다.

Shared(S)

공유잠금(S)은 읽는 데이터의 일관성을 보장하기 위해 다른 트랜잭션에서 쓰기 작업을 허용하지 않습니다. SQL Server의 기본 격리 수준(Read Committed)에서 SELECT를 하면 리소스를 공유잠금(S) 모드로 읽고 조회가 끝나면 잠금이 해제됩니다. 단, NOLOCK 힌트 또는 READ UNCOMMITED 격리 수준에서는 Shared (S)잠금을 하지 않습니다.

공유잠금(S) 회피하기 위해 NOLOCK 테이블 힌트를 사용하면 커밋되지 않은 데이터를 읽기 때문에 Dirty Read가 발생할 수 있습니다.

-- T1 트랜잭션에서 특정 ROW에 배타적 잠금을 설정합니다.
UPDATE TEST
SET NAME = 'modify'
WHERE SEQ = 2;

-- T2 트랜잭션에서 배타적 잠금된 ROW를 SELECT 하면 블로킹 됩니다.
SELECT *
FROM TEST
WHERE SEQ = 2;

Update(U)

리소스에 업데이트 계획을 표시하는 잠금입니다. Update(U) 잠금을 획득한 상태에서는 다른 트랜잭션의 공유 잠금(S)만 허용합니다. 또한 UPDATE가 수행되면 모드가 배타적잠금(X)으로 변환되고 UPDATE되지 않고 트랜잭션이 종료되면 공유모드(S)로 변환됩니다. 일반적으로 WITH (UPDTLOCK) 테이블 힌트를 사용하면 Update(U)잠금을 획득합니다.

SELECT *
FROM TEST WITH (UPDLOCK)
WHERE SEQ = 2;

WITH (UPDTLOCK) 테이블 힌트는 다른 DBMS에서 SELECT … FOR UPDATE를 사용하는 것과 유사하지만 공유잠금(S)을 허용한다는 차이점이 있습니다. 또한 업데이트(U) 잠금 간의 호환성이 없기 때문에 WITH (UPDTLOCK)문을 실행하는 트랜잭션 간 상호배타적으로 동작합니다.

-- T1 트랜잭션
SELECT *
FROM TEST WITH (UPDLOCK)
WHERE SEQ = 2;

-- T2 트랜잭션 (블로킹 상태로 WAIT)
SELECT *
FROM TEST WITH (UPDLOCK)
WHERE SEQ = 2;

-- T3 트랜잭션 (즉시 잠금을 획득하지 못하면 오류를 발생시키고 종료)
-- SQL Error [1222] [S0051]: 잠금 요청 제한 시간이 초과되었습니다.
SELECT *
FROM TEST WITH (UPDLOCK, NOWAIT)
WHERE SEQ = 2;

-- T4 (블로킹되지 않고 실행됨)
SELECT *
FROM TEST
WHERE SEQ = 2;

-- T1 트랜잭션에서 UPDATE문을 실행하면 모드가 U -> X로 변경됨.
UPDATE test
SET NAME = 'modify'
where seq = 2;

-- T1 트랜잭션이 종료되면 T2 트랜잭션이 UPDATE(U)잠금을 획득합니다.
COMMIT;

만약 다른 DBMS의 SELECT … FOR UPDATE 처럼 SELECT 시 배타적잠금(X)을 획득하려면 WITH(XLOCK) 테이블 힌트를 사용할 수 있습니다. 그러나 실제 데이터가 변경되지 않는 SELECT … TABLE WITH(XLOCK) 문에서 획득한 배타적잠금(X)은 UPDATE가 발생하기 전까지 다른 트랜잭션의 공유 잠금(S)이 허용되기 때문에 XLOCK 테이블 힌트와 더불어 PAGLOCK 같은 세분성을 제공하는 힌트를 추가 하여 다른 트랜잭션의 SELECT를 방지할 수 있습니다. 페이지 단위의 잠금은 다른 행도 잠금이 될 수 있기 때문에 충분히 검토 후 사용해야 됩니다.

☞ XLOCK 잠금이 공유잠금(S)를 허용하는 경우, 테이블 힌트

Exclusive(X)

INSERT, UPDATE, DELETE와 같은 DML 문을 실행하면 배타적잠금(X) 잠금을 획득합니다. 배타적잠금(X) 잠금은 다른 잠금과 호환성이 없습니다.

UPDATE test
SET NAME = 'modify'
where seq = 2;

Key-Range

키 범위 잠금은 리소스의 키 범위의 데이터가 다른 트랜잭션에 의해 변경되지 못하도록 방지합니다. 키 범위 모드는 T-K 와 같이 하이픈(-)으로 연결된 혼합 잠금 모드를 나타냅니다. 첫 번째는 인덱스 범위(Range T)를 잠그는 데 사용하는 잠금 유형을 나타내고 두 번째는 특정 키(K)를 잠그는 데 사용하는 잠금 유형을 나타냅니다. 자세한 내용은 SQL Server 문서를 확인하세요.

범위	행	Mode	Description
RangeS	S	RangeS-S	공유 범위, 공유 리소스 잠금. 직렬화 가능한 범위 검색입니다.
RangeS	U	RangeS-U	공유 범위, 업데이트 리소스 잠금, 직렬화 가능한 업데이트 검색입니다.
RangeI	Null	RangeI-N	삽입 범위, null 리소스 잠금. 인덱스에 새 키를 삽입하기 전에 범위를 테스트하는 데 사용됩니다.
RangeX	X	RangeX-X	배타적 범위, 배타적 리소스 잠금. 범위 내의 키를 업데이트할 때 사용됩니다.

일반적으로 다음과 같이 키 범위 잠금이 사용됩니다.

Serializable 격리 수준에서 읽은 레코드 집합의 키 범위에서 다른 트랜잭션이 새로운 키를 삽입하여 변경하는 것을 방지합니다. (Phantom Read 방지)
- 테이블에 아래와 같은 데이터가 있고 seq는 primary key입니다.
- T1 트랜잭션에서 범위 조건절로 조회하면 다음과 같이 KEY 타입의 행에 RangeS-S 잠금 모드가 설정됩니다.
```
SELECT * 
FROM test
where seq BETWEEN 1 and 10;
```
- T2 트랜잭션에서 범위안에 키 값을 삽입하려고 시도하면 다음과 같이 블로킹 당합니다.
```
-- 블로킹(WAIT)
INSERT INTO TEST(SEQ, NAME, SEQ2)
VALUES(5, '1', '2');
-- 블로킹(WAIT)
INSERT INTO TEST(SEQ, NAME, SEQ2)
VALUES(20, '1', '2');
```
  Resource가 (fffffffffff)로 설정된 행은 실제 존재하는 행의 잠금이 아니라 새로운 키에 대한 잠금입니다. 즉 T2에서 BETWEEN 범위 밖에 있는 키 값을 INSERT하려고 해도 블로킹 당합니다.
기본격리 수준(Read Committed)이라도 부모-자식 테이블 참조 관계에서 CASCADE와 같이 부모의 기본키를 변경할 때 자식 테이블의 외래키 값도 변경되어야 한다면 자식 테이블이 키 범위 잠금을 획득합니다.

Intent

Intent Lock은 잠금 관리자가 리소스의 잠금을 효율적으로 관리하기 위해 사용됩니다. 잠금 관리자가 잠금을 획득한 Data Row를 확인하기 위해 모든 테이블과 페이지의 Data Row를 탐색해야 한다면 비효율적이기 때문에 리소스가 잠금을 획득할 때 상위 리소스(페이지, 테이블)에 잠금 정보를 표시합니다. 이 후 잠금 정보를 확인할 필요가 있다면 페이지 또는 테이블만 탐색하면 되기 때문에 효율적으로 잠금을 파악할 수 있습니다. 이 처럼 상위 계층 리소스에 하위 리소스의 잠금을 표시하는 잠금을 Intent Lock이라고 합니다.

잠금 모드	Description
내재된 공유(IS)	계층 구조의 아래쪽에 있는 일부 리소스에 대해 요청되거나 확보된 공유 잠금을 보호합니다.
의도 배타(IX)	계층 구조의 아래쪽에 있는 일부 리소스에 대해 요청되거나 확보된 배타 잠금을 보호합니다. IX는 IS의 상위 집합으로, 하위 수준 리소스에 대한 공유 잠금 요청도 보호합니다.
의도 배타 공유(SIX)	계층 구조의 아래쪽에 있는 모든 리소스에 대해 요청되거나 확보된 공유 잠금 및 하위 수준 리소스 일부에 대해 요청되거나 확보된 의도 배타 잠금을 보호합니다. 최상위 수준 리소스에서는 동시 IS 잠금이 허용됩니다. 예를 들어 테이블에 대한 SIX 잠금을 확보하면 수정되는 페이지에 대한 의도 배타 잠금 및 수정되는 행에 대한 배타 잠금도 동시에 확보됩니다. 리소스당 한 번에 하나의 SIX 잠금을 설정할 수 있으므로 다른 트랜잭션이 테이블 수준에서 IS 잠금을 얻어 계층 구조 아래쪽에 있는 리소스를 읽을 수는 있어도 다른 트랜잭션이 리소스를 업데이트할 수는 없습니다.
의도 업데이트(IU)	계층 구조 아래쪽에 있는 모든 리소스에 대해 요청되거나 확보된 업데이트 잠금을 보호합니다. IU 잠금은 페이지 리소스에만 사용됩니다. 업데이트 작업이 발생하면 IU 잠금이 IX 잠금으로 변환됩니다.
공유 의도 업데이트(SIU)	S 잠금과 IU 잠금이 결합된 것으로, 두 잠금을 별도로 확보한 후 동시에 동시에 보유할 경우 설정됩니다. 예를 들어 트랜잭션이 PAGLOCK 힌트가 있는 쿼리를 실행한 다음 업데이트 작업을 실행하면 PAGLOCK 힌트가 있는 쿼리는 S 잠금을 확보하고 업데이트 작업은 IU 잠금을 확보합니다.
업데이트 의도 배타(UIX)	U 잠금과 IX 잠금이 결합된 것으로, 두 잠금을 별도로 확보한 후 동시에 동시에 보유할 경우 설정됩니다.

Granted mode	IS	S	U	IX	SIX	X
Intent shared (IS)	Yes	Yes	Yes	Yes	Yes	No
Shared (S)	Yes	Yes	Yes	No	No	No
Update (U)	Yes	Yes	No	No	No	No
Intent exclusive (IX)	Yes	No	No	Yes	No	No
Shared with intent exclusive (SIX)	Yes	No	No	No	No	No
Exclusive (X)	No	No	No	No	No	No

Bulk Update (BU)

여러 스레드가 데이터를 동시에 같은 테이블로 대량 로드하는 것은 허용하고, 데이터를 대량 로드하지 않는 다른 프로세스가 테이블에 액세스하는 것은 방지하기 위한 잠금입니다.

Transact-SQL BULK INSERT 문 사용
OPENROWSET(BULK) 함수를 사용
.NET SqlBulkCopy, OLEDB 빠른 로드 API
ODBC 대량 복사 API와 같은 BULK INSERT 명령 중 하나를 사용
JDBC 드라이버에서 대량 복사 사용

TABLOCK 힌트를 사용하는 INSERT INTO…SELECT 문은 테이블에 대해 배타적(X) 잠금을 획득하기 때문에 병렬 INSERT 작업을 할 수 없지만 단순 INSERT문 보다 처리 속도는 빠릅니다. (최소 로그 작업, 잠금의 수 등)

☞ INSERT INTO…SELECT를 사용하여 최소 로깅 및 병렬 처리로 데이터를 대량으로 가져오기

☞ SQL Server 대용량 데이터 입력 속도 향상 방법

간단하게 INSERT 와 TABLOCK를 사용한 INSERT를 비교해보면 TABLOCK으로 테이블 수준의 잠금을 할 경우 잠금의 수가 줄어 오버헤드 감소하지만 동시성이 떨어집니다.

INSERT INTO TEST(SEQ, NAME, SEQ2)
VALUES(4, '4', '4');
INSERT INTO TEST(SEQ, NAME, SEQ2)
VALUES(5, '5', '5');
INSERT INTO TEST(SEQ, NAME, SEQ2)
VALUES(6, '6', '6');

INSERT INTO TEST WITH(TABLOCK) (SEQ, NAME, SEQ2)
VALUES(4, '4', '4');
INSERT INTO TEST WITH(TABLOCK) (SEQ, NAME, SEQ2)
VALUES(5, '5', '5');
INSERT INTO TEST WITH(TABLOCK) (SEQ, NAME, SEQ2)
VALUES(6, '6', '6'); 

Schema

테이블의 스키마에 종속되는 작업이 실행될 때 획득하는 잠금입니다.

열을 추가하거나 테이블을 삭제하는 등의 테이블 DDL(데이터 정의 언어)이 실행되면 스키마 수정(Sch-M) 잠금을 획득합니다.
Sch-M 잠금이 유지되는 동안에는 테이블에 대한 동시 액세스(DDL, TRUNCATE 같은 일부 DML)가 차단됩니다.
쿼리를 컴파일하고 실행할 때 스키마 안정성(Sch-S) 잠금을 사용하고, Sch-S 잠금은 배타적(X) 잠금 등의 트랜잭션 잠금을 차단하지 않습니다.

잠금 에스컬레이션(Lock Escalation)

잠금 에스컬레이션은 다수의 행 잠금과 페이지 잠금 같은 세분화 잠금으로 인해 오버헤드가 너무 높아지면 테이블 잠금으로 변환되는 것을 말하며 잠금 에스컬레이터라는 프로세스가 이를 수행합니다. 만약 잠금 에스컬레이터 없이 행 크기가 500byte인 30,000개의 데이터 행을 업데이트 해야 된다면, 행에 배타적잠금(X) 그리고 테이블과 페이지에 의도배타적잠금(IX)을 약 1,800개를 획득해야 합니다. 이런 작업들이 병렬로 실행된다면 잠금 관리자가 작업을 원활하게 수행할 수 있도록 하기 위해 상당한 리소스가 필요할 수 있습니다. 따라서 SQL Server는 잠금 에스컬레이션을 사용하여 세분화 잠금을 테이블 잠금으로 변환하고 잠금 리소스의 부하를 줄입니다.

다음과 같은 상황에서 에스컬레이터가 발생할 수 있습니다.

메모리 임계값 잠금 메모리의 40%에 도달하거나 잠금 메모리가 버퍼 풀의 24%를 초과할 경우
획득한 잠금의 수가 5,000개를 초과할 경우

잠금 에스컬레이터의 기본값은 테이블이며, 다음 옵션으로 에스컬레이터를 변경할 수 있습니다.

ALTER TABLE <table_name> SET ( LOCK_ESCALATION = { TABLE | AUTO | DISABLE } )

잠금 에스컬레터 테스트를 위해 10,000개 행을 생성.

DECLARE @CNT INT
SET @CNT = 0

WHILE @CNT <= 10000
BEGIN  
	SET @CNT=@CNT+1;
	INSERT INTO TEST WITH(TABLOCK) (SEQ, NAME) 
	VALUES(@CNT, CAST(@CNT AS VARCHAR));
END 

COMMIT;

5000개 행을 업데이트 하면 행 수 만큼 잠금이 생성 되었으며 6200개까지 UPDATE하여도 잠금 에스컬레이터가 발생하지 않았습니다. SQL Server 문서에서는 5,000개 이상이면 잠금 에스컬레이터가 발생 한다고 했지만 실제 행의 크기에 따라 에스컬레이터 되는 행의 수는 달라지는 것 같습니다.

UPDATE TEST
set NAME = 'MODIFY'
where SEQ <= 5000;

6200개 이상 UPDATE 시 잠금 에스컬레이터로 인해 테이블 잠금으로 변환 되었습니다.

UPDATE TEST
SET NAME = 'MODIFY'
WHERE SEQ <= 6300;

외래키(Foreign Key) Lock

부모 테이블과 자식 테이블간에 참조 관계에 있을 때, 부모 테이블의 기본키를 수정하거나 행을 삭제할 경우 참조하고 있는 테이블의 외래키 또한 변경 되어야 되기 때문에 참조 테이블에도 잠금(Lock)을 획득하게 됩니다.

다음과 같은 참조 관계에서 상황별로 Lock이 어떻게 발생하는지 알아 보겠습니다.

ALTER TABLE TEST_CHILD ADD CONSTRAINT FK_TEST_LOCK FOREIGN KEY (PARENT_SEQ) REFERENCES TEST_PARENT(PARENT_SEQ) 
ON DELETE CASCADE 
ON UPDATE CASCADE;

참조 관계에서 외래키 값은 null 중복을 허용합니다.

CASCADE옵션은 부모 테이블의 기본키 값이 변경/삭제될 때 참조하는 외래키 값도 함께 변경/삭제됩니다.

Case 1) 부모 테이블의 기본키 값을 변경하는 경우

부모 테이블의 기본키(PARENT_SEQ) 값을 변경경우에는 부모테이블 행은 배타적잠금(X)을 획득하고, ON UPDATE 옵션이 CASCADE일 때 기본키를 참조하는 자식 테이블의 행도 변경되어야 되기 때문에 RangeX-X(범위 쓰기 잠금)을 획득합니다. 단, 부모 테이블의 기본키(PARENT_SEQ)가 아닌 일반 컬럼의 값을 변경하면 자식 테이블에는 잠금을 하지 않습니다.

UPDATE PARENT 
SET PARENT_SEQ = 5000
WHERE PARENT_SEQ = 1000;

자식 테이블에 기본키 인덱스(Cluster Index)가 없으면 행 수준의 범위 잠금이 아닌 테이블 잠금을 획득합니다.

- 자식 테이블에 인덱스가 없는 경우

- 자식 테이블에 외래키 인덱스만 있는 경우

- 자식 테이블에 기본키 인덱스가 있는 경우

Case 2) 자식 테이블 외래키 값을 변경하는 경우

부모 테이블의 기본키 값을 참조하는 외래키 값을 변경하려면 부모 테이블의 기본키가 존재하는지 확인해야 되기 때문에 부모 테이블에 공유잠금(S)를 요청합니다.

UPDATE CHILD 
SET PARENT_SEQ = 1000 
WHERE CHILD_SEQ = 1;

SQL Server의 기본 격리 수준(Read Committed)에서 READ_COMMITTED_SNAPSHOT 옵션을 ON으로 설정하면 일반적인 SELECT 연산에 공유잠금(S)을 요청하지 않지만 위의 경우 처럼 참조되는 기본키(PARENT_SEQ) 값을 확인할 때는 참조 무결성을 위해 공유잠금(S)을 요청합니다.

부모 테이블 기본키 값을 삭제하는 경우

부모 테이블의 기본키 값을 삭제하는 경우 부모 테이블은 배타적잠금(X)를 획득하고, CASCADE 또는 SET NULL 옵션이 설정되어 있다면 기본키를 참조하고 있는 자식 테이블에도 배타적잠금(X)을 획득합니다. (No Action 옵션이면 공유잠금(S)를 획득)

DELETE FROM PARENT
WHERE PARENT_SEQ = 1000;

자식 테이블 DELETE

삭제되는 행들만 배타적잠금(X)을 획득하고 부모 테이블은 잠금하지 않습니다.