You Asked

Is this what I am observing here is true ?
What if I want to rollback the whole transaction even if the outer procedure fails after the call to inner procedure ?

I have 2 procedures here ...
The 1st procedure is called inside the 2nd one. I am making the outside procedure fail by inserting duplicate PK values.
But to my surprise - this is what is happening
In pr_test procedure the 1st insert gets commited.
In pr_nestedtest procedure the insert gets commited.
But when it comes back to pr_test to do the 2nd duplicate insert which fails and does not get commited but I thought that it will rollback my 1st insert from pr_test but it doesn't !!!!
So if the outside procedure fails before calling the inner procedure everything gets rollbacked, but if it fails after the call to inside procedure and inside one gets commited, everything till the end of the inside procedure gets commited.

Work_id is a Primary key of this table.

Create or replace procedure pr_nestedtest(inold_ID int)
IS
Begin
INSERT into work (Work_ID, Work_Level, WORK_CHARGEBACK)
values(199,inold_ID, 50);

Commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END pr_nestedtest;
/

----------------------------------------------------------------------------------------------------------------------

create or replace procedure pr_test(inold_ID int)
IS
Begin
INSERT into work (Work_ID, Work_Level, WORK_CHARGEBACK)
values(inold_ID ,100, 50);

pr_nestedtest(inold_ID); ---Execute PROCEDURE

-- making outside procedure fail by inserting duplicate PK values

INSERT into work (Work_ID, Work_Level, WORK_CHARGEBACK)
values(inold_ID ,500, 50);

Commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END pr_test;
/

If I take out the commit statement from the inner procedure everything gets rollbacked which is what I would want. But the real case which I am concerned about has a inner procedure inside cursor and is huge. So if I take out the commit then I will run out of Rollback segment, what should I do ? I want the whole outer procedure along with inner to rollback if anything anywhere fails. So how do we make the whole thing rollback?

Can I use savepoints ? How are they different from rollback ? I am also reading your book right now to see if it has any hints.

In the procedure can I specify the rollback segment name? We may need to have a really big rollback segment for this. Or maybe it can be part of the create procedure statement?

I cannot do set transaction rollback xxx; because this procedure will be called from external program.

Create or replace procedure pr_nestedtest(inold_ID int)
IS
Begin
INSERT into mwebwork (Work_ID, Work_Level, WORK_CHARGEBACK)
values(199,inold_ID, 50);

END pr_nestedtest;
/

Thanks
Sonali

and Tom said...

wow, you really need to understand more about transactions.

A commit is a commit -- it commits all outstanding work and cannot be undone. The entire purpose, goal and only meaning of commit is to make changes permanent.

The "nesting" of procedures has nothing to do with this behavior. Whether you coded in a linear fashion (no procedures) or modular -- the effect would be the same. Commit -- and the data is committed, will not be rolled back.

Look at your logic in thinking here:

<quote>
If I take out the commit statement from the inner procedure everything gets
rollbacked which is what I would want. But the real case which I am concerned
about has a inner procedure inside cursor and is huge. So if I take out the
commit then I will run out of Rollback segment, what should I do ?

</quote>

well, if you release that rollback (you appear to be trying to "save" on some resource here) how would we be able to rollback???

Savepoints won't release rollback -- if they did, we wouldn't be able to roll back!!

You should use equi-sized rollback segments. You need to bite the bullet and size your system for what you do. Having a "big" rbs and using "set transaction" is something i really don't like to do (yes, you can do this in the procedure as long as the transaction has not yet begun as the set transaction must be the first statement in there).

My approach would be to SIZE rollback appropriate so that any rbs would do.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sonali, December 28, 2001 - 1:14 pm UTC

I know what commit means I should have been more clear about my question.
What I was confused about was -
Que1 -
In this case something failed after the call to inner procedure ( which had its own begin and commit), so inner procedure will get commited but I thought because I had exception,rollback on the outer one, and the statement in the outer procedure failed after the call to the inner procedure,
I thought that if anything fails/ anywhere in the outer procedure will rollback everything in the outer procedure commiting only the inner procedure. But like I said, I was wrong about that.

Que2-
this is a different que- I want to rollback everything no matter where it fails, inner/outer so obviously I will not have commit in inner procedure, so I was thinking if I can use savepoint( but you said I cannot- got it) or if I can set transaction rollback segment xxx; inside procedure, which you said is not a good idea, why do you say that having equal RB is better than setting one for this procedure ?
I have equal rollback set segments set on the server, but was going to set one for this so that it will not slow others down, or in other words did not wanted to use all RB space making others unable to work.

thanks
Sonali

