SQL Server 2008 封鎖 診斷與解決：從 DMV 定位到鎖等待逾時設定

前言

封鎖 是 SQL Server 並發控制的常態，短暫封鎖無傷大雅，但長時間封鎖會嚴重拖垮效能。本文說明長時間封鎖常見成因，並示範如何使用動態管理視圖 (DMV) 快速定位封鎖鏈、擷取封鎖 SQL 文本、強制終止進程，以及設定鎖等待逾時避免無限期等待。

長時間封鎖常見五大成因

1. 缺乏索引導致大量列鎖升級為表鎖：未建立適當索引，掃描大量資料列觸發鎖升級。
2. 應用程式在交易中等待使用者互動：GUI 輸入時保持交易開啟，長期佔用資源。
3. 交易開始前資料已被其他交易修改：資料版本衝突導致等待。
4. 不當使用鎖定提示：如僅存取少量資料卻強制 TABLOCK。
5. 長時間大量更新交易：單一交易更新大量資料列/資料表，應拆分為多個小批次交易。

一、使用 DMV 尋找並解決封鎖進程

1. 找出封鎖鏈

SELECT 
    blocking_session_id, 
    wait_duration_ms, 
    session_id 
FROM sys.dm_os_waiting_tasks 
WHERE blocking_session_id IS NOT NULL;

結果範例：blocking_session_id=52 封鎖 session_id=54。

2. 擷取封鎖者正在執行的 SQL 文本

SELECT t.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE c.session_id = 52;  -- 封鎖者的 session_id

輸出範例：(@1 int,@2 tinyint,@3 tinyint) UPDATE [Production].[ProductInventory] SET [Quantity] = @1 WHERE [ProductID]=@2 AND [LocationID]=@3

3. 強制終止封鎖進程

KILL 52;

KILL 三大參數說明：

• session_id：終止一般/分散式交易。
• UOW (Unit of Work GUID)：終止孤立分散式交易 (session_id = -2)，可從 sys.dm_tran_locks.request_owner_guid 取得。
• WITH STATUSONLY：僅顯示回滾進度，不實際終止。

注意：若封鎖成串 (A→B→C)，需遞迴查找 blocking_session_id 直到根因進程。

二、設定陳述式鎖等待逾時：SET LOCK_TIMEOUT

語法

SET LOCK_TIMEOUT timeout_ms;  -- 毫秒，超時回傳錯誤 1222

實戰示範

視窗 1 (持有鎖)：

USE AdventureWorks;
BEGIN TRAN;
UPDATE Production.ProductInventory 
SET Quantity = 400 
WHERE ProductID = 1 AND LocationID = 1;

視窗 2 (等待鎖，設定 1 秒逾時)：

USE AdventureWorks;
SET LOCK_TIMEOUT 1000;
UPDATE Production.ProductInventory 
SET Quantity = 406 
WHERE ProductID = 1 AND LocationID = 1;
/* 1 秒後報錯：
Msg 1222, Level 16, State 51, Line 3
Lock request time out period exceeded.
The statement has been terminated. */

解析：LOCK_TIMEOUT 僅限制「等待其他進程釋放鎖」的時間，不影響持有鎖進程的執行時間。善用此設定可避免應用程式無限期卡住，配合錯誤處理實現重試或降級邏輯。

總結

封鎖診斷核心流程：dm_os_waiting_tasks 找封鎖鏈 → dm_exec_connections + dm_exec_sql_text 取 SQL 文本 → KILL 終止根因進程。輔以 SET LOCK_TIMEOUT 保護應用層不因單一陳述式長時間等待而掛起。平時應從索引設計、交易縮短、避免使用者互動在交易中等良好習慣預防長時間封鎖。