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