理解 SELECT 查询中的 SQL Server 锁定

Question

理解 SELECT 查询中的 SQL Server 锁定

sql-servert-sqllinq-to-sql

102

如果只有SELECT查询语句会影响到该表，那么在表上使用 SELECT WITH (NOLOCK) 的好处是什么？

SQL Server 如何处理这种情况？一个 SELECT 查询语句是否会阻塞另一个 SELECT 查询语句？

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

(编辑)

关于性能：

如果使用了锁定的 SELECT，第二个 SELECT 是否必须等待第一个 SELECT 完成？
相对于 SELECT WITH (NOLOCK) 呢？

- Francis P

6个回答

39

在性能方面，您需要专注于选择。
共享锁不会阻塞读取。
共享锁会阻塞更新操作。
如果您有数百个共享锁，更新操作将需要一段时间才能获取独占锁，因为它必须等待共享锁被清除。

默认情况下，选择（读取）会获取共享锁。
共享（S）锁允许并发事务读取（SELECT）资源。
共享锁对其他选择（1或1000）没有影响。

nolock和共享锁的区别在于它们对更新或插入操作的影响方式。

只要资源上存在共享（S）锁，其他事务就无法修改数据。

共享锁会阻止更新！
但是nolock不会阻止更新。

这可能会对更新的性能产生巨大的影响。它也会影响插入操作。

脏读（nolock）听起来很脏。您永远不会得到部分数据。如果一个更新正在将John更改为Sally，您永远不会得到Jolly。

我经常使用共享锁来实现并发。数据在读取时就已经过时了。读取John，下一毫秒变成Sally的数据是过时的数据。读取Sally，在下一毫秒得到被回滚为John的数据也是过时的数据。这是在毫秒级别上的情况。如果用户使用共享锁，我有一个数据加载器需要花费20个小时才能运行；如果用户不使用锁，它只需要4个小时。在这种情况下，共享锁会导致数据过期16个小时。

不要错误地使用nolocks。但是它们确实有用处。如果您打算在将字节设置为1时切割支票，然后在切割支票时将其设置为2-这不是使用nolock的时候。

- paparazzo

2

谢谢。我们看到了类似的性能特征。如果我们需要在读取时加锁，我们的网站将无法运行，在大多数情况下没有它的影响只是微不足道的。 - Brian White

@BrianWhite 谢谢。有人懂了。我在更新和插入时会获取很多表锁。我的方法是进去，完成任务，然后离开。 - paparazzo

2

脏读（无锁）听起来很肮脏。你永远不会得到部分数据。如果更新将John更改为Sally，你永远不会得到Jolly。- 我们读取的是John，对吧？ - Furkan Gözükara

2

SQL Server中的更新使用更新锁（U），稍后转换为排他锁（X）。（请参见http://www.madeiradata.com/role-update-lock-sql-server/）更新锁不会阻止共享锁，但排他锁会阻止所有其他锁定（请参见https://msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx）。 - kolobok

@kolobok 正在进行更新，需要一段时间来获取独占锁定。 - paparazzo

18

我必须添加一条重要的评论。每个人都提到NOLOCK只读取脏数据。这并不准确。在您的读取过程中，也有可能会两次获取相同的行或跳过整行数据。原因是当SQL Server重新平衡B树时，您可能同时请求某些数据。

请查看其他线程

https://dev59.com/GHRB5IYBdhLWcg3wN1AQ#5469238

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

通过使用NOLOCK提示（或将会话的隔离级别设置为READ UNCOMMITTED），您告诉SQL Server您不期望一致性，因此没有任何保证。请注意，“不一致的数据”不仅意味着您可能会看到稍后回滚的未提交更改或事务的中间状态下的数据更改。还意味着在扫描所有表/索引数据的简单查询中，SQL Server可能会丢失扫描位置，或者您可能最终获取相同的行两次。

- Milan Matějka

11

在我们的工作中，有一个非常大的系统同时在许多PC上运行，使用了具有数十万行，甚至数百万行的大型表。当您对一个非常大的表进行SELECT查询时，比如说您想知道用户在过去10年中的每笔交易，并且该表的主键没有以有效的方式构建，那么查询可能需要几分钟才能运行。

然后，我们的应用程序可能会同时运行在许多用户的PC上，访问同一个数据库。因此，如果某个人试图插入正在另一个SELECT读取的表中（在SQL正在尝试读取的页面中），那么就会发生锁定，并且这两个事务将互相阻塞。

我们不得不在我们的SELECT语句中添加"NO LOCK"，因为它是对一个被许多用户同时频繁使用的表进行的巨大SELECT查询，并且我们一直遇到锁定问题。

我不知道我的例子是否足够清晰？这是一个真实的例子。

- Danielle Paquette-Harvey

谢谢您提供的示例，但我只是想知道SELECT查询如何影响同一张表上的其他SELECT查询。 - Francis P

1

他们不会，但是选择语句可以是包含更新的事务的一部分。在 tbl 表中进行更新：set x = (select max(y) from tbl) where z = (select min(a) from tbl)。如果你同时有一个从 tbl 表中选择 z 的查询，其他的查询不会阻塞它，但是更新操作会。 - Brian White

1

我也遇到了这个问题，一个长时间运行的查询阻塞了我的插入操作。 - nojetlag

3

这些交易不会相互阻塞，但是选择操作会阻塞更新操作。以下链接可能会对您更好地了解此类操作的工作方式有所帮助：第一个链接和第二个链接。 - JonnyRaa

@JonnyLeeds：你的第二个链接已经失效了。这里是一个SQL Server锁定基础知识的存档链接。 - stomy

3

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

维基百科有一篇有用的文章：Wikipedia: Isolation (database systems) 这也在其他stackoverflow文章中广泛讨论。

- rghome

感谢rghome提供的额外信息。 - Francis P

这就是为什么我更喜欢在合适的情况下使用READUNCOMMITTED（NOLOCK的别名）提示。这样做可以使实际操作，虽然并非真正“无锁”，但更加清晰明了。 - user2864740

1

无锁选择 - 将选择可能/可能不会被插入的记录。您将读取脏数据。

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

当您进行选择时，您将获得这1000行。

- Royi Namir

但是如果没有意图在该表中插入记录，那么NO LOCK是否仍然相关？ - Francis P

不，它不会。因为read使用的是共享锁，可以被多个会话获取。所以不可能出现脏数据。 - Royi Namir

我宁愿不回答我不确定的事情。 :-) - Royi Namir

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- marc_s · Accepted Answer

在SQL Server中，SELECT语句会在表行上放置一个共享锁 - 第二个SELECT语句也需要共享锁，并且这些锁是相互兼容的，因此一个SELECT语句不能阻塞另一个SELECT语句。 WITH (NOLOCK)查询提示用于读取正在插入（由另一个连接）并尚未提交的数据。

如果没有该查询提示，在进行INSERT（或 UPDATE）语句时，SELECT可能会被阻塞读取表中的数据行，该操作将对行（或可能是整个表）进行排他锁定，直到该事务被提交（或回滚）。

使用WITH (NOLOCK)提示的问题是：您可能正在读取最终根本不会插入的数据行（如果INSERT事务被回滚），因此您的报告可能显示从未真正提交到数据库的数据。

还有另一个查询提示可能会很有用 - WITH (READPAST)。这指示SELECT命令只跳过它尝试读取并被独占锁定的任何行。 SELECT将不会被阻塞，并且将不会读取任何“脏”未提交的数据-但它可能会跳过一些行，例如不显示表中的所有行。