MS-SQL 鎖機制深度解析：從原理到實戰的完整指南

本文系統闡述 SQL Server 鎖機制的核心原理、分類體系、粒度層次、持有時機、自訂控制與監控診斷，並提供大量實戰範例與最佳實務建議，幫助 DBA 與開發者徹底掌握並發控制。

一、為什麼需要鎖？並發帶來的三大不一致問題

• 丟失更新：兩交易讀取同一資料並修改，後提交者覆蓋先提交者的修改 (典型如訂票系統超賣)
• 髒讀：交易 A 修改資料未提交，交易 B 讀取了這筆「髒資料」，A 回滾後 B 手中資料與資料庫不一致
• 不可重複讀：交易 A 兩次讀取同一資料，中間交易 B 修改並提交，導致 A 前後讀取結果不一致

鎖透過在一段時間內禁止特定操作，強制序列化衝突操作，從而保證隔離性與一致性。

二、鎖模式完整分類 (官方體系)

1. 基礎資源鎖模式

2. 意向鎖 - 建立層級鎖保護

意向鎖表示「我打算在較低層級 (頁/列) 申請 S/X 鎖」，讓 SQL Server 只需檢查表級意向鎖即可判斷是否可對整表加鎖，避免掃描所有頁/列鎖。

3. 其他特殊鎖

• 結構鎖：Sch-M (結構修改，DDL 專用)、Sch-S (結構穩定，編譯查詢計劃時)
• 大量更新鎖：BU，BULK INSERT ... WITH (TABLOCK) 時使用

三、鎖粒度：從列到資料庫的層級體系

SQL Server 支援多層次鎖粒度，粒度越細並發度越高但開銷越大：

動態鎖升級：當單一交易持有大量細粒度鎖 (預設 > 5000) 時，自動嘗試升級為表級鎖以節省記憶體。可用 Trace Flag 1211/1224 或 ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE) 控制。

四、鎖持有時間與隔離級別的關係

五、自訂鎖行為的五大手段

1. 死鎖優先級：SET DEADLOCK_PRIORITY LOW/NORMAL/HIGH/@var，決定死鎖犧牲者
2. 鎖等待逾時：SET LOCK_TIMEOUT 1800 (毫秒)，超時自動取消陳述式回傳錯誤 1222
3. 隔離級別：SET TRANSACTION ISOLATION LEVEL ... 或查詢提示 WITH (READUNCOMMITTED) 等
4. 表級鎖提示：SELECT ... WITH (HOLDLOCK, ROWLOCK, UPDLOCK, TABLOCKX, NOLOCK...)
5. 索引鎖粒度設定：EXEC sp_indexoption 'IndexName', 'DisAllowPageLocks', 'TRUE'

六、監控與診斷工具

• EXEC sp_lock / SELECT * FROM sys.dm_tran_locks：檢視當前鎖狀態
• DBCC TRACEON(1204, -1) / 1222：死鎖資訊寫入錯誤日誌 (文字 / XML 格式)
• 查詢分析器 Ctrl+2：即時鎖資訊視窗
• Extended Events (XEvents) lock_deadlock、lock_escalation：生產環境輕量級監控

七、死鎖預防與處理實戰清單

1. 縮短交易邏輯，盡早 COMMIT/ROLLBACK
2. 設定合理 LOCK_TIMEOUT (建議 3-10 分鐘)，避免進程無限期掛起
3. 優化索引與查詢計劃，減少掃描範圍與鎖持有時間
4. 所有 SP 加入 TRY...CATCH 錯誤處理，檢查 @@ERROR/ERROR_NUMBER()
5. 統一多表存取順序 (如永遠先鎖 TableA 再鎖 TableB)
6. 避免在高並發熱點表上執行長時間報表查詢，採用 RCSI 或唯讀副本

八、實戰鎖提示範例

-- 1. 鎖定單列 (需配合索引)
SELECT * FROM Orders WITH (ROWLOCK, UPDLOCK) WHERE OrderID = 1001;

-- 2. 表級共享鎖 (允許讀、阻擋寫)
SELECT * FROM ConfigTable WITH (HOLDLOCK, TABLOCK);

-- 3. 表級排它鎖 (阻擋所有其他存取)
SELECT * FROM LogTable WITH (TABLOCKX) WHERE 1=0;

-- 4. 跳過被鎖列 (適用佇列處理)
SELECT TOP 1 * FROM TaskQueue WITH (READPAST, ROWLOCK, UPDLOCK) WHERE Status=0;

九、應用程式鎖

透過 sp_getapplock / sp_releaseapplock 實現邏輯層面的互斥，不鎖定實體資料資源，適用於跨表、跨資料庫甚至分散式協調場景。

十、常見鎖提示對照表 (SQL Server 2000/2005 相容)

關鍵區別：WITH (HOLDLOCK) 允許其他讀取；WITH (TABLOCKX) 完全封鎖表，連讀取也阻擋。