Re: row-level deadlock problem

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