FIX : Rollback to Savepoint Causes Assertion and Errors (294902)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q294902
BUG #: 352501 (SHILOH_bugs)

SYMPTOMS

If a client allocates the first, root, and firstIAM pages of a table within a user transaction, and the client uses the ROLLBACK command to rollback to a savepoint within that same transaction, error message 3624 (retail assertion) may occur and the client connection to SQL Server may be terminated. The following error message is returned in Query Analyzer if Query Analyzer is used as the client:
Server: Msg 3624, Level 20, State 1, Line 1

Location: page.cpp:2777
Expression: rowLength >= offset + deleteLength
SPID: 51
Process ID: 596

Connection Broken
In addition, errors 3448, 3314 and stack dumps may appear in the SQL Server error log file.

CAUSE

In SQL Server 2000 tables are created as empty tables. This means that when a table is created no pages are allocated to that table. Pages are allocated to a table when data is inserted into the table.

Therefore, the following scenario may lead to an assertion and errors messages:
  • A user transaction is started with the BEGIN TRAN statement.
  • A table is created with the CREATE TABLE statement.
  • A savepoint is specified within the transaction.
  • Data Manipulation Language (DML) statements are applied to the table mentioned in the second item of this list, thereby allocating the first, root, and firstIAM pages of that table.
  • The ROLLBACK command is used to rollback to the savepoint mentioned in the third item of this list.
  • Additional DML statements are applied to the table mentioned in the second item of this list.
This problem occurs when the additional DML statements mentioned in the last item of the preceding list do not reallocate pages for the table. DML statements do not reallocate pages for the table because, based on information from an invalid SQL Server cache, they think that pages have already been allocated to the table. The SQL Server cache is not aware that the DML statements that had allocated pages for the table were rolled back.

RESOLUTION

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

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

MORE INFORMATION

A savepoint is a marker within a transaction that defines a location to which a transaction can return, if part of the transaction is conditionally canceled. For more information about savepoint, refer to the "SAVE TRANSACTION" topic in SQL Server Books Online.

To reproduce the problem run the following script: 
use master
go
create database repro
go
use repro
go
-- use tempdb
go
begin tran
go
create table t1 (i1 int null)
create table t2 (i2 int null)
go
-- SIMPLE WITH CHECK
--
create view vt as
  select * from t1 where i1 between 1 and 10
  with check option
go

save tran empty
go
insert vt values(1)	-- OK
insert vt values(5)	-- OK
insert vt values(10)	-- OK
insert vt values(0)	-- FAIL (<1)
insert vt values(11)	-- FAIL (>10)
insert vt values(null)	-- FAIL (null)
go
rollback tran empty
go

-- WITH CHECK JOIN
--
create view vtJN as
  select * from t1 inner join t2 on t1.i1 = t2.i2
  with check option
go

