SQL Server 中的 WITH (NOLOCK) 示例详解

SQL Server 中 WITH (NOLOCK) 详解

1. 基本概念

什么是 NOLOCK

WITH (NOLOCK) 是 SQL Server 中的表提示（Table Hint），用于指定查询在执行时不申请共享锁，从而避免因锁竞争导致的阻塞。

-- 基本语法
SELECT * FROM 表名 WITH (NOLOCK) WHERE 条件

2. 使用示例

基本查询示例

-- 使用 NOLOCK 查询
SELECT * FROM Orders WITH (NOLOCK) 
WHERE OrderDate > '2023-01-01'

-- 多表连接
SELECT o.OrderID, c.CustomerName
FROM Orders o WITH (NOLOCK)
INNER JOIN Customers c WITH (NOLOCK) 
    ON o.CustomerID = c.CustomerID

-- 子查询中使用
SELECT * FROM Orders
WHERE CustomerID IN (
    SELECT CustomerID FROM Customers WITH (NOLOCK)
    WHERE Country = 'USA'
)

在不同场景下的使用

-- 1. 只读报表查询
SELECT 
    YEAR(OrderDate) AS Year,
    COUNT(*) AS OrderCount,
    SUM(TotalAmount) AS TotalSales
FROM Orders WITH (NOLOCK)
GROUP BY YEAR(OrderDate)

-- 2. 数据分析（允许脏读）
SELECT * FROM LogTable WITH (NOLOCK)
WHERE LogTime BETWEEN @StartTime AND @EndTime

-- 3. 历史数据查询
SELECT * FROM ArchiveData WITH (NOLOCK)
WHERE Status = 'Completed'

3. NOLOCK 的工作原理

隔离级别影响

-- 设置会话隔离级别（NOLOCK 相当于 READ UNCOMMITTED）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Orders

-- 上面的查询等价于
SELECT * FROM Orders WITH (NOLOCK)

与锁的关系表

特性	有 NOLOCK	无 NOLOCK
共享锁	不申请	申请
排他锁	不阻塞	可能阻塞
脏读	允许	不允许
幻读	允许	可避免

4. 实际应用场景

适合使用 NOLOCK 的场景

-- 场景1：实时监控仪表板
CREATE PROCEDURE GetDashboardStats
AS
BEGIN
    -- 允许脏读，获取近似实时数据
    SELECT 
        (SELECT COUNT(*) FROM Orders WITH (NOLOCK) WHERE Status = 'Pending') AS PendingOrders,
        (SELECT COUNT(*) FROM Users WITH (NOLOCK) WHERE LastLogin > DATEADD(HOUR, -24, GETDATE())) AS ActiveUsers,
        (SELECT SUM(Amount) FROM Transactions WITH (NOLOCK) WHERE TransactionDate > CONVERT(DATE, GETDATE())) AS TodayRevenue
END

-- 场景2：大数据量历史分析
SELECT 
    CustomerID,
    COUNT(*) AS TotalOrders,
    AVG(TotalAmount) AS AvgOrderValue
FROM OrderHistory WITH (NOLOCK)  -- 历史表，数据稳定
WHERE OrderDate BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY CustomerID

应避免使用 NOLOCK 的场景

-- 金融交易（需要数据准确性）
BEGIN TRANSACTION
    -- 不要使用 NOLOCK！
    SELECT Balance FROM Accounts WHERE AccountID = @AccountID

    -- 基于查询结果进行更新
    UPDATE Accounts SET Balance = Balance - @Amount 
    WHERE AccountID = @AccountID
COMMIT TRANSACTION

-- 库存管理
-- 错误的做法（可能导致超卖）：
DECLARE @CurrentStock INT
SELECT @CurrentStock = StockQuantity FROM Products WITH (NOLOCK) WHERE ProductID = 1

-- 正确的做法：
SELECT @CurrentStock = StockQuantity FROM Products WHERE ProductID = 1

5. 性能对比示例

性能测试对比

-- 测试准备：创建测试表
CREATE TABLE TestTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Data VARCHAR(100),
    CreateDate DATETIME DEFAULT GETDATE()
)

-- 插入测试数据
INSERT INTO TestTable (Data)
SELECT TOP 1000000 'TestData' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(10))
FROM sys.objects a, sys.objects b, sys.objects c

