Re: serializable isolation level behavior question

Bob Jones wrote:
> <hasta_l3_at_hotmail.com> wrote in message
> news:1161414027.408546.305470_at_i42g2000cwa.googlegroups.com...
> > Bob Jones wrote :
> >
> >> >> >
> >> >> > The OP transaction performed casher DML statements.
> >> >> > It had transaction level serializable.
> >> >>
> >> >> No, the session that was serializable was doing a truncate that is NOT
> >> >> a
> >> >> DML.
> >> >>
> >> >
> >> > Well, the way I read the OP description copied below,
> >> > the serializable transaction (in session 2) issued only selects.
> >> >
> >> >
> >>
> >> Okay, I got it backwards, but it still makes no difference to the
> >> argument.
> >
> > It changes a lot, in my opinion, Bob.
> >
> > Let's wear the hat of the innocent Oracle user/developer.
> >
> > We write a nice serializable transaction, that uses only casher DDL.
> >
> > Being good programmers, we envision that Oracle sometimes raises
> > all sorts of errors, and make sure that our transaction is rolled back
> > if one happens.
> >
> > Now, assume that while our innocent, well-written code is running
> > our transaction, somebody else truncates the temporary table we
> > are using, perhaps by error.
> >
> > And ***horror***, our transaction does not get an error. It does not
> > get rolled back. It continues quietly, with wrong data, perhaps
> > printing out a wrong financial report, or storing in the database
> > incorrect clinical data.
> >
> > This is just plain wrong !!!
> >
> > You know, one of the things I like most with Oracle is its
> > philosophy - and reality - that either its promises to a transaction
> > are fulfilled, or an error is raised.
> >
> > But the behavior here discussed is just not on par. I dont care
> > about serializable transactions. But I do care about the philosophy,
> > because my data depends on it.
> >
> > Have a nice day.
> >

>

>

Of course, there is no way to fully protect against mistakes.

But good design, good security *and* good error detection from software components - including Oracle ! - together go a long way to mitigate the risks.

> 2. If you do not want anybody else to DDL the table during your transaction,
> lock the table. Serializable only works when all parties are performing
> transactions. That was why I suggested using delete.

Agreed, but we are not discussing whether the transaction should complete successfully after a DDL, Bob. Everybody agrees it should not.

We are discussing whether an error should be reported by Oracle.

And IMO it should.

Because pretty no application wants DDL to be performed against tables it is using. And nevertheless these applications do *not* lock their tables,
for concurrency reasons. They all rely on Oracle to either prevent the DDL or report an error when the invariants are no longer true.

And that's as it should be. Received on Sun Oct 22 2006 - 00:47:55 CDT