Subject Re: [firebird-support] Re: Poor selectivity in foreign keys
Author Helen Borrie
At 12:55 PM 16/10/2004 +0000, you wrote:

>If it's read committed you can only see committed values! The update
>in trans 1 isn't committed yet - so isn't visible from trans 2.
>Try it!
>I always think Firebird isolation is a bit like Relativity...
>everything depends of frames of reference!
>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.

In the case of ReadCommitted isolation, what happens depends on how the
transactions are committed - which affects both declarative RI and custom
RI triggers.

>In fact it would stop you refering to both trans1.old and
>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.
Any applicable triggers - declarative RI, custom RI or other, have already
finished, whether by doing their work or by excepting.

The old and new values remain available and consistent within the
transaction that created them. They don't come into existence until the
request is posted and, once they exist, they are never affected by other

>If you commit trans1 you could
>then refer to, 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.

>This kind of magic is only possible with indexes.

No kidding!!!