了解SQL Server对SELECT查询的锁

I'm wondering what is the benefit to use SELECT WITH (NOLOCK) on a table if the only other queries affecting that table are SELECT queries.

如果影响该表的查询只有SELECT查询，那么在表上使用SELECT WITH (NOLOCK)有什么好处呢?

How is that handled by SQL Server? Would a SELECT query block another SELECT query?

SQL Server是如何处理的?一个SELECT查询会阻塞另一个SELECT查询吗?

I'm using SQL Server 2012 and a Linq-to-SQL DataContext.

我使用的是SQL Server 2012和linqto -SQL DataContext。

(EDIT)

(编辑)

About performance :

性能:

Would a 2nd SELECT have to wait for a 1st SELECT to finish if using a locked SELECT?
如果使用锁定选择，第二个选择是否必须等待第一个选择完成?
Versus a SELECT WITH (NOLOCK)?
与(NOLOCK)选择相比?

Thanks.

谢谢。

6 个解决方案

#1

117

A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.

SQL Server中的SELECT将在表行上放置一个共享锁——而第二个SELECT也将需要一个共享锁，并且这些锁相互兼容。

So no - one SELECT cannot block another SELECT.

所以没有一个选择不能阻止另一个选择。

What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.

使用WITH (NOLOCK)查询提示的目的是能够读取被插入过程中(通过另一个连接)尚未提交的数据。

Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).

如果没有查询提示，SELECT可能会被正在执行的INSERT(或UPDATE)语句阻止，该语句将独占锁放置在行(或者可能是整个表)上，直到提交(或回滚)操作的事务。

Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.

WITH (NOLOCK)提示的问题是:您可能正在读取根本不会被插入的数据行，最后(如果插入事务被回滚)——所以您的示例报告可能会显示从未真正提交到数据库的数据。

There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.

还有另一个查询提示可能有用——与(READPAST)。这将指示SELECT命令只跳过它试图读取的和锁定的任何行。SELECT将不会阻塞，也不会读取任何“脏”未提交的数据——但是它可能会跳过一些行，例如不显示表中的所有行。

#2

On performance you keep focusing on select.
Shared does not block reads.
Shared lock blocks update.
If you have hundreds of shared locks it is going to take an update a while to get an exclusive lock as it must wait for shared locks to clear.

在性能上你一直关注选择。共享不会阻塞读取。共享锁块更新。如果您有数百个共享锁，那么将需要一段时间才能获得独占锁，因为它必须等待共享锁清除。

By default a select (read) takes a shared lock.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource.
A shared lock as no effect on other selects (1 or a 1000).

默认情况下，select (read)使用共享锁。共享锁允许并发事务读取(选择)资源。共享锁对其他选择没有影响(1或1000)。

The difference is how the nolock versus shared lock effects update or insert operation.

不同之处在于nolock和共享锁效果是如何更新或插入操作的。

No other transactions can modify the data while shared (S) locks exist on the resource.

在共享(S)锁存在于资源上时，没有其他事务可以修改数据。

A shared lock blocks an update!
But nolock does not block an update.

共享锁会阻止更新!但是nolock没有阻止更新。

This can have huge impacts on performance of updates. It also impact inserts.

这将对更新的性能产生巨大的影响。它还影响插入。

Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly.

脏读(群读)听起来很脏。你永远不会得到部分数据。如果一个更新把约翰变成了莎莉，你永远不会开心。

I use shared locks a lot for concurrency. Data is stale as soon as it is read. A read of John that changes to Sally the next millisecond is stale data. A read of Sally that gets rolled back John the next millisecond is stale data. That is on the millisecond level. I have a dataloader that take 20 hours to run if users are taking shared locks and 4 hours to run is users are taking no lock. Shared locks in this case cause data to be 16 hours stale.

