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: (view raw, whole thread or download thread mbox)
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       
"I can see support will not be a problem.  10 out of 10."    (Simon Wittber)

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-2017 The PostgreSQL Global Development Group