Execution of sequential delete and uncommitted read

Question

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):

DELETE FROM Table1
WHERE colum1 = somevalue1
AND column2 = somevalue2

SELECT COUNT(*) 
FROM Table1 WITH (NOLOCK)
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?

Answer 1

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.