Обсуждение: referential integrity preventing simultaneous insert

Hello,

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.
Here is an example of how to recreate the problem. Please ignore the
lack of sequences, since that is irrelevent to the problem. I have
tested this under pg 7.4.3.

----------------------------------------------------------
-- Set up test database:

CREATE TABLE model (
    id INTEGER PRIMARY KEY,
    name varchar(20)
);

CREATE TABLE car (
    id INTEGER PRIMARY KEY,
    model_id INTEGER REFERENCES model
);

INSERT INTO model VALUES (1, 'one');

----------------------------------------------------------
-- First connection opens a transaction and adds a reference to model id=1.
BEGIN; INSERT INTO car VALUES (5, 1);
-- Do not commit this transaction.
----------------------------------------------------------
-- Open a separate connection.
-- Second connection does the same and will hang until the
-- first transaction ends.
BEGIN; INSERT INTO car VALUES (6, 1);
----------------------------------------------------------

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

Thanks,
Edwin Grubbs

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/