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: Mon, 26 Jan 2004 22:31:28 GMT
Message-ID: <0zgRb.154071$I06.1552615@attbi_s01>


Hello Jonathan,

Please see below.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bv3c9o$qrv$1_at_sparta.btinternet.com...
> >
> > The constraint is right there, in the statement itself:
> >
> > update t1 set amount=amount-80
> > where (select t1.amount+t2.amount from t1 join t2 on t1.id=t2.id) > 0;
> >
>
> That's not a constraint, that's just a simple condition.
>

Well, a constraint is nothing more but a conditional expression that's required to evaluate to true. Since Oracle does not have database constraints (constraints enforced between different tables), one has to manually code them

> The following statement works just as well on your original
> dataset (and I suspect it would do the same thing on all the
> commercial RDBMSs):
>

update t1 set amount=amount-1000
> where (select t1.amount+t2.amount from t1 join t2 on t1.id=t2.id) > 0;

Thank you for pointing out my error. The condition should rather be as follows:

update t1 set amount=amount - the_update_amount  where (select t1.amount+t2.amount from t1 join t2 on t1.id=t2.id) > the_update_amount;

> I think the point you are really making isn't about
> serializability, it's about commercial databases failing
> to implement some features of generic constraints.

No, actually my point was precisely about serializability. Earlier in the thread Heikki Tuuri mentioned that neither Oracle nor Postgres (which mimics Oracle's concurrency mechanism) implement the true SERIALIZABLE IL. Someone, I think Ed Avis, asked to give an example of a scenario in which non-serializable transactions use 'pure' SQL without PL/SQL extensions (although I am not sure why it matters).

Here's my example:

the following schedule (S1) whilst runnig under Oracle's SIL:

T1(update);T2(update);T1(commit);T2(commit);

produces a result different from the SERIAL execution (S2):

T1(update);T1(commit);T2(update);T2(commit);

Therefore, S1 is not serializable.

Rgds.

VC Received on Mon Jan 26 2004 - 16:31:28 CST

Original text of this message

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