快速掌握 SQL Server 鎖機制與鎖模式：從原理到實戰

鎖是多使用者環境下保證資料一致性與隔離性的核心機制。SQL Server 採用多粒度、動態升級的鎖架構，本文系統解析鎖粒度、鎖模式、常見並發問題與觀察工具。

為何需要鎖？四大並發異常

鎖粒度層級 (由細到粗)

動態鎖升級：當單一交易持有鎖數量達閾值 (約 5000)，SQL Server 自動將列/頁鎖升級為表鎖，降低記憶體壓力。閾值與升級行為由引擎自動決定，不可手動設定。

核心鎖模式

更新鎖 (U) 如何防死鎖？

若無 U 鎖：兩交易先取 S 鎖讀取，再嘗試升級為 X 鎖 → 互相等待對方釋放 S 鎖 → 死鎖。
有 U 鎖：尋找階段直接取 U 鎖 (與 S 相容、與 U/X 不相容)，同一時間僅一交易持有 U 鎖，確定修改再升級為 X，打破循環等待條件。

觀察與排查工具

• SSMS 活動監視器：管理 → 目前活動 → 鎖/處理序 圖形化檢視、刷新、終止處理序。
• sp_lock：EXEC sp_lock [spid] 列出指定/所有處理序持有鎖。
• sys.dm_tran_locks (DMV)：現代替代方案，提供資源類型、模式、請求狀態、關聯物件等豐富欄位。

  SELECT resource_type, resource_description, request_mode, request_status, request_session_id
  FROM sys.dm_tran_locks;
  

• DBCC INPUTBUFFER(spid)：查看封鎖者正在執行的 T-SQL 批次。

死鎖處理原則

SQL Server 死鎖偵測器定期掃描等待圖，發現環狀等待時選擇「犧牲成本最低」的受害者 (通常是執行最少工作、最易重試的交易)，回滾並回傳錯誤 1205。應用層需捕捉 1205 並實作重試邏輯。

最佳實踐建議

1. 縮短交易範圍、降低隔離層級 (預設 READ COMMITTED)，減少鎖持有時間。
2. 統一存取順序 (如永遠先鎖 Table A 再鎖 Table B)，避免循環等待。
3. 善用索引縮小掃描範圍，降低升級為表鎖機率。
4. 必要時使用鎖提示 (ROWLOCK, UPDLOCK, HOLDLOCK, TABLOCKX 等) 精準控制，但濫用會適得其反。
5. 啟用 READ_COMMITTED_SNAPSHOT 以版本存放取代共享鎖，大幅降低讀寫封鎖。

深入理解鎖機制，才能在高並發系統中寫出高效、穩定的資料存取邏輯。