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

Hello Galen,

"Galen Boyer" <galenboyer_at_hotpop.com> wrote in message news:ur7xmii44.fsf_at_standardandpoors.com...
> On 26 Jan 2004, boston103_at_hotmail.com wrote:
>
> > 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;
>
> How is the constraint in your statement? You have only
> restricted the selected rows.

As I've alredy responded to others, the constraint is just a condition and since Oracle does not provide database constraints, a programmer has to code them manually as I did in my trivial example.

In order to mimic an 'automatic' database level constraint, the condition above can be enforced via a trigger throwing an exception when the condition evaluates to false.

>You have said nothing about what
> should be done with the rows on update.

Please elaborate on the above.

>
> What if I type:
>
> update employee set salary = salary*.1 +salary where salary < min_wage;
>
> With your argument, the above should fail.

I am not sure what you want to demonstrate with your 'employee' SQL. Please elaborate.

Actually, my small test has got nothing to do with constraint implementation in Oracle or any other database. I just wanted to show that a simple transaction schedule consisting of two transactions written in 'pure' SQL is not serializable under Oracle's "SERIALIZABLE" isolation level.

VC

> --
> Galen Boyer
Received on Mon Jan 26 2004 - 16:54:35 CST