Could not continue scan with NOLOCK due to data movement

在产品环境中定时执行SP时,偶尔会遇到SP执行失败的情况,SQL Server抛出的错误消息是:

Could not continue scan with NOLOCK due to data movement

 从错误消息的字面意思上来理解,存储过程执行失败的原因,很有可能是:SQL Server正在对基础表进行全表扫描,表带有NOLOCK锁提示,在扫描的当前位置缺少一个或多个数据页。但是,缺失的数据页并不能说明数据库中存在损坏问题,此错误的根本原因是由于DML语句引起的页面拆分导致页面被删除或移动,在某一个时刻,数据库无法保证数据的一致性,导致SQL Server无法继续扫描操作。为了验证是否是数据页导致的,可以使用CHECKDB命令:

DBCC CHECKDB(MY_DATABASE) WITH PHYSICAL_ONLY

如果数据库出现故障,或者数据出现丢失,该命令会显示出详细的错误信息。

但是,通常情况下,出现数据库故障的情况非常少,因此,该命令通常不会返回任何错误消息。排除掉数据库故障这个错误之外,那么出现这种错误的原因,只有一个可能,那就是在堆表上执行SELECT命令的时候,使用了表提示(hint) with(nolock),同时有一个update命令在更新该表,增加了表中字段长度,使得某一个或某几个数据页被拆分。由于带with(nolock)的select 查询不会申请任何锁,不会阻塞X锁的执行,这使得update操作和select操作可以同时执行。

要解决该问题,最简单的方案是把with(nolock) hint去掉,让select 操作阻塞update操作的执行,也就是说,在查询表的时候,不能更新表,就可以避免该问题的再次发生。

该问题发生的条件,我认为是以下三个条件共同作用的结果:

  • 该表是一个堆表,且数据量较大,行宽较大,包含LOB类型的数据列
  • 对堆表同时执行查询操作和更新操作,并且查询操作带有with(nolock) hint
  • 查询操作是一个Join操作,并且连接操作(很可能是哈希join)持续的时间较长

 

DBCC CHECKDB是什么?

DBCC CHECKDB 的作用是检查数据库中所有对象的逻辑和物理完整性,这个命令实际上调用多个命令来完成完整性检查:

  • 运行DBCC CHECKALLOC 检查数据库中硬盘空间分配结构的一致性
  • 运行DBCC CHECKTABLE 检查构成表或索引视图的所有页面和结构的完整性。
  • 运行 DBCC CHECKCATALOG 检查catalog的一致性
  • 验证数据库中每个索引视图的内容的有效性
  • 对于使用FILESTREAM,当把varbinary(max)数据存储再文件系统中时,验证表的元数据和文件系统(目录和文件)之间链接的一致性。

DBCC CHECKDB命令的语法是:

DBCC CHECKDB [ ( database_name | database_id | 0 [ , NOINDEX | , Repair ]  ) ]    
    [ WITH      
            [ ALL_ERRORMSGS ]    
            [ , EXTENDED_LOGICAL_CHECKS ]     
            [ , NO_INFOMSGS ]    
            [ , TABLOCK ]    
            [ , ESTIMATEONLY ]    
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]    
            [ , MAXDOP  = number_of_processors ]      
    ]    

参数注释:

database_name | database_id | 0: 指定执行完整性检查的数据库,如果不指定该参数,默认值是当前的数据库

NOINDEX:指定不对用户表的非聚集索引进行密集检查,这有助于减少命令整体执行的时间, NOINDEX不会影响系统表,因为始终对系统表索引执行完整性检查。

Repair:用于指定命令如何修复发现的错误,仅在万不得已时才使用REPAIR选项,指定的数据库必须处于单用户模式下才能使用修复选项,修复选项有三个有效值:

  • REPAIR_ALLOW_DATA_LOSS:尝试修复所有的错误,但是可能导致数据丢失
  • REPAIR_REBUILD:执行无丢失数据的修复,此选项无法修复跟FILESTREAM 数据相关的错误。
  • REPAIR_FAST:仅仅是向后兼容,不做任何修复

ALL_ERRORMSGS:显示素有的错误消息,这是默认的设置

EXTENDED_LOGICAL_CHECKS:对索引视图、XML索引、空间索引执行逻辑一致性检查

NO_INFOMSGS:不显示信息性消息

TABLOCK:使命令执行时对基础表上表级锁,这使得命令执行的更快,但是会降低数据库的并发性。

ESTIMATEONLY:用于显示运行DBCC CHECKDB所需的tempdb空间消耗的估计数量,这数量是估计的,不做实际的数据库检查。

PHYSICAL_ONLY:将检查范围限制在页面和记录头部(Record Header)的物理结构的完整性,以及数据库空间分配的一致性上,该选项用于使用相对少的开销对数据库物理一致性的检查。

DATA_PURITY:指定检查列值的范围,列值完整性检查默认情况下处于启用状态,并且不需要DATA_PURITY选项。如果指定了PHYSICAL_ONLY选项,那么不会执行列完整性的检查。

MAXDOP:执行命令运行的最大并发程度

 

 

参考文档:

DBCC CHECKDB (Transact-SQL)

Error 601: Could not continue scan with NOLOCK due to SQL Server data movement

posted @ 2020-06-22 13:37  悦光阴  阅读(47)  评论(0编辑  收藏