Re: serializable isolation level behavior question

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:

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 !!