Re: referential integrity preventing simultaneous insert

On Thu, Sep 09, 2004 at 12:53:57PM -0500, Edwin Grubbs wrote:

> I have experienced problems with postgres hanging when two inserts
> reference the same foreign key. It appears that the second insert is
> waiting for the first insert to release a lock.

You can also create a deadlock situation:

transaction 1:  INSERT INTO car VALUES (5, 1);
transaction 2:  INSERT INTO car VALUES (6, 2);
transaction 1:  INSERT INTO car VALUES (7, 2);
transaction 2:  INSERT INTO car VALUES (8, 1);

ERROR:  deadlock detected
DETAIL:  Process 16919 waits for ShareLock on transaction 14686; blocked by process 16920.
Process 16920 waits for ShareLock on transaction 14687; blocked by process 16919.
CONTEXT:  SQL query "SELECT 1 FROM ONLY "public"."model" x WHERE "id" = $1 FOR UPDATE OF x"

There was a thread about this a couple of years ago:

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

Apparently the exclusive lock is necessary to prevent other
transactions from modifying the foreign key before this transaction
commits.  As one of the followups to the above thread mentioned,
it would be nice to have a FOR PREVENT UPDATE lock that could be
shared, but we don't, so PostgreSQL uses the exclusive FOR UPDATE.

If you set up your foreign key references as DEFERRABLE, you can
avoid the blocking and potential deadlock by issuing SET CONSTRAINTS
ALL DEFERRED at the beginning of your transactions.  But then you
won't detect foreign key violations until the COMMIT, which might
be a problem for your application.

> Is this fixed in postgres 8? If it still requires work, I may be able
> to help fund it to get it completed sooner.

PostgreSQL 8.0.0beta2 still behaves this way.  Maybe one of the
developers can comment on the possibility of a shared FOR PREVENT
UPDATE lock.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/