📘 SQL Server 常用 WITH (Hint) 用法與組合場景對照表

? 常見 Hint 組合推薦(按場景)

?? 使用注意事項

• 多個 Hint 用英文逗號隔開，如：

  SELECT * FROM MyTable WITH (UPDLOCK, HOLDLOCK, ROWLOCK)

先查后改 — 避免“幻讀”和臟寫

BEGIN TRAN;SELECT Quantity
FROM Inventory WITH (UPDLOCK, HOLDLOCK)
WHERE ProductID = 1001;-- 假設庫存充足，執行扣減
UPDATE Inventory
SET Quantity = Quantity - 1
WHERE ProductID = 1001;COMMIT TRAN;

批量刪除數據時鎖表保護

BEGIN TRAN;DELETE FROM Orders WITH (TABLOCKX)
WHERE OrderDate < '2020-01-01';COMMIT TRAN;

強制使用索引加快查詢

SELECT OrderID, OrderDate
FROM Orders WITH (INDEX(IX_Orders_OrderDate))
WHERE OrderDate > '2025-01-01';

控制鎖粒度，避免鎖升級

UPDATE Products WITH (ROWLOCK, UPDLOCK)
SET Stock = Stock - 10
WHERE ProductID = 1234;

查詢某條記錄時保持鎖直到事務結束，防止臟寫

BEGIN TRAN;SELECT * FROM Accounts WITH (HOLDLOCK, ROWLOCK)
WHERE AccountID = 5678;-- 處理業務邏輯UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 5678;COMMIT TRAN;

復雜聯表查詢時，指定索引和讀未提交

SELECT o.OrderID, c.CustomerName
FROM Orders o WITH (NOLOCK, INDEX(IX_Orders_CustomerID))
JOIN Customers c WITH (NOLOCK)
ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > '2025-01-01';

防止死鎖，鎖定行用于更新

BEGIN TRAN;SELECT * FROM Inventory WITH (UPDLOCK, ROWLOCK)
WHERE ProductID = 789;-- 執行更新
UPDATE Inventory
SET Quantity = Quantity - 5
WHERE ProductID = 789;COMMIT TRAN;

為什么用多個鎖提示？比如 UPDLOCK + HOLDLOCK

UPDLOCK的作用
只在讀取這條數據時加更新鎖(一種介于共享鎖和排他鎖的鎖)。

更新鎖允許其他事務讀取，但阻止其他事務獲得寫鎖。

這防止了兩個事務同時讀取后，都去更新數據造成沖突。

HOLDLOCK的作用
默認情況下，鎖在語句結束時釋放(例如，SELECT 語句讀完馬上釋放共享鎖或更新鎖)。

加了 HOLDLOCK，會將鎖保持到整個事務結束，防止其他事務在當前事務未提交前修改數據。

這樣可以避免“不可重復讀”和“幻讀”問題。

BEGIN TRAN;SELECT Quantity
FROM Inventory WITH (UPDLOCK, HOLDLOCK)
WHERE ProductID = 1001;-- 這里做業務判斷，比如庫存是否足夠
IF @quantity > 0
BEGINUPDATE InventorySET Quantity = Quantity - 1WHERE ProductID = 1001;
ENDCOMMIT TRAN;

如果只有 UPDLOCK，SELECT 語句結束后，鎖就釋放了，后續操作可能會被其他事務修改。

如果只有 HOLDLOCK，它是共享鎖，會阻止寫，但無法阻止其他事務讀取并加更新鎖，存在寫寫沖突風險。

兩者結合，讀的時候加更新鎖，且保持鎖直到事務結束，保證了安全性。