Re: row-level deadlock problem

On Fri, 26 Nov 2004, Tom Lane wrote:

> > 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.
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.
Consider more general case with following table:
CREATE TABLE test(id serial not null primary key,val integer not null);

Does it mean that there's no simple way(without explicit locking) to run:
UPDATE test SET val=1;
safely in concurrent transactions(let's name them T1,T2) assuming that
other transactions are modifing table frequently, e.g.:
T1: UPDATE started
... some other activity on table test ...
T2: UPDATE started(with different order then T1's update)
DEADLOCK

If it looks like this, I'm probably lucky it didn't bite me before.
Best regards.
--
Kamil Kaczkowski
kamil(at)kamil(dot)eisp(dot)pl