Re: Foreign Keys and Deadlocks

Excerpts from David Kerr's message of mié nov 09 14:52:01 -0300 2011:
> On Wed, Nov 09, 2011 at 11:11:23AM -0300, Alvaro Herrera wrote:

> - This case is not helped by the patch I'm working on. As far as I can
> - see, if you got rid of the PK in table a in your example script, things
> - should work just fine. There is no way to cause FK-induced deadlocks
> - with only inserts in 8.1 and later.
>
> Ok, well that's good to know. I had planned on testing my script w/o FKs but it slipped
> my mind.
>
> So, aside from removing the PKs do i have any other options? (we use Hibernate and
> i don't think that I'll be able to removet he Primary Keys, and a serial primary key
> probably isn't great for this table because it's sort of a staging area (so it gets
> written to and wiped out frequently)

Not sure about that.

> Would you consider this a problem in Pg or is it unavoidable?

Well, you have to ensure that only one copy of two or more concurrent
insertions of a given PK value will survive. Otherwise the unique
constraint would be violated. This is currently implemented with sleeps
in the second inserter, which waits until the first transaction is
closed. Note that there is some code to support deferred uniqueness
checks, which might help, but I don't know if it can be applied to
primary keys. I'd recommend reading the manual on that subject.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support