Re: row-level deadlock problem

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.
--
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.