Re: row locking and inserts

On Mar 29, 6:15 am, xhos..._at_gmail.com wrote:
> "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
>
>
>
> > On Mar 27, 8:55 pm, xhos..._at_gmail.com wrote:
> > > "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
>
> > > > > 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.
>
> > Ok, probably I misunderstood this part (btw, SQL:1999 bears the same
> > note,) and the definition is this:
> > "The execution of concurrent SQL-transactions at isolation level
> > SERIALIZABLE is guaranteed to be
> > serializable. A serializable execution is defined to be an execution
> > of the operations of concurrently
> > executing SQL-transactions that produces the same effect as some
> > serial execution of those same
> > SQL-transactions. A serial execution is one in which each SQL-
> > transaction executes to completion
> > before the next SQL-transaction begins."
> > Still, I'm yet to see an example of violation of this definition in
> > Oracle. That is, a set of transactions each running at SERIALIZABLE
> > isolation level and producing a database state that can't be achieved
> > by executing these transactions serially in any possible order.
>
> I think the original poster had a complicated real-world case. For a
> simple (but rather silly) pedagogical example:
>
> create table foo ( thing1 number, pk varchar2(20));
>
> T1: set transaction isolation level serializable;
> T1: insert into foo (thing1, pk) values ( 100 - nvl((select sum(thing1)
> T1: from foo where pk='x'),0),'x');
> T2: set transaction isolation level serializable;
> T2: insert into foo (thing1, pk) values ( 100 - nvl((select sum(thing1)
> T2: from foo where pk='x'),0),'x');
> T1: commit;
> T2: commit;
>

Nice, and indeed a violation of the isolation level requirements. A primary key constraint would prevent it, but it's irrelevant to the case.

Thanks,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Mar 29 2007 - 11:15:33 CDT