您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

深入解析 SQL Server 鎖機制：從核心概念到死鎖防範實戰

編輯：關於SqlServer

前言：為何必須掌握鎖機制？

在 SQL Server 這類多用戶資料庫系統中，鎖機制是確保交易完整性與資料一致性的基石。然而，許多開發人員只在遭遇死鎖或效能瓶頸時才被迫接觸這個領域。事實上，要有效研究並優化鎖機制，系統需同時滿足兩個前提條件：龐大的資料量與高併發的多用戶存取。若缺乏這兩者，資料庫極難觸發死鎖，盲目研究恐事倍功半；反之，若具備條件卻仍沿用預設設定，則極易引發「遺失更新」、「髒讀」、「不可重複讀」等經典併發異常。

一、鎖的核心概念

鎖是實現併發控制最常用的手段。當事務欲存取資源（如資料表、資料列）前，必須先向系統請求鎖定該資源。取得鎖後，事務即獲得資料控制權，在釋放鎖之前，其他事務無法更新該資料。事務回滾或提交時，系統自動釋放被鎖定資源。簡單說，當一位使用者鎖住某個資料庫物件時，其他使用者便無法存取該物件，以此保護資料完整性。

二、鎖的粒度與升級策略

SQL Server 支援多粒度鎖定，涵蓋 RID（列鎖）、Key（索引鍵鎖）、Page（頁鎖）、Extent（區塊鎖）、Table（資料表鎖）、Database（資料庫鎖）六個層級。粒度越細（如列鎖），併發度越高，但系統維護鎖的開銷越大；粒度越粗（如資料表鎖），開銷越低，但嚴重限制併發。

為平衡效能與併發，SQL Server 引入鎖升級機制：當單一交易持有的鎖數量達到臨界值，系統自動將大量細粒度鎖（列、頁）升級為粗粒度鎖（資料表），以降低系統負荷。值得注意的是，升級門檻與過程完全由系統自動判定，無需人工干預。

三、鎖模式詳解

SQL Server 定義多種鎖模式以應對不同業務場景：

共享鎖 (S)：用於唯讀操作（如 SELECT），允許並發讀取，阻擋修改。
更新鎖 (U)：用於可更新資源，預防多會話讀取、鎖定後更新時的常見死鎖形式。
排他鎖 (X)：用於資料修改（INSERT/UPDATE/DELETE），確保同一資源不被多重更新。
意向鎖 (IS/IX/SIX)：當獲取低層級鎖時，在上層物件加置的標記，讓系統快速判斷是否可在上層加鎖。
結構描述鎖 (Sch-M/Sch-S)：執行依賴資料表結構的操作時使用（如 DDL）。
大量更新鎖 (BU)：大量複製資料並指定 TABLOCK 時使用。

四、關鍵鎖設定與調優參數

1. 死鎖優先級

透過 SET DEADLOCK_PRIORITY { LOW | NORMAL } 控制死鎖發生時的犧牲者選擇。設為 LOW 的會話優先被終止。

2. 鎖超時設定

SET LOCK_TIMEOUT 設定陳述式等待阻塞資源的最長時間（毫秒）。超時後自動取消並回傳錯誤 1222。例如 SET LOCK_TIMEOUT 1800 設為 1.8 秒。可用 @@LOCK_TIMEOUT 查看目前設定。

3. 索引鎖定粒度控制

使用系統預存程序 sp_indexoption 設定特定索引的鎖定粒度（如禁止頁鎖、列鎖）。

4. 交易隔離等級

透過 SET TRANSACTION ISOLATION LEVEL 設定隔離等級（Read Uncommitted, Read Committed, Repeatable Read, Serializable），直接影響鎖的持有時間與相容性。

五、監控與檢視鎖資訊

執行 EXEC SP_LOCK 回報目前鎖狀態。
查詢分析器中按 Ctrl+2 開啟鎖資訊視窗。
動態管理檢視 sys.dm_tran_locks 提供更詳細的即時鎖資訊（適用新版本）。

