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

Hello Christopher,

"Christopher Browne" <cbbrowne_at_acm.org> wrote in message news:bv1hof$n0den$4_at_ID-125932.news.uni-berlin.de...
> And note that this scenario involves not one, but TWO misreadings of
> SQL.
>
> 1. It is treating aggregates as if they were attributes. They are
> not.

Please elaborate on 'treating aggregates as if they were attributes'. What has it got to do with serializability ?

>
> 2. It assumes that there is a serialization problem.
>

The scenario does not assume that there is a serializability problem -- it clearly demonstrates that there indeed is a problem.

of the transactions, with time thrown in as
> a variable, is more or less the following:
>
> T1:
> If the number of entries in table 1 when Transaction T1 starts is <
> 10 then insert into table1 values (...);
>
> T2:
> If the number of entries in table 1 when Transaction T2 starts is <
> 10 then insert into table1 values (...);
>
> If T1 and T2 each start before the other one finishes, with 9 values
> in the table, then there will indeed be 2 entries inserted, and there
> is nothing mis-serialized about it.

How so ? If the facts do not match the theory, so much worse for the facts ?
It's a clear cut case of the SNAPSHOT IL _phantom_ (which can also be viewed as 'write skew' anomaly).

> > (I am assuming that if/then/else is not part of the SQL standards,
> > if this is not the case, please let me know.)
>
> Nope.

Well, PL/SQL's not being part of the SQL standard has got nothing to do with the transaction serializabily.

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.

In transaction one:

alter session set isolation_level=serializable; update t1 set amount=amount-80 where (select t1.amount+t2.amount from t1 join t2 on t1.id=t2.id) > 0;
commit;

In transaction two:

alter session set isolation_level=serializable; update t2 set amount=amount-80 where (select t1.amount+t2.amount from t1 join t2 on t1.id=t2.id) > 0;
commit;

In any transaction:

SQL> select t1.amount+t2.amount from t1 join t2 on t1.id=t2.id;

T1.AMOUNT+T2.AMOUNT

                -60

The constraint is clearly violated -- therefore, the transaction schedule is not serializable.

Rgds.

VC Received on Mon Jan 26 2004 - 05:26:39 CST