Could not continue scan with NOLOCK due to data movement

Question

We run SQL Server 2000 and we get a few of these errors every night.

Could not continue scan with NOLOCK due to data movement

The query that throws this error is a large complex query that joins over a dozen tables. Our underlying data can be updated frequently.

The cultural 'best-practice' is that, in the past, introduction of NOLOCK hints increased performance and improved concurrency. This query doesn't need to be 100% accurate, i.e. we will tolerate dirty reads etc. However, we are struggling to understand why the database is throwing this error, even though we have all these locking hints.

Can anyone shed some light on this - be gentle, I'm actually a programmer, not a DBA :)

PS: We have applied the fix mentioned below previously: http://support.microsoft.com/kb/815008

Answer 1

This is a reasonably well-known issue with SQL Server 2000 - essentially, what happens is if a row gets deleted by process A while process B is doing a scan (either at READ UNCOMMITTED or WITH (NOLOCK)), then process B goes "huh what happened to this data" when it tries to read it. More precisely, the row has to be deleted after process B reads the index, but before it attempts to read the data row.

Craig Freedman gives a good write up here

Fortunately, the fix is relatively simple: http://support.microsoft.com/kb/815008

If that doesn't work, you have the slightly more painful option of removing all your WITH (NOLOCK) hints and setting your transaction isolation level to something above READ UNCOMMITTED.

Answer 2

The possibility of error 601 is unavoidable when performing scans at READ UNCOMMITTED isolation level in any version of SQL Server. The 'solution' is either to use a different isolation level or handle the error gracefully (as you might do for a deadlock).

Remus Rusanu tells me SQL Server 2012 recovers gracefully in many cases of data movement under dirty scans (continues on next page in allocation order), also cursors backed by dirty scans should handle this more gracefully than before.

Even so, there are still cases where this error can occur.