Re: row-level deadlock problem

On Fri, 26 Nov 2004, Tom Lane wrote:

> Kamil Kaczkowski <kamil(at)kamil(dot)eisp(dot)pl> writes:
> > I have problems with deadlocks caused by(at least I think so) row-level
> > locks and I can't find the reason.
>
> The failure seems clearly a deadlock on row-level locks. Are you
> certain you've removed all relevant FKs (those pointing to the table
> as well as out of it)?
Yes, I browsed whole database schema, all FKs has been dropped.
> Another possible explanation is if the UPDATE
> command can update more than one row --- in that case different backends
> might happen to reach the target rows in different orders.
This could be it.
Yes, this UPDATE changes several rows, I didn't know this can be a
problem.
My understanding was that row-level lock at UPDATE statement is somehow
atomic and it locks all rows matched at once.
But what's the solution? How can I force UPDATEs to lock rows in the same
order? There's no ORDER BY clause for UPDATE.
Thanks for help.
--
Kamil Kaczkowski
kamil(at)kamil(dot)eisp(dot)pl