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

Christopher,

"Christopher Browne" <cbbrowne_at_acm.org> wrote in message news:bv3r66$n642e$1_at_ID-125932.news.uni-berlin.de...
> After a long battle with technology, boston103_at_hotmail.com (VC), an
earthling, wrote:
> > I see you've conveniently excised the update statement I supplied.
> > 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;
> >
> > However, it's not honored by Oracle in the SERIALIZABLE IL.
>
> SQL has a keyword for expressing constraints, called CONSTRAINT; you
> have "conveniently" chosen not to use it. You have instead set up a
> query that is NOT a "constraint."

The constraint is a _condition_ which is automatically enforced by the database. Since Oracle does not have database constraints, one has to code them manually as I did directly in the query. This is the condition:

 (select t1.amount+t2.amount from t1 join t2 on t1.id=t2.id) > [the_update_value] --(corrected thanks to Jonathan Lewis)

But whether the above is called a condition, or predicate or constraint is beside the point. What I was trying to show was that the transaction schedule in my previous posting is not seriaizable under Oracle's "SERIALIZABLE" IL. VC

> --
> wm(X,Y):-write(X),write('@'),write(Y). wm('aa454','freenet.carleton.ca').
> http://cbbrowne.com/info/sgml.html
> REALITY is a mescaline deficiency.
Received on Mon Jan 26 2004 - 16:38:54 CST