Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ravi.

Asked: July 05, 2005 - 1:58 pm UTC

Answered by: Tom Kyte - Last updated: April 24, 2013 - 3:42 pm UTC

Category: SQL*Plus - Version: 9i

Viewed 10K+ times! This question is

You Asked

Tom,
drop table tx;
create table tx (val number);

create or replace procedure p10
as begin
insert into tx values(1);
p11;
end;
/

create or replace procedure p11
as
begin
raise no_Data_found;
end;
/

whenever sqlerror continue commit;

exec p10;

select *from tx
/

----

My question is I thought the rule was if Oracle decides an Exception will remain permanently unhandled, it does the Implicit Rollback of current uncommitted transaction.

From your book it should be:

Savepoint xx;
<<Execute Client Job>>
if error rollback to xx;

Now,

1) The result of exec P10 above is no data being inserted. But with SQL*PLUS, I've actually told if Error then Commit, I thought.

Why still rollback to the invisible Savepoint?


2)Will Other tools like Forms etc have implicit rollbacks as well, ie all uncommitted transactions are rolled back when confronted with unhandled error conditions?

and we said...

You are confusing "sqlplus a client program" with "Oracle the database and what it does"

In the book - I explained that to the database -- the:

begin p10; end;

is a single statment -- it either ENTIRELY happens or it ENTIRELY DOES NOT happen and the way that works is the database does the logical equivalent of:


begin
savepoint foo;
<<your statement>>
exception
when others then rollback to foo;
RAISE;
end;

Now, SQLPlus gets the error AFTER this implicit processing has already taken place -- there is nothing to commit, before sqlplus ever gets control back -- the rollback to savepoint already happen, it happens before we ever leave the database for this failed statement, you can see this:


ops$tkyte@ORA9IR2> whenever sqlerror continue commit;
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into tx values ( -1 );

1 row created.

ops$tkyte@ORA9IR2> exec p10;
BEGIN p10; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "OPS$TKYTE.P11", line 4
ORA-06512: at "OPS$TKYTE.P10", line 4
ORA-06512: at line 1



Commit complete.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *from tx;

VAL
----------
-1

ops$tkyte@ORA9IR2> rollback;

Rollback complete.

ops$tkyte@ORA9IR2> select *from tx;

VAL
----------
-1


See how the -1 is there -- and it is very much committed. The 1 is not there because exec p10 was reall

begin
savepoint X;
p10;
exception
when others
then rollback;
raise;
end;

and sqlplus gets the error from the raise, there is nothing to "commit" of p10 by the time sqlplus gets it.



This happens regardless of the client -- the DATABASE does this, if the statement raises an error back to the client -- the client knows that statements uncommitted work was already rolled back.

and you rated our response

  (10 ratings)

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

Reviews

July 08, 2005 - 8:59 am UTC

Reviewer: Ravi

That was pretty good, thanks for the reply.

Tom if inside PL/SQL, I modify P10 a bit to:


create or replace procedure p10
as begin
insert into tx values(1);
commit;--- NEW CODE
insert into tx values(2); -- NEW CODE
p11;
end;
/

This looks like the code gets implicitly transformed to

create or replace procedure p10
as begin
insert into tx values(1);
commit;
<< CREATE IMPLICIT SAVEPOINT >>
insert into tx values(2);
p11;
WHEN OTHERS THEN ROLLBACK TO SAVEPOINT <<IMPLICIT SAVEPOINT>>
end;
/

That is even if P11 had multiple data modification statements but as long as it does not either have a commit or rollback or an exception handler, then the Savepoint is as above.
1) Is that correct?

2) I've stated that each time a transaction statement (commit or rollback) or an exception handling statement which has a commit or rollback occurs, the Old Savepoint is Destroyed and a new one Implicitly created, right?

The original version of P10 would have been implicitely modified to

<<IMPLICIT SAVEPOINT>>
create or replace procedure p10
as begin
insert into tx values(1);
p11;
exception when others then
ROLLBACK to <<IMPLICIT SAVEPOINT>>
end;
/

3) Am I correct?

Tom Kyte

Followup  

July 08, 2005 - 9:53 am UTC