六、經典案例：看似奇怪的 SQL 技巧

BEGIN TRAN
UPDATE titles SET title_id = title_id WHERE 1 = 2  -- 永不成立的條件
IF (SELECT AVG(price) FROM titles) > 15
BEGIN
    UPDATE titles SET price = price * 1.10 WHERE price < (SELECT AVG(price) FROM titles)
END
COMMIT TRAN

此處 WHERE 1=2 雖不更新任何列，但優化器因找不到有效 SARG（搜尋引數），會強制採用資料表掃描並施加排他資料表鎖 (X Lock)。雖然 WHERE 立即評估為 FALSE 而不執行實際掃描，但該交易已成功取得資料表級排他鎖。此技巧可確保後續邏輯執行期間無其他交易修改資料，實現可重複讀，且避免 HOLDLOCK 可能導致的死鎖風險。代價是增加資料表鎖爭用，需權衡「避免死鎖」與「允許併發存取」的優先序。

七、死鎖防範八大實戰策略

縮短交易邏輯：盡早提交或回滾，減少鎖持有時間。
設定合理鎖超時：建議 3-10 分鐘，超時自動放棄，防止程序掛起。
完善錯誤處理：所有預存程序需包含 @@ERROR 處理邏輯。
避免強行加鎖：一般不修改預設隔離等級，信任引擎優化器。
優化存取順序：所有交易遵循相同資料表存取順序，打破循環等待條件。
減少使用者介入：交易中避免等待使用者輸入，縮短持鎖窗口。
善用髒讀：NOLOCK 或 READ UNCOMMITTED 可避免鎖衝突，適用於可容忍髒資料的報表查詢。
時域與空間離散：錯開批次作業執行時間；將大表拆分為小表或按用戶群分表，降低熱點爭用。
採用較低隔離等級：過高隔離等級（如 Serializable）劇增死鎖機率，依業務需求取捨。
綁定連接：Bound Connections 允許多連接共享交易與鎖，消除內部衝突。
樂觀鎖或預先獨占鎖：版本號/時間戳機制或交易伊始即取得獨占鎖。

八、實戰：如何精準鎖定列、資料表、資料庫

1. 鎖定單一資料列

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM TableName WITH (ROWLOCK) WHERE ID = 1

2. 鎖定整張資料表

SELECT Col1 FROM TableName WITH (TABLOCKX) WHERE 1=1
-- 鎖定期間他人不可操作，COMMIT 或 ROLLBACK 釋放

3. 完整死鎖重現範例

建立測試資料表 table1、table2 並插入樣本資料。透過兩個交易以相反順序更新兩張表並加入 WAITFOR DELAY，即可穩定重現死鎖：交易 A 鎖 table1 等 table2，交易 B 鎖 table2 等 table1，雙方陷入無限等待。

九、SQL Server 表級鎖定提示完整表

提示	說明
HOLDLOCK	持有共享鎖至交易結束，等同 SERIALIZABLE。
NOLOCK / READUNCOMMITTED	不加共享/排他鎖，可能讀到髒資料，僅限 SELECT。
PAGLOCK	指定使用頁鎖（預設可能升級為表鎖）。
READCOMMITTED	以提交讀隔離等級語義執行掃描（預設行為）。
READPAST	跳過已鎖定列，不阻塞等待，僅限 READ COMMITTED 下的 SELECT。
REPEATABLEREAD	設定可重複讀隔離等級。
ROWLOCK	強制使用列鎖，抑制頁/表鎖升級。
SERIALIZABLE	以可序列化語義掃描，等同 HOLDLOCK。
TABLOCK	使用表級共享鎖，語句結束釋放；搭配 HOLDLOCK 持有至交易結束。
TABLOCKX	表級排他鎖，阻擋所有其他讀寫，至語句/交易結束。
UPDLOCK	讀取時加更新鎖而非共享鎖，持有至交易結束，允許並發讀但防止更新衝突。