Re: transaction question

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!