Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: Should commit be error handled? And even with rollback?

    Hello,

    I ve read this paper about error handling :

    Implementing Error Handling with Stored Procedures

    I have question about commit handling, there are examples that manage that this way:

    Code:
    COMMIT TRANSACTION
    SELECT @err = @error if @err <> 0 RETURN @err
    or in a loop

    Code:
    COMMIT TRANSACTION
    SELECT @err = @error if @err <> 0 BREAK
    What you think about this? Is there need to hande error about commit?

    at 1) I mostly saw that many dont test @error after commit (they do error handling with rollback after any statement but COMMIT stays unhandled)...so there is no need do that

    at 2) Is necessery to handle COMMIT but in example above the right way is:

    COMMIT TRANSACTION
    SELECT @err = @error
    if @err <> 0
    BEGIN
    ROLLBACK TRANSACTION
    RETURN @err
    END
    or in a loop

    COMMIT TRANSACTION
    SELECT @err = @error
    if @err <> 0
    BEGIN
    ROLLBACK TRANSACTION
    BREAK
    END
    and there is last question is possible also continue the loop or if COMMIT failed it means that any other action has no sense and should be avoided (it indicates serious DB problem..)

    COMMIT TRANSACTION
    SELECT @err = @error
    if @err <> 0
    BEGIN
    ROLLBACK TRANSACTION
    CONTINUE
    END
    Last edited by Musil David; 10-14-09 at 05:09.

  2. #2
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    No one answers?

  3. #3
    Join Date
    Oct 2009
    Posts
    1
    According to me, there's absolutly NO need to handle error on COMMIT !
    (specialy in a stored proc)
    What is the goal of this ???

    If you do so, why not handling the error on the ROLLBACK TRANSACTION in case of error, and so on ...

  4. #4
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    You print error message to log and cos you are in transaction and you should ROLLBACK it.

    Two scenarios....

    If ROLLBACK of transaction (s) is OK there is no need to do anything else after ROLLBACK....

    If ROLLBACK fails there you cannot do anything with this so there is no need to do anything else after ROLLBACK

  5. #5
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    You are correct, there is no need to check @@error after commit or rollback.

    If you read the "paper" you will notice the writer:
    • did not have error checking
    • found out the hard way, that error checking is essential
    • came up with a simple error checking construct, and put it in everywhere, after every statement

    Not only is it overkill (re it is not required for commit and rollback), that is very simplistic and not mature, I would not recommend it; plus it causes repetitive code, and it is hard to follow. Good practice is a standard common error block at the end of the proc (all transactions should be procs) which is AWARE of transactions; tran state, @@trancount, the error severity, etc. All DML inline, in the body of the proc, needs to check for @@error and GOTO ERROR_BLOCK. Likewise, there has to be a prepare block at the top, which checks for tran states not allowed before starting the xact.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •