使用 NOLOCK 提示时应考虑什么

• 下载 nolock_script.zip - 811 B

引言

对于 SQL Server，NOLOCK 提示非常诱人，尤其是在至少有一个表经常更新的 SELECT 语句中。 使用 NOLOCK 可以消除所有排队问题，并且 SELECT 语句可以顺利运行，但并非没有副作用。

为什么我们需要提示

第一个问题可能是为什么我们需要添加此提示才能使语句在没有额外等待的情况下运行。 答案在于基本的数据库管理系统原则：ACID（原子性、一致性、隔离性和持久性）。 隔离性 属性定义了其他事务可能看不到不完整的修改。 由于 SQL Server（当使用默认锁定方案时）使用排他锁来防止获取读（共享）锁，因此所有读取操作必须等待直到排他锁被释放。 这会导致 SELECT 语句在要获取的数据被独占锁定时等待。

使用 NOLOCK 提示（与 READUNCOMMITTED 完全相同）实际上绕过了对排他锁的检查，并且根本不设置共享锁。 因此，它的另一个含义是查询看到尚未提交到数据库的数据，这可能会导致脏读。 这意味着修改没有被隔离，因此忽略了主要的数据库原则之一。 这就是默认情况下不使用类似 NOLOCK 的行为的原因。

它实际意味着什么

让我们举个例子。 首先，让我们创建必要的对象。

----------------------------------
-- Create test objects
----------------------------------
-- Schema
CREATE SCHEMA LockTest;
GO

-- OrderEntry -table
IF OBJECT_ID ( 'LockTest.OrderEntry', 'U' ) IS NOT NULL 
DROP TABLE LockTest.OrderEntry;
GO

CREATE TABLE LockTest.OrderEntry (
Id        int     not null identity(1,1) primary key,
Amount    int     not null,
UnitPrice decimal not null,
Area      int     not null
);
GO

-- AddOrders -procedure
IF OBJECT_ID ( 'LockTest.AddOrders', 'P' ) IS NOT NULL 
DROP PROCEDURE LockTest.AddOrders;
GO

CREATE PROCEDURE LockTest.AddOrders @OrderCount int AS
BEGIN
   DECLARE @counter int = 0;

   WHILE (@counter < @OrderCount) BEGIN
      INSERT INTO LockTest.OrderEntry
         (Amount, UnitPrice, Area) 
      VALUES 
         (ROUND(RAND()*100, 0), ROUND(RAND()*100, 2), ROUND(RAND()*10, 0));

      SET @counter = @counter + 1;
   END;
END;
GO

现在我们有了一个表和一个过程来创建一些随机数据。 让我们向表中添加一些内容。

-- Add some data to the table
BEGIN TRANSACTION;
EXEC LockTest.AddOrders @OrderCount=100;
COMMIT;

现在，如果我们获得每个区域的销售额，结果如下所示。 请注意，由于随机值，您的结果会有所不同。

-- Get the sales grouped by area
SELECT TOP(3)
Area, SUM(Amount * UnitPrice) AS TotalSales
FROM LockTest.OrderEntry
GROUP BY Area
ORDER BY Area;

结果

Area  TotalSales
----  ----------
0     10454
1     22608
2     22038

同步事务，正常行为

现在，如果数据库同时更新和查询，正常情况下会发生什么？ DML 操作会获取必要的锁，并可能阻止查询完成。 这可以通过以下方法进行测试。 该测试要求您同时与数据库建立两个连接。

在会话 1 中，在事务中添加新行，保持事务打开。

-- Session 1: Add data to the table and leave the transaction open
BEGIN TRANSACTION
EXEC LockTest.AddOrders @OrderCount=10000;

并使用会话 2，再次查询前 3 个区域。 此查询不会完成，而是保持运行并等待锁被释放。

-- Session 2: Get the sales grouped by area
-- won't return until transaction in session 1 is ended
SELECT TOP(3)
Area, SUM(Amount * UnitPrice) AS TotalSales
FROM LockTest.OrderEntry
GROUP BY Area
ORDER BY Area;

现在，当会话 1 回滚时，事务锁被释放，会话 2 中的查询继续。

-- Session 1: roll back the modifications
ROLLBACK;

结果会立即显示在会话 2 中。

Area  TotalSales
----  ----------
0     10454
1     22608
2     22038

正如您所看到的，由于所有修改都被回滚，因此结果与之前相同。

使用 NOLOCK 时的结果

现在我们使用与之前完全相同的场景，但这次使用 NOLOCK 提示。

在会话 1 中，再次在事务中添加新行，并保持事务打开。

-- Session 1: Add data to the table and leave the transaction open
BEGIN TRANSACTION
EXEC LockTest.AddOrders @OrderCount=10000;

并使用会话 2，再次查询前 3 个区域，但这次使用 NOLOCK 提示。

 -- Session 2: Get the sales grouped by area WITH NOLOCK
SELECT TOP(3)
Area, SUM(Amount * UnitPrice) AS TotalSales
FROM LockTest.OrderEntry WITH (NOLOCK)
GROUP BY Area
ORDER BY Area;

结果会立即显示（同样，由于随机值，您的结果可能会有所不同）。

Area    TotalSales
----    ----------
0       1321810
1       2417946
2       2539965

现在会话 1 回滚事务。

-- Session 1: roll back the modifications
ROLLBACK;

并在会话 2 中执行完全相同的查询。

 -- Session 2: Get the sales grouped by area WITH NOLOCK
SELECT TOP(3)
Area, SUM(Amount * UnitPrice) AS TotalSales
FROM LockTest.OrderEntry WITH (NOLOCK)
GROUP BY Area
ORDER BY Area;

结果是原始的，因为会话 1 回滚了整个过程。

Area  TotalSales
----  ----------
0     10454
1     22608
2     22038

结论

我们看到的是脏读。 在一段时间内，我们看到了数据库中的数据，这些数据（在隔离的意义上）不是持久的。 那么这不好吗？ 好吧，这取决于业务逻辑。 想象一下，您正在创建一个银行应用程序，我敢打赌，脏读是您真正想要避免的。 另一方面，如果您通常没有回滚，并且如果有回滚，并且您可能看到的错误数据没有负面影响，那么您可以考虑使用 NOLOCK。

历史

• 2011年2月11日: 创建。