Q: RI and isolevels
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
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:
set transaction isolation mode serializable;
select * from f; -- just to ensure that xaction began -:)
session-2:
insert into p values (1);
commit;
session-1:
insert into f values (1);
--
-- Results? Abort?
--
What's the result in the case of read committed isolevel in
session-1? Is insert succeeded?
TIA!
Vadim
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
Import Notes
Resolved by subject fallback
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