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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
