SQL Server 2008 鎖機制深度解析：模式、資源、升級與表級控制

一、鎖的基本概念

鎖是關聯式資料庫為保證資料完整性、防止並發問題（髒讀、不可重複讀、幻讀、更新丟失）而實施的同步機制。SQL Server 動態管理鎖，但理解其運作有助於調優與故障排查。

二、主要鎖模式

三、可鎖定資源與粒度

粒度權衡：細粒度（ROW/KEY）並發高但記憶體消耗大；粗粒度（TABLE）記憶體少但並發低。SQL Server 會自動升級鎖。

四、鎖相容性矩陣（核心規則）

• X 與任何鎖不相容。
• U 僅相容 S。
• S 相容 S、U。
• 意向鎖（IS/IX/SIX）間相容規則較複雜，核心是 IX 與 IX 相容，IX 與 S 不相容等。

五、鎖升級

觸發條件：

1. 單一 T-SQL 陳述式在單一表/索引上取得 > 5,000 個鎖。
2. 實例總鎖數超過記憶體閾值（約 40% 非 AWE 記憶體）。

升級將細粒度鎖（ROW/PAGE）轉為粗粒度（TABLE），釋放記憶體但降低並發。SQL Server 2008 引入表選項控制升級行為：

六、SQL Server 2008 新增：表級鎖升級控制 (ALTER TABLE ... SET (LOCK_ESCALATION = ...))

範例

-- 設為分區級升級
ALTER TABLE Person.Address SET (LOCK_ESCALATION = AUTO);
SELECT lock_escalation, lock_escalation_desc FROM sys.tables WHERE name = 'Address';
-- 結果: 2, AUTO

-- 停用升級
ALTER TABLE Person.Address SET (LOCK_ESCALATION = DISABLE);
SELECT lock_escalation, lock_escalation_desc FROM sys.tables WHERE name = 'Address';
-- 結果: 1, DISABLE

七、實戰：用 sys.dm_tran_locks 監視鎖活動

-- 會話 1：加排他表鎖
BEGIN TRAN
SELECT * FROM Production.ProductDocument WITH (TABLOCKX);

-- 會話 2：查詢當前鎖
SELECT request_session_id AS sessionid,
       resource_type AS type,
       resource_database_id AS dbid,
       OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS objectname,
       request_mode AS rmode,
       request_status AS rstatus
FROM sys.dm_tran_locks
WHERE resource_type IN ('DATABASE', 'OBJECT');

結果會顯示 ProductDocument 上的 X 鎖（GRANT）。

資源關聯實體 ID 解析規則

• resource_type = OBJECT → resource_associated_entity_id 為 object_id，可用 sys.objects 查名稱。
• resource_type = ALLOCATION_UNIT → 該 ID 為 allocation_unit_id，需 join sys.allocation_units → sys.partitions 取 object_id。
• resource_type = KEY/PAGE/RID/HOBT → 該 ID 為 hobt_id，直接查 sys.partitions 取 object_id。
• resource_type = DATABASE/EXTENT/APPLICATION/METADATA → 該 ID 為 0。

八、調優建議

• 適當索引減少掃描範圍，降低鎖數量與升級機率。
• 避免長事務、使用者互動在事務中、大批次更新拆分。
• 分區大表可設 LOCK_ESCALATION = AUTO 將升級限制在分區層級。
• 高並發唯讀場景考慮 READ COMMITTED SNAPSHOT (RCSI) 或快照隔離，以行版本替代共享鎖。