Thread: FK deadlock problem addressed

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

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

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
>