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

From: Christopher Browne <cbbrowne_at_acm.org>
Date: 26 Jan 2004 04:03:18 GMT
Message-ID: <bv23i5$muf8m$3@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:03:18 CST