-- 测试查询（在有大量写入时对比）
-- 查询1：使用 NOLOCK
DECLARE @StartTime1 DATETIME = GETDATE()
SELECT COUNT(*) FROM TestTable WITH (NOLOCK)
WHERE CreateDate > DATEADD(DAY, -7, GETDATE())
DECLARE @EndTime1 DATETIME = GETDATE()

-- 查询2：不使用 NOLOCK（模拟并发写入阻塞）
DECLARE @StartTime2 DATETIME = GETDATE()
SELECT COUNT(*) FROM TestTable
WHERE CreateDate > DATEADD(DAY, -7, GETDATE())
DECLARE @EndTime2 DATETIME = GETDATE()

SELECT 
    DATEDIFF(MS, @StartTime1, @EndTime1) AS TimeWithNoLock,
    DATEDIFF(MS, @StartTime2, @EndTime2) AS TimeWithoutNoLock

6. 替代方案和最佳实践

1. 使用 READ UNCOMMITTED 隔离级别

-- 会话级别设置
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 所有查询都相当于使用了 NOLOCK
SELECT * FROM Orders
SELECT * FROM Customers
-- 恢复默认隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

2. 使用快照隔离（SQL Server 2005+）

-- 启用数据库快照隔离
ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

-- 使用快照隔离
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
    SELECT * FROM Orders  -- 读取事务开始时的数据版本
    -- 其他操作
COMMIT TRANSACTION

3. 使用行版本控制

-- 启用读提交快照
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON

-- 此时 READ COMMITTED 隔离级别使用行版本控制
-- 避免了锁竞争，同时保证读取已提交的数据

7. 综合示例：实际应用

-- 示例：报表系统存储过程
CREATE PROCEDURE GenerateSalesReport
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 对于报表查询，可以使用 NOLOCK 提高性能
    -- 但要注意数据可能不是最新的

    SELECT 
        c.CustomerName,
        COUNT(DISTINCT o.OrderID) AS OrderCount,
        SUM(od.Quantity * od.UnitPrice) AS TotalAmount,
        MAX(o.OrderDate) AS LastOrderDate
    FROM Customers c WITH (NOLOCK)
    INNER JOIN Orders o WITH (NOLOCK) 
        ON c.CustomerID = o.CustomerID
    INNER JOIN OrderDetails od WITH (NOLOCK) 
        ON o.OrderID = od.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
        AND o.Status IN ('Completed', 'Shipped')
    GROUP BY c.CustomerID, c.CustomerName
    ORDER BY TotalAmount DESC

    -- 关键指标汇总（使用 NOLOCK 快速获取）
    SELECT 
        COUNT(*) AS TotalOrders,
        SUM(TotalAmount) AS GrandTotal,
        AVG(TotalAmount) AS AverageOrderValue
    FROM Orders WITH (NOLOCK)
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
        AND Status IN ('Completed', 'Shipped')
END

8. 注意事项和警告

重要提醒

-- 1. NOLOCK 可能导致脏读
-- 假设以下情况：
-- 事务A开始更新
BEGIN TRANSACTION
UPDATE Orders SET TotalAmount = 1000 WHERE OrderID = 1
-- 此时事务A未提交

-- 事务B使用 NOLOCK 查询（可能读取到未提交的 1000）
SELECT TotalAmount FROM Orders WITH (NOLOCK) WHERE OrderID = 1

-- 2. NOLOCK 可能导致数据不一致
-- 在分页查询中可能出现重复或丢失行
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNum, *
    FROM Orders WITH (NOLOCK)
) AS Temp
WHERE RowNum BETWEEN 1 AND 10
-- 由于脏读，相同数据可能出现在不同页面

-- 3. 不建议在 UPDATE/DELETE 语句中使用
-- 这是错误的！
UPDATE Products WITH (NOLOCK) 
SET Stock = Stock - 1 
WHERE ProductID = 1
-- NOLOCK 在这里会被忽略，UPDATE 仍然需要锁

总结建议

使用场景：

只读报表和分析查询
对实时性要求不高的大数据量查询
监控和日志查看

避免场景：

财务、交易系统
需要精确计数的场景
有数据依赖的业务逻辑

最佳实践：

-- 明确注释使用 NOLOCK 的原因
SELECT * FROM LogTable WITH (NOLOCK) -- 仅用于监控，允许脏读
WHERE LogTime > DATEADD(HOUR, -1, GETDATE())

-- 考虑使用快照隔离作为替代
-- 或者在非高峰时段执行重要报表

性能监控：