At 02:31 PM 16/10/2004 +0000, johnmancuk wrote:

>--- 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???? ;)

Out of context, yes.

>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".

You did - in context, my point was that, even *without* the declarative
RI Trans2 trying to update or delete something that Trans1 has a pending
operation on, and vice versa, will fail. (BTW, forget "indexes" in this
argument. Indexes don't "know" anything. It's the CONSTRAINT--the
existence rules embodied in the supporting triggers--not the indexes, that
enforces the relationship.

With Fb/IB, we happen to have a problem with the way RI was implemented,
specifically, that the key-for-key index on the foreign key column(s) is
mandatory. It is there for the purpose of enabling the constraint triggers
to do an indexed search on the children -- an acceptable design decision if
only Fb/IB didn't have this problem with any index of very low selectivity.

In practice, if it were possible to allow the constraint to use a
user-specified index for its search, there would be no need to consider
custom RI, which has some risks if used in an environment where Read
Committed isolation is used carelessly -- as you demonstrated.

In fact, it had been the intention for Fb 1.5 to fix the problem of the
mandatory index, for exactly the reason we are currently discussing. It
was Ann who researched the implications and found that the "reach" of the
current implementation was too pervasive to fix easily.

Until this design wart is fixed - either by doing the stuff needed to allow
a foreign key constraint to be defined so that the USING clause can specify
an existing index that is not key-for-key, or by improving the geometry of
indexes, we can, with care, use custom RI triggers to solve the performance
problems with lookup keys.

./heLen