Re: row locking and inserts

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.
>
> Xho
>

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

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Mar 27 2007 - 01:36:58 CDT