Re: Fwd: Dead lock

On Mon, Jun 14, 2010 at 11:58 AM, Dave Crooke <dcrooke@gmail.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'tknow if that is a guarantee, if you're not actually displaying the results then the DB may be technically allowed
tooptimize 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