Re: Tracking down deadlocks - Mailing list pgsql-general

Hello,

Deferred checks can greatly reduce the deadlock chance because of the
timing of the foreign key check. I won't say it can eliminate them,
and I don't think anyone here would suggest that you don't use Foreign keys.

Sincerely,

Joshua D. Drake

Ben wrote:
> Thanks for the quick reply (and summary!).
>
> According to the messages I've found on the list, basically the answer
> seems to be, "don't do this." On the other hand, pretty much every
> message on the subject is pre-7.4. There is some mention of using
> deferred foreign keys to reduce the chance for a deadlock, but nothing
> says doing that actually eliminates the chance.
>
> Is this just a known limitation? In this particular instance, I probably
> could get rid of my foreign keys and if things go bad it wouldn't hurt
> anything.... but I make heavy use of foreign keys throughout the rest of
> my schema, which are useful for the programs that aren't doing data
> mining. I wouldn't want to get rid of those foreign keys.
>
> On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote:
>
>> Hi Ben,
>>
>> Check this mailing list for "foreign keys" and "deadlock".
>> Short info:
>> Postgres exclusively locks the referenced records of a foreign key
>> relationship when the child record is updated, so multiple runs (in
>> different transactions) of one insert query could cause deadlock if they
>> update rows which reference the same parent keys in reverse order.
>> Check your foreign keys...
>>
>> HTH,
>> Csaba.
>>
>> On Wed, 2004-06-16 at 17:33, Ben wrote:
>>
>>> I'm doing a bunch of data mining against a postgres database and have
>>> run into an interesting problem with deadlocks. The problem is,
>>> postgres is detecting them and then wacking the offending process, and
>>> I can't figure out what's causing them. I have a ton of select queries
>>> (but none for update), and then a single query to insert into a table.
>>> Nothing selects from that table. So where could the deadlock be?
>>>
>>> pg_stat_activity has a column named current_query, which would seem
>>> useful in tracking this down, but it's not being populated.
>>>
>>> Oh, I'm running 7.4.2.
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 5: Have you checked our extensive FAQ?
>>>
>>>                http://www.postgresql.org/docs/faqs/FAQ.html
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL