Re: serializable isolation level behavior question

On Oct 19, 11:46 pm, "Martin T." <bilbothebagginsb..._at_freenet.de> wrote:
> DA Morgan wrote:
> > joeNOS..._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:
>
> 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 !!

I agree with you, and I suggest you open a TAR/CR with oracle support and/or get Tom Kyte to discuss the issue.
  You can simplify the issue by simply stating that there is a table with rows, and that one session truncates the table while another is doing a serializable tx and has already read from the table. A second read should either fail or get the same results as the first but it doesn't.

Joe Weinstein at BEA Systems Received on Fri Oct 20 2006 - 10:50:29 CDT