Re: row locking and inserts

On Mar 27, 8:55 pm, xhos..._at_gmail.com wrote:
> "Vladimir M. Zakharychev" <vladimir.zakharyc..._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.
>

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. Which state of all possible it will be, and is this state desired/expected, is another question. :)

Regards,

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