Can someone explain the implications of using with (nolock) on queries, when you should/shouldn't use it?
有人能解释一下在查询中使用with (nolock)的含义吗?
For example, if you have a banking application with high transaction rates and a lot of data in certain tables, in what types of queries would nolock be okay? Are there cases when you should always use it/never use it?
例如，如果您有一个具有高事务率和特定表中大量数据的银行应用程序，那么nolock是什么类型的查询呢?在某些情况下，你应该永远使用它/永远不使用它吗?
16 个解决方案
#1
380
WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it IMHO.
使用(NOLOCK)等同于将READ uncommgiven作为事务隔离级别。因此，您将面临读取随后回滚的未提交行的风险，即从未将其放入数据库的数据。因此，尽管它可以防止读取被其他操作阻塞，但它也存在风险。在高交易率的银行应用程序中，它可能不是您试图用它解决的任何问题的正确解决方案。
#2
150
The question is what is worse:
问题是什么更糟:
- a deadlock, or
- 死锁,或者
- a wrong value?
- 一个错误的值?
For financial databases, deadlocks are far worse than wrong values. I know that sounds backwards, but hear me out. The traditional example of DB transactions is you update two rows, subtracting from one and adding to another. That is wrong.
对于金融数据库来说，死锁远比错误的值更糟糕。我知道这听起来有点倒，但听我说。DB事务的传统示例是更新两行，从一行减去一行，然后添加到另一行。这是错误的。
In a financial database you use business transactions. That means adding one row to each account. It is of utmost importance that these transactions complete and the rows are successfully written.
在金融数据库中，您使用业务事务。这意味着在每个帐户中增加一行。最重要的是，这些事务完成并成功地写入了行。
Getting the account balance temporarily wrong isn't a big deal, that is what the end of day reconciliation is for. And an overdraft from an account is far more likely to occur because two ATMs are being used at once than because of a uncommitted read from a database.
暂时把账户余额搞错并不是什么大问题，这就是今天的和解的目的。而且，一个帐户透支的可能性要大得多，因为两个自动取款机同时被使用，而不是因为从数据库中读取数据。
That said, SQL Server 2005 fixed most of the bugs that made NOLOCK necessary. So unless you are using SQL Server 2000 or earlier, you shouldn't need it.
也就是说，SQL Server 2005修复了大多数导致NOLOCK的缺陷。因此，除非您正在使用SQL Server 2000或更早的版本，否则您不应该使用它。
Further Reading
Row-Level Versioning
进一步阅读行级版本控制
#3
48
The text book example for legitimate usage of the nolock hint is report sampling against a high update OLTP database.
nolock提示的合法用法的教科书示例是针对一个高更新OLTP数据库的报告抽样。
To take a topical example. If a large US high street bank wanted to run an hourly report looking for the first signs of a city level run on the bank, a nolock query could scan transaction tables summing cash deposits and cash withdrawals per city. For such a report the tiny percentage of error caused by rolled back update transactions would not reduce the value of the report.
举一个时事例子。如果一家大型美国商业街银行想要每小时发布一份报告，寻找该银行首次出现市级挤兑的迹象，那么nolock查询可以对包含每个城市的现金存款和现金提取的交易表进行扫描。对于这样的报告，由回滚更新事务导致的微小错误百分比不会降低报告的价值。
#4
47
Unfortunately it's not just about reading uncommitted data. In the background you may end up reading pages twice (in the case of a page split), or you may miss the pages altogether. So your results may be grossly skewed.
不幸的是，这不仅仅是读取未提交的数据。在后台，你可能会读两页(在页面分割的情况下)，或者你可能会完全错过页面。所以你的结果可能有很大的偏差。
Check out Itzik Ben-Gan's article. Here's an excerpt:
看看伊茨克·本根的文章。这里的一个摘录:
" With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don't expect consistency, so there are no guarantees. Bear in mind though that "inconsistent data" does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice. "
使用NOLOCK提示(或将会话的隔离级别设置为读取未提交的会话)，您可以告诉SQL Server，您不希望得到一致性，因此没有保证。请记住，“不一致的数据”不仅意味着您可能会看到后来回滚的未提交的更改，或者在事务的中间状态中看到数据更改。它还意味着，在一个简单的查询中，扫描所有表/索引数据SQL服务器可能会丢失扫描位置，或者您可能会得到两次相同的行。”
#5
30
Not sure why you are not wrapping financial transactions in database transactions (as when you transfer funds from one account to another - you don't commit one side of the transaction at-a-time - this is why explicit transactions exist). Even if your code is braindead to business transactions as it sounds like it is, all transactional databases have the potential to do implicit rollbacks in the event of errors or failure. I think this discussion is way over your head.
不知道为什么不在数据库事务中包装金融事务(例如，当您将资金从一个帐户转移到另一个帐户时—您不会每次提交事务的一端—这就是为什么存在显式事务)。即使您的代码听起来像业务事务，但所有事务数据库都有可能在发生错误或失败时进行隐式回滚。我认为这次讨论超出了你的想象。
If you are having locking problems, implement versioning and clean up your code.
如果您遇到锁定问题，请实现版本控制并清理代码。
No lock not only returns wrong values it returns phantom records and duplicates.
没有锁不仅返回错误的值，它还返回幻像记录和副本。
It is a common misconception that it always makes queries run faster. If there are no write locks on a table, it does not make any difference. If there are locks on the table, it may make the query faster, but there is a reason locks were invented in the first place.
一个常见的误解是，它总是使查询运行得更快。如果表上没有写锁，也没有什么区别。如果表上有锁，它可能会使查询速度更快，但首先发明锁是有原因的。
In fairness, here are two special scenarios where a nolock hint may provide utility
公平地说，这里有两个特殊的场景，其中nolock提示可以提供实用价值
1) Pre-2005 sql server database that needs to run long query against live OLTP database this may be the only way
1) 2005年以前需要对实时OLTP数据库运行长查询的sql server数据库这可能是唯一的方法
2) Poorly written application that locks records and returns control to the UI and readers are indefinitely blocked. Nolock can be helpful here if application cannot be fixed (third party etc) and database is either pre-2005 or versioning cannot be turned on.
2)编写糟糕的应用程序，该应用程序锁定记录并将控制权返回给UI和读取器，并被无限期地阻塞。如果应用程序不能被修复(第三方等)，并且数据库在2005年之前或者版本控制不能被打开，Nolock将会很有帮助。
#6
23
NOLOCK is equivalent to READ UNCOMMITTED, however Microsoft says you should not use it for UPDATE or DELETE statements:
NOLOCK相当于未提交的文本，但微软表示，你不应该将其用于更新或删除语句:
For UPDATE or DELETE statements: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
对于更新或删除语句:该特性将在Microsoft SQL Server的未来版本中删除。避免在新的开发工作中使用该特性，并计划修改当前使用该特性的应用程序。
http://msdn.microsoft.com/en-us/library/ms187373.aspx
http://msdn.microsoft.com/en-us/library/ms187373.aspx
This article applies to SQL Server 2005, so the support for NOLOCK exists if you are using that version. In order to future-proof you code (assuming you've decided to use dirty reads) you could use this in your stored procedures:
本文适用于SQL Server 2005，因此，如果您正在使用该版本，则对NOLOCK的支持是存在的。为了防止将来发生，您可以在存储过程中使用这个代码(假设您已经决定使用脏读):
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
设置未提交的事务隔离级别读取
#7
15
Another case where it's usually okay is in a reporting database, where data is perhaps already aged and writes just don't happen. In this case, though, the option should be set at the database or table level by the administrator by changing the default isolation level.
另一个通常没问题的情况是在报表数据库中，数据可能已经老化，写入不会发生。但是，在这种情况下，管理员应该通过更改默认隔离级别在数据库或表级别设置选项。
In the general case: you can use it when you are very sure that it's okay to read old data. The important thing to remember is that its very easy to get that wrong. For example, even if it's okay at the time you write the query, are you sure something won't change in the database in the future to make these updates more important?
在一般情况下:当您非常确定可以读取旧数据时，可以使用它。重要的是要记住，这很容易出错。例如，即使在编写查询时没有问题，您确定将来数据库中不会发生什么变化，从而使这些更新变得更重要吗?
I'll also 2nd the notion that it's probably not a good idea in banking app. Or inventory app. Or anywhere you're thinking about transactions.
我还想说，这在银行应用或库存应用中可能不是个好主意。
#8
15
You can use it when you're only reading data, and you don't really care about whether or not you might be getting back data that is not committed yet.
当你只读取数据时，你可以使用它，你并不关心你是否得到了尚未提交的数据。
It can be faster on a read operation, but I cannot really say by how much.
它在读取操作上可以更快，但我不能确切地说有多快。
In general, I recommend against using it - reading uncommitted data can be a bit confusing at best.
一般来说，我建议不要使用它——读取未提交的数据最多也会有点混乱。
#9
13
Simple answer - whenever your SQL is not altering data, and you have a query that might interfere with other activity (via locking).
简单的回答——只要SQL没有修改数据，并且有可能干扰其他活动的查询(通过锁定)。
It's worth considering for any queries used for reports, especially if the query takes more than, say, 1 second.
对于任何用于报表的查询都值得考虑，特别是如果查询花费的时间超过(例如)1秒。
It's especially useful if you have OLAP-type reports you're running against an OLTP database.
如果您有针对OLTP数据库运行的olap类型报告，那么它就特别有用。
The first question to ask, though, is "why am I worrying about this?" ln my experience, fudging the default locking behavior often takes place when someone is in "try anything" mode and this is one case where unexpected consequences are not unlikely. Too often it's a case of premature optimization and can too easily get left embedded in an application "just in case." It's important to understand why you're doing it, what problem it solves, and whether you actually have the problem.
第一个要问的问题是，“我为什么要担心这个?”通常情况下，这是一种过早的优化，很容易被嵌入到应用程序中，“以防万一”。重要的是要理解你为什么要这么做，它解决了什么问题，以及你是否真的有这个问题。
#10
9
My 2 cents - it makes sense to use WITH (NOLOCK) when you need to generate reports. At this point, the data wouldn't change much & you wouldn't want to lock those records.
我的2分—当您需要生成报告时，可以使用(NOLOCK)。此时，数据不会有太大的变化&您不会希望锁定这些记录。
#11
8
If you are handling finance transactions then you will never want to use nolock. nolock is best used to select from large tables that have lots updates and you don't care if the record you get could possibly be out of date.
如果你在处理金融交易，那么你永远不会想要使用nolock。nolock最适合用来从有大量更新的大表中进行选择，如果你得到的记录可能是过时的，你也不介意。
For financial records (and almost all other records in most applications) nolock would wreak havoc as you could potentially read data back from a record that was being written to and not get the correct data.
对于财务记录(以及大多数应用程序中的几乎所有其他记录)，nolock将会造成巨大的破坏，因为您可能会从正在写入的记录中读取数据，而不会得到正确的数据。
#12
7
I've used to retrieve a "next batch" for things to do. It doesn't matter in this case which exact item, and I have a lot of users running this same query.
我曾经为要做的事情检索“下一批”。在这种情况下，具体是哪个项目并不重要，我有很多用户在运行相同的查询。
#13
6
Use nolock when you are okay with the "dirty" data. Which means nolock can also read data which is in the process of being modified and/or uncommitted data.
当你对“脏”数据没有意见时，使用nolock。这意味着nolock还可以读取正在修改和/或未提交数据的数据。
It's generally not a good idea to use it in high transaction environment and that is why it is not a default option on query.
在高事务环境中使用它通常不是一个好主意，这就是为什么它不是查询的默认选项。
#14
6
I use with (nolock) hint particularly in SQLServer 2000 databases with high activity. I am not certain that it is needed in SQL Server 2005 however. I recently added that hint in a SQL Server 2000 at the request of the client's DBA, because he was noticing a lot of SPID record locks.
我使用(nolock)提示，特别是在具有高活动的SQLServer 2000数据库中。但是我不确定SQL Server 2005是否需要它。我最近应客户的DBA的请求在SQL Server 2000中添加了这个提示，因为他注意到很多SPID记录锁。
All I can say is that using the hint has NOT hurt us and appears to have made the locking problem solve itself. The DBA at that particular client basically insisted that we use the hint.
我所能说的是，使用这个提示并没有伤害到我们，并且似乎已经使锁定问题自己解决了。在那个特定客户端的DBA基本上坚持我们使用这个提示。
By the way, the databases I deal with are back-ends to enterprise medical claims systems, so we are talking about millions of records and 20+ tables in many joins. I typically add a WITH (nolock) hint for each table in the join (unless it is a derived table, in which case you can't use that particular hint)
顺便说一下，我处理的数据库是企业医疗索赔系统的后端，因此我们在许多连接中讨论了数百万条记录和20多个表。我通常为连接中的每个表添加一个WITH (nolock)提示(除非它是一个派生表，在这种情况下，您不能使用这个特殊提示)
#15
4
Short answer:
简短的回答:
Never use WITH (NOLOCK).
从来没有使用(NOLOCK)。
Long answer:
长一点的回答:
NOLOCK is often exploited as a magic way to speed up database reads, but I try to avoid using it wherever possible.
NOLOCK通常被用作加速数据库读取的神奇方法，但我尽量避免使用它。
The result set can contain rows that have not yet been committed, that are often later rolled back.
结果集可以包含尚未提交的行，这些行通常会在以后回滚。
An error or Result-set can be empty, be missing rows or display the same row multiple times.
错误或结果集可以是空的、丢失的行或多次显示同一行。
This is because other transactions are moving data at the same time you're reading it.
这是因为其他事务正在读取数据的同时移动数据。
READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.
READ COMMITTED增加了一个额外的问题，即数据在单个列中被破坏，多个用户同时更改同一个单元。
There are other side-effects too, which result in sacrificing the speed increase you were hoping to gain in the first place.
还有其他的副作用，这将牺牲你最初希望获得的速度提升。
It can be argued that it is fine to use it in places where you can get away with it, but what's the point? I can't think of any situation where corrupted data is acceptable.
可以争辩说，在你可以侥幸逃脱的地方使用它是可以的，但有什么意义呢?我想不出有什么情况可以接受损坏的数据。
Never ever use NOLOCK ever.
永远不要使用诺洛克。
(ever)
(永远)
#16
3
The simplest answer is a simple question - do you need your results to be repeatable? If yes then NOLOCKS is not appropriate under any circumstances
最简单的答案是一个简单的问题——你需要你的结果是可重复的吗?如果是，那么在任何情况下停止是不合适的
If you don't need repeatability then nolocks may be useful, especially if you don't have control over all processes connecting to the target database.
如果您不需要可重复性，那么nolocks可能是有用的，尤其是当您无法控制连接到目标数据库的所有进程时。
#1
380
WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it IMHO.
使用(NOLOCK)等同于将READ uncommgiven作为事务隔离级别。因此，您将面临读取随后回滚的未提交行的风险，即从未将其放入数据库的数据。因此，尽管它可以防止读取被其他操作阻塞，但它也存在风险。在高交易率的银行应用程序中，它可能不是您试图用它解决的任何问题的正确解决方案。
#2
150
The question is what is worse:
问题是什么更糟:
- a deadlock, or
- 死锁,或者
- a wrong value?
- 一个错误的值?
For financial databases, deadlocks are far worse than wrong values. I know that sounds backwards, but hear me out. The traditional example of DB transactions is you update two rows, subtracting from one and adding to another. That is wrong.
对于金融数据库来说，死锁远比错误的值更糟糕。我知道这听起来有点倒，但听我说。DB事务的传统示例是更新两行，从一行减去一行，然后添加到另一行。这是错误的。
In a financial database you use business transactions. That means adding one row to each account. It is of utmost importance that these transactions complete and the rows are successfully written.
在金融数据库中，您使用业务事务。这意味着在每个帐户中增加一行。最重要的是，这些事务完成并成功地写入了行。
Getting the account balance temporarily wrong isn't a big deal, that is what the end of day reconciliation is for. And an overdraft from an account is far more likely to occur because two ATMs are being used at once than because of a uncommitted read from a database.
暂时把账户余额搞错并不是什么大问题，这就是今天的和解的目的。而且，一个帐户透支的可能性要大得多，因为两个自动取款机同时被使用，而不是因为从数据库中读取数据。
That said, SQL Server 2005 fixed most of the bugs that made NOLOCK necessary. So unless you are using SQL Server 2000 or earlier, you shouldn't need it.
也就是说，SQL Server 2005修复了大多数导致NOLOCK的缺陷。因此，除非您正在使用SQL Server 2000或更早的版本，否则您不应该使用它。
Further Reading
Row-Level Versioning
进一步阅读行级版本控制
#3
48
The text book example for legitimate usage of the nolock hint is report sampling against a high update OLTP database.
nolock提示的合法用法的教科书示例是针对一个高更新OLTP数据库的报告抽样。
To take a topical example. If a large US high street bank wanted to run an hourly report looking for the first signs of a city level run on the bank, a nolock query could scan transaction tables summing cash deposits and cash withdrawals per city. For such a report the tiny percentage of error caused by rolled back update transactions would not reduce the value of the report.
举一个时事例子。如果一家大型美国商业街银行想要每小时发布一份报告，寻找该银行首次出现市级挤兑的迹象，那么nolock查询可以对包含每个城市的现金存款和现金提取的交易表进行扫描。对于这样的报告，由回滚更新事务导致的微小错误百分比不会降低报告的价值。
#4
47
Unfortunately it's not just about reading uncommitted data. In the background you may end up reading pages twice (in the case of a page split), or you may miss the pages altogether. So your results may be grossly skewed.
不幸的是，这不仅仅是读取未提交的数据。在后台，你可能会读两页(在页面分割的情况下)，或者你可能会完全错过页面。所以你的结果可能有很大的偏差。
Check out Itzik Ben-Gan's article. Here's an excerpt:
看看伊茨克·本根的文章。这里的一个摘录:
" With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don't expect consistency, so there are no guarantees. Bear in mind though that "inconsistent data" does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice. "
使用NOLOCK提示(或将会话的隔离级别设置为读取未提交的会话)，您可以告诉SQL Server，您不希望得到一致性，因此没有保证。请记住，“不一致的数据”不仅意味着您可能会看到后来回滚的未提交的更改，或者在事务的中间状态中看到数据更改。它还意味着，在一个简单的查询中，扫描所有表/索引数据SQL服务器可能会丢失扫描位置，或者您可能会得到两次相同的行。”
#5
30
Not sure why you are not wrapping financial transactions in database transactions (as when you transfer funds from one account to another - you don't commit one side of the transaction at-a-time - this is why explicit transactions exist). Even if your code is braindead to business transactions as it sounds like it is, all transactional databases have the potential to do implicit rollbacks in the event of errors or failure. I think this discussion is way over your head.
不知道为什么不在数据库事务中包装金融事务(例如，当您将资金从一个帐户转移到另一个帐户时—您不会每次提交事务的一端—这就是为什么存在显式事务)。即使您的代码听起来像业务事务，但所有事务数据库都有可能在发生错误或失败时进行隐式回滚。我认为这次讨论超出了你的想象。
If you are having locking problems, implement versioning and clean up your code.
如果您遇到锁定问题，请实现版本控制并清理代码。
No lock not only returns wrong values it returns phantom records and duplicates.
没有锁不仅返回错误的值，它还返回幻像记录和副本。
It is a common misconception that it always makes queries run faster. If there are no write locks on a table, it does not make any difference. If there are locks on the table, it may make the query faster, but there is a reason locks were invented in the first place.
一个常见的误解是，它总是使查询运行得更快。如果表上没有写锁，也没有什么区别。如果表上有锁，它可能会使查询速度更快，但首先发明锁是有原因的。
In fairness, here are two special scenarios where a nolock hint may provide utility
公平地说，这里有两个特殊的场景，其中nolock提示可以提供实用价值
1) Pre-2005 sql server database that needs to run long query against live OLTP database this may be the only way
1) 2005年以前需要对实时OLTP数据库运行长查询的sql server数据库这可能是唯一的方法
2) Poorly written application that locks records and returns control to the UI and readers are indefinitely blocked. Nolock can be helpful here if application cannot be fixed (third party etc) and database is either pre-2005 or versioning cannot be turned on.
2)编写糟糕的应用程序，该应用程序锁定记录并将控制权返回给UI和读取器，并被无限期地阻塞。如果应用程序不能被修复(第三方等)，并且数据库在2005年之前或者版本控制不能被打开，Nolock将会很有帮助。
#6
23
NOLOCK is equivalent to READ UNCOMMITTED, however Microsoft says you should not use it for UPDATE or DELETE statements:
NOLOCK相当于未提交的文本，但微软表示，你不应该将其用于更新或删除语句:
For UPDATE or DELETE statements: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
对于更新或删除语句:该特性将在Microsoft SQL Server的未来版本中删除。避免在新的开发工作中使用该特性，并计划修改当前使用该特性的应用程序。
http://msdn.microsoft.com/en-us/library/ms187373.aspx
http://msdn.microsoft.com/en-us/library/ms187373.aspx
This article applies to SQL Server 2005, so the support for NOLOCK exists if you are using that version. In order to future-proof you code (assuming you've decided to use dirty reads) you could use this in your stored procedures:
本文适用于SQL Server 2005，因此，如果您正在使用该版本，则对NOLOCK的支持是存在的。为了防止将来发生，您可以在存储过程中使用这个代码(假设您已经决定使用脏读):
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
设置未提交的事务隔离级别读取
#7
15
Another case where it's usually okay is in a reporting database, where data is perhaps already aged and writes just don't happen. In this case, though, the option should be set at the database or table level by the administrator by changing the default isolation level.
另一个通常没问题的情况是在报表数据库中，数据可能已经老化，写入不会发生。但是，在这种情况下，管理员应该通过更改默认隔离级别在数据库或表级别设置选项。
In the general case: you can use it when you are very sure that it's okay to read old data. The important thing to remember is that its very easy to get that wrong. For example, even if it's okay at the time you write the query, are you sure something won't change in the database in the future to make these updates more important?
在一般情况下:当您非常确定可以读取旧数据时，可以使用它。重要的是要记住，这很容易出错。例如，即使在编写查询时没有问题，您确定将来数据库中不会发生什么变化，从而使这些更新变得更重要吗?
I'll also 2nd the notion that it's probably not a good idea in banking app. Or inventory app. Or anywhere you're thinking about transactions.
我还想说，这在银行应用或库存应用中可能不是个好主意。
#8
15
You can use it when you're only reading data, and you don't really care about whether or not you might be getting back data that is not committed yet.
当你只读取数据时，你可以使用它，你并不关心你是否得到了尚未提交的数据。
It can be faster on a read operation, but I cannot really say by how much.
它在读取操作上可以更快，但我不能确切地说有多快。
In general, I recommend against using it - reading uncommitted data can be a bit confusing at best.
一般来说，我建议不要使用它——读取未提交的数据最多也会有点混乱。
#9
13
Simple answer - whenever your SQL is not altering data, and you have a query that might interfere with other activity (via locking).
简单的回答——只要SQL没有修改数据，并且有可能干扰其他活动的查询(通过锁定)。
It's worth considering for any queries used for reports, especially if the query takes more than, say, 1 second.
对于任何用于报表的查询都值得考虑，特别是如果查询花费的时间超过(例如)1秒。
It's especially useful if you have OLAP-type reports you're running against an OLTP database.
如果您有针对OLTP数据库运行的olap类型报告，那么它就特别有用。
The first question to ask, though, is "why am I worrying about this?" ln my experience, fudging the default locking behavior often takes place when someone is in "try anything" mode and this is one case where unexpected consequences are not unlikely. Too often it's a case of premature optimization and can too easily get left embedded in an application "just in case." It's important to understand why you're doing it, what problem it solves, and whether you actually have the problem.
第一个要问的问题是，“我为什么要担心这个?”通常情况下，这是一种过早的优化，很容易被嵌入到应用程序中，“以防万一”。重要的是要理解你为什么要这么做，它解决了什么问题，以及你是否真的有这个问题。
#10
9
My 2 cents - it makes sense to use WITH (NOLOCK) when you need to generate reports. At this point, the data wouldn't change much & you wouldn't want to lock those records.
我的2分—当您需要生成报告时，可以使用(NOLOCK)。此时，数据不会有太大的变化&您不会希望锁定这些记录。
#11
8
If you are handling finance transactions then you will never want to use nolock. nolock is best used to select from large tables that have lots updates and you don't care if the record you get could possibly be out of date.
如果你在处理金融交易，那么你永远不会想要使用nolock。nolock最适合用来从有大量更新的大表中进行选择，如果你得到的记录可能是过时的，你也不介意。
For financial records (and almost all other records in most applications) nolock would wreak havoc as you could potentially read data back from a record that was being written to and not get the correct data.
对于财务记录(以及大多数应用程序中的几乎所有其他记录)，nolock将会造成巨大的破坏，因为您可能会从正在写入的记录中读取数据，而不会得到正确的数据。
#12
7
I've used to retrieve a "next batch" for things to do. It doesn't matter in this case which exact item, and I have a lot of users running this same query.
我曾经为要做的事情检索“下一批”。在这种情况下，具体是哪个项目并不重要，我有很多用户在运行相同的查询。
#13
6
Use nolock when you are okay with the "dirty" data. Which means nolock can also read data which is in the process of being modified and/or uncommitted data.
当你对“脏”数据没有意见时，使用nolock。这意味着nolock还可以读取正在修改和/或未提交数据的数据。
It's generally not a good idea to use it in high transaction environment and that is why it is not a default option on query.
在高事务环境中使用它通常不是一个好主意，这就是为什么它不是查询的默认选项。
#14
6
I use with (nolock) hint particularly in SQLServer 2000 databases with high activity. I am not certain that it is needed in SQL Server 2005 however. I recently added that hint in a SQL Server 2000 at the request of the client's DBA, because he was noticing a lot of SPID record locks.
我使用(nolock)提示，特别是在具有高活动的SQLServer 2000数据库中。但是我不确定SQL Server 2005是否需要它。我最近应客户的DBA的请求在SQL Server 2000中添加了这个提示，因为他注意到很多SPID记录锁。
All I can say is that using the hint has NOT hurt us and appears to have made the locking problem solve itself. The DBA at that particular client basically insisted that we use the hint.
我所能说的是，使用这个提示并没有伤害到我们，并且似乎已经使锁定问题自己解决了。在那个特定客户端的DBA基本上坚持我们使用这个提示。
By the way, the databases I deal with are back-ends to enterprise medical claims systems, so we are talking about millions of records and 20+ tables in many joins. I typically add a WITH (nolock) hint for each table in the join (unless it is a derived table, in which case you can't use that particular hint)
顺便说一下，我处理的数据库是企业医疗索赔系统的后端，因此我们在许多连接中讨论了数百万条记录和20多个表。我通常为连接中的每个表添加一个WITH (nolock)提示(除非它是一个派生表，在这种情况下，您不能使用这个特殊提示)
#15
4
Short answer:
简短的回答:
Never use WITH (NOLOCK).
从来没有使用(NOLOCK)。
Long answer:
长一点的回答:
NOLOCK is often exploited as a magic way to speed up database reads, but I try to avoid using it wherever possible.
NOLOCK通常被用作加速数据库读取的神奇方法，但我尽量避免使用它。
The result set can contain rows that have not yet been committed, that are often later rolled back.
结果集可以包含尚未提交的行，这些行通常会在以后回滚。
An error or Result-set can be empty, be missing rows or display the same row multiple times.
错误或结果集可以是空的、丢失的行或多次显示同一行。
This is because other transactions are moving data at the same time you're reading it.
这是因为其他事务正在读取数据的同时移动数据。
READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.
READ COMMITTED增加了一个额外的问题，即数据在单个列中被破坏，多个用户同时更改同一个单元。
There are other side-effects too, which result in sacrificing the speed increase you were hoping to gain in the first place.
还有其他的副作用，这将牺牲你最初希望获得的速度提升。
It can be argued that it is fine to use it in places where you can get away with it, but what's the point? I can't think of any situation where corrupted data is acceptable.
可以争辩说，在你可以侥幸逃脱的地方使用它是可以的，但有什么意义呢?我想不出有什么情况可以接受损坏的数据。
Never ever use NOLOCK ever.
永远不要使用诺洛克。
(ever)
(永远)
#16
3
The simplest answer is a simple question - do you need your results to be repeatable? If yes then NOLOCKS is not appropriate under any circumstances
最简单的答案是一个简单的问题——你需要你的结果是可重复的吗?如果是，那么在任何情况下停止是不合适的
If you don't need repeatability then nolocks may be useful, especially if you don't have control over all processes connecting to the target database.
如果您不需要可重复性，那么nolocks可能是有用的，尤其是当您无法控制连接到目标数据库的所有进程时。