萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 鎖機制深度解析:鎖類型、死鎖偵測與相容性矩陣

前言

鎖是 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

1. 共享鎖 (Shared Lock, S)

讀取資料時取得,允許並發讀取,阻擋修改。

USE AdventureWorks2008;
BEGIN TRAN;
SELECT * FROM Sales.SalesOrderHeader WITH (HOLDLOCK) WHERE SalesOrderID = 43662;
-- 另開 Session 查詢 sys.dm_tran_locks 觀察 S 鎖
-- ROLLBACK TRAN;
共享鎖示範

2. 更新鎖 (Update Lock, U)

讀取階段取得 U 鎖,後續轉換為 X 鎖進行更新,減少死鎖機率。

BEGIN TRAN;
SELECT * FROM Sales.SalesOrderHeader WITH (UPDLOCK) WHERE SalesOrderID = 43662;
-- 觀察 U 鎖
ROLLBACK TRAN;
更新鎖示範

3. 獨占鎖 (Exclusive Lock, X)

修改資料 (INSERT/UPDATE/DELETE) 時取得,完全阻擋其他存取(除非使用 NOLOCK)。


BEGIN TRAN;
UPDATE Sales.SalesOrderHeader SET ShipDate = GETDATE() WHERE SalesOrderID = 43662;
-- 觀察 X 鎖
ROLLBACK TRAN;
獨占鎖示範

4. 意圖鎖 (Intent Locks: IS, IX, SIX)

建立鎖層級,保護較低層級的 S/X 鎖不被衝突的表層鎖破壞。

5. 結構描述鎖 (Schema Locks: Sch-S, Sch-M)

  • Sch-S:編譯查詢計畫時取得,不阻擋資料存取。
  • Sch-M:DDL 操作 (ALTER TABLE, DROP TABLE) 時取得,阻擋所有存取。

BEGIN TRAN;
CREATE TABLE MyTable (ID INT, NAME VARCHAR(20));
-- 觀察 Sch-M 鎖
ROLLBACK TRAN;
結構描述鎖示範

6. 大量更新鎖 (Bulk Update, BU)

BULK INSERT 搭配 TABLOCK 提示時使用,允許平行大量載入,阻擋非大量載入存取。

7. 鍵範圍鎖 (Key-Range Lock)

可序列化隔離等級下隱式使用,防止幻讀。

二、死鎖與解除

死鎖形成條件

兩個以上交易循環等待對方持有的資源,形成循環依賴:

交易 A 鎖定資源 1 → 請求資源 2 (被 B 鎖定)
交易 B 鎖定資源 2 → 請求資源 1 (被 A 鎖定)

SQL Server 死鎖處理機制

  • 死鎖監視器定期掃描,選擇「犧牲者」終止交易 (錯誤 1205)。
  • 犧牲者選擇依據:交易成本、執行時間、鎖定資源量等。
  • 應用層應實作重試邏輯 (指數退避)。

死鎖偵測工具

  • sys.dm_tran_locks:即時鎖狀態。
  • SQL Server Profiler / Extended Events:Deadlock Graph 事件圖形化分析。
Profiler 死鎖圖形

三、鎖相容性矩陣

判斷兩把鎖能否同時授予同一資源:

鎖相容性矩陣1 鎖相容性矩陣2 鎖相容性矩陣3

核心原則:X 鎖與任何鎖不相容;S 鎖與 S/U 相容,與 X 不相容;意圖鎖僅與同層級或更高層級衝突檢查相關。

四、最佳實務總結

  1. 縮短交易範圍:盡早 COMMIT/ROLLBACK,減少鎖持有時間。
  2. 固定存取順序:所有交易依相同順序存取資料表/索引,打破循環等待。
  3. 適當索引:減少掃描範圍,降低鎖升級 (Lock Escalation) 機率。
  4. 隔離等級選擇:讀密集場景考慮 READ COMMITTED SNAPSHOT (RCSI) 以行版本替代 S 鎖。
  5. 監控與告警:設定 Extended Events 捕捉 lock_deadlock、lock_escalation 事件。

結語

鎖管理器內部自動運作,開發者無需直接請求鎖,但理解其原理能寫出高並發、低阻塞的 T-SQL 程式碼。善用 DMV 與 Profiler 進行可視化分析,是解決鎖爭用問題的關鍵。

copyright © 萬盛學電腦網 all rights reserved