Re: Need LIMIT and ORDER BY for UPDATE

D. Dante Lorenso wrote:

> Doesn't this create race condition in the query where multiple
> processes might find the same invoice_id while executing the inner
> select. The update would then update the same record more than
> once during the update step and 2 processes might get the same
> invoice_id returned. In otherwords, moving the select criteria
> into a sub-query breaks the atomic nature of the update. Right?

Hmm, dunno. Sorry, my grasp of concurrency issues is still infantile.

> I have been trying to doing something like this, though:

...

> By checking the reserve_ts inside the SELECT and again inside the
> UPDATE this should catch the race condition and only allow one
> process to perform the update on a given match. If the other
> process has updated the reserve_ts already, the reserve_ts would
> not pass the second check. However, the new side-effect is that
> one process would receive a NULL return result when the race
> condition occurs rather than just picking up the next queue
> invoice_id.

But this could happen in any event, if there are no more invoices to
process, yes? I'm picturing a set of queue consumers, each of which
is already looping around such issues, anyway.

- John D. Burger
MITRE