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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
