nolock使用_如何使用NOLOCK可以阻止您的查询

nolock使用

注意：下面描述的问题适用于所有SELECT查询，而不仅仅是装饰有NOLOCK提示的查询。 它适用于NOLOCK查询的事实让我感到非常惊讶，因此也获得了标题。

很多人不喜欢SQL Server中的NOLOCK （即读取的未提交隔离级别），因为它可能返回不正确的数据。 我已经看到很多论点，它们警告开发人员不要检索未提交的读取，因为它们如何返回脏数据，幻像读取和不可重复的读取 。

我已经知道所有上述问题，但是直到最近我才听说过一个问题： NOLOCK可以阻止其他查询运行。

您还可以 在YouTube上 观看本周的 帖子

让我们退后一步，了解为什么我过去经常使用NOLOCK。 我使用NOLOCK的一个相当典型的例子是当我想让查询运行一整夜以返回一些大数据时。 我可以处理数据中的某些不一致问题（例如脏读等）。 我主要关心的是，我不希望长时间运行的查询妨碍其他进程。

我一直认为NOLOCK是这种情况的理想解决方案，因为它从不锁定读取的数据-结果可能并不完美，但是至少查询不会对服务器上的任何其他进程产生负面影响。

这是我对NOLOCK的理解错误的地方： 虽然NOLOCK 不会锁定行级数据，但它将取出模式稳定性锁定。

模式稳定性（Sch-S）锁定可防止在执行查询时更改表的结构。 所有SELECT语句，包括处于读取未提交/ NOLOCK隔离级别的语句，都将获得Sch-S锁定。 这是有道理的，因为我们不想开始从表中读取数据，然后在数据检索过程中途更改列结构。

但是，这也意味着某些操作可能会被Sch-S锁阻止。 例如，在这种情况下，任何请求模式修改（Sch-M）锁定的命令都会被阻止。

哪些命令请求Sch-M锁？

诸如索引REBUILD或sp_recompile表之类的东西。 这些是在夜间维护作业中运行的命令类型，我一开始尝试通过使用NOLOCK来避免造成伤害！

重申一下，我曾经认为使用NOLOCK提示是防止长时间运行的查询阻塞的好方法。 但是，事实证明，我的NOLOCK查询实际上阻止了我的夜间索引作业（在此示例中，所有SELECT查询都阻止了，但是我发现NOLOCK特别容易引起误解），这也导致其他SELECT语句也被阻止！

让我们看看实际情况。 在这里，我有一个查询，该查询创建数据库，表，然后使用NOLOCK运行长时间运行的查询：

DROP DATABASE IF EXISTS [Sandbox] 
GO 
CREATE DATABASE [Sandbox] 
GO 
USE [Sandbox] 
GO 
DROP TABLE IF EXISTS dbo.Test 
CREATE TABLE dbo.Test 
( 
  c0 int IDENTITY PRIMARY KEY, 
  c1 varchar(700) default REPLICATE(‘a’,700) 
) 
CREATE NONCLUSTERED INDEX IX_Id ON dbo.Test (c1); 
GO

INSERT INTO dbo.Test DEFAULT VALUES; 
GO 1000

-- Read a billion records 
SELECT * 
FROM dbo.Test t1 (NOLOCK) 
CROSS JOIN dbo.Test t2 (NOLOCK) 
CROSS JOIN dbo.Test t3 (NOLOCK)

现在，在进行十亿行读取的同时，我们可以通过查看sys.dm_tran_locks来验证查询是否已锁定Sch-S：

SELECT * 
FROM sys.dm_tran_locks 
WHERE resource_type = ‘OBJECT’

在运行时，如果我们尝试重建索引，则该重建将被阻止（显示为WAIT）：

USE [Sandbox] 
GO

ALTER INDEX IX_Id ON dbo.Test REBUILD

我们的索引重建查询将保持阻塞状态，直到我们的十亿行NOLOCK SELECT查询完成运行（或被终止）。 这意味着我本打算完全不引人注意的查询现在阻止了我的每晚索引维护作业。

