Hi,
I just got trapped by one of my own features in the referential integrity area.
The problem is, that the trigger run on the FK row at UPDATE allways checks and locks the referenced PK, even
ifthe FK attributes didn't change. That's because if there'd be an ON DELETE SET DEFAULTS and someone deletes a
PKconsisting of all the FK's column defaults, we wouldn't notice and let it pass through.
The bad thing on it is now, if I have one XACT that locks the PK row first, then locks the FK row, and I have
another XACT that just want's to update another field in the FK row, that second XACT must lock the PK row in the
firstplace or this entire thing leads to deadlocks. If one table has alot of FK constraints, this causes not
reallywanted lock contention.
The clean way to get out of it would be to skip non-FK-change events in the UPDATE trigger and do alot of extra
workin the SET DEFAULTS trigger. Actually it'd be to check if we're actually deleting the FK defaults values
fromthe PK table, and if so we'd have to check if references exist by doing another NO ACTION kinda test.
Any other smart idea?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com