Re: row locking and inserts

"Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com> wrote:
> On Mar 26, 10:35 pm, xhos..._at_gmail.com wrote:
> > "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> > > On Mar 26, 2:29 am, xhos..._at_gmail.com wrote:
> > > > sybra..._at_hccnet.nl wrote:
> > > > > On 25 Mar 2007 09:40:59 -0700, "pp" <pedro.e.pi..._at_gmail.com>
> > > > > wrote:
> >
> > > > > >For correctness, my application requires that access to this
> > > > > >subset be serialized.
> >
> > > > > Oracle allows you to set the isolation_level of a transaction to
> > > > > serializible.
> >
> > > > No, Oracle lets you set the isolation_level to "serializable".
> > > > However, the scare quotes are necessary, as it is not truly
> > > > serializable.
> >
> > > Hmm... How come it's not "truly serializable"?
> >
> > Because there are transaction regimes for which the net result of two
> > successful concurrent transactions are not the same as at least one of
> > the serial execution orders of those same two regimes.
> >
> > (for example, the one that the OP brought up)
> >
> > > Afaik, Oracle's
> > > implementation of serializable isolation level fully conforms to
> > > SQL92 definition and prevents all phenomena that it is required to.
> >
> > I don't know if it is SQL92 or one of the earlier standards, but
> > whatever one I went back and read a while ago defined serializable as
> > being, well, serializable. Any successful conclusion of a concurrent
> > set of trasactions must leave the system in the same state as at least
> > one non-concurrent ordering of those same transactions would have.
> >
> > It then gave a non-exhaustive and non-definitive list of common
> > phenomena that are examples of nonserializibility. Oracle avoided
> > those common phenomena, but in the process introduced new ones (without
> > pithy names) that violate serializibility.
> >
> > I'm not a standards-lawyer, but it did seem pretty clear to me at the
> > time.
> >
> I don't think the list is "non-exhaustive and non-definitive". Just
> checked SQL:1999 and there seem to be no statement supporting this
> claim; surely SQL92 didn't state that either. Actually, the isolation
> levels themselves are defined in terms of which phenomena of the three
> defined and described they must prevent.

The standard specifically disclaims that assertion for the case of SERIALIZABLE: "Note: The exclusion of these phenomena for SQL-transactions executing at isolation level SERIALIZABLE is a consequence of the requirement that such transactions be serializable."

(SQL92, I don't have other ones handy).

Those phenomena are not the definition of SERIALIZABLE, they are merely consequences of that definition.

> And Oracle's implementation
> of SERIALIZABLE does prevent all three (put aside infamous TRUNCATE
> behavior.) Or can you provide examples when it doesn't?

As I said, it prevents these three specific phenomena, but in the process introduced other complex phenomena which still violate serializability.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB