Re: row-level deadlock problem

Kamil Kaczkowski <kamil(at)kamil(dot)eisp(dot)pl> writes:
>> 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.

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

Nope.

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

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.

regards, tom lane