掌握並發控制核心，解決髒讀、不可重複讀、幻讀三大異常

事務隔離級別定義了事務間可見數據的邊界。SQL Server 支持四級標準隔離級別，並通過鎖、行版本控制 (RCSI/SI) 實現。本文系統拆解鎖類型、隔離級別行為、表提示、死鎖預防與監控診斷。

鎖類型層級體系

四大隔離級別行為矩陣

行版本控制隔離級別 (無鎖讀)

表提示覆蓋隔離級別

SELECT * FROM T WITH (NOLOCK)           -- 等同 READ UNCOMMITTED
SELECT * FROM T WITH (READPAST)         -- 跳過被鎖行，不等待
SELECT * FROM T WITH (UPDLOCK, HOLDLOCK) -- 模擬 SERIALIZABLE: U 鎖 + 範圍鎖持有至事務結束
SELECT * FROM T WITH (TABLOCKX)         -- 獨佔表鎖，DDL/大批量操作用
SELECT * FROM T WITH (INDEX(IX_Name))   -- 強制索引 (非隔離提示)

死鎖預防與診斷

1. 統一訪問順序：所有事務按相同順序訪問表/行 (如按 PK 升序)。
2. 縮短事務：事務中不做 I/O、不調用外部服務、提前獲取所有鎖。
3. 使用 UPDLOCK：SELECT ... WITH (UPDLOCK) 直接獲取 U 鎖，避免 S→X 升級死鎖。
4. 設置鎖超時：SET LOCK_TIMEOUT 5000 (5秒)，避免無限等待，捕獲 1222 錯誤重試。
5. 死鎖圖分析：Trace Flag 1222/1204、Extended Events xml_deadlock_report、sys.dm_tran_deadlocks (2012+)。

監控關鍵 DMV

• sys.dm_tran_locks：當前鎖請求/持有詳情。
• sys.dm_os_wait_stats：LCK_M_* 等待統計。
• sys.dm_exec_requests：blocking_session_id、wait_type、wait_time。
• sys.dm_db_index_operational_stats：頁/行鎖升級、等待計數。

選型建議

• OLTP 核心交易：READ COMMITTED + RCSI (預設 2019+ 新建庫默認開啟 RCSI)。
• 報表/分析查詢：SNAPSHOT 或 NOLOCK (容忍髒讀換吞吐)。
• 庫存扣減/票務搶單：REPEATABLE READ + UPDLOCK 或 SERIALIZABLE 確保不超賣。
• 批量裝載/ETL：TABLOCK + BULK INSERT 降低鎖開銷。

理解鎖與隔離的本質，才能在一致性與並發間找到業務最優平衡點。