unexplained ROLLBACK

sql v2k, this procedure has been in place for months and months, works just fine, from my perspective.  last week, however, it began to fail.  understand, it is invoked from a middle tier -- short story, the tier creates three large csv files, and then executes this procedure when they are complete, in order to load the data.  two of the files are loaded to working tables, flagged for dupes, and then loaded into the real tables (w/out the dupes).  indices are dropped/recreated on each.  the last of the 3 files is just bulk loaded straight into the real table.  that's it.

as of wednesday, it began to fail.  we have to terminate the app (kill spid), and then execute the proc ourselves.  it always works fine.  last night, for example, i get online at about 9PM, i see the files on disk, i check sp_who2, that login is active, EXECUTE is the command, but ROLLBACK is the status.  no clear reason for the ROLLBACK, so I kill the spid, wait for the ROLLBACK to clear, and I execute the procedure.  it runs perfectly.

so... i assume something has changed on the front end, and i intend to work w/them today in order to find it, and correct.

i just want to toss this out there, though.... it's my understanding that a ROLLBACK is not going to occur unless it is coded to (error handling), or the transaction is interrupted, in some way, in between the client and SQL.  Am I correct in that assumption, and does anybody see any reason for this?  Again, executed from SQL, it always completes fine.  From the middle tier, however, status becomes 'ROLLBACK' and never completes.  (this procedure has been in production for months, the tSQL has not been changed)

Any ideas?


CREATE PROC procname
AS
SET NOCOUNT ON
BEGIN
 
print 'Clean out OrderEvents Working.' + convert(varchar(30),getdate(),121)
TRUNCATE TABLE OrderEventsWorking

print 'Load OrderEvents ' + convert(varchar(30),getdate(),121)
    --load into Database.dbo.OrderEventsWorking
    DECLARE @fileExists1 INT
    EXEC master.dbo.xp_fileexist '\\SERVERNAME\Database\orders.csv', @fileExists1 OUTPUT
    IF (@fileExists1 = 1)
    BEGIN
        BULK INSERT Database.dbo.OrderEventsWorking
        FROM '\\SERVERNAME\Database\orders.csv'
        WITH(FORMATFILE = 'E:\MSSQL\Tools\orders.fmt',FIELDTERMINATOR = ',',ROWTERMINATOR = '\r',FIRSTROW=1,TABLOCK,ROWS_PER_BATCH = 100000)
    END
    ELSE PRINT 'orders.csv does not exist.'
 
print 'Check Internal Dupes Database.dbo.OrderEventsWorking ' + convert(varchar(30),getdate(),121)
    --Check and flag any dupes... Internally first (keeping the most recent)
    update Database.dbo.OrderEventsWorking set status_flag = 1
    where Database.dbo.OrderEventsWorking.eventid <> (select max(d.eventid) from Database.dbo.OrderEventsWorking d
    where d.orderno = Database.dbo.OrderEventsWorking.orderno and d.MsgIndex = Database.dbo.OrderEventsWorking.MsgIndex)
    OPTION (MAXDOP 1)
 
print 'Check Prior Loaded Dupes Database.dbo.OrderEventsWorking ' + convert(varchar(30),getdate(),121)
    --Check and flag any dupes... Then Compare with previously loaded
    update Database.dbo.OrderEventsWorking set status_flag = 1
    from Database.dbo.OrderEventsWorking
    INNER JOIN OrderEvents D ON D.OrderNo = Database.dbo.OrderEventsWorking.OrderNo
    and D.MsgIndex = Database.dbo.OrderEventsWorking.MsgIndex
    OPTION (MAXDOP 1)

-------------------------------------------
/* drop indexes on OrderEvents here if needed   */
-------------------------------------------
print 'Dropping OrderEvents indices....' + convert(varchar(30),getdate(),121)
.......... five indices are dropped, if exists

print 'Insert From Database.dbo.OrderEventsWorking ' + convert(varchar(30),getdate(),121)
    --Insert rows into Database.dbo.OrderEvents from Database.dbo.OrderEventsWorking where OrderNo,MsgIndex doesn't already exist...
    --Set rowcount to 100000 to limit number of inserts per batch
          begin transaction
      while @@rowcount >0
      set rowcount 100000
              
      INSERT INTO Database.dbo.OrderEvents (EventTime,MsgType,MsgIndex,OrderNo,ClorID,ExchangeID,GWID,
        EndPoint,TraderID,AccountID,Client,Symbol,Price,Side,Quantity,TimeInForce,ExecInstruction)
             SELECT w.EventTime,w.MsgType,w.MsgIndex,w.OrderNo,w.ClorID,w.ExchangeID,w.GWID,
        w.EndPoint,w.TraderID,w.AccountID,w.Client, w.Symbol,w.Price,w.Side,w.Quantity,w.TimeInForce,w.ExecInstruction
              FROM Database.dbo.OrderEventsWorking w WITH (NOLOCK)
              WHERE w.status_flag = 0
      OPTION (MAXDOP 1)

      select @@rowcount
      set rowcount 0
      if @@error = 0
      begin
            commit tran
      end
      else
            rollback tran

