Re: serializable isolation level behavior question

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 20 Oct 2006 18:20:57 -0700
Message-ID: <1161393655.419046@bubbleator.drizzle.com>

Bob Jones wrote:

> "Martin T." <bilbothebagginsbab5_at_freenet.de> wrote in message 
> news:1161326760.938515.185890_at_m73g2000cwd.googlegroups.com...

>> DA Morgan wrote:

>>> joeNOSPAM_at_BEA.com wrote:
>>>
>>>>>>> I would question the reason for would allowing two different 
>>>>>>> isolation
>>>>>>> levels within a single application. What is the business case?
>>>>>>> --
>>>>>>> Daniel A. Morgan
>>>>>> Hi Dan. I didn't notice where anyone was limiting to one application
>>>>>> or specifying multiple isolation levels. Let us posit one application
>>>>>> that hopes to use Oracle's serializable isolation level, and one
>>>>>> rogue/bumbling admin that mistakenly truncates a table. Should
>>>>>> Oracle behave as claimed for the application's serializable tx?
>>>>>> JoeBumbling or not ... if that admin truncates a production table 
>>>>>> while
>>>>> people are using the app I would expect that to be their last day of
>>>>> employment.
>>>>>
>>>>> You are correct no one limited it to a single app. But if it was two
>>>>> different apps I would expect that they would never be hitting the
>>>>> same objects.
>>>>> --
>>>>> Daniel A. Morgan
>>>>> University of Washington
>>>> You're welcome! I think we agree that idiocy or malice is required to
>>>> trigger this issue.
>>>>    To narrow back in on the point, it is still something I would report
>>>> to Oracle for them to harden their transaction-safety in this regard.
>>>> The innocent ongoing serializable transaction should not be allowed
>>>> to silently corrupt. If a drunk drives his SUV the wrong way onto the
>>>> freeway, he is fully responsible for the results, but if he should hit
>>>> another SUV, the occupants of this second SUV should rightly
>>>> expect that at least their seatbelts and airbags will work as
>>>> advertised.
>>>> The SUV vendor would certainly be very interested in preventing any
>>>> circumstance where an accident could occur without the car's safety
>>>> devices deploying or working when they could have helped.
>>>>
>>>> Joe Weinstein at BEA Systems
>>> Bob Jones is pointing out, correctly, that TRUNCATE is DDL: Not DML.
>>>
>>> Given that he is correct about this then I would expect that the
>>> behaviour given a truncate should be the same as that from DROP TABLE.
>>> With that consideration how do you feel about what you are observing?
>>>

>> *I* feel that this should be considered a bug:

>>
> 
> I can't drive my car on water. There must be a bug.
> 

>> Compare 1,2,3,4,5,6A,7
>> with 1,2,3,4,5,6B,7
>> (Oracle 9i2)

>>

>> cheers,
>> Martin

>>

>> -- SESSION 1

>>

>> -- 1)
>> create table test_table as
>> select object_id myid, object_name myname from all_objects
>> where rownum < 100;

>>

>> -- 2)
>> select count(*)
>> from test_table;
>> -- ... 99

>>

>> -- 3)
>> commit;

>>

>> -- 6 A)
>> drop table test_table;

>>

>> -- 6 B)
>> truncate table test_table;

>>


>> -- *********************************

>>

>> -- SESSION 2

>>

>> -- 4)
>> commit

>>

>> ALTER SESSION set isolation_level=serializable

>>

>> --5)
>> select count(*)
>> from test_table
>> -- ... result = 99

>>

>> -- 7)
>> select count(*)
>> from test_table
>> -- A) ... ORA-00942: table does not exist
>> -- B) ... result = 0 !!

>>
> 
> For the last time, if you want this to work, either use delete or table 
> lock. Serializable does not affect DDLs.
> 
> Don't complain to the manufacturer if your SUV cannot run on water.

I have to confess I too am shaking my head in amazement.

Hopefully not betraying my ignorance but my understanding is the same as yours. I can't understand why anyone would use DDL and expect it to behave transactionally.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Oct 20 2006 - 20:20:57 CDT