December 28, 2001 - 1:48 pm UTC

procedures do not cause ANY nesting of transactions. You can use autonomous transactions (see
</code> http://asktom.oracle.com/~tkyte/autonomous/index.html <code>
) but they are a totally different beast.

You can use a savepoint, it just won't do anything for you in your case.

All RBS is shared by everyone. Using the set transaction use rollback segment won't affect:

<quote>
but was going to set one
for this so that it will not slow others down, or in other words did not wanted
to use all RB space making others unable to work.
</quote>

for the simple reason that the one you explicitly use will continue to be used by everyone else in a round robin fashion. It does not set it aside just for you.

nested transactions or dependent transactions

sjaiswal, November 03, 2004 - 5:20 pm UTC

Suppose we have a scenario like this

if we have a Proc A that calls Proc B, and both have transactions, then:

1. If proc B is called from Proc A, then we don't do the commit in Proc B

2. If Proc B is called independently, then we need to put commit in it.

how do we handle such situations?
Example would be parent child transactions
We have separate stored proc's for inserting into parent and child and both could be called as one transaction or called independently...

I had done something using TRANSACTION_ID but don't remember how....

November 05, 2004 - 11:21 am UTC

they don't "both have transactions"

there is "the transaction"

they are both "participating in the transaction"

I believe stored procedures should not have commits.

I believe only the client application knows when everything is done (that application can be many things)

So, the application knows if:

A+B is the transction
B+A is the transaction
A is the transaction
B is the transaction

That is all -- let the CALLER tell the database when the single well formed transaction is complete -- they are the only ones that know.

sounds like you have "table apis -- TAPI's". I don't like them. you should have API's -- that are passed the data and perform an operation.

Multiple execution of the ROLLBACK statement in case of Nested Packaged Procedures.

Hemal, January 15, 2007 - 2:45 am UTC

Hello Tom,
I am having the following query related to the multiple execution of the ROLLBACK statement in case of the nested packaged procedures.

In the below code ,from the procedure B_TEST I am calling the private procedure A_TEST.In procedure A_TEST,if "e_update_header" exeception occurs then I am calling the ROLLBACK statement.Also in the procedure B_TEST, we see that if the exeception occurs in A_TEST then again I am raising a exception "e_update_header_b" so that the processing is stopped and "ROLLBACK" is executed and the error code (p_o_nm = 1) is sent out to client.

Please advice me on the following points:
1) We see that the ROLLBACK statement is fired twice .Once in B_TEST procedure and once in A_TEST procedure.
Is it fine ? Will this create any problems with performance of the application.
2) Is there any other method to code these procedures.
-----------------------------------------------------
CREATE OR REPLACE PACKAGE DP_CAR_PKG
PROCEDURE B_TEST (p_o_nm OUT NUMBER);
END DP_CAR_PKG;
/
-------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY DP_CAR_PKG
IS
-----------------------------
/* PROCEDURE A_TEST is a private procedure*/
PROCEDURE A_TEST (p_o_nm OUT NUMBER,)
IS
e_update_header EXCEPTION;
BEGIN
.. ...
/*SOME CODE PERFORMING INSERT OR UPDATE*/
p_o_nm = 1 ;
RAISE e_update_header;
.. ...
/*SOME CODE*/
.. ...
EXCEPTION
WHEN e_update_header THEN
ROLLBACK; ------------ 1 st ROLLBACK
END A_TEST;
----------------------------
/* PROCEDURE B_TEST is a public procedure*/
PROCEDURE B_TEST (p_o_nm OUT NUMBER)
IS
e_update_header_b EXCEPTION;
p_i_nm NUMBER;
BEGIN
.. ...
/*SOME CODE PERFORMING INSERT OR UPDATE*/
.. ...
A_TEST(p_i_nm);
IF p_i_nm = 1 THEN
p_o_nm := p_i_nm;
RAISE e_update_header_b;
END IF;
.. ...
/*SOME CODE*/
.. ...
EXCEPTION
WHEN e_update_header_b THEN
ROLLBACK; ------------- 2 nd Rollback
END B_TEST;
-----------------------------------------------
END DP_CAR_PKG;
----------------------------------------------------------
Please Advice
Thanks and Best Regards
-Hemal

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

nested transactions or dependent transactions

Multiple execution of the ROLLBACK statement in case of Nested Packaged Procedures.

More to Explore

PL/SQL demos

PL/SQL docs

Nested Stored procedures

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

nested transactions or dependent transactions

Multiple execution of the ROLLBACK statement in case of Nested Packaged Procedures.

More to Explore

PL/SQL demos

PL/SQL docs