FIX: Query with transaction isolation level set to READ UNCOMMITTED fails with error 601

View products that this article applies to.

BUG #: 363988 (SHILOH_BUGS)

SYMPTOMS

When a query runs with its transaction isolation level set to READ UNCOMMITTED, SQL Server 2000 aborts the query and returns the following error message:

Server: Msg 601, Level 12, State 3, Line 14
Could not continue scan with NOLOCK due to data movement.

Back to the top

CAUSE

This behavior may occur when a row in a table is deleted between the time SQL Server reads the location of the row from an index and the time SQL Server fetches the row.

Back to the top

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 (http://support.microsoft.com/kb/290211/ ) How to obtain the latest SQL Server 2000 service pack

Back to the top

Hotfix information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

Date		Time	Version		Size		 File name
-----------------------------------------------------------------------------

07-Feb-2003	16:14			  786,432 bytes  Distmdl.ldf
07-Feb-2003	16:14			2,359,296 bytes  Distmdl.mdf
29-Jan-2003	12:25			      180 bytes  Drop_repl_hotfix.sql
31-Jan-2003	11:02	2000.80.763.0	1,557,052 bytes	 Dtsui.dll     
29-Jan-2003	15:48			  746,470 bytes  Instdist.sql
29-Jan-2003	12:25	                    1,402 bytes  Inst_repl_hotfix.sql
07-Feb-2003	17:10	2000.80.765.0      90,692 bytes  Msgprox.dll  
29-Jan-2003	11:48	7.0.9466.0        344,064 bytes  Msvcr70.dll      
29-Jan-2003	11:48	2000.80.722.0      57,904 bytes  Osql.exe         
07-Feb-2003	14:23                   1,065,895 bytes  Replmerg.sql
07-Feb-2003	17:10	2000.80.765.0	  221,768 bytes  Replprov.dll     
07-Feb-2003	17:10	2000.80.765.0     307,784 bytes  Replrec.dll      
29-Jan-2003	15:48		   	1,084,318 bytes  Replsys.sql
29-Jan-2003	11:48	2000.80.534.0	  127,548 bytes  Sqdedev.dll    
07-Feb-2003	17:10	2000.80.765.0	  176,696 bytes  Sqlmap70.dll  
07-Feb-2003	17:10	2000.80.765.0	   57,920 bytes  Sqlrepss.dll    
14-Feb-2003	13:22	2000.80.769.0   7,512,145 bytes  Sqlservr.exe   
29-Jan-2003	11:48	2000.80.194.0     180,290 bytes  Sqlunirl.dll     
07-Feb-2003	17:10	2000.80.765.0      45,644 bytes  Sqlvdi.dll   
07-Feb-2003	17:10	2000.80.765.0      82,492 bytes  Ssnetlib.dll

Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

Back to the top

MORE INFORMATION

After you install the hotfix, you can use trace flag 9134 to change the SQL Server behavior. To enable trace flag 9134 so that it is turned on whenever SQL Server starts, add the trace flag as a SQL Server startup parameter. To add trace flag 9134 as a SQL Server startup parameter, follow these steps:

In SQL Server Enterprise Manager, right-click the server that is running SQL Server, and then click Properties.
On the General tab, click Startup Parameters.
In the Parameter text box, type -T9134, click Add, and then click OK.
In the SQL Server Properties(Configure) - <Server Name> dialog box, click OK.
Restart the SQL Server service for the new parameter to take effect.

As an alternative, you can run the following query in Query Analyzer to dynamically turn on the trace flag:

DBCC TRACEON (9134, -1)

However, if you do not add trace flag 9134 as a startup parameter, the effect of the DBCC TRACEON command is lost when the SQL Server service restarts.

When trace flag 9134 is turned on and a row is deleted or modified between the time SQL Server reads the location of the row and the time it fetches the row through a bookmark lookup, the query will not abort or return Error 601. Instead, SQL Server ignores the row that cannot be located and it continues to scan for additional rows that meet the query criteria. Therefore, the query execution continues; however, the results that SQL Server returns may not include rows that were deleted or moved during the query execution.

Note The information in this article and the trace flag only apply to a query that is being run with the Transaction Isolation Level set to READ UNCOMMITTED. The information does not apply to a query that is run with the NOLOCK optimizer hint. In that case, the behavior remains unchanged from what is described in the "Symptoms" section.

Also, the trace flag does not affect the fact that other errors and data inconsistencies can occur when reading uncommitted data. For more information about the isolation levels supported by SQL Server and the effect of using each isolation level, see the "Locking" topic in SQL Server Books Online.

Microsoft does not recommend the use of this trace flag unless all the following apply:

The application in use requires a READ UNCOMMITTED isolation level.
The application in use is experiencing a large number of 601 errors.
You understand that when you use the trace flag, the rows that were deleted or moved during the query execution are ignored.

Note Before you use the READ UNCOMMITTED isolation level, you must understand its functionality. For more information about the READ UNCOMMITTED isolation level, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms171885.aspx (http://msdn2.microsoft.com/en-us/library/ms171885.aspx)

Back to the top

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

235880 (http://support.microsoft.com/kb/235880/EN-US/ ) INF: Optimizer Hint NOLOCK or Isolation Level READ UNCOMMITTED Generates Error 605

For more information, visit the following MSDN Web sites:

Troubleshooting Error 601
http://msdn2.microsoft.com/en-us/library/aa258726(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa258726(SQL.80).aspx)

SET TRANSACTION ISOLATION LEVEL
http://msdn2.microsoft.com/en-us/library/aa259216(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa259216(SQL.80).aspx)

Back to the top