Transaction Handling in Sybase

1) We have Sybase 12.5 ASE.

2) We have 12 stored procedures out of which 2 need transaction handling i.e. if there is error, rollback everything else commit at the end.

3) We tried using @@error & @@transtate with BEGIN TRAN, ROLLBACK TRAN & COMMIT TRAN and the later worked for a unique constraint violation.

4) Please suggest good transactional handling methods by which if any error occurs, entire transaction should rolled back else entire transaction should be commited at the end.
LVL 17
Who is Participating?
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
Nested transactions aren't always obvious. If you have an architecture that has every procedure doing a self-contained transaction - i.e. wrapped in a BEGIN TRAN and ROLLBACK/COMMIT TRAN - then any time a procedure calls a procedure, you have a nested transaction.

@@error always contains the error number raised by the most recent statement. It is reset after every statement (save for DECLARE, so you can capture it in a local var). That means any condition that doesn't raise an error message will leave @@error set to 0.

@@transtate reports on the current transaction, and can be :

0 - transaction in progress, all is well
1 - transaction completed successfully & committed
2 - most recent statement aborted, but transaction is still in progress
3 - transaction aborted and rolled back

It's @@transtate=2 that you have to worry about - something went wrong, but not enough to break the transaction. In theory this transaction could now be committed, but who knows what would happen with the statement that aborted. In this case to preserve the definition of a transaction a manual ROLLBACK TRAN is called for.

If you check for both @@error and @@transtate = 0, then you can conclude all is well. Remember these are both set after every command.
Joe WoodhousePrincipal ConsultantCommented:
Not sure I'm understanding your question... when you say

> if any error occurs, entire transaction should rolled back else entire transaction should be commited at the end

that's exactly what BEGIN/ROLLBACK/COMMIT TRAN do by definition. (It is possible to break the definition of a transaction by using so-called "partial rollbacks" and "savepoints" but in my opinion that's rarely a good idea.)

I think the approach you outline is exactly the way to go. Test @@error after every command you care about. Remember it is reset after *every* command except for DECLARE, so if you want to refer to an earlier value you will have to save it into a local variable.

Other gotchas that trip up some people, particularly with nested transactions:

- nothing commits until the very outermost COMMIT
- any ROLLBACK rolls back the entire transaction, not just the current nesting level

If you have these procedures calling each other, you'll need to go to some trouble to ensure each procedure deals with transaction management at its own level, and passes enough information back up the chain to let the calling environment do the same. The Sybase manuals have some discussion of this.

Good luck!
k_murli_krishnaAuthor Commented:
We have no nested transactions. Please explain the difference between the way @@error & @@transtate work i.e. the errors, warnings etc. that they trap enabling the implementation of transaction integrity.
Joe WoodhousePrincipal ConsultantCommented:
Hello! Glad I could help. Sorry for not responding sooner, I stopped receiving EE notifications for some reason...
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.