Microsoft KB Archive/827714

= FIX: A query may fail with retail assertion when you use the NOLOCK hint or the READ UNCOMMITTED isolation level =

Article ID: 827714

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Workgroup Edition
 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition 64-bit

-





SYMPTOMS
When you run a query with the NOLOCK hint or with the transaction isolation level set to READ UNCOMMITTED, and a concurrent user is deleting rows from the table that is accessed by the query, the query may fail with a retail assertion, and you may receive an assertion error message that is similar to the following:

Msg 3624

Location: :\sql\ntdbms\storeng\drs\include\record.inl:1447

Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW

SPID: 63

Process ID: 1776

-or-

Msg 3624

Location: recbase.cpp:1378

Expression: m_offBeginVar < m_SizeRec

SPID: 55

Process ID: 1776



CAUSE
When the Microsoft SQL Server engine tries to search for a row when a query uses the NOLOCK hint or the READ UNCOMMITTED transaction isolation level, the target row may be deleted. Before you install this fix, SQL Server 2000 may incorrectly handle this situation and the query may fail with the assertion that is mentioned in the &quot;Symptoms&quot; section of this article.

Note The READ UNCOMMITTED transaction isolation level is also known as a &quot;dirty read.&quot;



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

290211 How to obtain the latest SQL Server 2000 service pack

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 --  31-May-2003  06:15  2000.80.818.0     78,400  Console.exe 24-Jun-2003 12:31  2000.80.818.0     33,340  Dbmslpcn.dll 24-Apr-2003 13:42                   786,432  Distmdl.ldf 24-Apr-2003 13:42                 2,359,296  Distmdl.mdf 29-Jan-2003 13:25                       180  Drop_repl_hotfix.sql 26-Aug-2003 07:46  2000.80.854.0    528,960  Dtspump.dll 23-Jun-2003 10:10  2000.80.837.0  1,557,052  Dtsui.dll 23-Jun-2003 10:10  2000.80.837.0    639,552  Dtswiz.dll 23-Apr-2003 14:21                   747,927  Instdist.sql 02-May-2003 13:26                     1,581  Inst_repl_hotfix.sql 07-Feb-2003 18:10  2000.80.765.0     90,692  Msgprox.dll 31-Mar-2003 13:37                     1,873  Odsole.sql 04-Apr-2003 13:16  2000.80.800.0     62,024  Odsole70.dll 07-May-2003 08:11  2000.80.819.0     25,144  Opends60.dll 02-Apr-2003 09:18  2000.80.796.0     57,904  Osql.exe 02-Apr-2003 10:45  2000.80.797.0    279,104  Pfutil80.dll 04-Aug-2003 05:47                   550,780  Procsyst.sql 22-May-2003 10:27                    19,195  Qfe469571.sql 11-Jul-2003 04:34                 1,084,147  Replmerg.sql 04-Apr-2003 09:23  2000.80.798.0    221,768  Replprov.dll 07-Feb-2003 18:10  2000.80.765.0    307,784  Replrec.dll 13-Aug-2003 03:58                 1,086,797  Replsys.sql 13-Aug-2003 03:58                   986,603  Repltran.sql 29-Jul-2003 07:43  2000.80.819.0    492,096  Semobj.dll 31-May-2003 05:57  2000.80.818.0    172,032  Semobj.rll 05-Aug-2003 08:36                   127,884  Sp3_serv_uni.sql 31-May-2003 12:31  2000.80.818.0  4,215,360  Sqldmo.dll 07-Apr-2003 05:14                    25,172  Sqldumper.exe 19-Mar-2003 05:50  2000.80.789.0     28,672  Sqlevn70.rll 01-Jul-2003 11:48  2000.80.834.0    180,736  Sqlmap70.dll 02-Sep-2003 14:26  2000.80.857.0    188,992  Sqlmmc.dll 02-Sep-2003 10:33  2000.80.857.0    479,232  Sqlmmc.rll 07-Feb-2003 18:10  2000.80.765.0     57,920  Sqlrepss.dll 02-Sep-2003 14:27  2000.80.857.0  7,598,161  Sqlservr.exe 25-Jul-2003 09:14  2000.80.845.0    590,396  Sqlsort.dll 07-Feb-2003 18:10  2000.80.765.0     45,644  Sqlvdi.dll 24-Jun-2003 12:31  2000.80.818.0     33,340  Ssmslpcn.dll 31-May-2003 12:31  2000.80.818.0     82,492  Ssnetlib.dll 31-May-2003 12:31  2000.80.818.0     25,148  Ssnmpn70.dll 31-May-2003 12:31  2000.80.818.0    158,240  Svrnetcn.dll 31-May-2003 06:29  2000.80.818.0     76,416  Svrnetcn.exe 30-Apr-2003 11:22  2000.80.816.0     45,132  Ums.dll 01-Jul-2003 11:49  2000.80.834.0     98,816  Xpweb70.dll 23-Jun-2003 10:10  2000.80.837.0  1,557,052  Dtsui.dll 23-Jun-2003 10:10  2000.80.837.0    639,552  Dtswiz.dll 02-Apr-2003 09:18  2000.80.796.0     57,904  Osql.exe 02-Apr-2003 10:45  2000.80.797.0    279,104  Pfutil80.dll 29-Jul-2003 07:43  2000.80.819.0    492,096  Semobj.dll 31-May-2003 05:57  2000.80.818.0    172,032  Semobj.rll 31-May-2003 12:31  2000.80.818.0  4,215,360  Sqldmo.dll 07-Apr-2003 05:14                    25,172  Sqldumper.exe 02-Sep-2003 14:26  2000.80.857.0    188,992  Sqlmmc.dll 02-Sep-2003 10:33  2000.80.857.0    479,232  Sqlmmc.rll 31-May-2003 12:31  2000.80.818.0    158,240  Svrnetcn.dll 31-May-2003 06:29  2000.80.818.0     76,416  Svrnetcn.exe

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