insert vtJN(i1) values(1)	-- FAIL (no match)
insert t2(i2) values(1)		-- OK (prime row)
insert vtJN(i1) values(1)	-- FAIL (no match)
Here is the short stack dump for this assertion on retail build 8.00.194: 
-------------------------------------------------------------------------------
Short Stack Dump
77F97AC6 Module(ntdll+00017AC6) (ZwGetContextThread+0000000B)
0092569E Module(sqlservr+0052569E) (utassert_fail(enum UTASSERT_TYPE,char const *,char const *,int,char const *,...)+000002E9)
00843FAC Module(sqlservr+00443FAC) (Page::ModifyRow(int,unsigned int,unsigned int,unsigned int,void const *,unsigned int,class XdesId const *)+000000D0)
0042DEAB Module(sqlservr+0002DEAB) (PageRef::ModifyRow(struct BUF *,int,unsigned int,unsigned int,unsigned int,void const *,enum ETabStatus,int,class XDES *,void const *,unsigned int)+0000033A)
0041CC24 Module(sqlservr+0001CC24) (AllocationReq::AllocatePages(void)+00000690)
0041B777 Module(sqlservr+0001B777) (AllocationReq::Allocate(void)+00000078)
0043EF30 Module(sqlservr+0003EF30) (AllocateHeapPage(struct CINSTABLE *)+0000007D)
0080C76D Module(sqlservr+0040C76D) (AllocatePageForInsert(class SDES *,struct INDEX *)+000000CD)
0043461A Module(sqlservr+0003461A) (FreeSpaceScan::GetNextPage(struct BUF * *)+000003B0)
00434A1B Module(sqlservr+00034A1B) (FindExistingFreeSpace(class SDES *,struct INDEX *,unsigned int,int &)+000000B8)
004348AD Module(sqlservr+000348AD) (ncinsert(class SDES * volatile,unsigned char *,int,class Scan_rid &)+00000081)
00418543 Module(sqlservr+00018543) (rowinsert(class SDES * volatile,void *,int,unsigned char)+00000118)
004185E0 Module(sqlservr+000185E0) (insert(class SDES *,void *,int)+00000013)
00433D8F Module(sqlservr+00033D8F) (RowsetSS::InsertRow(unsigned long,unsigned long,void *,unsigned long *)+00000201)
0042EE6A Module(sqlservr+0002EE6A) (CValRow::SetDataX(unsigned long *,class CXVariant *)+00000025)
00431C2F Module(sqlservr+00031C2F) (CEs::FastMoveEval(unsigned long *,class CEsExec *,class CXVariant *)+00000039)
00433FD6 Module(sqlservr+00033FD6) (CQScanScalarInsert::GetRow(unsigned long *,unsigned long *)+00000035)
0042C85E Module(sqlservr+0002C85E) (CQScanNLJoin::GetRow(unsigned long *,unsigned long *)+00000112)
004C09E1 Module(sqlservr+000C09E1) (CQScanAssert::GetRow(unsigned long *,unsigned long *)+0000001B)
00427985 Module(sqlservr+00027985) (CQueryScan::GetRow(unsigned long *,unsigned long *)+00000014)
00426F64 Module(sqlservr+00026F64) (CStmtQuery::ErsqExecuteQuery(class CMsqlExecContext *,class CEsComp const *,class CEsComp const *,unsigned long *,int,int)const +000003C4)
0042EA36 Module(sqlservr+0002EA36) (CStmtDML::XretExecuteNormal(class CMsqlExecContext *)const +000002F0)
0042E82D Module(sqlservr+0002E82D) (CStmtDML::XretExecute(class CMsqlExecContext *)const +0000001C)
004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E)
00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7)
00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343)
005A683F Module(sqlservr+001A683F) (CStmtPrepQuery::XretExecute(class CMsqlExecContext *)const +00000211)
004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E)
00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7)
00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343)
00459A54 Module(sqlservr+00059A54) (language_exec(struct srv_proc *)+000003C8)
004175D8 Module(sqlservr+000175D8) (process_commands(struct srv_proc *)+000000E0)
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class UmsWorkQueue *)+00000264)
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)+000000BC)
7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE)
77E92CA8 Module(KERNEL32+00012CA8) (CreateFileA+0000011B)
-------------------------------------------------------------------------------
Here is a short stack dump with error 3448 on retail build 8.00.194: 
-------------------------------------------------------------------------------
Short Stack Dump
77F97AC6 Module(ntdll+00017AC6) (ZwGetContextThread+0000000B)
00926069 Module(sqlservr+00526069) (ex_raise2(int,int,int,int,void *,char *)+00000174)
0046C368 Module(sqlservr+0006C368) (ex_raise(int,int,int,int,...)+00000063)
008686FE Module(sqlservr+004686FE) (FixPageForUndo(class PageRef &,class DBTABLE *,class LSN const &,enum LatchBase::LATCH_TYPE,class PageLog const &)+00000098)
00863962 Module(sqlservr+00463962) (XdesRMReadWrite::UndoPageOperation(class PageLog const *,class LSN const &,int)+0000032B)
00440FBA Module(sqlservr+00040FBA) (XdesRMReadWrite::RollbackToLsn(class LSN const &,class LogIterBackLink &,class IndexErrorTable *,int)+000002EB)
00865932 Module(sqlservr+00465932) (XdesRMFull::RollbackNested(int)+00000143)
0086062F Module(sqlservr+0046062F) (XactRM::RollbackNestedXact(int)+0000009E)
0085DEB5 Module(sqlservr+0045DEB5) (FullXactImpBase::RollbackNestedXact(int)+00000037)
0064089F Module(sqlservr+0024089F) (CMsqlXact::RollbackNestedXact(enum CMsqlXact::ReadWriteMode,int)+00000017)
006403DD Module(sqlservr+002403DD) (CAutoMsqlXact::RollbackNestedXact(void)+00000032)
0062F2D1 Module(sqlservr+0022F2D1) (CStmtDML::XretExecuteNormal(class CMsqlExecContext *)const +00000456)
0042E82D Module(sqlservr+0002E82D) (CStmtDML::XretExecute(class CMsqlExecContext *)const +0000001C)
004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E)
00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7)
00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343)
005A683F Module(sqlservr+001A683F) (CStmtPrepQuery::XretExecute(class CMsqlExecContext *)const +00000211)
004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E)
00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7)
00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343)
00459A54 Module(sqlservr+00059A54) (language_exec(struct srv_proc *)+000003C8)
004175D8 Module(sqlservr+000175D8) (process_commands(struct srv_proc *)+000000E0)
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class UmsWorkQueue *)+00000264)
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)+000000BC)
7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE)
77E92CA8 Module(KERNEL32+00012CA8) (CreateFileA+0000011B)
-------------------------------------------------------------------------------
2001-03-30 03:08:44.56 spid51    Error: 3448, Severity: 21, State: 1
2001-03-30 03:08:44.56 spid51    Could not undo log record (5:23:99), for transaction ID (0:157), on page (1:76), database 'repro' (database ID 7). Page information: LSN = (5:23:96), type = 1. Log information: OpCode = 4, context 1..
2001-03-30 03:08:44.56 spid51    ex_raise2: Exception raised, major=34, minor=48, severity=25, attempting to create symptom dump
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbBug kbfix kbSQLServ2000sp1fix KB294902
©2003 Microsoft Corporation. All rights reserved.