We help IT Professionals succeed at work.

Could not continue scan with NOLOCK due to data movement

cp30 asked

I have a SQL Server 2008 R2 database that has base data tables that are constantly being updated with merge statements (mostly updates, some inserts and deletes etc.) by a multi-threaded windows application that is constantly throwing new data at it.  

I have a stored procedure that selects from a view based on the data tables that are being updated and, because it does not matter if I get dirty reads (I would rather dirty read than creating locking issues), then I have included WITH(NOLOCK) table hint on the select from the view.  This seems to work fine the majority of the time but very occasionally I get the following error logged, "Could not continue scan with NOLOCK due to data movement".

The same error is also caused by another stored procedure that selects from the constantly changing data tables and merges the results with another table elsewhere.  I amended this procedure recently so that it selects the data WITH (NO LOCK) into a temp table and then merges the temp table with the other table, this was an attempt to grab the data as quickly as possible and not hold up the base data tables while I did my merge, and it did seem to reduce the errors I received greatly.

My question is, is this something to worry about or is it just that the data being searched has changed so much (split pages etc.? not sure what that means!) that it just couldn't get a result and I should catch the error in code and try to run the stored procedure again?

The stored procedures are run hundreds of times a day (maybe more) and I only get errors during times of very high volume (i.e. lots of data changing on base tables) and, even then, I only get 5 errors in a day maybe, just want to make sure I'm not missing something before I code a retry attempt.

Many thanks in advance

Watch Question

Top Expert 2011
you should write/structure your code to deal with the problem ... its an inherent possibilty when processing with nolock
in a DIRTY READ environment....

to mitigate the problem perhaps you should try and limit the scope of your selects ....

ie select top (10) ...

rather than trying to select all possible rows for your condition at once?

you perhaps need to re-visit your Unit of work strategy to ensure that impacts are minimised...
can these Reader applications afford to be delayed during High maintenance periods?

(ie on encountering the error sleep for a minute before continuing... or instead of selecting 100 rows only select 10...
 you'd need to build in some sort of control table to allow you to track and control this....

  i suppose it really depends on whether this problem appears to be increasing in frequency or is "stable" at this low level (5)
  occurrences per day....




Thanks for your input.  One stored procedure is called every minute on a schedule as it updates some other tables, so as there's 7 similar procedures and they each get called every minute and out of all 7 only one has failed once on any day I think I will monitor and if it does not get any worse I can probably leave that as the next execution a minute later currently works fine.

The other stored procedure is actually a search triggered from the website which is limited to top 200 already, again that has been executed over 1000 times today and only threw that error on 2 separate occasions so I may add a check in the website error handling that if the error thrown is the NOLOCK scan issue that it retries once or twice and see if that subsequent attempt is successful.

Would appreciate your thoughts on whether this sounds like a sensible approach to take at this stage.

Many thanks