Re: row-level deadlock problem - Mailing list pgsql-general

From Kamil Kaczkowski
Subject Re: row-level deadlock problem
Date
Msg-id Pine.LNX.4.58.0411271519110.10312@virgo
Whole thread Raw
In response to Re: row-level deadlock problem  (Martijn van Oosterhout <kleptog@svana.org>)
Responses Re: row-level deadlock problem
List 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@kamil.eisp.pl

pgsql-general by date:

Previous
From: Weiping
Date:
Subject: Could we hide the table name listing from unprivileged user?
Next
From: Martijn van Oosterhout
Date:
Subject: Re: row-level deadlock problem