SQL Server 鎖機制深度解析：從紅綠燈比喻到鎖升級實戰觀測

核心比喻：鎖 = 紅綠燈，鎖管理器 = 交通警察

無鎖 = 十字路口無號誌，資料不一致（髒讀、不可重複讀、幻讀）；鎖太狠 = 全紅燈，併發效能為零。SQL Server 鎖管理器在「資料一致性」與「併發效能」間動態平衡。

鎖分類速查表

鎖升級：從列鎖到表鎖的自動轉換

觸發條件（任一滿足即嘗試升級）

1. 鎖數量臨界值：單一查詢持有的鎖數量超過動態閾值（啟發式演算法）
2. 記憶體臨界值：單一查詢鎖佔用記憶體超過閾值（每鎖約 96 bytes）

注意：臨界值非固定，SQL Server 依系統負載動態調整。

控制鎖升級

• 資料表層級（SQL 2008+）：ALTER TABLE dbo.Test SET (LOCK_ESCALATION = AUTO | TABLE | DISABLE)
  • AUTO（預設）：分區表升級到分區層級，非分區表升級到表級
  • TABLE：直接升級到表級
  • DISABLE：禁用升級（謹慎使用，可能導致記憶體耗盡）
• 查詢層級 Hint：WITH (TABLOCK)、WITH (PAGLOCK) 強制粗粒度鎖
• 追蹤旗標 1211/1224：全域禁用/調整升級邏輯（不建議常駐）

實戰觀測：Profiler 捕捉 Lock:Escalation 事件

環境準備

CREATE DATABASE TestLock;
CREATE TABLE dbo.Test (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50) NOT NULL DEFAULT '',
    CreatedTime DATETIME NOT NULL DEFAULT GETDATE()
);

-- 插入 30 萬筆測試資料
WHILE 1=1 INSERT INTO dbo.Test(Name) VALUES('kk');

觀測步驟

1. SSMS → sys.dm_tran_locks 查看目前鎖狀態
2. Profiler 新增追蹤 → 只勾選 Lock:Escalation 事件
3. 執行大量更新：UPDATE dbo.Test SET Name = 'name' WHERE Name = 'kk';
4. 觀察 Profiler 觸發 Lock:Escalation，鎖從 KEY (X) → OBJECT (X)

鎖快照演進

-- 插入階段
Database (S) ×3, Page (IX), Object (IX), Key (X) ×2

-- 更新初期
Key (X) 大量增加

-- 觸發升級瞬間
Object (X) 出現，Schema (Sch-S) 保護中繼資料

-- 升級完成
僅保留 Object (X)，釋放所有 Key (X) 與 Sch-S

死鎖與隔離等級

• 悲觀鎖隔離等級：Read Uncommitted → Read Committed → Repeatable Read → Serializable
• 樂觀鎖隔離等級：Read Committed Snapshot (RCSI)、Snapshot Isolation (SI)
• 死鎖處理：SQL Server 自動偵測犧牲者（依成本），回滾其交易，錯誤 1205

實務建議

1. 縮短交易範圍，越快釋放鎖越好
2. 避免在交易中進行使用者互動/外部呼叫
3. 適當索引減少掃描範圍，降低鎖升級機率
4. 高併發場景評估 RCSI/SI，以版本存儲取代鎖競爭
5. 監控 sys.dm_os_waiting_tasks 與 sys.dm_tran_locks 定位阻塞鏈