[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

unexplained ROLLBACK

Posted on 2008-11-04
22
Medium Priority
?
513 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:dbaSQL
  • 14
  • 8
22 Comments
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22875778
Rollback is automatically executed upon unexpected disconnection of MSSQL session. Including, by timeout.

Maybe it was in place? Only one-time event?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 22875953
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
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22876012
Why not add some debug lines near COMMIT and ROLLBACK to make sure it is explicit or implicit ROLLBACK?
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 17

Author Comment

by:dbaSQL
ID: 22876660
how so?
0
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22877361
     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
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22877385
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22878553
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22878605
trying the changed version now
0
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22878608
you forgot include last two lines into begin..end block

Very common T-SQL bug :(
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 22879049
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22879108
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22879133
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22879570
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22880311
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22880520
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
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22880784
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
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22880849
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22881801
cedric, i am at v2000 sp4 on the sql server
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 22884813
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
 
LVL 7

Accepted Solution

by:
Cedric_D earned 1500 total points
ID: 22886355
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22929863
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
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 31513051
not a solution, per say, but a good pointer to where the flaw may be.  thank you for the input, cedric
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question