Re: Foreign key order evaluation

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:mike(at)fuhr(dot)org]
Sent: Monday, September 27, 2004 11:35 PM
To: Shawn Chisholm
Cc: pgsql-general(at)postgresql(dot)org
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/