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

Re: row-level deadlock problem

From: Kamil Kaczkowski <kamil(at)kamil(dot)eisp(dot)pl>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
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 14:40:11
Message-ID: Pine.LNX.4.58.0411271519110.10312@virgo (view raw or flat)
Thread:
Lists: pgsql-general
On Sat, 27 Nov 2004, Martijn van Oosterhout wrote:

> On Sat, Nov 27, 2004 at 03:20:16AM +0100, Kamil Kaczkowski wrote:
> > > Change things so you don't need to update more than one row per query,
> > > perhaps?  The lack of any primary key on that table was already pretty
> > > disturbing from a database-theory point of view.  Maybe you should
> > > rethink the table layout.
> > Yes, I know. I'm not the developer of this application, my job is
> > only to find reasons behind those deadlocks and suggest solution.
> > Anyway I'm suprised that UPDATE statements are so locking sensitive.
>
> 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.
Regards.
--
Kamil Kaczkowski
kamil(at)kamil(dot)eisp(dot)pl

In response to

Responses

pgsql-general by date

Next:From: Fabien FournierDate: 2004-11-27 15:41:09
Subject: Boolean error
Previous:From: WeipingDate: 2004-11-27 14:21:25
Subject: Could we hide the table name listing from unprivileged user?

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