Re: serializable isolation level behavior question

From: joel garry <joel-garry_at_home.com>
Date: 19 Oct 2006 15:25:01 -0700
Message-ID: <1161296701.677249.93920@k70g2000cwa.googlegroups.com>

joeNOSPAM_at_BEA.com wrote:
> On Oct 19, 9:20 am, DA Morgan <damor..._at_psoug.org> 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 SystemsBob 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?
> > --
> > Daniel A. Morgan

>


>       Well, I suspect that the OP wouldn't care if the miscreant used

> DDL, DML, or DDT. As long as it's a public Oracle API, the question
> is whether Oracle should react by terminating the unsuspecting and
> innocent application's transaction in a visible, atomic way rather than
> permit it to continue/fail silently. In fact, the DBMS needn't fail the
> tx
> instantly during the second read. As far as I would say, if the DBMS
> simply ensures that the serializable tx fails later during commit
> (preferably
> with a clear message about someone DDL'ing away their data) there
> is nothing for Oracle to fix.

My opinion at this point is that Oracle should fail or lock out the DDL with a
"Hey, someone is using this stuff in a transaction!" since that gives the admin
the motivation to check out who.

Waiting for DML to fail during the commit - man, that could be way in the future.
So could a rollback.

Then again, maybe flashback could be physically used by the DML to finish the transaction,
or reading out of the recycle bin if enabled? So many possibilities... so many
performance problems.

--
@home.com is bogus.
Imagine possibilities!  http://catless.ncl.ac.uk/Risks/24.45.html#subj9

Received on Thu Oct 19 2006 - 17:25:01 CDT