Skip site navigation (1) Skip section navigation (2)

Re: referential integrity preventing simultaneous insert

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Edwin Grubbs <edwin(dot)grubbs(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: referential integrity preventing simultaneous insert
Date: 2004-09-10 14:39:31
Message-ID: 20040910143931.GA11825@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-general
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/

In response to

pgsql-general by date

Next:From: Ennio-SrDate: 2004-09-10 15:18:43
Subject: Re: Display of text fields
Previous:From: Bart McFarlingDate: 2004-09-10 14:26:58
Subject: Canceling Query due to user request

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group