Re: RI oddness

hi, there!

On Mon, 23 Apr 2001, Jan Wieck wrote:

> 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 if the FK
> attributes didn't change. That's because if there'd be an ON
> DELETE SET DEFAULTS and someone deletes a PK consisting 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 first place or this
> entire thing leads to deadlocks. If one table has alot of FK
> constraints, this causes not really wanted 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 work in the
> SET DEFAULTS trigger. Actually it'd be to check if we're
> actually deleting the FK defaults values from the PK table,
> and if so we'd have to check if references exist by doing
> another NO ACTION kinda test.
>
> Any other smart idea?

read-write locks?

/fjoe