Re: row-level deadlock problem

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