FIX: A query may fail with retail assertion when you use the NOLOCK hint or the READ UNCOMMITTED isolation level (827714)
The information in this article 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 Evaluation Edition
- Microsoft SQL Server 2000 Workgroup Edition
- Microsoft SQL Server 2000 Desktop Engine (MSDE)
- Microsoft SQL Server 2000 Developer Edition 64 bit
- Microsoft SQL Server 2000 Enterprise Edition 64-bit
SYMPTOMSWhen 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
CAUSEWhen 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."RESOLUTIONService 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. WORKAROUNDTo 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.STATUSMicrosoft 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 INFORMATIONAfter 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: 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
| Modification Type: | Minor | Last Reviewed: | 11/22/2005 |
|---|
| Keywords: | kbHotfixServer kbQFE kbQuery kberrmsg kbTSQL kbSQLServ2000preSP4fix kbfix kbbug KB827714 kbAudDeveloper |
|---|
|