Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: rollback in a stored procedure

    Hi,

    I have a question regarding ROLLBACK in my stored procedure. Basically if there is any error, I would like the transaction to rollback. Am I right in assuming that I need the first ROLLBACK below? Thanks!!


    CREATE PROCEDURE SPROC (id VARCHAR(24), OUT errormsg VARCHAR(128))
    RESULT SETS 0
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL

    BEGIN
    DECLARE pid INTEGER DEFAULT -1;
    DECLARE errorCode INTEGER DEFAULT 1;
    DECLARE SQLCODE INTEGER DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SET errorCode = SQLCODE;

    SELECT pkeyid INTO pid FROM abc WHERE empid = id;--
    IF pid = -1 THEN
    --------- ROLLBACK; ------- ( Do I need this??? )
    SET errormsg = 'Error : Failed to lookup id';
    RETURN;
    END IF;


    INSERT INTO XYZ (....) VALUES (....);
    IF errorCode != 1 THEN
    ROLLBACK;
    SET errormsg = 'Error : Failed to insert into xyz table';
    RETURN;
    END IF;
    END
    ;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It is often considered a bad idea to issue either ROLLBACK or COMMIT in a routine, unless it is absolutely necessary. The routine should raise an error condition, which the calling application should handle and roll back the transaction, retry, or perform other actions as appropriate.

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by n_i View Post
    It is often considered a bad idea to issue either ROLLBACK or COMMIT in a routine, unless it is absolutely necessary. The routine should raise an error condition, which the calling application should handle and roll back the transaction, retry, or perform other actions as appropriate.

    thanks for the reply n_j! Can you elaborate a little as to why it's a bad idea to rollback in the routine itself? thanks!!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    At some point your application may grow to more than one stored procedure and nested SP calls. Transaction management should happen at a higher level in the application; a lower-level component, such as a stored procedure, does not necessarily have the context available to it in order to make a decision whether the transaction should be committed or rolled back. It may not even know that it is only a small part of a larger transaction, especially in two-phase commit situations.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In short: a COMMIT or ROLLBACK inside a stored procedure actually impacts the outside transaction in which the procedure was called. ROLLBACK is a bit of a special case, however. You can (and probably should) set a savepoint and then roll back to that savepoint only.

    As to your question: you don't need a ROLLBACK here:
    Code:
    IF pid = -1 THEN
        --------- ROLLBACK; ------- ( Do I need this??? ) --> NO
        SET errormsg = 'Error : Failed to lookup id';
        RETURN;
    END IF;
    The reason is that you only did a SELECT and no data modifications. So no data changes need to be un-done. The only thing a ROLLBACK would achieve is to release any locks, depending on the isolation level.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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