Re: row locking and inserts

From: <xhoster_at_gmail.com>
Date: 29 Mar 2007 02:15:02 GMT
Message-ID: <20070328221505.690$Ta@newsreader.com>

"Vladimir M. Zakharychev" <vladimir.zakharychev_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;

Xho

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

Received on Wed Mar 28 2007 - 21:15:02 CDT