阻塞 是 SQL Server 併發環境的頭號效能殺手。當連線 A 持有鎖，連線 B 請求相衝突的鎖類型時，B 將被迫等待。絕大多數嚴重阻塞源於 **應用程式設計缺陷** 而非資料庫本身。本文提煉八大設計鐵律，從源頭杜絕阻塞。
鐵律一：拒絕長時間運行的查詢混入 OLTP
- 現象： 大量刪除/更新、複雜報表查詢持有大量鎖，甚至升級為表鎖，封鎖高併發 OLTP。
- 對策：
- 物理隔離：報表/分析查詢導向唯讀複本 (AlwaysOn 可讀次要、還原備份、ETL 至數據倉庫)
- 查詢優化：建立覆蓋索引、拆分大查詢、使用分頁、避免
SELECT *
- 游標改寫：集合式操作 (JOIN/EXISTS/窗口函數) 替代逐列游標，效能常提升 10-100 倍
鐵律二：取消查詢必須配套 ROLLBACK/COMMIT
- 現象： 應用呼叫
SqlCommand.Cancel() 或 ODBC SQLCancel() 後，未發送 ROLLBACK，交易保持開啟，鎖持續佔有。
- 對策：
- 客戶端程式碼 必須 在
catch/finally 區塊顯式 ROLLBACK (或 COMMIT)
- 連線池機制：
SqlConnection.Close() 會自動回滾未完成交易，確保正確使用 using 區塊
- 監控：
sys.dm_tran_active_transactions 定期檢查長時間閒置交易
鐵律三：務必讀取完所有結果集
- 現象： 應用執行查詢後僅讀取前幾列就關閉
DataReader，剩餘結果集未讀取，導致共享鎖 (S 鎖) 持有到連線重置。
- 對策：
- 循環
while (reader.Read()) 讀取至結尾
- 或使用
DataAdapter.Fill(DataTable) 一次性載入
- 若框架不支援 (如舊版報表工具)，將該應用隔離至專用唯讀複本
鐵律四：交易越短越好，嚴禁使用者互動在交易中
- 現象： 交易開啟後等待使用者輸入 (彈窗確認、Web 頁面停留)，鎖持有時間以「分鐘/小時」計算。
- 對策：
- 所有輸入參數在
BEGIN TRAN 前蒐集完畢
- Web 應用採用「樂觀併發」：讀取時記錄版本號/時間戳，更新時
WHERE Version = @OldVersion
- 批次作業拆分為多個微交易，每筆提交
鐵律五：合理設計索引，減少不必要的鎖升級
- 缺少適當索引 → 掃描大量列 → 鎖升級為表鎖 (Tab Lock) → 全表封鎖
- 為
WHERE/JOIN/ORDER BY 欄位建立索引，實現精準鎖定 (Key/Row Lock)
- 避免過寬索引鍵，增加非叢集索引大小與鎖記憶體開銷
鐵律六：謹慎使用隔離等級，預設 READ COMMITTED 通常最佳
- SERIALIZABLE / REPEATABLE READ： 持有共享鎖至交易結束，極易阻塞，僅用於極少數嚴格一致性場景
- READ UNCOMMITTED (NOLOCK)： 髒讀風險，不可用於財務/庫存等關鍵業務
- READ COMMITTED SNAPSHOT (RCSI)： **強烈推薦** 開啟資料庫選項
ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON，讀寫不阻塞，語句級一致性，零代碼修改
鐵律七：避免 IN/OR 子句導致計畫失效與鎖升級
- 大量
IN 清單、OR 條件常導致優化器選擇掃描而非尋找
- 拆分為多個簡單查詢，或使用暫存表/TVP
JOIN
- 必要時加入
OPTION (RECOMPILE) 獲取參數化計畫
鐵律八：監控與告警機制常態化
- 部署
sp_WhoIsActive、Blocked Process Report (Trace Flag 1222/Extended Events) 自動捕獲阻塞鏈
- 關鍵指標告警：
Lock Wait Time > 5s、Blocking Chain Length > 3、Deadlocks/sec > 0
- 建立阻塞知識庫：記錄每次阻塞的 SQL、執行計畫、應用呼叫堆疊、解決方案，沉澱組織經驗
總結：阻塞治理 = 索引優化 + 交易縮短 + 隔離等級選對 + 監控到位
將這八大鐵律納入開發規範、代碼審查清單、上線檢查表，可從根本上消除 90% 以上的應用層阻塞問題。
