了解SQL Server中NOLOCK和WITH NOLOCK表提示的影响

Every once in a while, SQL Server database administrators find themselves in disagreements with their application developer counterparts – particularly when it comes to some of the latter’s Transact SQL (T-SQL) developmental practices. One of my first observations when I joined my current employer is that almost all T-SQL scripts written by application developers uses the NOLOCK table hint. However, from the interactions that I have had with these esteemed developers it doesn’t seem like they understand how the NOLOCK table hint works. Furthermore, although they seem to be aware of a distinction between NOLOCK and the WITH NOLOCK table hint, they again do not seem to comprehend how the two differ from one another. In this article, I explore the internal workings of the NOLOCK table hint and examine the implications of omitting the WITH keyword.

SQL Server数据库管理员每隔一段时间就会发现自己与应用程序开发人员的意见分歧，尤其是在后者的Transact SQL（T-SQL）开发实践方面。 当我加入现任雇主时，我的最初观察之一是，几乎所有由应用程序开发人员编写的T-SQL脚本都使用NOLOCK表提示。 但是，从我与这些受人尊敬的开发人员的互动来看，他们似乎并不了解NOLOCK表提示的工作原理。 此外，尽管他们似乎意识到了NOLOCK和WITH NOLOCK表提示之间的区别，但是他们似乎仍然无法理解两者之间的区别。 在本文中，我探讨了NOLOCK表提示的内部工作原理，并研究了省略WITH关键字的含义。

了解NOLOCK提示 (Understanding NOLOCK Hint)

The default behaviour in SQL Server is for every query to acquire its own shared lock prior to reading data from a given table. This behaviour ensures that you are only reading committed data. However, the NOLOCK table hint allows you to instruct the query optimiser to read a given table without obtaining an exclusive or shared lock. The benefits of querying data using the NOLOCK table hint is that it requires less memory and prevents deadlocks from occurring with any other queries that may be reading similar data. The only drawback is that using the NOLOCK table hint may accidentally result into reading uncommitted “dirty” data. In the following sections, I will provide practical examples for using NOLOCK and WITH NOLOCK table hints.

SQL Server中的默认行为是，每个查询在从给定表中读取数据之前先获取其自己的共享锁。 此行为可确保您仅读取已提交的数据。 但是，使用NOLOCK表提示可以指示查询优化程序读取给定的表，而无需获取互斥或共享锁。 使用NOLOCK表提示查询数据的好处在于，它需要较少的内存，并且可以防止可能读取相似数据的任何其他查询发生死锁。 唯一的缺点是使用NOLOCK表提示可能会偶然导致读取未提交的“脏”数据。 在以下各节中，我将提供有关使用NOLOCK和WITH NOLOCK表提示的实际示例。

For the purposes of this discussion, I will use a SQL Server 2016_FIFABallonDOr table that contains the top 3 footballers nominated for FIFA’s Ballon d’Or award in 2016. A preview of this SQL Server table is shown in
Table 1.

为了便于讨论，我将使用一个SQL Server 2016_FIFABallonDOr表，该表包含2016年获得FIFA金球奖提名的前3名足球运动员。此SQL Server表的预览显示在
表1 。

Nominee	Club	Jersey Number	Votes	Date of Birth	Place of Birth	Nationality	Height
Antoine Griezmann	Atletico Madrid	7	198	21 March 1991	Mâcon	France	1.75 m
Lionel Messi	FC Barcelona	10	316	24 June 1987	Rosario	Argentina	1.70 m
Cristiano Ronaldo	Real Madrid	7	745	05 February 1985	Funchal	Portugal	1.85 m

被提名人	俱乐部	球衣号码	投票数	出生日期	出生地	国籍	高度
安托万·格里兹曼	马德里竞技	7	198	1991年3月21日	马Kong	法国	1.75米
莱昂内尔·梅西	巴塞罗那足球俱乐部	10	316	1987年6月24日	罗萨里奥	阿根廷	1.70米
克里斯蒂亚诺·罗纳尔多	皇家马德里	7	745	1985年2月5日	丰沙尔	葡萄牙	1.85米

Now let’s assume that having seen his opponents’ jersey number, Lionel Messi later decides to change his jersey number from 10 to 7. In order to reflect this change, we would need to run an UPDATE statement against our SQL Server table. The sample UPDATE script that we would need to run is shown in Script 1.

现在，假设看到对手的球衣号码，Lionel Messi后来决定将他的球衣号码从10更改为7。为了反映此更改，我们需要对SQL Server表运行UPDATE语句。 脚本1中显示了我们需要运行的示例UPDATE脚本。

BEGIN TRANSACTION updateJerseyNr
	UPDATE A
	SET [Jersey Number] = 7
	FROM [SQLShack].[dbo].[2016_FIFABallonDOr] A
	WHERE [Nominee] = 'Lionel Messi'

You will notice that Script 1 doesn’t have a corresponding COMMIT TRANSACTION updateJerseyNr, which means that the changes we’ve just made haven’t been committed to disk yet. Thus, if another database user were to attempt to read the value of Lionel Messi’s jersey number as shown in Figure 1, they would receive an endless Executing query … message.

您会注意到， 脚本1没有相应的COMMIT TRANSACTION updateJerseyNr ，这意味着我们刚刚进行的更改尚未提交到磁盘。 因此，如果另一个数据库用户试图读取Lionel Messi的球衣号码（如图1所示）的值，他们将收到无尽的Executing query …消息。

Well, what is happening here is that as part of retrieving Lionel Messi’s jersey number, the script in Figure 1 needs to firstly acquire a shared lock against the 2016_FIFABallonDOr table, but it ends up having to wait for Script 1 to complete its changes first.

