Re: transaction question
Date: 1996/09/17
Message-ID: <323ebc4d.5835170_at_dcsun4>
On Tue, 17 Sep 1996 12:32:26 GMT, chuckh_at_dvol.com (Chuck Hamilton) wrote:
>Gert Rijs <gem_at_wirehub.net> wrote:
>
>>Chuck Hamilton wrote:
>>>
>>> If a statement fails due to the "rollback segment too small" error,
>>> won't that force the entire transaction to be rolled back?
>>>
>>> Assume I do the following....
>>>
>>> DELETE...
>>> INSERT...
>>> COMMIT;
>>>
>>> If the INSERT fails, won't that rollback the DELETE too? And then
>>> COMMIT an empty transaction?
>>if you are running this from sql*plus, be sure to have autocommit OFF
>
>Autocommit was off.
>
>The reason I ask is because I had just such a scenario and the
>behaviour exhibited by Oracle was not at all what you'd expect from a
>transaction oriented database. I thought perhaps I was doing something
>wrong. It rolled back the INSERT but not the DELETE.
>
>I was under the impression that until I explicitly issued a COMMIT or
>ROLLBACK (COMMIT in this case), everything up to that point was
>considered a single transaction. So if a command failed, it should
>rollback everything for the current transaction. But it appears to
>only be rolling back part of the transaction - the INSERT.
>
>So much for Oracle being transaction oriented.
>--
>Chuck Hamilton
>chuckh_at_dvol.com
>
>This message delivered by electronic sled dogs. WOOF!
I have to disagree with the above...
This is exactly the
- documented behaviour.
- the behaviour mandated by ANSI.
- the 'sensible' thing to do.
A statement is atomic. A statement will either succeed or fail. A transaction is atomic, a transaction will either succeed or fail. A transaction is a series of statements executed in order to take the database from one consistent state to the next. A statement either works or doesn't work. If a statement fails, it is up to you, the developer to decide whether the transaction can continue or whether it must fail.
This is the behaviour you must expect from any ANSI SQL database actually.
The level of error handling is at the statement level, always (except in the case where the error is caused by the database becoming unavailable, then it will rollback).
If you insert and that fires a trigger that updates another table and that fires a trigger that deletes from some other table and that fires an update on another table that FAILS, the original insert, and the triggered update, delete, and update statements are rolled back. Any statement you executed sucessfully before that is not, any statement you successfully execute after that is not.
In fact, if you execute a stored procedure such as:
create procedure testi
as
begin
insert into t values ( 1 );
insert into t values ( 1 );
end;
/
against a table T ( x int primary key )
You will find that the execution of the procedure is atomic. Since it does no error handling, it will insert NO data, not 1 row with the second one failing.
Lots of times people cause errors to occurr as part of their normal processing. If Oracle rolled back the entire transaction, anytime an error occurred, we really wouldn't need a rollback statement.
For example, should "select * from emp where empno = 100 for update nowait" rollback an entire transaction if it fails? Should "select * into :x, :y, :z from T where x = 5" rollback a transaction if no data is found? Both of those statements will cause errors to occurr if data is locked or if no data is found. If some errors did cause a rollback to the beginning of the transaction and some did not, who would decide what statements are 'bad' enough to cause a full rollback and how would you the developer be able to tell if your transaction was rolled back or not? We would be constantly arguing that such and such a statement is not bad enough to cause a rollback but such and such a statement is.
What about an interactive environment where the user types in lots of changes on the screen. As they change the data on the screen, we are posting changes to the database. During their 20'th change, they type in a date wrong. Should we rollback the 19 successful changes, make them do it over again, or should we give them a chance to fix the date and retry the failed statement?
If you want a series of statements to execute like a single statement and either commit if successful or rollback if failed, I suggest you use a block like:
begin
insert into ....
update ....
delete ....
....
commit;
exception
when others then
rolllback;
raise;
end;
/
that block will either execute and commit or not (and rollback). Actually, you could just:
begin
insert into ....
update ....
delete ....
....
end;
/
And either every statement in the block will succeed or none will (apparently) execute (some will execute, they will just be rolled back).
OR, just use procedures as your transactions, don't allow insert/update/delete in the database at all. The procedures will either commit or rollback.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Sep 17 1996 - 00:00:00 CEST