SQL Server 更新鎖（U Lock）深度解析：為什麼 UPDATE 不直接用共享鎖或排他鎖？

在講授 SQL Server 鎖與阻塞時，最常被問到的問題：**UPDATE 語句掃描階段為什麼獲取更新鎖（U Lock），而不是共享鎖（S Lock）或直接獲取排他鎖（X Lock）？** 本文從死鎖避免、併發提升、非對稱兼容性三個維度深度解析 U Lock 的必要性與設計精妙。

UPDATE 執行計劃三階段與鎖演進

階段 1：讀取數據（定位行）          → 獲取 更新鎖 (U Lock)
階段 2：計算新值（CPU 運算）         → 持有 U Lock
階段 3：寫入數據（修改頁）            → U Lock 轉換為 排他鎖 (X Lock)

若階段 1 用 S Lock，階段 3 轉 X Lock 會發生什麼？

核心理由一：避免經典死鎖（Deadlock Avoidance）

假設兩個並發 UPDATE 同一張表，不同行，但掃描路徑重疊：

• Session A：讀取行 1（獲取 S Lock）→ 讀取行 2（等待 S Lock）
• Session B：讀取行 2（獲取 S Lock）→ 讀取行 1（等待 S Lock）
• 兩者嘗試將 S Lock 轉換為 X Lock 寫入：
• A 需要 B 釋放行 2 的 S Lock 才能轉 X；B 需要 A 釋放行 1 的 S Lock 才能轉 X。
• 循環等待 → 死鎖。

U Lock 解決：U Lock **與 S Lock 兼容，但與 U Lock、X Lock 不兼容**。

• Session A 讀取行 1 獲取 U Lock。
• Session B 讀取行 2 獲取 U Lock。
• A 繼續掃描到行 2：請求 U Lock，**被 B 的 U Lock 阻塞**（U-U 不兼容）。
• B 繼續掃描到行 1：請求 U Lock，**被 A 的 U Lock 阻塞**。
• 雙方在**掃描階段**就因 U Lock 互斥而串行化，**根本無法進入轉 X Lock 階段**，死鎖在萌芽期被消除。

核心理由二：提升讀併發（Concurrency Boost）

若階段 1 直接獲取 X Lock：

• 任何並發 SELECT 都會被 X Lock 阻塞（X-S 不兼容）。
• 讀寫完全串行化，併發度極低。

U Lock 解決：U Lock **與 S Lock 兼容**。

• UPDATE 掃描階段持有 U Lock 時，**並發 SELECT 仍可獲取 S Lock 正常讀取數據**（讀已提交隔離級別下讀取的是舊版本/等待提交）。
• 只有階段 3 轉 X Lock 時，才短暫阻塞新的讀寫請求。
• 大幅提升「讀多寫少」負載下的整體吞吐。

核心理由三：非對稱兼容性的演進與 SQL Server 的實現

• 學術模型（System R）：U Lock 設計為**非對稱** —— U 兼容 S，但 S **不**兼容 U。這意味著：若 S Lock 先獲取，後來的 U Lock 請求會被阻塞；若 U Lock 先獲取，後來的 S Lock 可通過。這進一步保護寫入者優先權。
• SQL Server 實現：U Lock 實現為**對稱兼容** —— **U 與 S 雙向兼容**。
• 優點：簡化鎖管理器邏輯，避免「S Lock 已持有，U Lock 請求等待」導致的寫入飢餓，整體併發更高。
• 代價：極端場景下寫入者可能被大量讀者延遲，但實際負載中極少見，且可通過隔離級別/提示控制。

兼容性矩陣速查

實戰驗證腳本

-- 會話 1：開始更新，停在計算階段（模擬長事務）
BEGIN TRAN;
UPDATE dbo.BigTable SET Val = Val + 1 WHERE Id = 1; -- 持有 U Lock → 轉 X Lock
-- 不提交，保持鎖
-- 會話 2：並發 SELECT 同一行
SELECT * FROM dbo.BigTable WHERE Id = 1; -- 讀已提交：被 X Lock 阻塞
-- 會話 3：並發 UPDATE 不同行
UPDATE dbo.BigTable SET Val = Val + 1 WHERE Id = 2; -- 掃描階段獲取 U Lock，若掃描經過 Id=1 頁會被 U/U 阻塞
-- 觀察 sys.dm_tran_locks
SELECT request_session_id, resource_type, resource_description, request_mode, request_status
FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID();

總結

更新鎖（U Lock）是 SQL Server 為 **UPDATE/DELETE/MERGE** 的 **掃描-修改** 兩階段模式量身定制的混合鎖：

1. 掃描階段用 U Lock：避免 S→X 轉換死鎖，兼容並發讀（S Lock）。
2. 修改階段轉 X Lock：保證寫入排他性。
3. 對稱兼容 S Lock：最大化讀寫併發，簡化鎖管理器。

理解 U Lock，是掌握 SQL Server 併發控制、排查死鎖、設計高併發寫入邏輯的核心鑰匙。