Re: serializable isolation level behavior question

On Oct 18, 2:36 pm, DA Morgan <damor..._at_psoug.org> wrote:
> joeNOS..._at_BEA.com wrote:
>
> > On Oct 18, 10:07 am, DA Morgan <damor..._at_psoug.org> wrote:
> >> joeNOS..._at_BEA.com wrote:
>
> >>> On Oct 17, 6:07 pm, HansF <Fuzzy.Greybe..._at_gmail.com> wrote:
> >>>> On Tue, 17 Oct 2006 07:53:17 +0000, Laurenz Albe wrote:
> >>>>> But that is not standard compliant, is it?
> >>>> When did the truncate command become part of the SQL standard?
> >>> The issue at hand is not whether truncate is part of standard
> >>> SQL. The issue is that if a standard SQL client is doing a
> >>> serializable transaction*, and some other client does a truncate
> >>> or anything else, standard or not, should the tx client expect oracle
> >>> to either deliver on the specified isolation level guarantees or notify
> >>> the tx client of a failure? Is it acceptable that Oracle allow a silent
> >>> failure of the tx? As described, if a serializable tx gets different
> >>> results for repeats of the same query, that is already a silent
> >>> failure.
> >>> Joe Weinstein at BEA Systems
> >>> * (which does include a guarantee of repeatable reads)
> >> 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 Received on Thu Oct 19 2006 - 10:18:08 CDT