鎖提示大全:精準控制並行存取行為
SQL Server 提供豐富的表提示,讓開發者在查詢層面宣告鎖定粒度與隔離級別。正確使用可解決阻塞、死鎖、髒讀等並發問題;濫用則會降低並行度。本文系統整理 12 種鎖提示的語義、適用場景與副作用。
鎖提示速查表
| 提示 | 鎖類型 | 範圍 | 持續時間 | 典型用途 |
| HOLDLOCK | 共享鎖 (S) | 依查詢計畫 | 交易結束 | 模擬 SERIALIZABLE,需長時間保持讀取一致性 |
| NOLOCK / READUNCOMMITTED | 無鎖 | 資料列/頁 | 語句執行期間 | 報表查詢容忍髒讀,極大減少阻塞 |
| PAGLOCK | 頁鎖 (S/IX/X) | 8KB 頁 | 語句/交易 | 強制頁級鎖,避免升級為表鎖 |
| READCOMMITTED | 共享鎖 (S) | 資料列/頁 | 讀取瞬間 | 明確指定 Read Committed 隔離語義 |
| READPAST | 跳過鎖定行 | 資料列 | 語句執行期間 | 佇列處理:跳過被鎖訂單,處理下一筆 |
| REPEATABLEREAD | 共享鎖 (S) | 資料列/頁 | 交易結束 | 模擬 REPEATABLE READ 隔離級別 |
| ROWLOCK | 列鎖 (S/U/X) | 單一資料列 | 語句/交易 | 強制列級鎖,減少鎖升級,高併發 OLTP |
| SERIALIZABLE | 範圍鎖 (RangeS-S) | 範圍/表 | 交易結束 | 等同 HOLDLOCK,防止幻讀,並行度最低 |
| TABLOCK | 表共享鎖 (S) | 整張表 | 語句結束(或交易結束配合 HOLDLOCK) | 大量載入、整表掃描、避免鎖升級開銷 |
| TABLOCKX | 表排他鎖 (X) | 整張表 | 語句/交易結束 | DDL 維護、整表重建、獨佔載入 |
| UPDLOCK | 更新鎖 (U) | 資料列/頁 | 交易結束 | 「先讀後寫」模式:讀取時加 U 鎖,防止其他交易修改,升級為 X 鎖時無衝突 |
選擇決策樹
- 唯讀報表、可容忍髒讀 →
NOLOCK(或資料庫層級 READ_COMMITTED_SNAPSHOT ON 更佳)。
- 高併發 OLTP 單行更新 →
ROWLOCK + UPDLOCK(SELECT ... UPDLOCK, ROWLOCK → UPDATE)。
- 佇列消費者模式 →
READPAST + ROWLOCK(跳過被處理訂單)。
- 大量批次匯入 →
TABLOCK(配合 BULK INSERT、TABLOCK 可最小化日誌)。
- 需防止幻讀的報表 →
SERIALIZABLE / HOLDLOCK(並行度極低,慎用)。
- 整表維護 →
TABLOCKX(離線窗口執行)。
常見誤區與最佳實踐
- NOLOCK 不是萬靈丹:可能讀到半寫入資料(撕裂頁)、重複行、遺漏行。關鍵財務報表禁用。
- ROWLOCK 可能適得其反:大範圍掃描時成千上萬列鎖會耗盡鎖記憶體,觸發鎖升級為表鎖。配合
PAGLOCK 或讓引擎自行決定更佳。
- UPDLOCK 必須在交易中:自動提交模式下語句結束即釋放,失去保護作用。
- 監控鎖等待:
sys.dm_tran_locks、sys.dm_os_wait_stats (LCK_M_*)、sp_lock(舊版)。
- 死鎖預防:固定存取順序(表 A → 表 B)、縮短交易範圍、使用
ROWLOCK 減少衝突面。
隔離級別 vs 鎖提示對應表
| 隔離級別 | 預設鎖行為 | 等效提示 |
| READ UNCOMMITTED | 無共享鎖 | NOLOCK / READUNCOMMITTED |
| READ COMMITTED | 讀取瞬間 S 鎖,讀完即釋放 | READCOMMITTED |
| REPEATABLE READ | S 鎖持有至交易結束 | REPEATABLEREAD / HOLDLOCK |
| SERIALIZABLE | 範圍鎖持有至交易結束 | SERIALIZABLE / HOLDLOCK |
| SNAPSHOT | 版本存儲,無讀鎖 | 資料庫層級 ALLOW_SNAPSHOT_ISOLATION |