Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

transaction rollback during unexpected error

Posted on 2006-06-28
11
Medium Priority
?
1,468 Views
Last Modified: 2008-02-01
Hi,
I have a stored proc that inserts into a some tables within a transaction. In this proc I check the @@error to see if it's not equal to 0 after each insert. If it's not equal to zero, then I do a rollback as follows:
if (@@Error !=0 and @@trancount > 0)
                begin
                        rollback tran
                end

The problem I am having is if there was an unexpected error like an implicit conversion error, Sybase does not continue processing the procedure and exits without doing the rollback.
Doe anyone know how I can catch all errors to make sure it will do a rollback?

Thanks in advance
0
Comment
Question by:pn77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 17006401
Do the transaction management outside the procedure, and test the procedures return status? Commit or rollback according to the status. Errors like conversions will still leave a trappable return code.
0
 

Author Comment

by:pn77
ID: 17014583
Actually I am doing that and in the calling procedure, I check the return code, if it's !=0 then I would goto the error handler which checks the @@trancount > 0 and then does a rollback. I have nested transactions. So in the calling proc, it started a transaction and it called stored proc 2 which also has a transaction that completed and committed successfully, then it calls proc 3 which starts a transaction and fails with the implicit conversion error. I would expect the calling proc to handle the rollback and roll back to the outermost begin Tran, but for some reason the transaction in proc 2 did not get rolled back.

Thanks,

0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 17015330
Are you capturing the return status separately to @@error?

Some errors could immediately halt the executing procedure without causing the rollback, but surely that would be reported by the return status? Try explicitly setting a (positive) return status at the end of proc 2 and proc 3, and test for that in the calling proc...?

I agree it's a bit weird!
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 

Author Comment

by:pn77
ID: 17020724
I am checking the return code separate from the @@error:

exec @RcIn = proc2   parameter, parameter
               
if (@RcIn != 0)
     goto ErrorHandler

The ErrorHandler does the rollback if @@trancount > 0

Yet still every time we get this error, it does not do the rollback of that data inserted from proc2.


0
 

Author Comment

by:pn77
ID: 17020755
In proc2 and proc3, I do explicitly set the return status to 100 if it fails to do the insertion. Thus, after the insert statement, I check the @@error and then set the return status to 100 then goto the errorhandler to rollback and return the return status. (It doesn't even run this code because the stored proc halts). I assumed that the calling stored proc would catch the return code if it checks (@RcIn != 0) and do a rollback, but for some reason it does not.

                if (@@error != 0)
                begin --{
                        select    @RcOut = 100
                        goto ErrorHandler
                end --}

  ErrorHandler:
                if @@trancount > 0
                begin
                        rollback tran
                end
                return @RcOut
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 100 total points
ID: 17021789
I wonder if this would do the job:

   exec @RcIn = proc2   parameter, parameter

   if (@@error != 0) or (@RcIn != 0)
        goto ErrorHandler

I'm not sure what the return status will be for an error that halts the proc. As you say, testing for @@error inside proc2 doesn't help because in the case of a termination it never gets that far. This covers both bases.
0
 

Author Comment

by:pn77
ID: 17031091
Joe,
I actually did try that as well, but still it did not rollback.
I am now trying to look through my code carefully and debug using RapidSQL.

Thanks,
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 17034503
Ok, this officially beats me now!! 8-)
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 17036835
it sounds like some arithmatic overflow on one of the parameter being passed to the procedure, so it is never actually being called

have you selected your parameters out to check the values before the exec ?
you also seem to be using unnamed parameters in the call, can be misleading to which value is going where
0
 

Author Comment

by:pn77
ID: 17043183
Thank you for your reply.
The posted sql is not my actual code. I was just giving an example.
I actually did name my parameters. The error is an implicit conversion error and it occurs in the procedure where it's trying to do an insert into a field that is a datetime. I have verified that it has entered the procedure and then halts when it tries to do the insert. I know why I got this error and I have fixed it so it does not give the implicit conversion error. The issue I had was that the transactions did not get rolled back when the unexpected error occurred.
I just want to be able to set up my transaction management so that everything gets rolled back to the outermost begin trans in case of other unexpected errors.

Thanks,
0
 

Author Comment

by:pn77
ID: 17044666
After looking through this again, I have mistaken a record that was inserted to be the record that my proc inserted. I found that my proc was doing the transaction rollback as expected. I was just looking at the wrong record in the table.....duhhhhh.
I'm sorry for wasting everyone's time.

Thanks,
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Here in this article, you will get a step by step guidance on how to restore an Exchange database to a recovery database. Get a brief on Recovery Database and how it can be used to restore Exchange database in this section!
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

598 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