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

Hello Lee,

Please see below:

"Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message news:4015AC2D.3992_at_ix.netcom.com...
> Does a VIEW and WITH CHECK OPTION work for Oracle? That construct provides
an
> enforceable constraint.
>

Yes, it does. Actually, it's a very good idea to use the construct; although it does not change the substance of my experiment as it may help to emphasize the point that an honest to God constraint is not enforced in Oracle's SERIALIZABLE IL:

Given:

drop table t1;
create table t1(id int, x int);
insert into t1 values(1, 50);

drop table t2;
create table t2(id int, x int);
insert into t2 values(1, 50);

drop view v1;
create view v1
as
select id, x from t1
where x + (select x from t2 where id=t1.id) >0 with check option /

drop view v2;
create view v2
as
select id, x from t2
where x + (select x from t1 where id=t2.id) >0 with check option /

In transaction T1:
alter session set isolation_level=serializable; select * from v1;

In transaction T2:
alter session set isolation_level=serializable; select * from v2;

In transaction T1:
update v1 set x=-49 where id=1;
commit;

In transaction T2:
update v2 set x=-49 where id=1;
commit;

And then in any transaction:

SQL> select * from t1,t2;

        ID X ID X ---------- ---------- ---------- ----------

         1 -49 1 -49

The constraint has been silently violated.

Rgds.

VC

> --
> Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
> ==============================================================
> * The Ultimate DBMS is here!
> * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
Received on Mon Jan 26 2004 - 20:14:09 CST