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.
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
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.