Re: [GENERAL] FK deadlock problem addressed

If Liberty RMS help pay for your work on this, send them my thanks, please!

Jan Wieck wrote:
> On behalf of Liberty RMS I looked at the deadlock problems caused by our
> implementation of foreign keys. Thanks to peer review and help from
> Stephan Szabo and Tom Lane (Stephan actually had "the" idea) it turned
> out that the comment I wrote in December 1999 was wrong.
>
> I just committed a small change to ri_triggers.c that fires the NO
> ACTION trigger every time after the SET DEFAULT trigger updated the FK
> table. That will catch the case where we delete or update the primary
> key consisting of the default values of a foreign key, which was the
> reason why we did the check on UPDATE even if the foreign key values
> don't change.
>
> Updating a row that has foreign keys without touching the foreign key
> values will not try to lock the referenced rows any more. This should
> avoid most of the deadlock problems reported (we still have to do the
> FOR UPDATE lock if the FK values change until we have a better lock or
> lookup mechanism). As a side effect, it increases the performance of
> such updates significantly.
>
> I also have patches for this available for 7.3.2 and 7.2.4. I will post
> a separate message for discussion if we want to backpatch.
>
>
> Jan
>