any outstanding work performed by a statement is rolled back upon a client receiving an error.

if you committed 50 times, then did a little more, then hit an error, the 50 committed things would stay committed but the little more would be undone.

If you used autonomous transactions directly or indirectly (eg: select seq.nextval from dual), they will not rollback since they were committed immediately.


The crux of what you need to understand is:

Any outstanding work performed by a statement that fails will be undone.




I use the:

begin
savepoint foo;
<<your statement>>
exception
when others then
rollback to foo;
raise;
end;


as a way to convey the concept -- it should not be taken literally that the code is rewritten -- but people understand that concept easier than:

Any outstanding work performed by a statement that fails will be undone.


July 08, 2005 - 9:57 am UTC

Reviewer: A reader

Sure Tom, thanks, just correcting myself really.
Cheers
Ravi

Excellent !

June 20, 2006 - 8:16 am UTC

Reviewer: Anu Paul from India


Implicit Commit /Rollback

May 10, 2007 - 4:53 pm UTC

Reviewer: Rita

Hi,
Suppose in a pl/sql procedure , there are some updates to a remote table and some to a local table. There is no Commit at all within the entire procedure. If the procedure fails for some reason after some updates have been done , will the updates done so far on the remote table be committed or rollbacked ? There are also no DDL anywhere in the procedure.

Thanks,
Rita
Tom Kyte

Followup  

May 11, 2007 - 11:07 am UTC

statement level atomicity rules.


when a client application issues:

begin procedure( ... ); end;


that is - invokes a stored procedure, conceptually what happens is:


begin
   savepoint FOO;
   procedure( .... );
exception
   when others then
       rollback to FOO;
       raise;
end;



that is, your procedure either

a) entirely succeeds
b) entirely "never ran"

if it fails, we put the database(s) back the way they were right before the procedure was executed.

deadlock and exception handling

September 11, 2009 - 11:34 am UTC

Reviewer: jamil from France

Dear Tom,

I'm thrilled as it's my first post to asktom.
Here's my remark.

I intentionally provoked a deadlock between two sessions.
Session 1 gets the deadlock error, but still all its actions are not rollbacked and session 2 continues to waits for session 1 to release its lock.

What's the correct coding for the correct handling of the deadlocks? Should we make an exception handler and make a explicit ROLLBACK?

can you explain why deadlocks are not handled the same way as regular errors (why is the rollback not implicit?) and are there any other type of errors that behave as deadlocks do.

here is my test case.

procedures definition

CREATE OR REPLACE procedure P_1_1 as
begin
  update tbl set col='1' where pk='0H5060V68937';
end;
/
CREATE OR REPLACE procedure P_1_2 as
begin
  update tbl set col='1' where pk='0H5060V66673';
end;
/

CREATE OR REPLACE procedure P_2_1 as
begin
  update tbl set col='2' where pk='0H5060V66673' ;
end;
/

CREATE OR REPLACE procedure P_2_2 as
begin
  update tbl set col='2' where pk ='0H5060V68937';
end;
/



flow of actions

session 1:
 begin   p_1_1; end; 


session 2:
 begin   p_2_1; end; 


session 1:
 begin   p_1_2; end; 


session 2:
 begin   p_2_2; end; 

Tom Kyte

Followup  

September 14, 2009 - 1:00 pm UTC

... What's the correct coding for the correct handling of the deadlocks? Should we make an exception handler and make a explicit ROLLBACK? ...

You tell me?

What would YOU like to have happen?


I'm not a fan (in fact, I think error handling is something most people do entirely WRONG, ineptly even - and catching exceptions you cannot deal with is a big error, big bug) of coding the exception block - exception blocks should be coded for one of two things:

a) at the top level - the block of code that the client submits - in order to LOG the error, eg: client can submit:

begin p(....); exception when others then log_error( ...); RAISE; end;

and that should be at the TOP LEVEL only - not in every silly routine, just in the bit of code that calls your routine


b) when you EXPECT the error, eg:

begin
   ...
   begin
       select x into y from t where ...;
   exception
       when no_data_found then y := some_default;
   end;
   ..........


and can recover from it (eg: it is NOT an error).


By default all statements are ATOMIC, the procedure you call either entirely succeeds or entirely fails - UNLESS you

