Foreign key deadlocks - Mailing list pgsql-general

Hi all,

I'm currently porting an application from Oracle to Postgres.
Most of the things are working fine, but I still couldn't sort the problems
with foreign keys.
When creating the foreign key constraints as they are in the Oracle schema,
the application deadlocks quite often.
The reason is AFAICT the update lock placed on the referenced row when
inserting in a table with foreign key constraints.
In some tables we have lots of foreign key constraints, and the application
has some fairly long transactions running concurrently, and even if I order
the updates/inserts so that they lock the tables in the same order all the
time, I still can't controll the locking done by the foreign key
constraints... so I get deadlocks.
The fact that an insert will cause an exclusive lock on the referenced row
for a foreign key constraint is a severe performance restriction anyway,
because no other concurrent transaction can insert a row in any other table
referencing the same row... the only way to work this around was not to use
foreign key constraints at all, but this is not an acceptable resolution.
I will give an example to make things clear. Let's say I have the following
(simplified) tables:

create table table_1 (
    key_1     bigint    primary key,
    description    text
);

create table table_2 (
    key_2        bigint    primary key,
    key_1        bigint    references table_1 (key_1),
    description    text
);

create table table_3 (
    key_3        bigint    primary key,
    key_1        bigint    references table_1 (key_1),
    description    text
);

The usage scenario is as follows:

  - table_1 is updated fairly often, with all possible operations
(insert/delete/update);
  - table_2 and table_3 are updated frequently, with all possible operations
(insert/delete/update);
  - the operations have to run in fairly long transactions, affecting many
rows in table_2/table_3;
  - the system is highly concurrent;

Any insert in table_2/table_3 will block the referenced row in table_1, and
so any other concurrent insert which would reference that row in any of
table_2/table_3 will wait until the locking transaction finishes... even if
they would not need to (they don't change the referenced row...)
This is an unnecessary exclusive lock on that row (a shared lock would do).
Using table locks as a workaround is not an option either, as the
transactions are fairly long running, and it would be an even bigger
performance problem.
Now this scenario is a simple one, imagine what happens if there are more
tables hierarchically related by foreign key constraints involved in
different transactions. I personally was unable to figure out how to avoid
deadlocks between rows locked in multiple tables by foreign key
constraints...
I can't believe this is a deliberate way to handle row locks, it just
doesn't make sense. Am I missing some crucial design issue which makes the
shared row locks impossible ?

I have read the faq, documentation (to a reasonable degree), but I found no
viable solution. I've tried also the DEFERRABLE INITIALLY DEFERRED approach
but that won't work either, because some of the code relies on getting the
referenced key checked immediately on an insert, and abort transaction if it
fails (with deferring, the transaction goes on after an insert with broken
foreign reference and rolls back on finish - unacceptable given the length
of the transactions).

Is there any guidline about how to solve this ? Rewriting the code is not an
option, it relies too heavily on how foreign key constraints work in Oracle.


Thanks in advance,
Csaba.