更糟糕的是，其他任何在REBUILD查询之后尝试运行的查询（或任何其他请求Sch-M锁定的命令）也将被阻止！ 如果我尝试运行一个简单的COUNT（*）查询：

USE [Sandbox] 
GO

SELECT COUNT(*) FROM dbo.Test

封锁！ 这意味着不仅我的初始NOLOCK查询导致我的索引REBUILD维护作业等待，而且REBUILD维护作业放置的Sch-M锁导致该表上的所有后续查询都被阻塞并被迫等待。 我只是用阻塞性的NOLOCK语句破坏了我的维护工作和后续查询的及时性！

解决方案

不幸的是，这是一个棘手的问题，并且没有一种千篇一律的补救措施。

解决方案＃1：不要运行长时间运行的查询

我会避免在运行整夜查询时，他们可能会碰到我的索引维护工作。 这将防止这些索引维护作业和后续查询被延迟，但这意味着我最初的十亿行选择查询将不得不更早运行，从而在一天中可能更繁忙的时间内对服务器性能产生了负面影响。

解决方案2：使用WAIT_AT_LOW_PRIORITY

从2014年开始，我可以使用WAIT_AT_LOW_PRIORITY选项集进行在线索引重建：

ALTER INDEX IX_Id ON dbo.Test REBUILD 
WITH ( 
  ONLINE = ON (
    WAIT_AT_LOW_PRIORITY (
      MAX_DURATION = 1 MINUTES , 
      ABORT_AFTER_WAIT = BLOCKERS)))

该查询基本上提供当前正在运行的任何阻塞SELECT查询1分钟以完成执行，否则此查询将杀死它们，然后执行索引重建。 另外，我们也可以设置ABORT_AFTER_WAIT = SELF，并且重建查询会终止自身，从而使NOLOCK billion SELECT行可以完成运行，而不会阻止任何其他查询的运行。

这不是一个很好的解决方案，因为这意味着要么长时间运行的查询被杀死，要么索引REBUILD被杀死。

解决方案3：如果没有Sch-S，则重新构建；否则，重新组织

可以编写一个程序化的解决方案来尝试重建索引，但是如果知道必须等待Sch-M锁，则退回到REORGANIZE。

我已经在下面创建了样板作为起点，但是天空是您可以做的事的极限（例如，创建WHILE循环以每x秒检查一次锁定，为脚本何时停止创建超时尝试重新构建，而只是重新组织，等等……）

-- Idea for how to rebuild/reorganize based on a schema stability lock.
-- More of a starting point than fully functional code.
-- Not fully tested, you have been warned!
DECLARE 
 @TableName varchar(128) = 'Test',
 @HasSchemaStabilityLo ck bit = 0
 
SELECT TOP 1 @HasSchemaStabilityLo ck = 
 CASE WHEN l.request_mode IS NOT NULL THEN 1 ELSE 0 END
 FROM
  sys.dm_tran_locks as l
 WHERE
  l.resource_type = 'OBJECT'
  AND l.request_mode = 'Sch-S'
  AND l.request_type = 'LOCK'
  AND l.request_status = 'GRANT'
  AND OBJECT_NAME(l.resource_associated_entity_id) = @TableName 
 
IF @HasSchemaStabilityLo ck = 0
BEGIN
 -- Perform a rebuild
 ALTER INDEX IX_Id ON dbo.Test REBUILD
 PRINT 'Index rebuilt'
END
ELSE
BEGIN
 -- Perform a REORG
 ALTER INDEX IX_Id ON dbo.Test REORGANIZE
 PRINT 'Index reorganized'
END

此解决方案是我的最爱，因为：

1. 长期运行的特殊查询不会被杀死（处理所花费的所有时间都不会浪费）
2. 通过REBUILD进行的Sch-M锁定尝试不会阻止其他选择查询
3. 即使最终成为REORGANIZE而不是REBUILD，索引维护仍然会发生

谢谢阅读。 您可能还喜欢 在Twitter上关注我。

翻译自: https://hackernoon.com/how-using-nolock-can-block-your-queries-adc8611105ff

nolock使用