使用子查询更新多行的竞争条件

I´ve searched half a day now without finding any answer. I hope anyone could give me one.

Its about order management. Our employees are picking orders. Because of some other reasons our database somtimes lags a little bit. The original code selected the order in a first sql statement, and did an update in a second sql statement. Now the problem is, that both employees are getting the order, but each should get a different one. So select and update have to be done in a single step.

The goal is, to dynamically select a row in a table and update it. But the select process includes the table which should be updated. So I can´t do something like this

UPDATE table SET field = (SELECT field2 FROM table WHERE field = 0)

because setting a tables value while filtering on the same field is not possible. Also I´m using ORDER BY, what is not allowed in mutli table updates (enter link description here).

I have a sql statement like this:

UPDATE t1 
INNER JOIN (
  SELECT t3.order_id
  FROM t1
  LEFT JOIN t3 USING(order_id)
  WHERE t1.error = 0
  AND t1.status = 0
  AND t1.user_id = 0
  ORDER BY t1.express DESC, t1.time_insert
) AS t2 USING(order_id)
SET t1.user = ' . mysql_real_escape_string($user->getId()) . ', t1.time_update = NOW()

I thought that I have solved the problem with that statement, but our employees are still repporting the problem.

Has anybody an idea if there might be still any race conditions or something else?

And please don´t tell me to fix the db lag. I will have to redesign the whole db for that. ;)

Thanks for any clue dops

I'm not sure what your longer query has to do with the shorter one. But, you can implement the shorter one as:

UPDATE table t CROSS JOIN
       (SELECT field2 FROM table WHERE field = 0) t0
    SET t.field = t0.field2;