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 as documented in KB815008.

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.

---

Example

The following produces error 601 on SQL Server 2022 CU15-GDR:

Setup

DROP TABLE IF EXISTS dbo.ReadMe;
CREATE TABLE dbo.ReadMe 
(
    i nvarchar(max) 
        COLLATE Latin1_General_100_BIN2 NULL
);

First session

-- Session 1
-- Set this running first
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET XACT_ABORT, NOCOUNT ON;
DECLARE @i nvarchar(max);
BEGIN TRANSACTION E601;
WHILE 1 = 1
BEGIN
    SELECT 
        @i = RM.i 
    FROM dbo.ReadMe AS RM;
END;

Second session

-- Session 2
-- Run this second
DECLARE @i nvarchar(max) = N'x';
SET @i = REPLICATE(@i, 8040);
SET @i = REPLICATE(@i, 160);
SET NOCOUNT ON;
WHILE EXISTS
(
    SELECT 
        T.* 
    FROM sys.dm_tran_active_transactions AS T
    WHERE 
        T.[name] = N'E601'
)
BEGIN
    INSERT dbo.ReadMe (i) VALUES (@i);
    INSERT dbo.ReadMe (i) VALUES (@i);
    INSERT dbo.ReadMe (i) VALUES (@i);
    DELETE dbo.ReadMe;
END;

The error will occur after a few seconds in session 1.
Session 2 will stop automatically when that happens.