1) put a commit/rollback in there - then game over, the caller of your procedure must deal with the MESS you've left them in - which is an unknown database state (maybe they called 50 other routines before your code - it is UP TO THEM whether they should a) commit, b) rollback, c) retry some operation)

2) put a when others not followed by a raise - then the work that is partially done "stays" and the work never gotten to - is never gotten to. Again, an unknown database state.


The client should receive the error "deadlock detected"

The client should decide whether to a) rollback, b) commit, c) retry operation, d) do something else - only THEY are smart enough to know what is proper in this case.


Your code - should do nothing in this case, you don't know enough at your low level.



still in doubt

September 17, 2009 - 9:44 am UTC

Reviewer: jamil from France

Well that's exactly how I handle the errors : I usually never catch the error in an EXCEPTION handler; except at the top level for ex. if have to log it in a log table.

BUT deadlocks make me want to change my mind because deadlocks do NOT make an implicit rollback.

For instance let's say i have a PL procedure "DoSomething" that runs Proc_A and Proc_B.

We want "DoSomething" to manage the whole aspect of the transaction ie "DoSomething" does the commit or rollback of whatever Proc_A and Proc_B did and all locks should be released at the end of its execution (a uncommon behaviour thought).

Now, if "DoSomething" encounters an error:

- Situation 1: it's a "divide-by-zero-style" or "foreign-key" error => "DoSomething" makes an implicit rollback and all locks are wiped.. no need for EXCEPTION block.

- Situation2: it's a "deadlock". DoBatch has to implement an EXCEPTION block and make an explicit rollback in order to wipe the locks.

Not knowing in advance the type of errors "DoSomething" will encounter in the process of its happy life, i conclude that it should ALWAYS trap the errors with an EXCEPTION block and make an explicit ROLLBACK.

Generalizing: More often than not, top level PL procedures should implement an EXCEPTION block and make an explicit rollback in that block.

What is wrong with this thinking?


Tom Kyte

Followup  

September 17, 2009 - 1:13 pm UTC

... BUT deadlocks make me want to change my mind because deadlocks do NOT make an
implicit rollback. ...

YES, THEY DO

of the offending statement - or statements if the deadlock happened in the middle of a procedure with other code.


You cannot, in your procedure, know if it is OK to rollback the entire transaction OR NOT. The client gets the error, the client that has been calling your procedure and that other procedure - it will decide

a) rollback, retry
b) not terribly important your bit didn't run, your bit didn't run at all (it rolled back itself nicely) so we can do something else and then commit.

ONLY the client knows this.


I don't see the difference between the two cases you gave. The locks gained by teh failed statements in both cases are released. DoSomething in your first case ONLY RELEASES LOCKS GAINED BY DOSOMETHING


...
Generalizing: More often than not, top level PL procedures should implement an
EXCEPTION block and make an explicit rollback in that block.

What is wrong with this thinking?....


Todays top level procedure is tomorrows bottom of the stack - or second in a list of five.

It is up to the client to control transactions - only.


I wish plsql did not have:

commit
rollback
triggers
autonomous transactions
when others

There would be a lot better code out there for it. Of course, I'd have another list....

To jamil "What is wrong with this thinking?"

September 18, 2009 - 8:03 am UTC

Reviewer: Sokrates

a deadlock always is caused by a bug in the code.
when you get a deadlock-exception, the bug should be fixed.

Implicit Rollback

November 02, 2012 - 1:22 am UTC

Reviewer: Ranjith from Shanghai

I think author of the below article, is slightly misrepresenting the fact about rollback during unhandled exceptions.


http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22plsql-1518275.html

ctrl+F Exceptions and Rollbacks

it states:
"PL/SQL returns an unhandled exception error to SQL*Plus (or whichever host environment is being used). The default behavior of SQL*Plus (and all host environments I know of) is to issue a rollback of any outstanding changes in the session and display the error information."


But what we understood from Tom is - this rollback happens entirely in the DB, regardless of client.

Hope Oracle has not changed this feature,so that we don't have to explicitly rollback upon exceptions.

Tom Kyte

Followup  

November 05, 2012 - 8:31 am UTC

