SQL Server U 鎖深度解析：從 sp_lock 現象到鎖升級機制

現象重現

兩張表結構相同，差異在於索引類型：

• tb1：唯一叢集索引 on a
• tb2：唯一非叢集索引 on a

兩個連線同步執行：

BEGIN TRAN;
UPDATE tb1 SET b = b + 1 WHERE a = 1;  -- 連線 A
UPDATE tb1 SET b = b + 1 WHERE a = 1;  -- 連線 B（阻塞）

sp_lock 觀察

連線 B 等待時，sp_lock 顯示連線 A 持有：

• 資料庫層級：S 鎖
• 頁面層級（PAG 1:89）：IX 鎖
• 鍵/行層級：U 鎖 → 轉換為 X 鎖

U 鎖（更新鎖）核心機制

定義

U 鎖是「意圖更新」的非對稱鎖，專為 讀取階段尋找符合條件的行 而設計，防止死鎖。

生命週期

1. 掃描/尋找： 讀取資料頁/索引頁時，在符合 WHERE 條件的行上申請 U 鎖（而非 S 鎖）
2. 資格檢查： 確認行確實需更新
3. 轉換： 將 U 鎖升級為 X 鎖（排他鎖） 完成實際修改

相容性矩陣（關鍵差異）

關鍵：U-U 不相容，兩條 UPDATE 同時尋找同一行時，第二條會在 U 鎖處等待，避免「雙方各持 S 鎖互相等待升級 X」的死鎖。

叢集 vs 非叢集索引的差異

• 叢集索引： 資料即索引，直接在叢集鍵上導航 → 行級 U 鎖 → X 鎖
• 非叢集索引： 先在非叢集索引尋找鍵值（U 鎖），再透過 RID/叢集鍵回查基表（再次 U→X），鎖路徑更長，死鎖風險略高

實戰建議

• 縮短交易範圍，減少 U 鎖持有時間
• 確保 WHERE 條件有高選擇性索引，避免全表掃描產生大量 U 鎖
• 使用 READ_COMMITTED_SNAPSHOT（RCSI）將讀操作移至版本存儲，間接減少 U 鎖爭用
• 監控 sys.dm_tran_locks 中 resource_type=KEY 且 request_mode=U 的等待鏈

結語

U 鎖是 SQL Server 鎖管理器為「搜尋-更新」模式量身打造的防死鎖機制。理解其「先 U 後 X」的轉換邏輯與非對稱相容性，是診斷 UPDATE 阻塞與死鎖的關鑰鑰匙。