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

Christopher,

OK. On the second reading, I can see that you did respond to my message contaning the SQL code that demonstrated a non-serializable schedule. See below:

"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.
>

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.

It appears that you are not quite familiar with the serializability/concurrency notions. Please consult the following before discussing the subject any further:

"Concurrency Control and Recovery in Database Systems", by P. Bernstein et al.

Rgds.

VC Received on Mon Jan 26 2004 - 08:23:31 CST