Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Avoiding any locks in SQL Servers - read and understand....its magic.

Re: Avoiding any locks in SQL Servers - read and understand....its magic.

From: VC <boston103_at_hotmail.com>
Date: Thu, 29 Jan 2004 23:56:32 GMT
Message-ID: <Q4hSb.182146$xy6.877273@attbi_s02>


Hello Ed,

"Ed Prochak" <ed.prochak_at_magicinterface.com> wrote in message news:RYfSb.45$rK6.32_at_fe02.usenetserver.com...
> VC wrote:
> > Hello Ed,
> >
> > "Ed Avis" <ed_at_membled.com> wrote in message
> > news:l1isj1l2ym.fsf_at_budvar.future-i.net...
> >
> >>"Heikki Tuuri" <Heikki.Tuuri_at_innodb.com> writes:
> >>Is there an example of plain SQL (not using 'if' or other PL/SQL
> >>constructs) which is not serializable in SERIALIZABLE mode?
> >
> >
> > It's a rather well-known fact that Oracle's SERIALIZABLE IL is a
misnomer.
> > What Oracle calls SERIALIZABLE is, in fact, the SNAPSHOT isolation
level.
> >
> > As such, it exhibits, for example, the write-skew anomaly:
> >
> > Let's assume a bank has two linked accounts for married couple with a
> > constarint X+Y >0 (the total balance should be positive).
> >
> > Further, X0 = 70 and Y0 = 80.
> >
> > Transaction T1 running in the SIL subtracts 100 from X: update x set
> > amount=amount - 100;
> > Transaction T2 running in the SIL subtracts 120 from Y (because it sees
> > X0+Y0 = 150): update y set amount = amount - 120;
> > Both transactions commit and the constarint X+Y > 0 is violated;
>
> I'd be curious how this constraint is implemented, can a constraint refer
to
> two tables?

Please re-read the thread. Oracle does not currently support database-level constraints(assertions). There is a round-about way to describe such a constraint via a checked view (see my response to Lee Fesperman) but Oracle silently ignores it. I used a condition in the update statement in another version of my bank example in order to emultae such a constraint.

> And by "is not serializable" I take it you mean that C2, the second
commit,
> completes without returning an error.

Informal definitions: When two or more transactions are executing concurrently, the relative sequence of the read and write operations in the two transactions constitutes a SCHEDULE. The SERIAL schedule means executing one transaction in its entirety, then the other, etc. A schedule is SERIALIZABLE if its effect is logically equivalent to some SERIAL schedule. Since, in my bank example, the concurrent schedule is not equivalent to any SERIAL execution of T1 and T2, it's not SERILAIZABLE (although it was executed in the Oracle "SERILIZABLE" IL.

The issue of constraint implementation is secondary in my test.. What it demonstrated was that a schedule implemented using just SQL (as one poster requested) is not SERIALIZABLE in Oracles's SIL.

Rgds.

VC Received on Thu Jan 29 2004 - 17:56:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US