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

Hello,

Please see my next posting with a SQL including the constraint. Apparently, your newsreader has not received it yet.

Rgds.

VC

"Christopher Browne" <cbbrowne_at_acm.org> wrote in message news:bv23i5$muf8m$3_at_ID-125932.news.uni-berlin.de...
> Martha Stewart called it a Good Thing when "VC" <boston103_at_hotmail.com>
wrote:
> > But, anyway, here's an example for SQL purists:
> >
> > create table t1(id int, amount int);
> > create table t2(id int, amount int);
> > insert into t1 values(1, 40);
> > insert into t2 values(1, 60);
> > commit;
> >
> > -- two linked account have 100 total. The constraint is that the total
> > should not go below zero.
>
> What constraint? You have not described a constraint expressible in
> the database; only one that is in your head. The constraint is an
> artifact, not a fact or a rule.
>
> Try out:
>
> create table t1(id int, amount int);
> create table t2(id int, amount int);
> create table balance (amount int, constraint cannot_go_negative check
> (amount > 0));
>
> insert into t1 values(1, 40);
> insert into t2 values(1, 60);
> insert into balance (amount) values (100);
>
> The action of taking money out of either 'account' is expressed thus:
>
> begin;
> update t1 set amount = amount - 80;
> update balance set amount = amount - 80;
> commit;
>
> Take too much out of either account, and the balance will try to fall
> below zero, and gripe. That includes the situation where you were
> using SERIALIZABLE mode.
> --
> wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
> http://cbbrowne.com/info/linuxxian.html
> "Tough cookies. Technology makes some good business models go bad and
> eliminates certain categories of jobs. It happened for farming, it
> happened for manufacturing, why should newspapers or content providers
> be exempt?" -- Original Source Unknown
Received on Sun Jan 25 2004 - 22:27:37 CST