sql server中的with 锁各种区别

📘 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，它是共享锁，会阻止写，但无法阻止其他事务读取并加更新锁，存在写写冲突风险。

两者结合，读的时候加更新锁，且保持锁直到事务结束，保证了安全性。