鎖是 SQL Server 確保交易隔離性與資料一致性的核心機制。理解鎖類型、相容性與死鎖處理,對效能調校與問題排查至關重要。本文系統整理 SQL Server 鎖的完整知識體系。
透過 sys.dm_tran_locks 動態管理檢視可即時觀察鎖狀態。關鍵欄位:
resource_type:資源類型 (OBJECT, PAGE, KEY, RID...)request_mode:鎖模式 (S, U, X, IS, IX, Sch-S, Sch-M...)request_session_id:持有/請求鎖的 Session ID讀取資料時取得,允許並發讀取,阻擋修改。
USE AdventureWorks2008;
BEGIN TRAN;
SELECT * FROM Sales.SalesOrderHeader WITH (HOLDLOCK) WHERE SalesOrderID = 43662;
-- 另開 Session 查詢 sys.dm_tran_locks 觀察 S 鎖
-- ROLLBACK TRAN;
讀取階段取得 U 鎖,後續轉換為 X 鎖進行更新,減少死鎖機率。
BEGIN TRAN;
SELECT * FROM Sales.SalesOrderHeader WITH (UPDLOCK) WHERE SalesOrderID = 43662;
-- 觀察 U 鎖
ROLLBACK TRAN;
修改資料 (INSERT/UPDATE/DELETE) 時取得,完全阻擋其他存取(除非使用 NOLOCK)。
BEGIN TRAN;
UPDATE Sales.SalesOrderHeader SET ShipDate = GETDATE() WHERE SalesOrderID = 43662;
-- 觀察 X 鎖
ROLLBACK TRAN;
建立鎖層級,保護較低層級的 S/X 鎖不被衝突的表層鎖破壞。
BEGIN TRAN;
CREATE TABLE MyTable (ID INT, NAME VARCHAR(20));
-- 觀察 Sch-M 鎖
ROLLBACK TRAN;
BULK INSERT 搭配 TABLOCK 提示時使用,允許平行大量載入,阻擋非大量載入存取。
可序列化隔離等級下隱式使用,防止幻讀。
兩個以上交易循環等待對方持有的資源,形成循環依賴:
交易 A 鎖定資源 1 → 請求資源 2 (被 B 鎖定) 交易 B 鎖定資源 2 → 請求資源 1 (被 A 鎖定)
sys.dm_tran_locks:即時鎖狀態。Deadlock Graph 事件圖形化分析。
判斷兩把鎖能否同時授予同一資源:
核心原則:X 鎖與任何鎖不相容;S 鎖與 S/U 相容,與 X 不相容;意圖鎖僅與同層級或更高層級衝突檢查相關。
READ COMMITTED SNAPSHOT (RCSI) 以行版本替代 S 鎖。lock_deadlock、lock_escalation 事件。鎖管理器內部自動運作,開發者無需直接請求鎖,但理解其原理能寫出高並發、低阻塞的 T-SQL 程式碼。善用 DMV 與 Profiler 進行可視化分析,是解決鎖爭用問題的關鍵。