Re: Foreign key order evaluation

On Tue, Sep 28, 2004 at 01:35:32PM -0400, Shawn Chisholm wrote:

> 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)

If these two inserts occur in different transactions then the second
transaction will block. This is different than a deadlock, which
is when two transactions are each waiting for a lock that the other
holds, in which case PostgreSQL will raise an error. That situation
is possible but isn't shown here; see some of the other messages
in this thread for an example.

> 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.

Agreed.

> 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...

You'll still get blocking if more than one transaction refers to
the same foreign key. Perhaps less than with your current
implementation, but it could still happen. I'm not sure the kludgy
workaround would be worth whatever small gain you realized (if any).

> 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.

Your hack might not be as effective as you'd like -- have you done
any tests with it? Until a shared lock is implemented, you might
be stuck with the way things are.

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