We are running the following queries using dynamic SQL, which is erroring out for few cases when we run concurrent instances. The error is:

Could not continue scan with NOLOCK due to data movement...

This question says this error is thrown when a process reads data which is being deleted by another process. Our process is deleting and reading the same rows, however one after the other as per following query (the SELECT is after the DELETE):

WHERE colum1 = somevalue1
AND column2 = somevalue2

WHERE colum1 = somevalue1
AND column2 = somevalue2

I am trying to understand the execution of above query. As the SELECT is uncommitted, does it start executing before the DELETE is committed? Would this mean that removing the NOLOCK hint would stop the error?

  • 3
    The error isn't caused by the DELETE ... SELECT from a single session. That always runs sequentially. The issue is the SELECT (NOLOCK) running concurrently with data modifications in a different session. – Martin Smith Feb 17 '13 at 18:48
  • @MartinSmith So.. SELECT (NOLOCK) will not ignore the locks caused by DELETE and will begin execution only after DELETE is committed ? Then probably error is because of some other query as we are not running more than one instance of DELETE ... SELECT parallely acting on same rows. – kanu Feb 17 '13 at 19:10
  • Can you use snapshot isolation instead of NOLOCK? – A-K Feb 17 '13 at 20:09
  • @kanu - It will begin executing after the DELETE has finished because that's the way TSQL works. It won't fire off an asynch delete then start on the SELECT before that statement has finished. Whether it is committed or not depends if both statements are running in the same transaction or not. But either way it is irrelevant. As stated earlier you are getting this because of activity from a different spid. – Martin Smith Feb 17 '13 at 22:21

As the SELECT is uncommitted, does it start executing before the DELETE is committed?

No, T-SQL statements always execute sequentially in SQL Server. The point is the SELECT may read uncommitted changes made by other concurrently-executing transactions.

Would this mean that removing NOLOCK should stop the error?

Yes, but only because error 601 is only possible when the transaction isolation level is READ UNCOMMITTED. Moving to a different isolation level prevents that particular error from occurring.

Additional information

Error 601 can occur for a variety of reasons, but all share a common theme: the SQL Server engine was following some pointer chain or other, when it encountered a situation where a structure it was expecting to be present had been moved or deleted by another concurrently-executing process.

The number of cases where error 601 can occur has been progressively reduced over SQL Server releases, with SQL Server 2012 the least likely to return this error, though it is still possible.

My own view is that all error 601 incidents are bugs - for a suitable value of 'bug'. The behaviour is certainly undesirable, and goes well beyond the SQL standard's description of phenomena that may be encountered under the READ UNCOMMITTED isolation level.

While it is certainly true that the SQL standard does not define the detailed behaviours of the different isolation levels very well, what it does say leads to the common belief that the only consequence of READ UNCOMMITTED is that a transaction may see data that has been changed by another transaction before that other transaction commits. As a result of that, using READ UNCOMMITTED isolation level is often justified on the basis that almost all transactions commit very quickly (and very few roll back) so reading 'dirty' data is just a timing difference.

Unfortunately, the implementation of READ UNCOMMITTED in SQL Server goes much further than simple dirty reads. A READ UNCOMMITTED transaction in SQL Server can return duplicated data, a partial read of a large data type, skip data records entirely, or simply fail with an 601 error.

It is possible to experience some of these behaviours under READ COMMITTED and even REPEATABLE READ. This leads some to conclude that the only acceptable isolation levels are those that provide at least statement-level consistency, namely row-versioning READ COMMITTED, SNAPSHOT isolation, or SERIALIZABLE.

Of these, row-versioning READ COMMITTED is usually the easiest to transition to. See this Books Online Topic and its subtree for details of row-versioning isolation levels.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.