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:
-or-
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."
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 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 "Applies to" 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 "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:
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:
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 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
