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

Re: Pet Peeves?

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Guy Rouillier <guyr-ml1(at)burntmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Pet Peeves?
Date: 2009-01-30 17:01:10
Message-ID: 20090130170110.GA3218@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-general
Guy Rouillier wrote:

> Back in March 2005, I started an email thread titled "Debugging  
> deadlocks".  Most of the experienced PGers participated in that thread.  
> The basic issue at that time was that inserting a row into a table with a 
> foreign key placed an exclusive row-level lock (SELECT FOR UPDATE) on the 
> reference table (the table to which the foreign key refers).  If you 
> happen to do inserts on two different tables, each with a foreign key to 
> the same reference table, deadlocks are pretty easy to create.  This is 
> especially true if the reference table has low cardinality, which is 
> often the case.
>
> I don't know if this situation has been improved since that time.

We fixed this in 8.1 IIRC.  FKs now use "SELECT FOR SHARE", which only
takes a shared lock not exclusive, and does away with most deadlocks of
this ilk.  Of course, there are other ways to get in deadlock still.

-- 
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"I can see support will not be a problem.  10 out of 10."    (Simon Wittber)
      (http://archives.postgresql.org/pgsql-general/2004-12/msg00159.php)

In response to

pgsql-general by date

Next:From: Merlin MoncureDate: 2009-01-30 17:02:09
Subject: Re: Pet Peeves?
Previous:From: Igor KatsonDate: 2009-01-30 16:29:19
Subject: Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling

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