淺談 Microsoft SQL Server 鎖機制：分類、粒度、持續時間、自定義與死鎖防範

一、為何需要鎖

多用戶並發操作導致三大資料不一致問題：

• 遺失更新：兩用戶讀同一資料並修改，後寫入覆蓋前寫入 (如訂票系統)
• 髒讀：A 修改未提交，B 讀取，A 回滾 → B 讀到不存在的資料
• 不可重複讀：A 讀取，B 修改提交，A 再讀 → 前後不一致

鎖即在一段時間內禁止特定操作，實現悲觀並發控制。

二、鎖模式分類 (資源鎖)

程式設計師視角：樂觀 vs 悲觀

• 樂觀鎖：依賴資料庫版本戳/時間戳，提交時檢查衝突 (適合低衝突)
• 悲觀鎖：明確加鎖 (SELECT ... WITH (UPDLOCK/HOLDLOCK))，SQL Server 採此機制

三、鎖粒度

粒度越細並發度越高但開銷越大，SQL Server 動態升級 (Row → Page → Table)。

四、鎖持續時間

• 共享鎖 (S)：READ COMMITTED 僅讀取頁期間；HOLDLOCK/REPEATABLE READ/SERIALIZABLE 持有至交易結束
• 游標捲動鎖：至下一次 FETCH 或 CLOSE；加 HOLDLOCK 則至交易結束
• 排他鎖 (X)：**永遠持有至交易結束** (COMMIT/ROLLBACK)
• 阻塞等待：衝突鎖釋放或逾時 (SET LOCK_TIMEOUT)

五、鎖自定義與控制

六、檢視鎖資訊

• EXEC sp_lock 報告目前鎖狀態
• 查詢分析器 Ctrl+2 即時顯示
• DMV (SQL 2005+)：sys.dm_tran_locks, sys.dm_os_waiting_tasks

七、死鎖防範最佳實務

1. 縮短交易邏輯，盡早 COMMIT/ROLLBACK
2. 設定合理 LOCK_TIMEOUT (3~10 分鐘)，避免進程永久掛起
3. 統一多資源存取順序 (如固定按表名排序)
4. 所有 SP/腳本上線前嚴格測試並行場景
5. 所有 SP 包含 @@ERROR/TRY...CATCH 錯誤處理
6. 避免修改預設隔離級別、強行加鎖

八、常用加鎖語法速查

-- 鎖定單列
SELECT * FROM table WITH (ROWLOCK, UPDLOCK) WHERE id = 1;

-- 鎖定整表 (持有至交易結束)
SELECT * FROM table WITH (TABLOCK, HOLDLOCK);

-- Sybase 風格 (舊版相容)
UPDATE table SET col1 = col1 WHERE ...  -- 產生排他鎖