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

Re: row-level deadlock problem

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Kamil Kaczkowski <kamil(at)kamil(dot)eisp(dot)pl>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org
Subject: Re: row-level deadlock problem
Date: 2004-11-27 16:23:01
Message-ID: 20041127162256.GB17895@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
On Sat, Nov 27, 2004 at 03:40:11PM +0100, Kamil Kaczkowski wrote:
> > It's not the locking on the UPDATE that's getting you. Multiple updates
> > can run concurrently (depending on your serialization level anyway, I'm
> > talking about default setup here).
> >
> > Where the problem is is the foreign key locks. The usual thing is to
> > sort the rows you are updating in such a way that the foreign keys
> > references are always processed in the same order, hence can't
> > deadlock.
> See earlier posts in this thread, I have no foreign key constraints on
> this table.

Well, there has to be something, since an UPDATE in Read Committed mode
simply doesn't have any locks that can deadlock. It has to be at least
a SELECT FOR UPDATE, which is the lock foreign keys use.

-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Responses

pgsql-general by date

Next:From: Alvaro HerreraDate: 2004-11-27 16:27:16
Subject: Re: Query on exception handling in PL/pgSQL
Previous:From: Fabien FournierDate: 2004-11-27 15:41:09
Subject: Boolean error

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