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

Re: Foreign Key Deadlocking

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
Cc: 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:00:36
Message-ID: 049552E8-8EBB-4B44-8D65-6108456A2421@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
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.
>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2007-04-19 14:14:42
Subject: Re: Foreign Key Deadlocking
Previous:From: Merlin MoncureDate: 2007-04-19 00:16:18
Subject: Re: Basic Q on superfluous primary keys

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