Hi Helen!

Comments inline:

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:55 PM 16/10/2004 +0000, you wrote:
> >If you used proper RI and had indexes, the action in trans 2 would
> >fail because it would know the value was changed in trans 1, even
> >though it's not committed. Kinda like a dirty-read, but not!
>
> Hmmm, you seem to be a bit short on your understanding of

transaction

> isolation. Trans2 won't be able to update or delete anything that

trans1

> has pending; and Trans1 on't be able to update or delete anything

that

> trans2 has pending.

Arn't we agreeing there Helen???? ;) I said "The action in trans 2
would fail because it [the FK index] would know the value had
changed in trans 1" and you said "Trans2 won't be able to update ..
anything trans1 has pending".

> In the case of ReadCommitted isolation, what happens depends on

how the

> transactions are committed - which affects both declarative RI and

custom

> RI triggers.

I agree, but I gave a specific case with commits described. In that
example declaritive RI would protect you but triggers wouldn't. For
example:

create table master (id int not null primary key);
create table detail (id int not null primary key, masterid int not
null);

Update trigger on master:
update detail set masterid = new.id where masterid = old.id;

Any trans:
insert into master values (1);
insert into master values (2);
insert into detail values (1,1);
commit;

Begin Trans 1 (read committed):
update master set id = 99 where id = 1;
(ok, so trigger changes (1,1) in detail to (1,99). cool)

Begin Trans 2 (read committed):
insert into detail values (2,1);
Commit;

Trans 1:
commit;

Trans 3, any type:
select * from master;
99
2

select * from detail;
1,99
2,1

Broken RI.

This doesn't happen with declaritive RI, the insert in trans 2 would
fail ("violation of FK constraint").

> >In fact it would stop you refering to both trans1.old and

trans1.new

> >values, because both are in limbo.
>
> Incorrect. A limbo transaction is one across multiple databases

that was

> prevented from completing the second phase of two-phase

commit/rollback.

I didn't say a "limbo transaction", I was talking about limbo values
when indexes will (in some situations) act as if both old and new
values exist, even though trans 1 sees only newvalue, and trans 2
sees only oldvalue.
Limbo means "a state of uncertainty", which is exactly what I
meant ;)

For example, when the update:

update master set id = 99 where id = 1;

is executed in trans1, another transaction could not add *either* 1
or 99, until trans1 was ended. If trans1 is committed you can insert
1 in the other trans, if trans1 is rolled back, you can insert 99.

That's what I mean by limbo values! One value blocking two inserts.

> >If you commit trans1 you could
> >then refer to trans1.new, if you rollback trans1 you could then
> >refer to trans1.old.
>
> Nope. Either commit or rollback ends the transaction and the new

and old

> values cease to exist.

Obviously, there is no syntax for referencing TransactionName.NEW or
TransactionName.OLD. I was using shorthand for "The original value,
as it was at the start of transaction 1" and "The new values when it
has been changed inside trans 1".

Hence when you commit trans1, trans1.new (ie the value you've
changed in trans 1) can be referenced by FK's in other transactions,
even though they may not be able to see it using 'select' - in the
case of snapshot isolation. It could be seen by read committed
transactions.
Similarly, if you rollback trans1, then you are once again allowed
to reference trans1.old (ie the value as it was before trans1
altered it) using other transactions and FK's.

Did I get that right Helen?? ;)

John