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