WORKAROUND
To avoid this problem, do not use the NOLOCK hint or the READ UNCOMITTED transaction isolation level, or do not run the query when the affected rows are deleted by other users.



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



MORE INFORMATION
After you apply the fix that is mentioned in the &quot;Resolution&quot; section of this article, and then you try to access the deleted records, you may receive the following error message instead of a retail assertion:

Msg 601

Could not continue scan with NOLOCK due to data movement

In some situations, the query processor can internally handle a 601 error and can silently skip the row that led to the 601 error. However, in other cases, this behavior cannot happen and the query processor must fail the query with a 601 error. Applications that use the NOLOCK hint or the READ UNCOMMITTED transaction isolation level must always account for a possible 601 error as part of typical result processing and must take the appropriate action that is described in the SQL Server Books Online under the description for &quot;Error 601.&quot; For more information about error 601, visit the following topic in SQL Server 2000 Books Online:

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

Before you install this hotfix, the incorrect clustered index row may have been returned to the query processor. This behavior did not previously cause an assertion failure, but you may notice an error 601 in this situation with the hotfix installed. These anomalies are a result of using the NOLOCK hint or the READ UNCOMMITTED isolation level while users are updating or deleting the data at the same time.

When the problem that is mentioned in the &quot;Symptoms&quot; section of this article occurs, you may notice that a stack dump is generated in the SQL Server error log and the stack dump is followed by the assertion expression. The generated stack dump may be similar to the following short stack dump:

 Short Stack Dump 00871255 Module(sqlservr+00471255) (CStackDump::GetContextAndDump+0000002E) 00872C81 Module(sqlservr+00472C81) (stackTrace+0000021D) 009286FA Module(sqlservr+005286FA) (utassert_fail+000002E3) 007F12E8 Module(sqlservr+003F12E8) (RecBase::ReSizeVar+00000069) 00404DD9 Module(sqlservr+00004DD9) (RecBase::Resize+0000005B) 00429024 Module(sqlservr+00029024) (RowsetSS::GetColumns+000000B6) 0042AE29 Module(sqlservr+0002AE29) (RowsetSS::GetData+00000071) 00528CFF Module(sqlservr+00128CFF) (CValSSTable::GetDataWithBuffer+00000031) 00528CD3 Module(sqlservr+00128CD3) (GetDataWithBuffer+00000028) 00419B19 Module(sqlservr+00019B19) (CEs::GeneralEval4+00000075) 0042C37D Module(sqlservr+0002C37D) (CQScanHashMatch::Iterate+000015B4) 0042D80E Module(sqlservr+0002D80E) (CQScanHashMatch::Open+00000065) 0043D029 Module(sqlservr+0003D029) (CQScanNLJoin::Open+00000014) 0042970E Module(sqlservr+0002970E) (CQScan::Open+0000001C) 004297EB Module(sqlservr+000297EB) (CQScanStreamAggregate::Open+0000000E) 0041DB69 Module(sqlservr+0001DB69) (CQueryScan::Startup+0000010D) 0041955F Module(sqlservr+0001955F) (CStmtQuery::ErsqExecuteQuery+0000026B) 00428562 Module(sqlservr+00028562) (CStmtSelect::XretExecute+00000229) 00410855 Module(sqlservr+00010855) (CMsqlExecContext::ExecuteStmts+000003DD) 0040FEB4 Module(sqlservr+0000FEB4) (CMsqlExecContext::Execute+000001B6) 0041164F Module(sqlservr+0001164F) (CSQLSource::Execute+00000331) 004BA63E Module(sqlservr+000BA63E) (CStmtExec::XretLocalExec+0000014D Line 4058+0000002F) 004BA4DA Module(sqlservr+000BA4DA) (CStmtExec::XretExecute+0000031A Line 3990+00000017) 00410855 Module(sqlservr+00010855) (CMsqlExecContext::ExecuteStmts+000003DD) 0040FEB4 Module(sqlservr+0000FEB4) (CMsqlExecContext::Execute+000001B6) 0041164F Module(sqlservr+0001164F) (CSQLSource::Execute+00000331) 0053EC96 Module(sqlservr+0013EC96) (language_exec+000003E1) 0041226E Module(sqlservr+0001226E) (process_commands+000000EC) 41073379 Module(UMS+00003379) (ProcessWorkRequests+0000024A) 41073071 Module(UMS+00003071) (ThreadStartRoutine+000000BD) 7800C9EB Module(MSVCRT+0000C9EB) (exception::exception+0000000E) 77E8B2D8 Module(KERNEL32+0000B2D8) (lstrcmpiW+000000B7)

2003-08-19 10:56:52.97 spid62   SQL Server Assertion: File: , line=1378 Failed Assertion = 'm_offBeginVar < m_SizeRec'.

Note Some parts of the stack dump information and the assertion expression may be different because of the following reasons:
 * The query execution plan that is used by the query processor in order to run the query is different.
 * The difference in the time of the row deletion and the query evaluation.

If the condition that is mentioned in this article does not apply, the assertion that you encountered in the Recbase.cpp file may have occurred because of another reason.

For more information about assertion errors that are reported from Recbase.cpp, click the following article number to view the article in the Microsoft Knowledge Base:

828337 An assertion in the Recbase.cpp file or the Record.inl file may occur when an operation is performed on an instance of SQL Server

Keywords: kberrmsg kbbug kbfix kbtsql kbqfe kbsqlserv2000presp4fix kbquery kbhotfixserver KB827714

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.