[SQL] DDL 封鎖 NOLOCK

延續 [SQL] 資料表提示-NOLOCK 該篇筆記，原以為使用 NOLOCK 就不會被鎖定，在 資料表提示 上看見這段文字

READUNCOMMITTED 和 NOLOCK 提示只適用於資料鎖定。 所有的查詢 (包括具有 READUNCOMMITTED 和 NOLOCK 提示的查詢)，都會在編譯和執行期間取得 Sch-S (結構描述穩定性) 鎖定。 因此，當並行交易在資料表上保有 Sch-M (結構描述修改) 鎖定時，查詢將會遭到封鎖。 例如，資料定義語言 (DDL) 作業會在修改資料表的結構描述資訊之前先取得 Sch-M 鎖定。 任何並行查詢，包括以 READUNCOMMITTED 或 NOLOCK 提示執行的查詢，會在嘗試取得 Sch-S 鎖定時遭到封鎖。 相反地，保有 Sch-S 鎖定的查詢將會封鎖嘗試取得 Sch-M 鎖定的並行交易。

原來操作 DDL 時，即使有使用 NOLOCK 也會被鎖定

Sample Code

USE AdventureWorks
GO 

DROP TABLE IF EXISTS tbDDLBlockDemo

CREATE TABLE tbDDLBlockDemo
(
  ID int identity(1,1) Primary key,
  Col1 int
)

----- DDL Block NOLOCK Demo
----- Session1
-- Step1：開啟交易並更改 Col1 資料型態，此時交易還在
begin transaction 
ALTER TABLE tbDDLBlockDemo ALTER COLUMN Col1 bigint

-- Step4：把交易 commit 後，Session2 就會馬上返回結果
COMMIT

----- Session2
-- Step2：利用 NOLOCK 查詢資料，此時 Session1 還在交易內
SELECT * FROM tbDDLBlockDemo WITH (NOLOCK)

----- Session3：Step3：利用 DMV 來觀察 Lock 情況
set transaction isolation level read uncommitted
SELECT *
FROM sys.dm_tran_locks 
WHERE resource_database_id IN (SELECT database_id FROM sys.databases WHERE [Name] = 'AdventureWorks')
ORDER BY request_session_id

觀察下圖右方 Session2 可以發現，查詢被 Block 住啦，故意讓它跑久一些

利用 DMV 可以觀察到 Session1 有 Sch-M 存在