Article ID: 827714 - Last Review: November 2, 2007 - Revision: 4.5

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

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

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: Drive:\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

Back to the top

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 "Symptoms" section of this article.

Note The READ UNCOMMITTED transaction isolation level is also known as a "dirty read."

Back to the top

RESOLUTION

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

Back to the top

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.

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 apply the fix that is mentioned in the "Resolution" 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 "Error 601." 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 (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 "Symptoms" 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: <recbase.cpp>, 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 (http://support.microsoft.com/kb/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

Back to the top