Re: serializable isolation level behavior question

From: Bob Jones <email_at_me.not>
Date: Sat, 21 Oct 2006 00:34:22 GMT
Message-ID: <iWd_g.16683$e66.13901@newssvr13.news.prodigy.com>

"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. Received on Fri Oct 20 2006 - 19:34:22 CDT