Transaction Handling in Sybase

Posted on 2005-04-14
Last Modified: 2012-06-27
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.
Question by:k_murli_krishna
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    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!
    LVL 17

    Author Comment

    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.
    LVL 24

    Accepted Solution

    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.
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    Hello! Glad I could help. Sorry for not responding sooner, I stopped receiving EE notifications for some reason...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now