SQL 2008+ NOLOCK对READPAST报告准确性的考虑

Question

了解最终决策是业务决策，在SQL 2008 R2中运行的NOLOCK和READPAST之间的准确性考虑是什么？在与业务领域讨论变更之前，我希望有一个更好的理解。

我继承了许多查询，用于为管理报告创建数据视图。“‘WITH(NOLOCK)”被广泛使用，但不一致。正在读取的数据来自广泛使用的应用程序的生产服务器，该应用程序正在不断更新。我们正在从SQL 2005服务器迁移到SQL 2008 R2服务器。这些报告希望数据比存档服务器上的24小时数据更新鲜。NOLOCK的使用意味着一个过去的决定；潜在的冲突是存在的，它有一点准确性的损失是可以接受的。数据被用来填充仪表板，用于人类意识/决策。

所有查询都是选择的，数据视图登录的只读访问权限。大多数查询都是单个表，有几个2和3个表联接。考虑到联接级别较低，使用()似乎比设置事务隔离级别{}更好。

表提示(Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187373.aspx (以及关于SO的多个问题)指出，NOLOCK和/或READUNCOMMITTED除了丢失锁定的记录外，还可能存在重复读取问题。

READPAST看起来更准确，因为它只会错过锁定的记录，而不会有复制的机会。但我不确定丢失锁定记录的水平是否与NOLOCK一致。

Tim有一篇比较好的文章，但它是在2007年写的，大部分评论都围绕着2000 & 2005，有一条评论表明，在2008年的R2中，READPAST是有问题的。

参考文献

NOLOCK提示在SELECT语句中的作用

你应该什么时候使用"with (nolock)“

在Server中使用NOLOCK和READPAST表提示(由Tim编写)

编辑：

快照隔离在下面的两个答案中提出。快照隔离是数据库的依赖设置，这个Q/A https://serverfault.com/questions/117104/how-can-i-tell-if-snapshot-isolation-is-turned-on描述了如何查看数据库上的设置。我现在知道它是禁用的，我正在阅读来自一个主要应用程序数据库的报告。更改设置不是一个选项。+-几%的准确度是可以接受的，应用程序(OLTP)的影响是不可接受的。大多数简单查询不需要考虑锁，但在某些极端情况下，需要考虑锁。随着SQL 2005快照隔离的出现，很少有关于SQL 2008或更高版本中NOLOCK和READPAST行为的信息。但他们仍然是我唯一的选择。

Answer 1

在用SQLQueryStress http://www.datamanipulation.net/sqlquerystress/进行压力测试之后，回答我自己的问题(这是一个非常容易使用的好工具)。来自SQLQueryStress的结果将在Server上进行测试；其准确性与Server相同，尽管岁差是小数点的两位，减少了一秒钟(但足以进行此测试)。

正如问题中提到的，主要关注的是应用性能的影响，其次是报告的准确性和性能。所有测试都发生在测试应用程序处于活动状态的测试服务器上，并且有一些次要的活动。

在下载并熟悉了SQLQueryStress之后，我设置了一个简单的‘ReportQuery’来充当资源霸主。它设置为运行15个线程的15个迭代(225个总查询)。总运行时间约为28秒，平均迭代时间为1.49秒。

创建了一个添加/删除‘ApplicationQuery’来表示正在进行的应用程序活动。它被设置为运行一个线程的2000次迭代。有两个版本，一个select语句(运行31秒)和一个select语句(运行28秒)。它们代表正常的峰值时间应用程序活动。

运行三个版本的“ReportQuery”中的每一个版本的10次测试运行，这是为了确定‘with(Nolock)’、‘with(Readpast)’和没有提示之间是否有任何性能上的好处。结果表明，ReportQuery在28秒内持续运行，平均迭代时间为1.5秒，差异无显着性。

没有大的离群点，所以，决定下降到5测试运行以下测试。

5使用select语句运行ApplicationQuery的测试；三个版本的“ReportQuery”中的每一个都在运行。在15个总计测试的每个测试中，ApplicationQuery都是手动启动的，ReportQuery随即手动启动。此场景表示一个资源密集型的报表查询，该查询与应用程序正在进行的资源活动进行斗争。

重复测试运行，但这次使用的是没有select语句的ApplicationQuery。

结果:在所有病例中，当ApplicationQuery运行时，ReportQuery几乎没有向前移动。

当针对数据库使用多个ApplicationQuery的资源时，ReportQuery没有明显的性能损失。

ApplicationQuery能够运行与ReportQuery并行的查询，但是在争夺资源时进展非常缓慢。本质上，运行2000 Application /Delete查询的总时间是由ReportQuery使用的时间延长的。

最初的问题是哪个更准确，变得毫无意义。使用或不使用提示NOLOCK或READPAST本质上没有报告或应用程序性能的差异，因此不要在繁忙的数据库中使用这两种方法，并获得尽可能高的精度。

‘ReportQuery’

select 
ID
, [TABLE_NAME]
, NUMBER
, FIELD
, OLD_VALUE
, NEW_VALUE
, SYSMODUSER
, SYSMODTIME
, SYSMODCOUNT

from dbo.UPMCINCIDENTMGMTAUDITRECORDSM1 

where Number like '%'
or NUMBER like '2010-01-01'

‘ApplicationQuery’(带有Select语句)

select * 

from dbo.UPMCINCIDENTMGMTAUDITRECORDSM1 
where FIELD = 'JJTestingPerformance'


insert into dbo.UPMCINCIDENTMGMTAUDITRECORDSM1 (ID
, [TABLE_NAME]
, NUMBER
, FIELD
, OLD_VALUE
, NEW_VALUE
)

values ('Test+Time'
, 'none'
, 'tst01'
, 'JJTestingPerformance'
, 'No Value'
, 'Test'
)

delete from dbo.UPMCINCIDENTMGMTAUDITRECORDSM1

where FIELD = 'JJTestingPerformance'

‘ApplicationQuery’(没有选择语句)

insert into dbo.UPMCINCIDENTMGMTAUDITRECORDSM1 (ID
, [TABLE_NAME]
, NUMBER
, FIELD
, OLD_VALUE
, NEW_VALUE
 )

values ('Test+Time'
, 'none'
, 'tst01'
, 'JJTestingPerformance'
, 'No Value'
, 'Test'
)

delete from dbo.UPMCINCIDENTMGMTAUDITRECORDSM1

where FIELD = 'JJTestingPerformance'

​

​

Answer 2

值得考虑的一个更好的选项是为数据库本身启用已提交的读取快照。这将使用tempdb中的版本控制来捕获事务开始时表的状态。

在http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/上，对NOLOCK，READPAST等的各个方面都有很好的阅读

当您选择表时，如果有人正在更新表，WITH (NOLOCK)可能会提供不正确的结果。如果在读取表时由于插入而发生分页，而新页恰好超出了所读的点，WITH (NOLOCK)将已经从旧页返回行，然后将从新页返回重复行。这只是说明为什么(NOLOCK)不好的一个例子。

WITH (READPAST)将跳过正在从表中读取时正在更新或插入的任何记录。在繁忙的数据库中，这两个选项都是好的。

鉴于最近对您的问题进行的编辑--您声明不能更改READ COMMITTED SNAPSHOT的数据库设置--也许您应该考虑使用存储过程为您收集报告，并使用SET TRANSACTION ISOLATION LEVEL SNAPSHOT;在存储proc的开头设置事务隔离级别。为此，需要更改数据库选项“允许快照隔离”。

来自Server联机丛书：

快照

指定事务中任何语句读取的数据将是事务开始时存在的数据的事务一致性版本。事务只能识别在事务开始之前提交的数据修改。在当前事务开始后，其他事务所做的数据修改对于在当前事务中执行的语句是不可见的。其效果就像事务中的语句获得事务开始时存在的已提交数据的快照一样。

除非数据库正在恢复，否则快照事务在读取数据时不会请求锁。快照事务读取数据不会阻止其他事务写入数据。写入数据的事务不会阻止快照事务读取数据。

在数据库恢复的回滚阶段，如果尝试读取正在回滚的另一个事务锁定的数据，快照事务将请求锁定。快照事务将被阻塞，直到该事务回滚为止。该锁在被授予后立即释放。

在启动使用快照隔离级别的事务之前，必须将ALLOW_SNAPSHOT_ISOLATION数据库选项设置为ON。如果使用快照隔离级别的事务访问多个数据库中的数据，则必须在每个数据库中将ALLOW_SNAPSHOT_ISOLATION设置为ON。

不能将事务设置为与另一个隔离级别一起启动的快照隔离级别；这样做将导致事务中止。如果事务在快照隔离级别启动，则可以将其更改为另一个隔离级别，然后返回到快照。事务第一次访问数据时启动。

在快照隔离级别下运行的事务可以查看该事务所做的更改。例如，如果事务对表执行更新，然后针对同一表发出SELECT语句，则修改后的数据将包含在结果集中。

Answer 3

NOLOCK可以导致重复的数据被读取，数据被丢失，查询实际上由于一个错误消息而失败(带有“数据移动”的内容)。

另一方面，非NONLOCK查询也可以读取重复数据和mis数据！它绝不是数据库的一致快照。区别在于它不会读取未提交的数据，也不会失败。

NOLOCK的问题主要是它可能随机失败，所以您需要重试。另外，读取错误数据的概率略高一些。

在进行表扫描时，NOLOCK有一个很大的优势: Server可以使用分配顺序扫描而不是索引顺序扫描。TABLOCK也有同样的效果。在存在碎片的情况下，这可能是一个显著的加速。

考虑使用快照隔离级别，因为它消除了所有这些问题。它与其他一些权衡，你没有得到分配顺序扫描。但它永久地、全面地消除了锁定问题。