Thread: Deadlocks with foreign key references

Hi Everyone,

We are getting intermittent deadlocks in our application due to foreign
key references.   We've cooked up a simple case to exercise the problem -
but I can see from previous mailing list posts that people are aware of
the issue:

http://archives.postgresql.org/pgsql-hackers/2002-03/msg01130.php

My questions are as follows:

1) Previous posts have recommended setting the constraints to INITIALLY
DEFERRED.
    a) Why does this solve the problem?
    b) Do I need to do this with every table involved?

2) Is there an alternative solution?

3) Are there any provisions in a future release (7.3?) to address this
issue.  It
seems to be a pretty severe problem that in normal operation you can get
false
deadlocks do to foreign key constrains.

Thanks for any help,

Mike

On Fri, 21 Jun 2002, Mike Schroepfer wrote:

> We are getting intermittent deadlocks in our application due to foreign
> key references.   We've cooked up a simple case to exercise the problem -
> but I can see from previous mailing list posts that people are aware of
> the issue:
>
> http://archives.postgresql.org/pgsql-hackers/2002-03/msg01130.php
>
> My questions are as follows:
>
> 1) Previous posts have recommended setting the constraints to INITIALLY
> DEFERRED.
>     a) Why does this solve the problem?
>     b) Do I need to do this with every table involved?

In reverse order, probably, and it doesn't solve this problem precisely,
but makes the problem much less likely to occur in the case that the
actual conflict is due to the locks grabbed by the foreign keys themselves
(trans 1 gets a lock on a row of table1 due to an fk insert/update, then
wants a lock on row of table2 due to fk insert/update and trans 2 has the
reverse) due to the fact that the locks are held for a shorter amount of
time.

> 3) Are there any provisions in a future release (7.3?) to address this

We are working on it.  I'm not sure it'll make 7.3 or not.  There are some
issues with each solution I've tried and I'd like to make sure that we
don't add new problem cases when we fix the current ones (for example, the
one in the message above may add a problem case where a pk row is modified
twice while an fk row is added between the modifications if we don't
do additional checks, and I haven't had the time to sit down and work out
what those are)