problems with ROLLBACK

[welchdor]

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.

[chrisrlong]

Does update_table2 have a COMMIT in it?

- Chris

[welchdor]

Yes, update_table2 has it's own COMMIT

[chrisrlong]

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

[lesstjm]

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

[welchdor]

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.

[chrisrlong]

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