Subject Re: [firebird-support] update, foreign keys, locking
Author unordained
Thanks Helen,

I think part of the reason for the confusion is that in this particular application, we killed off
the ability to delete rows. Normal users simply can't do it -- we had too many problems on
the "old" system [paradox database] with users randomly deleting rows (and it occasionally made
sense, so taking the priv. away wasn't really an option.) They have an update mechanism for marking
rows as "bad" but they can't delete them themselves. We also never change the value of the PK such
that it would need to cascade -- in other words, I can't think of any cases where in -our-
situation, the server would need to lock the child record because of an action it might need to
take that would cascade to dependent records. I -do- see however why this is possible (and a good

How far does FB know to go? If you have a cascade-delete, wouldn't it need to lock not only the
child record, but grandchildren also? (or rather, as far down the chain as is necessary given each
table's delete/update rules) ... Circular dependencies? A->B->C->A, lock a record in any of
them ... all of them set to cascade ... that could be a lot of locks, depending on graph of FK's.

As to deadlock -- I think the problem is just that the name reminds me of 'gridlock', or deadlock
in the old IBM sense: multiple processes holding on to their cursors while trying to advance,
keeping each other from what they wanted ... but yes. We love deadlocks. I'd hate to deal with our
project without transaction consistency! (Upon deadlock, we specifically tell the users that they,
or someone else, may have the current record unlocked for editing ... or a parent record ... or
something related, and that they should just poke around the office asking questions, or just wait
a minute and try again. Our network-update protocol catches the commit() when it happens, so
they'll know when whoever was busy with the record(s) is done.)

Thanks -- working on a project long enough makes you forget random possibilities you've not had to
deal with in a while ... thanks for the refresher!