
In Informix:

> Standard (draft) says:
> 
>     Regardless of the isolation level of the SQL-transaction, phenomena
>     P1, P2, and P3 shall not occur during the implied reading of schema
>     definitions performed on behalf of executing an SQL-statement, the
> 
an alter table, create index or the like (tx must see new schema)

>     checking of integrity constraints, and the execution of referen-
>     tial actions associated with referential constraints. 
> 
> I'm not sure what they exactly mean. Could someone run two tests
> for me (in Oracle and Informix/Sybase)?
> 
> create table p (k integer primary key);
> create table f (k integer references p(k));
> 
> session-1:
begin work;
> set transaction isolation level serializable;
   Informix needs:           ^^^^^   level not mode
> select * from f; -- just to ensure that xaction began -:)
> 
> session-2:
begin work;
> insert into p values (1);
> commit work;
> 
> session-1:
> insert into f values (1);
> --
> -- Results? Abort?
> --
> 
Goes ok in both isolation levels. Only if session-2 insert is not committed,
the session-1 insert fails with:
   691: Missing key in referenced table for referential constraint
(zeu.r155_262).
   144: ISAM error: key value locked

> What's the result in the case of read committed isolevel in
> session-1? Is insert succeeded?
> 
Yes.

Andreas

Wed, 16 Jun 1999 10:09:44 +0200
ZEUGSWETTER Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
Re: [HACKERS] Q: RI and isolevels
ZEUGSWETTER Andreas IZ5 wrote:
> 
> In Informix:
> > set transaction isolation level serializable;
>    Informix needs:          ^^^^^   level not mode

This was my fault...

> > session-1:
> > insert into f values (1);
> > --
> > -- Results? Abort?
> > --
> >
> Goes ok in both isolation levels. Only if session-2 insert is not committed,

Well... Thanks!
The problem for us and Oracle: subsequent selects from p in
session-1 will not return key 1... So, I would like to know
what Oracle does...

Vadim

Wed, 16 Jun 1999 16:38:42 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Q: RI and isolevels