I've emailed Steven with my comments which are:


That is not technically accurate on two counts.

1) the client environment doesn't do the rolling back, all statements submitted to the datebase (be they update, delete, insert, merge, PL/SQL blocks, anything) are atomic. They either entirely succeed or entirely fail. The database is responsible for ensuring this. Hence, whenever a statement fails - the database rollsback the work of that statement. The client need not do anything - the client gets an error message and the client knows that any work not yet committed by that statement is rolled back. So, sql*plus isn't doing the rolling back - the database does it.

You can write a small program in any 3gl you want outside of the database and observe this, just

a) connect to database
b) insert a row into table T1
c) execute a procedure that inserts into T1 as well - and have it fail, end it with raise program_error or something like that
d) in the client program query T1, you'll find the row from (b) is there but the row from (c) is not


There will be no rollback or commit issued by the ciient yet - but the row inserted in (c) will not be there - nothing the client did.


2) the rolling back only affects the statement that failed, it is not "any outstanding changes in the session", it is only the work performed by that statement. for example:


ops$tkyte%ORA11GR2> create table t ( msg varchar2(30) );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t (msg) values ( 'this is before plsql' );

1 row created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
  2          insert into t (msg) values ( 'this is in plsql' );
  3          raise program_error;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at line 3


ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t;

MSG
------------------------------
this is before plsql

ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> select * from t;

no rows selected


April 24, 2013 - 1:30 pm UTC

Reviewer: russell from Zurich

Hi, am I missing something here? I would have expected both statements in the set_dob() procedure to have been rolled back when the exception was raised by this block (11g EE r11.1.0.7).

I would have expected an employee, but without a DoB or age.

create table emp (name varchar2(30) not null, dob date, age number);                       
alter table emp add constraint ck_age_not_negative check (age >= 0) ;                       

set serverout on

declare
  procedure set_dob (p_name varchar2, p_dob date) is
  begin
    update emp set dob = p_dob where name = p_name;
    update emp set age = sysdate - p_dob where name = p_name;
  end;
begin  
  insert into emp (name) values ('russell');
  begin
    dbms_output.put_line('call to set DoB ..');
    set_dob('russell', to_date('01.01.2070', 'dd.mm.yyyy'));
    dbms_output.put_line('..DoB has been set');
  exception  
    when others then dbms_output.put_line('Error raised when setting DoB, well it is optional so lets ignore it'); null; -- ignore exceptions when setting DoB, this data is optional
  end;
end;
/

drop table emp;

select * from emp;

drop table emp;


And the output ..

call to set DoB ..
Error raised when setting DoB, well it is optional so lets ignore it

PL/SQL procedure successfully completed.


NAME                DOB             AGE
------------------- -------- ----------
russell             01.01.70

Tom Kyte

Followup  

April 24, 2013 - 3:42 pm UTC

your exception 'handler' (which I HATE by the way, i hate your code - when others then null - ugh, hate it)


you turned the error into "not an error". you made the error "go away", you swallowed the exception, you said "everything between my begin/end preceding the when others can either run to completion, 50% of the way, 0% of the way - whatever, it doesn't matter to me"


this is not a bug, this is your code on "when others".

set_dob is not a 'statement', it was a procedure call inside of a statement. the statement was the anonymous block.


if you wanted this to "work" you would code:

 procedure set_dob (p_name varchar2, p_dob date) is
  begin
    savepoint foobar;
    update emp set dob = p_dob where name = p_name;
    update emp set age = sysdate - p_dob where name = p_name;
    exception when others then rollback to foobar;
  end;



Conflict with documentation?

September 26, 2013 - 2:33 am UTC

Reviewer: Bryn from Australia

Tom,

This response comes up in google searches on this topic even though you wrote it many years ago. However your very thorough explanation for Oracle 9i seems to conflict with more recent documentation for Oracle 11g
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#i1889

"If a stored subprogram exits with an unhandled exception, PL/SQL does not roll back database changes made by the subprogram."

Your response seems to say the opposite of this: that in the event of an unhandled exception the changes made so far within the PL/SQL block ARE rolled back. I can't find a clear statement in any documentation that this has changed between 9i and 11g.

Could you please clarify?