Here is an example, pardon syntax sloppiness:
create table locations
(
id serial,
address varchar,
...
)
-- In the real application there are 5 tables like this
create table a1
(
from int,
to int,
...
FOREIGN KEY (from) references locations(id),
FOREIGN KEY (to) references locations(id)
)
This causes deadlock with two simple inserts
insert into a1(from,to) values (1,2)
insert into a1(from,to) values (2,1)
I called this a scalability problem because clients timeout and retry properly so it
has negligible impact with 20 clients, noticeable with 50, and unacceptable with 70.
The solution I am looking at is to duplicate the locations table since I can put
appropriate protections to make sure they stay in sync (it is already a modify
restricted table and is rarely updated):
create table locations_1 ( ... )
create table locations_2 ( ... )
then have all "from" foreign keys in tables a1 through a5 reference locations_1 and
all "to" foreign keys reference locations_2. This will work provided that all inserts
into tables a1 through a5 do their foreign key locks in deterministically the same
order...
I would agree that the real solution is to make the foreign key locks shared so these
kinds of hacks arent necessary, unfortunately I have to make do with the present state
of things.
Thanks,
Shawn
-----Original Message-----
From: Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent: Monday, September 27, 2004 11:35 PM
To: Shawn Chisholm
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Foreign key order evaluation
On Mon, Sep 27, 2004 at 03:19:47PM -0400, Shawn Chisholm wrote:
>
> Hi, I am trying to deal with a deadlock situation caused by foreign
> key references on insert and I was wondering if anyone knows what
> order the foreign keys are locked (or evaluated) in for a particular
> table? Deferring the locks is unfortunately not a good option for me...
What do you mean by "what order the foreign keys are locked"? Can
you give us an example of what you're doing and what problem you're
trying to solve? As I mentioned in reply to your earlier message,
foreign key locking and the potential for deadlock were recently
brought up in pgsql-general:
http://archives.postgresql.org/pgsql-general/2004-09/msg00405.php
http://archives.postgresql.org/pgsql-general/2004-09/msg00442.php
My followup to that thread (the second link above) mentions somebody
else's suggestion for a shared lock on the foreign key, but as far
as I can tell, no such solution has been implemented as of 8.0.0beta3.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
