SQL Server 鎖機制全解析：獨占、共享、更新鎖與樂觀/悲觀鎖策略

鎖是資料庫實現並發控制、保障資料一致性的核心機制。本文從資料庫系統視角與程式設計師視角兩個維度，系統梳理 SQL Server 的鎖類型、相容性與應用策略。

一、資料庫系統視角：三大基礎鎖模式

1. 獨占鎖 - Exclusive Lock (X Lock)

• 觸發場景：INSERT、UPDATE、DELETE 等資料修改操作
• 特性：資源被獨佔，其他任何交易不可讀取也不可修改 (除非使用 NOLOCK 或 READ UNCOMMITTED)
• 持有時間：直到交易 COMMIT 或 ROLLBACK 結束才釋放
• 相容性：與所有其他鎖模式不相容

2. 共享鎖 - Shared Lock (S Lock)

• 觸發場景：SELECT 讀取操作
• 特性：允許多交易同時讀取同一資源，但禁止修改
• 持有時間：
  • 預設 READ COMMITTED 隔離級別：讀完該頁/列即釋放
  • HOLDLOCK / REPEATABLE READ / SERIALIZABLE：持有至交易結束
• 相容性：與 S、U、IS 相容；與 X、IX、SIX 不相容

3. 更新鎖 - Update Lock (U Lock)

• 設計目的：解決「讀取→更新」模式下的死鎖問題
• 運作流程：
  1. 交易準備更新資料時，先申請 U 鎖 (允許並發讀取，但阻擋其他 U/X 鎖)
  2. 確定要執行更新時，將 U 鎖轉換為 X 鎖
• 關鍵特性：同一資源同一時間只能有一個交易持有 U 鎖，有效避免兩個交易同時持有 S 鎖再嘗試轉 X 鎖造成的死鎖

二、程式設計師視角：並發控制策略

樂觀鎖

• 核心假設：資料衝突機率低，讀取時不加鎖
• 實作方式：資料表加 RowVersion (timestamp) 欄位或業務版本號；更新時在 WHERE 條件檢查版本號
• 優點：無鎖開銷、高並發吞吐、無死鎖風險
• 缺點：衝突時需應用層重試、適用「讀多寫少」場景
• SQL Server 支援：READ_COMMITTED_SNAPSHOT (RCSI) 隔離級別提供陳述式層級的樂觀讀取

悲觀鎖

• 核心假設：資料衝突機率高，必須預先加鎖
• 實作方式：SELECT ... WITH (UPDLOCK, HOLDLOCK) 或 WITH (XLOCK) 明確指定鎖提示
• 優點：強一致性、簡單直觀、適用「寫多」或高衝突場景
• 缺點：鎖開銷大、降低並發、易發生阻塞與死鎖

三、鎖相容性矩陣速查

✓ = 相容 (可同時) | ✗ = 不相容 (阻塞等待)

四、實務建議

1. 優先考慮 RCSI：開啟 READ_COMMITTED_SNAPSHOT ON 可消除讀寫阻塞，是現代 OLTP 系統的標準配置
2. 縮短交易範圍：只在必要時開啟交易，盡早提交
3. 統一存取順序：所有程式碼以相同順序存取資料表，避免死鎖
4. 避免過度鎖提示：除非有明確效能問題並經測試驗證，否則勿濫用 NOLOCK、XLOCK 等提示
5. 監控工具：使用 sp_lock、sys.dm_tran_locks、Erend Profiler / Extended Events 監控鎖等待與死鎖