print 'Ged rid of all but the violations.' + convert(varchar(30),getdate(),121)
begin transaction
while @@rowcount >0
set rowcount 100000
     DELETE Database.dbo.OrderEventsWorking WHERE status_flag <> 1 OPTION (MAXDOP 1)
select @@rowcount
set rowcount 0
if @@error = 0
begin
      commit tran
end
else
      rollback tran


----------------------------------------------
/* Rebuild indexes on OrderEvents here if needed   */
----------------------------------------------
print 'Rebuild OrderEvents Indexes if needed ' + convert(varchar(30),getdate(),121)
.....five indices are recreated
 
print 'Clean out CancelEvents Working.' + convert(varchar(30),getdate(),121)
TRUNCATE TABLE CancelEventsWorking

print 'Load CancelEvents ' + convert(varchar(30),getdate(),121)
    --load into Database.dbo.CancelEventsWorking
    DECLARE @fileExists2 INT
    EXEC master.dbo.xp_fileExist '\\SERVERNAME\Database\cancels.csv',@fileExists2 OUTPUT
    IF (@fileExists2 = 1)
    BEGIN
        BULK INSERT Database.dbo.CancelEventsWorking
        FROM '\\SERVERNAME\Database\cancels.csv'
        WITH(FORMATFILE = 'E:\MSSQL\Tools\cancels.fmt',FIELDTERMINATOR = ',',ROWTERMINATOR = '\r',FIRSTROW=1,TABLOCK,ROWS_PER_BATCH = 100000)
    END
    ELSE PRINT 'cancels.csv does not exist.'
   
print 'Check Internal Dupes Database.dbo.CancelEventsWorking ' + convert(varchar(30),getdate(),121)
    --Check and flag any dupes... Internally first (keeping the most recent)
    update Database.dbo.CancelEventsWorking set status_flag = 1
    where Database.dbo.CancelEventsWorking.eventid <> (select max(d.eventid)
    from Database.dbo.CancelEventsWorking d
      where d.orderno = Database.dbo.CancelEventsWorking.orderno
    and d.MsgIndex = Database.dbo.CancelEventsWorking.MsgIndex)
      OPTION (MAXDOP 1)
 
print 'Check Prior Loaded Dupes Database.dbo.CancelEventsWorking ' + convert(varchar(30),getdate(),121)
    --Check and flag any dupes... Then Compare with previously loaded
    update Database.dbo.CancelEventsWorking set status_flag = 1
    from Database.dbo.CancelEventsWorking
    INNER JOIN CancelEvents D
        ON D.OrderNo = Database.dbo.CancelEventsWorking.OrderNo
      and D.MsgIndex = Database.dbo.CancelEventsWorking.MsgIndex
        OPTION (MAXDOP 1)
 
-------------------------------------------
/* drop indexes on CancelEvents here if needed  */
-------------------------------------------
print 'Dropping CancelEvents indices....' + convert(varchar(30),getdate(),121)
..........four indices are dropped, if exists

print 'Insert From Database.dbo.CancelEventsWorking ' + convert(varchar(30),getdate(),121)
    --Insert rows into CancelEvents from Database.dbo.CancelEventsWorking where OrderNo, MsgIndex doesn't already exist
    --Set rowcount to 100000 to limit number of inserts per batch
         begin transaction
          while @@rowcount >0
          set rowcount 100000

              INSERT INTO Database.dbo.CancelEvents (EventTime,MsgType,MsgIndex,OrderNo,ClorID,GWID)
              SELECT w.EventTime,w.MsgType,w.MsgIndex,w.OrderNo,w.ClorID,w.GWID
              FROM Database.dbo.CancelEventsWorking w WITH (NOLOCK)
              WHERE status_flag = 0
            OPTION (MAXDOP 1)

          select @@rowcount
      set rowcount 0
      if @@error = 0
      begin
            commit tran
      end
      else
            rollback tran

print 'Ged rid of all but the violations.' + convert(varchar(30),getdate(),121)
begin transaction
while @@rowcount >0
set rowcount 100000
       DELETE Database.dbo.CancelEventsWorking WHERE status_flag <> 1 OPTION (MAXDOP 1)
select @@rowcount
set rowcount 0
if @@error = 0
begin
      commit tran
end
else
      rollback tran

