Re: Fwd: Dead lock

On Mon, Jun 14, 2010 at 11:58 AM, Dave Crooke <dcrooke(at)gmail(dot)com> wrote:
> If you're doing straight SQL bulk updates, then as someone suggested, you could use an ORDER BY on a subquery, but I don't know if that is a guarantee, if you're not actually displaying the results then the DB may be technically allowed to optimize it out from underneath you. The only way to be sure is a cursor / procedure.

'order by' should be safe if you use SELECT...FOR UPDATE. update
doesn't have an order by clause. Using cursor/procedure vs a query
is not the material point; you have to make sure locks are acquired in
a regular way.

update foo set x=x where id in (select * from bar order by x) does
look dangerous.

I think:
update foo set x=x where id in (select * from bar order by x for update)
should be ok. I don't usually do it that way.

merlin