嗯，这里发生的是作为检索Lionel Messi球衣号码的一部分， 图1中的脚本需要首先获取针对2016_FIFABallonDOr表的共享锁，但最终必须等待脚本1首先完成其更改。

This is where the NOLOCK hint can be useful as it can allow for the retrieval of Lionel Messi’s jersey number without having to wait for any shared locks as shown in Script 2 and Figure 3, respectively.

在这里NOLOCK提示非常有用，因为它可以检索Lionel Messi的球衣号码，而不必等待分别如脚本2和图3所示的任何共享锁。

 
SELECT [Jersey Number]      
  FROM [SQLShack].[dbo].[2016_FIFABallonDOr] (NOLOCK)
  WHERE [Nominee] = 'Lionel Messi'

However, the dangers of using the NOLOCK hint is that if the transaction in Script 1 were to be rolled back, then the value of Lionel Messi’s jersey number would return back to 10, meaning if you were to rerun Script 2 you will get a different value then what you got earlier.

但是，使用NOLOCK提示的危险在于，如果要回滚脚本1中的事务，那么Lionel Messi的球衣号码的值将返回到10，这意味着如果您重新运行脚本2，您将得到一个不同的结果。值那么您早先得到的。

使用WITH关键字的好处 (Benefits of Using the WITH Keyword)

In terms of querying Lionel Messi’s jersey number by adding the WITH keyword in front of the NOLOCK hint, you would still retrieve similar results as shown in Script 3 and Figure 4, respectively. In fact, even the execution plan and TIME/IO statistics of Script 3 are still similar to that of Script 1.

通过在NOLOCK提示前面添加WITH关键字来查询Lionel Messi的球衣号码，您仍将分别检索类似于脚本3和图4所示的结果。 实际上，甚至脚本3的执行计划和TIME / IO统计信息仍然与脚本1相似。

SELECT [Jersey Number]      
  FROM [SQLShack].[dbo].[2016_FIFABallonDOr] WITH (NOLOCK)
  WHERE [Nominee] = 'Lionel Messi'

If the output of scripts using NOLOCK and WITH NOLOCK table hints is so identical, why then should we worry about using the one over the other? Well, it turns out that there are actually several differences between the two:

如果使用NOLOCK和WITH NOLOCK表提示的脚本输出是如此相同，那为什么我们还要担心使用一个而不是另一个呢？ 好吧，事实证明两者之间确实存在一些差异：

1.对不带WITH关键字的提示的支持将很快被弃用 (1. Support for hints without the WITH keyword will soon be deprecated)

As per Microsoft documentation shown in Figure 5, by continuing to exclude the WITH keyword in a table hint means that you are basically increasing your technical debt as you will have to go back and refactor your scripts once this functionality is removed in future versions of SQL Server.

根据图5所示的Microsoft文档，继续在表提示中排除WITH关键字意味着您基本上增加了技术负担，因为一旦在将来SQL版本中删除了此功能，就必须返回并重构脚本。服务器。

2.使用WITH关键字指定多个表提示 (2. Specify Multiple Table Hints using the WITH keyword)

Other than the fact that the makers of SQL Server have basically instructed us to the WITH keyword when specifying table hints, another benefit of using the WITH keyword is that you can include multiple table hints against the same table as shown in Script 4.

比的事实，指定表提示时，SQL服务器的制造商已基本指示我们WITH关键字等，使用的另一个好处WITH关键字是可以包括对同一个表的多个表提示，如图4脚本 。

 
SELECT 
[Jersey Number]      
  FROM [SQLShack].[dbo].[2016_FIFABallonDOr] WITH (TABLOCK, HOLDLOCK)
  WHERE [Nominee] = 'Cristiano Ronaldo'

If you specify multiple hints after having omitted the WITH keyword will simply result into the error shown in Script 5 and Figure 6, respectively.

如果在省略WITH关键字后指定多个提示，将分别导致分别在脚本5和图6中所示的错误。

SELECT 	
[Jersey Number]      
  FROM [SQLShack].[dbo].[2016_FIFABallonDOr] (TABLOCK, HOLDLOCK)
  WHERE [Nominee] = 'Cristiano Ronaldo'

3.没有WITH关键字，您仅拥有一个表别名 (3. Without the WITH Keyword you simply have a table Alias)

At this point you should already be leaning towards always specifying the WITH keyword when using table hints but just to convince you further, omitting the WITH keyword can have undesirable consequences. If you forget to include NOLOCK inside round brackets then the NOLOCK hint can be mistaken for a table alias by the query optimiser, which means that the query would then have to wait for shared lock before it can start reading a given table. For instance, I forgot to include NOLOCK inside opening and closing round brackets and this led to the query executing endlessly as it waited for a shared lock.

此时，您应该已经倾向于在使用表提示时始终指定WITH关键字，但只是为了进一步说服您，省略WITH关键字可能会带来不良后果。 如果您忘记在圆括号中包括NOLOCK ，则查询优化器可能会将NOLOCK提示误认为是表别名，这意味着查询随后必须等待共享锁，才可以开始读取给定表。 例如，我忘了在开括号和闭括号中包括NOLOCK ，这导致查询在等待共享锁时无休止地执行。

结论 (Conclusion)

There are benefits and drawbacks to specifying NOLOCK table hint as a result they should not just be included in every T-SQL script without a clear understanding of what they do. Nevertheless, should a decision be made to use NOLOCK table hint, it is recommended that you include the WITH keyword.

指定NOLOCK表提示有好处和坏处 ，因此，如果不明确了解它们的作用，则不应仅将它们包含在每个T-SQL脚本中。 但是，如果决定使用NOLOCK表提示，建议您包含WITH关键字。

翻译自: https://www.sqlshack.com/understanding-the-impact-of-nolock-and-with-nolock-table-hints-in-sql-server/