Subject Re: Poor selectivity in foreign keys
Author johnmancuk
Hi Helen,

Not kidding! There could still be potential problems with cascade
deletes when your using snapshot or read committed, eg (using the
simple tables I detailed last time):

Trans 1 (snapshot, or read committed):
delete from master where id = 1;
(user trigger cascases, and removed all linked detail rows)

Trans 2 (snapshot or read committed):
insert into detail values (99,1); (***)

Trans 1:

RI broken.

The step marked with *** would fail if FK constraints were used,
because the index would see the uncommitted change.

Of course this may not be an issue for German - if his Master table
is very static.

Table Stability isolation would would seem like a very good idea
when custom RI is being used in a multi-trans enrivonment. Of course
if he's only got one trans going at once on FB Embedded, he can use
whatever isolation level he wants! Including Tab Stab.


--- In, Helen Borrie <helebor@t...>
> At 11:06 PM 16/10/2004 +0000, you wrote:

> > > after having read all your replies, I still wonder if I can
keep the SP's for
> > > cascaded deletes. Maybe I forgot to say that this application
is using FB
> > > embedded and is single user, no network.

> >You could still have potential problems, if you're using multiple
> >simultanious transactions.
> >
> >TABLE STABILITY transaction isolation will ensure only one trans
> >using the table at once. Either use that, or use indexes, to be
> >ultra-safe.
> You're kidding, aren't you?