SQL Server 鎖機制完整解析：鎖類型、相容性、死鎖偵測與解除

SQL Server 透過鎖管理器自動管理並發控制，保護資料一致性。本文詳細說明各類鎖模式、相容性矩陣、動態管理檢視監控、死鎖成因與處理、以及實務解鎖腳本。

常見鎖類型

鎖相容性矩陣

若資源已被鎖定，新請求僅在模式相容時授予。例如：X 鎖與任何鎖皆不相容；S 鎖相容 S/U，不相容 X。詳細矩陣請參考官方文件。

監控鎖狀態

SELECT resource_type, request_mode, resource_description,
       request_session_id, DB_NAME(resource_database_id) AS db_name
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE';

死鎖偵測與處理

• 引擎自動偵測循環等待，選擇犧牲者終止交易 (錯誤 1205)。
• 可用 sys.dm_tran_locks、SQL Server Profiler 死鎖圖形事件分析。
• 手動解除：KILL 終止造成阻塞的工作階段。

查詢被鎖資料表與解鎖腳本

-- 查詢被鎖資料表
SELECT request_session_id AS spid,
       OBJECT_NAME(resource_associated_entity_id) AS tableName
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

-- 解鎖：終止 SPID
DECLARE @spid INT = 57;
EXEC('KILL ' + @spid);

實務建議

• 縮短交易範圍、避免長時間持有鎖。
• 使用適當隔離等級，必要時加入 NOLOCK (讀取未認可資料) 或 ROWLOCK 提示。
• 定期檢視 sys.dm_tran_locks 發現熱點資源。