problems with ROLLBACK
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: problems with ROLLBACK

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    I guess I'm having a bit of a logic problem. My procedure should update table1, and then call another proc to update table2. However, if the update to table2 is unsuccessful for any reason (the proc sends back a success code), I need to rollback the update to table1. The problem is, my rollback is not working; the update is happening anyway. Here is the gist of the code:
    begin
    UPDATE TABLE1
    set ....... where....;

    success_flag := 'N';
    update_table2(arg 1, arg 1, success_flag);
    if success_flag = 'Y' THEN
    COMMIT; /* commit the update to table 1 */
    else
    ROLLBACK; /* rollback the update to table 1 */
    end if;
    EXCEPTION
    when others then
    etc. etc.
    ROLLBACK;
    end;

    I could update table2 first, and if succesful update table1, but then I Have the same problem if the update to table1 is unsuccessful (less likely), I would need to rollback the update to table2.

    Any help is appreciated.



  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Does update_table2 have a COMMIT in it?

    - Chris

  3. #3
    Join Date
    Jan 2001
    Posts
    63
    Yes, update_table2 has it's own COMMIT

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    There's the problem.

    Your answer did not imply that you recognized that as the culprit, so I will explain.

    You only have a single transaction in a single connection.



    ...
    Called proc1
    ---...
    ---INSERT
    ---...
    ---Called proc2
    ------...
    ------COMMIT; -- Transaction 2 commits, including insert
    ------
    ------end;
    ---...
    ---ROLLBACK; -- only changes in transaction 3 are rolled back.


    HTH,

    - Chris


  5. #5
    Join Date
    Jan 2001
    Posts
    515

    Logic for table 2

    What is your logic for table2? It looks like wether the table2 update works or no it always passes back a success_flag ='Y'.

  6. #6
    Join Date
    Jan 2001
    Posts
    63
    I'm learning alot! Thanks to all for your input. I removed the commits to the main calling procedure, and then my rollback works, plus I think it is better structured now.

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by chrisrlong


    ...
    Called proc1
    ---...
    ---INSERT
    ---...
    ---Called proc2
    ------...
    ------COMMIT; -- Transaction 2 commits, including insert
    ------
    ------end;
    ---...
    ---ROLLBACK; -- only changes in transaction 3 are rolled back.
    Oy, I hate this forum software sometimes. Here is what it was intended to be, just for posterity:

    (last COMMIT or ROLLBACK)
    (transaction 2 is started)
    ...
    Called proc1
    ---...
    ---INSERT
    ---...
    ---Called proc2
    ------...
    ------COMMIT; -- Transaction 2 commits, including insert
    ------(transaction 3 is started)
    ------end;
    ---...
    ---ROLLBACK; -- only changes in transaction 3 are rolled back.

    - Chris

Posting Permissions

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



Click Here to Expand Forum to Full Width