----------------------------------------------
/* rebuild indexes on CancelEvents here if needed  */
----------------------------------------------
print 'Rebuild CancelEvents Indexes if needed ' + convert(varchar(30),getdate(),121)
   ....four indices are recreated
 
    DECLARE @fileExists3 INT
    EXEC master.dbo.xp_fileexist '\\SERVERNAME\Database\Rejects.csv', @fileExists3 OUTPUT
    IF (@fileExists3 = 1)
    BEGIN
        BULK INSERT Database.dbo.RejectEvents
        FROM '\\SERVERNAME\Database\Rejects.csv'
        WITH(FORMATFILE = 'E:\MSSQL\Tools\Rejects.fmt',FIELDTERMINATOR = ',',ROWTERMINATOR = '\n',FIRSTROW=1,TABLOCK,ROWS_PER_BATCH = 100000)
    END
    ELSE PRINT 'Rejects.csv does not exist.'

print 'load auditing data into processing ' + convert(varchar(30),getdate(),121)
   --load auditing data into processlog
    DECLARE @maxid1  int
    DECLARE @maxid2  int
    DECLARE @maxid3  int
    SELECT @maxid1 = IsNull (max(eventID), 0) from Database.dbo.OrderEvents
    SELECT @maxid2 = IsNull (max(eventID), 0) from Database.dbo.CancelEvents  
    SELECT @maxid3 = IsNull (max(eventID), 0) from Database.dbo.RejectEvents
 
    INSERT Database.dbo.ProcessLog (EventIDStart,EventIdEnd,InsertTime,FileName)
    SELECT @maxid1 + 1, (select IsNull (max(EventID), 0)  from Database.dbo.OrderEvents),  getdate(),'Orders'
    INSERT Database.dbo.ProcessLog (EventIDStart,EventIdEnd,InsertTime,FileName)
    SELECT @maxid2 + 1, (select IsNull (max(EventID), 0)  from Database.dbo.CancelEvents),  getdate(),'Cancels'
    INSERT Database.dbo.ProcessLog (EventIDStart,EventIDEnd,InsertTime,FileName)
    SELECT @maxid3 + 1, (select IsNull (max(EventID), 0)  from Database.dbo.RejectEvents),  getdate(),'Rejects'

print 'Final Archive of raw data ' + convert(varchar(30),getdate(),121)
    --exec the bat file to rename/relocate the current .csv's.
    EXEC master..xp_cmdshell '\\SERVERNAME\E$\MSSQL\Tools\Database.bat', no_output
 
END

SET NOCOUNT OFF
GO
LVL 17
dbaSQLAsked:
Who is Participating?
 
Cedric_DConnect With a Mentor Commented:
ROLLBACK is a normal process on any error.

So you should look at error "Unknown marker", why is it occured.
You may want to locate specific statement which caused it.

Can you execute just single statement on Python?

query="CREATE INDEX ix1 ....... "

try: without brakets, without WITH, without ON FILEGROUP etc..
0
 
Cedric_DCommented:
Rollback is automatically executed upon unexpected disconnection of MSSQL session. Including, by timeout.

Maybe it was in place? Only one-time event?
0
 
dbaSQLAuthor Commented:
I wish it were a one timer.... each night from Wednesday until last night, I actually thought the same -- just something odd, figured it was a mishap.  Last night, however, I got online from home just to check.   The application executed the procedure, and at some point, it went into ROLLBACK.

Possibly the timeout.  I will need to revisit w/the front end people, I suppose. But my assumption is correct - the connection has to be interrupted/disconnected, in order for this to occur.  Yes?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Cedric_DCommented:
Why not add some debug lines near COMMIT and ROLLBACK to make sure it is explicit or implicit ROLLBACK?
0
 
dbaSQLAuthor Commented:
how so?
0
 
Cedric_DCommented:
     if @@error = 0
      begin
            print 'This is COMMIT #1'
            commit tran
      end
      else
      begin
            print 'This is ROLLBACK #1'
            rollback tran
      end


....

      if @@error = 0
      begin
            print 'This is COMMIT #2'
            commit tran
      end
      else
      begin
            print 'This is ROLLBACK #2'
            rollback tran
      end


for all places.
0
 
Cedric_DCommented:
to see error number caused rollback, do:

declare @err int

........


    set @err = @@error
    if @err = 0
    begin
            print 'This is COMMIT #1'
            commit tran
      end
      else
      begin
            print 'This is ROLLBACK #1, caused by error #' + convert(varchar(20), @err)
            rollback tran
      end
0
 
dbaSQLAuthor Commented:
interesting.  i tried that, received this:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
This is commit #.1
Ged rid of all but the violations.2008-11-04 11:36:41.333

this is the relevant portion of the procedure:

