Re: Foreign Keys and Deadlocks

Hi David,

On Thu, 2011-11-03 at 15:30 -0700, David Kerr wrote:
> I suspect that it has to be a transaction, and that further up in the TX is an update to one of
> the reference tables in each TX.

This is your cause - updating the referenced table in the same
transaction. That will want an exclusive lock on the row, but the shared
lock taken by the foreign key check (in another process) is conflicting,
and will deadlock when the other process will also want to update some
row in the referenced table which is locked by a foreign key check in
this process.

While the lock on the referenced row was changed to be a shared lock
instead of an exclusive lock as in older postgres versions (see
http://archives.postgresql.org/pgsql-general/2002-11/msg00397.php for
the original problem, which is relaxed now), the lock is still too
strong and the deadlock problem remains. A solution is not trivial at
all, and involves only locking the row for changes of the referenced
columns (which postgres can't do currently).

While getting rid of the foreign key will solve your problem, I think
it's not the best solution - you can perhaps design a way to not update
the referenced tables in the same transaction.

Here we adopted a different solution - we run a patched postgres which
skips that lock altogether, which means a partially broken foreign key
code which mostly works but can leave orphans. I will not recommend to
do that though - the reasons we did it that way is that it was the path
of least resistance as the application was also running on other DBs
(which were the primary DB at that time) and there was no way to make
extensive changes to the application code.

If I were to change the code, I would have separated the updated fields
from the parent table to yet another child table, and have the parent
table never updated. That will still have some potential for deadlock
(if you don't order the inserts/updates properly) but much less.

Cheers,
Csaba.