Skip site navigation (1) Skip section navigation (2)

Re: Foreign Key Deadlocking

From: Erik Jones <erik(at)myemma(dot)com>
To: Dave Cramer <pg(at)fastcrypt(dot)com>
Cc: Csaba Nagy <nagy(at)ecircle-ag(dot)com>, Steven Flatt <steven(dot)flatt(at)gmail(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Foreign Key Deadlocking
Date: 2007-04-19 14:15:45
Message-ID: E23AE3D2-4529-49D6-A586-B8BB119F9308@myemma.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Apr 19, 2007, at 9:00 AM, Dave Cramer wrote:
>
> On 18-Apr-07, at 11:36 AM, Csaba Nagy wrote:
>
>>> Can someone confirm that I've identified the right fix?
>>
>> I'm pretty sure that won't help you... see:
>>
>> http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php
>>
>> The deadlock will be there if you update/insert the child table and
>> update/insert the parent table in the same transaction (even if you
>> update some other field on the parent table than the key  
>> referenced by
>> the child table). If your transactions always update/insert only  
>> one of
>> those tables, it won't deadlock (assuming you order the inserts/ 
>> updates
>> properly per PK).
>>
>> Cheers,
>> Csaba.

> Hi Csaba,
>
> I have a similar problem.
>
> In an attempt to avoid the overhead of select count(*) from mailbox  
> where uid = somuid I've implemented triggers on insert and delete.
>
> So there is a
>
> user table which refers to to an inbox table,
>
> so when people insert into the inbox there is an RI trigger  
> grabbing the shared lock, then the count triggers try to grab an  
> exclusive lock resulting in a deadlock.
>
> Can we safely remove the shared locks ?
>
> Is there a right way to implement the count triggers. I've tried  
> before triggers, and after triggers, both result in different kinds  
> of deadlocks.
>
> Dave

The ways I've done this in the past is to have the count triggers  
make inserts into some interim table rather than try to update the  
actual count field and have another process that continually sweeps  
what's in the interim table and makes aggregated updates to the count  
table.  Even if there isn't much to aggregate on any given sweep,  
this gives you a sequential pattern as your inserts/deletes on the  
main table don't depend on any locking in another table (well,  
strictly speaking, your inserts into the interim table would be  
blocked by any exclusive locks on it but you shouldn't need to ever  
do that anyway).


erik jones <erik(at)myemma(dot)com>
software developer
615-296-0838
emma(r)




In response to

pgsql-performance by date

Next:From: Csaba NagyDate: 2007-04-19 14:23:42
Subject: Re: Foreign Key Deadlocking
Previous:From: Alvaro HerreraDate: 2007-04-19 14:14:42
Subject: Re: Foreign Key Deadlocking

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group