begin transaction
      while @@rowcount >0
      set rowcount 100000
              
       INSERT INTO Database.dbo.OrderEvents (EventTime,MsgType,MsgIndex,OrderNo,ClorID,ExchangeID,GWID,
        EndPoint,TraderID,AccountID,Client,Symbol,Price,Side,Quantity,TimeInForce,ExecInstruction)
             SELECT w.EventTime,w.MsgType,w.MsgIndex,w.OrderNo,w.ClorID,w.ExchangeID,w.GWID,
        w.EndPoint,w.TraderID,w.AccountID,w.Client, w.Symbol,w.Price,w.Side,w.Quantity,w.TimeInForce,w.ExecInstruction
              FROM Database.dbo.OrderEventsWorking w WITH (NOLOCK)
              WHERE w.status_flag = 0
      OPTION (MAXDOP 1)

      select @@rowcount
      set rowcount 0
      if @@error = 0
      begin
            print 'This is commit #.1'
            commit tran
      end
      else
            print 'This is rollback #1.'
            rollback tran
0
 
dbaSQLAuthor Commented:
trying the changed version now
0
 
Cedric_DCommented:
you forgot include last two lines into begin..end block

Very common T-SQL bug :(
0
 
dbaSQLAuthor Commented:
oddly enough, having corrected the logic... it now completes... yet, it doesn't re-create all the indices.  they're all dropped with an IF EXISTS, and all re-created with an IF NOT EXISTS
checking now
0
 
dbaSQLAuthor Commented:
do you believe it's my new 'end' that is hindering the index recreation?


else
begin
     print 'this is rollback #2.'
      rollback tran
end

/* rebuild indexes on CancelEvents here if needed  */
print 'Rebuild CancelEvents Indexes if needed ' + convert(varchar(30),getdate(),121)
   ....four indices are recreated


0
 
dbaSQLAuthor Commented:
yep, that's it.  the logic executed outside the procedure recreates all indices fine.  yet, when the procedure is run, the indices are not recreated
0
 
dbaSQLAuthor Commented:
changing my if not exists....

before it was just

if not exists (select name from sysindexes wehre name = .........
create index

now i'm trying

if not exists (select name from sysindexes where name =...
begin
create index
end

should know soon
0
 
dbaSQLAuthor Commented:
ugh... my execution of the procedure continually works -- if i run it.  yet, if it is done at the front end, same as the other problem... the indices are not recreated
the front end, btw, is pymssql  (python)

dropped just fine... not recreated.  one of these for each relevant index:

   IF NOT EXISTS (SELECT name from  database...sysindexes WHERE name = 'indexName')
    BEGIN
CREATE INDEX [indexName] ON [database].[dbo].[tablename]([fieldname]) WITH  FILLFACTOR = 90 ON [filegroupname_Data]
   END


do you see it?
0
 
dbaSQLAuthor Commented:
the error from the front end is this:
pymssql.DatabaseError: internal error: DB-Lib error message 20019, severity 7:
Attempt to initiate a new SQL Server operation with results pending.
DB-Lib error message 20020, severity 9:
Unknown marker

That is returned in all executions  -- executions of procedures which work just fine in query analyzer, yet they all return this error when called at the front end, and none of the executions are complete.  for example, this proc drops the indices and recreates them... just fine, from QA.  At the front end, however, the indices do not re-create.

Any ideas?
0
 
Cedric_DCommented:
Idea is following:

you actually use old and obsolete DB-Library method for access to MSSQL.

QA uses Native Protocol, plus it have unlimited timeout.

Pls check pymssql (maybe, download a new one) settings to use Native instead of DB-library.

Otherwise, you may play with MSSQL Server protocol settings, for ex. disable Named Pipes and make TCP/IP with fixed port as default.
0
 
Cedric_DCommented:
test execution of single line on pymssql:
query="CREATE INDEX ix1 ....... "

try: without brakets, without WITH, without ON FILEGROUP etc..

Phrases:

Supports "almost all" of the DB-API 2.0.
Current version is pymssql v0.8.0 released 2006-09-24.

makes me pessimistic :(

Try install MSSQL Service Pack 2 to both server and client.
0
 
dbaSQLAuthor Commented:
cedric, i am at v2000 sp4 on the sql server
0
 
dbaSQLAuthor Commented:
any ideas at all?  regarding the unexplained ROLLBACK, or the db-lib error?  Again, these procedures work fine from query analyzer... exactly as they are coded to work.  Yet, they do not complete successfully when launched from python.  this is very, very urgent



the error from the front end is this:
pymssql.DatabaseError: internal error: DB-Lib error message 20019, severity 7:
Attempt to initiate a new SQL Server operation with results pending.
DB-Lib error message 20020, severity 9:
Unknown marker
0
 
dbaSQLAuthor Commented:
i believe my logic is lacking, simply in terms of transaction handling.  having changed the client to java, instead of python, it executes the procedure successfully.
still unsure of the specific error, but i will need to improve my logic.
thank you for looking
0
 
dbaSQLAuthor Commented:
not a solution, per say, but a good pointer to where the flaw may be.  thank you for the input, cedric
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.