我经常使用共享锁来实现并发。数据一旦读取就会失效。一读约翰的书，下一毫秒就会变成萨利，那就是陈腐的数据。当Sally读到下一毫秒就会回滚John的内容时，就会发现这是过时的数据。这是毫秒级的。我有一个dataloader，如果用户使用共享锁，则需要运行20小时;如果用户不使用锁，则需要运行4小时。在此情况下共享锁会导致数据16小时失效。

Don't use nolocks wrong. But they do have a place. If you are going to cut a check when a byte is set to 1 and then set it to 2 when the check is cut - not a time for a nolock.

不要使用nolock错了。但他们确实有一席之地。如果您打算在将一个字节设置为1时进行检查，然后将其设置为2，当检查被剪切时——而不是nolock的时间。

#3

At my work, we have a very big system that runs on many PCs at the same time, with very big tables with hundreds of thousands of rows, and sometimes many millions of rows.

在我的工作中，我们有一个非常大的系统，可以同时在许多个人电脑上运行，有非常大的表，有数十万行，有时还有数百万行。

When you make a SELECT on a very big table, let's say you want to know every transaction a user has made in the past 10 years, and the primary key of the table is not built in an efficient way, the query might take several minutes to run.

当您在一个非常大的表上进行选择时，假设您想知道一个用户在过去十年中所做的每一个事务，并且该表的主键不是以有效的方式构建的，查询可能需要几分钟才能运行。

Then, our application might me running on many user's PCs at the same time, accessing the same database. So if someone tries to insert into the table that the other SELECT is reading (in pages that SQL is trying to read), then a LOCK can occur and the two transactions block each other.

然后，我们的应用程序可能同时运行在许多用户的pc上，访问相同的数据库。因此，如果有人试图插入到另一个SELECT正在读取的表中(在SQL试图读取的页面中)，则会出现一个锁，并且两个事务会相互阻塞。

We had to add a "NO LOCK" to our SELECT statement, because it was a huge SELECT on a table that is used a lot by a lot of users at the same time and we had LOCKS all the time.

我们必须在SELECT语句中添加一个“无锁”，因为它是表上的一个巨大的选择，很多用户同时使用它，我们一直都有锁。

I don't know if my example is clear enough? This is a real life example.

我不知道我的例子是否足够清楚?这是一个现实生活中的例子。

#4

I have to add an important comment. Everyone is mentioning that NOLOCKreads only dirty data. This is not precise. It is also possible that you'll get same row twice or whole row is skipped during your read. Reason is that you could ask for some data in same time when SQL Server is re-balancing b-tree.

我必须加上一条重要的评论。每个人都提到，NOLOCKreads只读取脏数据。这不是精确的。您也可能会得到相同的行两次或整个行在读取期间被跳过。原因是，当SQL Server重新平衡b-tree时，您可以同时请求一些数据。

Check another threads

检查另一个线程

https://*.com/a/5469238/2108874

http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx)

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 Server可能会丢失扫描位置，或者您可能会得到相同的行两次。

#5

The SELECT WITH (NOLOCK) allows reads of uncommitted data, which is equivalent to having the READ UNCOMMITTED isolation level set on your database. The NOLOCK keyword allows finer grained control than setting the isolation level on the entire database.

SELECT WITH (NOLOCK)允许读取未提交的数据，这相当于在数据库上设置了读取未提交的隔离级别。NOLOCK关键字允许比在整个数据库上设置隔离级别更细粒度的控制。

Wikipedia has a useful article: Wikipedia: Isolation (database systems)

*有一篇很有用的文章:*:隔离(数据库系统)

It is also discussed at length in other * articles.

在其他*文章中也详细讨论了这个问题。

#6

select with no lock - will select records which may / may not going to be inserted. you will read a dirty data.

不加锁的选择-将选择可能/可能不会插入的记录。你会看到一些不干净的数据。

for example - lets say a transaction insert 1000 rows and then fails.

例如，假设一个事务插入1000行，然后失败。

when you select - you will get the 1000 rows.

当你选择-你会得到1000行。

#1

117