Deadlock Detected (revisited)

From: Wes <wespvp(at)syntegra(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Deadlock Detected (revisited)
Date: 2005-11-25 06:20:25
Message-ID: BFAC0949.1A497%wespvp@syntegra.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I haven't seen anything to indicate that 8.x improves foreign key
refererence locking and fixes the foreign key 'deadlock detected' issue.
Has that been addressed in 8.1?

I sort my records before loading, but am still getting 'deadlock detected'
(still running 7.3.4).

I have

B references C
B references A
A references C

Any new records for C are loaded first (both A and B depend on C).

A is loaded next (B depends on A).

B is loaded last.

Both A and B are loaded in 'Table C' sorted order. However, a deadlock can
occur with one process's A records and another's B records, due to the fact
that both have a foreign key reference to C.

The only way I know of to eliminate the deadlocks without serializing the
processes is to remove the foreign key reference from either A or B.
However, jeopardizes referential integrity if something goes wrong with the
load process.

I've never quite understood why a READ of a record with a foreign key
reference results in the referenced record being locked with more than a
shared lock.

Wes

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hubert depesz lubaczewski 2005-11-25 07:52:41 Re: postmaster does not come up
Previous Message Chris Travers 2005-11-25 06:14:01 Re: New user questions