MSSQL] Schema Locks

DB 엔진  isolation 수준

• Read uncommitted : 물리적으로 손상된 데이터만 읽지 않음
• Read committed : 이전에 읽은 수정되지 않은 데이터를 읽을 수 있음, SQL Server 기본수준

Schema Lock

• Sch-S(스키마 안정성 잠금) : SQL Server DB 엔진은 쿼리를 컴파일하고 실행할 때 스키마 안정성(Sch-S) 잠금을 사용합니다. 배타적 잠금등의 트랜잭션 잠금을 차단하지 않습니다. transaction isolation 수준에 상관없이 발생한다. Sch-M을 제외한 모든 Lock과 호환된다. 

테이블을 생성하고, 데이터를 추가하였다. 

create table dbo.LockData
(
	Id int not null,
	Placeholder char(200) not null
		constraint DEF_LockData_Placeholder
		default 'Placeholder',
	constraint PK_LockData
	primary key clustered(Id)
)
go
;WITH CTE(ID) AS
(
	SELECT 1


	UNION ALL


	SELECT ID + 1
	FROM CTE
	WHERE ID < 30000
)
INSERT INTO LockData(Id)
	select ID
	from CTE
OPTION (MAXRECURSION 0)
go

uncommitted level에서 8초 정도 소요되는 select문(CTE)을 실행해보자. 

set transaction isolation level read uncommitted
;with CTE
as
(
	select l1.Id
	from dbo.LockData l1 with (nolock) cross join
	 dbo.LockData l2 with (nolock) cross join
	 dbo.LockData l3 with (nolock)
)


select COUNT_BIG(*) from CTE
go

위 쿼리가 실행되는 동안, lock 데이터를 확인해보자. 

CREATE  PROCEDURE [dbo].[LOCKINFO]  
AS   
BEGIN


	SELECT
		TL1.resource_type
		,DB_NAME(TL1.resource_database_id) as [DB Name]
		,CASE TL1.resource_type
			WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id, TL1.resource_database_id)
			WHEN 'DATABASE' THEN 'DB'
			ELSE
				CASE
					WHEN TL1.resource_database_id = DB_ID()
					THEN
						(
							SELECT OBJECT_NAME(object_id, TL1.resource_database_id)
							FROM SYS.PARTITIONS
							WHERE hobt_id = TL1.resource_associated_entity_id
						)
					ELSE '(Run under DB context)'
				END
			END AS ObjectName
		,TL1.resource_description
		,TL1.request_session_id
		,TL1.request_mode
		,TL1.request_status
		,WT.wait_duration_ms as [Wait Duration (ms)]
		,(
			SELECT 
				SUBSTRING(
					S.Text,
					(ER.statement_start_offset / 2) + 1,
					((
						CASE
							ER.statement_end_offset
						WHEN -1
							THEN DATALENGTH(S.text)
							ELSE ER.statement_end_offset
						END - ER.statement_end_offset) / 2) + 1)
			FROM
				sys.dm_exec_requests ER
				CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) S
			WHERE
				TL1.request_session_id = ER.session_id
			) AS [Query]
			
	FROM
		sys.dm_tran_locks as TL1 left outer join sys.dm_os_waiting_tasks WT on 
			TL1.lock_owner_address = WT.resource_address and TL1.request_status = 'WAIT'
	WHERE
		TL1.request_session_id <> @@SPID and
		TL1.resource_type <> 'DATABASE'
	ORDER BY 
		TL1.request_session_id
     


  
END

Sch-S lock이 생성된 것을 확인할 수 있다. 

이번엔 select문(CTE) 실행 중 select문(CTE2)를 실행하고 LOCKINFO를 실행해보자. 

Sch-S Lock은 서로 호환되므로 2개가 동시처리 가능하다.

• Sch-M(스키마 수정 잠금) : SQL Server DB 엔진은 열을 추가하거나 테이블을 삭제하는 등의 테이블 DDL 작업 중에 스키마 수정(Sch-M)잠금을 사용한다. Sch-M잠금이 유지되는 동안에 테이블에 대한 동시 엑세스가 방지되며, 해제 전까지 모든 외부 작업을 차단한다. 어떤 Lock과도 호환되지 않는다. 

 select문(CTE) 실행 중, 아래 alter문을 다른 session에서 실행한다. 

begin tran
	alter table LockData
    add C1 int null
rollback

LOCKINFO를 실행해보자. 

 

Sch-M lock이 발생하였다. 

 

이번엔 select문(CTE) -> select문(CTE2) -> ALTER를 실행하고 LOCKINFO를 보자

Sch-S는 GTANT status이다.

 

이번엔 select문(CTE) -> ALTER ->select문(CTE2)를 실행하고 LOCKINFO를 보자

Sch-M과 Sch-S 하나는 WAIT status이다. ALTER문으로 Sch-M 잠금이 발생하면, 이후 쿼리(select문(CTE2))가 차단된다. 

 

이번엔 select문(CTE) -> ALTER ->INSERT를 실행하고 LOCKINFO를 보자

INSERT INTO LockData(ID) VALUES(-1)

 

 

Sch-M과 IX가 WAIT status이다.