SQL Server 鎖定機制實戰：並發 INSERT/SELECT 封鎖與死鎖的三種解決方案

案例：雙重 INSERT 觸發的鍵範圍鎖衝突

場景：兩用戶同時新增資料，程式邏輯：
1. `BEGIN TRAN` → 2. `INSERT INTO TableA ...` → 3. `SELECT COUNT(*) FROM TableA WHERE ...` 檢查唯一性 → 4. `COMMIT/ROLLBACK`

鎖衝突分析

• Step 2 `INSERT`：請求 IX (Intent Exclusive) 鎖 於 TableA。IX 鎖相容，兩連線均取得成功。
• Step 3 `SELECT`：請求 S (Shared) 鎖 於 TableA。S 鎖與 他人持有的 IX 鎖不相容。
• 連線 A 先執行 SELECT，因連線 B 持有 IX 鎖 → 等待 (Blocking)。
• 連線 B 隨後執行 SELECT，同樣等待連線 A 的 IX 鎖。
• 死鎖偵測器介入，選擇犧牲者 (通常成本較低者) → Deadlock Victim (Error 1205)，交易回滾。

三種解決方案比較

最佳實踐建議

1. 資料庫層面強制唯一性：建立 UNIQUE CONSTRAINT 或 UNIQUE INDEX。移除應用層 `SELECT COUNT(*)` 檢查，直接 `INSERT` 捕捉錯誤 2627/2601 回滾。此為根治方案，無鎖爭用、效能最佳、邏輯正確。
2. 若業務邏輯極其複雜無法單純靠約束，採用 方案 3 (sp_getapplock) 實現應用層序列化。
3. 避免方案 1 (NOLOCK) 用於寫入前驗證，避免方案 2 (TABLOCK) 用於高併發核心表。