SQL Server 資料表鎖定、解鎖與監控實戰指南

在並發控制場景中，有時需要手動鎖定資料表以進行維護、資料修正或防止髒讀。本文整理 SQL Server 中常用的表級鎖定語法、查詢被鎖資源的 DMV 查詢，以及透過系統預存程序 sp_lock 進行強制解鎖的完整流程。

一、資料表鎖定語法與鎖模式差異

1. HOLDLOCK (相當於 SERIALIZABLE 範圍鎖)

SELECT * FROM YourTable WITH (HOLDLOCK);

• 其他交易可讀取資料表（共享鎖相容）。
• 其他交易不可更新、刪除（排他鎖不相容）。
• 適用於需確保讀取一致性、防止幻讀的長時間交易。

2. TABLOCKX (排他資料表鎖)

SELECT * FROM YourTable WITH (TABLOCKX);

• 其他交易不可讀取、更新、刪除（完全排他）。
• 適用於大量資料匯入、結構變更等需獨佔資源的操作。

二、查詢目前被鎖定的資料表

利用 sys.dm_tran_locks 即時檢視物件層級鎖定：

SELECT
    request_session_id AS spid,
    OBJECT_NAME(resource_associated_entity_id) AS tableName
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

• spid：持有鎖定的工作階段 ID。
• tableName：被鎖定的資料表名稱。

三、強制解鎖（Kill 阻塞進程）標準流程

當必須強制釋放鎖定時，建議遵循以下四步驟：

步驟 1：建立暫存表承接 sp_lock 輸出

CREATE TABLE #HarveyLock (
    SPID INT, DBID INT, OBJID INT, INDID INT,
    TYPE VARCHAR(100), RESOURCE VARCHAR(100),
    MODE VARCHAR(100), STATUS VARCHAR(100)
);

步驟 2：將鎖定資訊寫入暫存表

INSERT INTO #HarveyLock EXEC sp_lock;

步驟 3：篩選目標鎖定資訊

SELECT * FROM #HarveyLock
WHERE OBJID = OBJECT_ID('YourTable') -- 依資料表過濾
  AND MODE IN ('X', 'IX', 'S', 'U'); -- 常見鎖模式

步驟 4：Kill 對應的 SPID

DECLARE @spid INT = 57; -- 取自上一步查詢結果
DECLARE @sql VARCHAR(1000) = 'KILL ' + CAST(@spid AS VARCHAR);
EXEC(@sql);

四、實務注意事項

• 謹慎使用 KILL：會導致該交易回滾，可能產生大量記錄檔 I/O 並延長資源釋放時間。
• 優先嘗試正常提交/回滾：聯繫持有鎖定的應用程式擁有者，請其正常結束交易。
• 鎖定升級：SQL Server 可能將大量列鎖升級為資料表鎖，透過 ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE) 可在資料表層級停用（需評估並發影響）。
• 延伸監控：結合 sys.dm_exec_requests 的 blocking_session_id、wait_type、wait_time 可建立完整阻塞鏈視圖。

五、SELECT 陳述式常用「加鎖選項」速查表

掌握上述工具與流程，即能在生產環境中快速定位、處理資料表鎖定爭用問題，保障系統穩定運行。