Re: using database for queuing operations?

Clinging to sanity, jamiel@istreamimaging.com (Jeff Amiel) mumbled into her beard:
> .....or instead change the logic to:
>
> So you:
>
> 1.  select for update, with the criteria outlined
> 2.  Check the state (again) to see of we had that particular race condition.
> 3.  If already processed or in processing, somebody else must already
> be working on it....go back to step 1
> 4,  change the state
> 5.  process the image
> 6.  delete.
> 7  go to step 1.
>
> change the state, then process the image....then delete.

If you can identify some form of "process ID" for each of the
processors running concurrently, you might do something like:

1.  Update for selection (converse of 'select for update' :-)

 update nameq set action = 'in process', pid = 45676
   where action <> 'in process' and (other criteria for grabbing the
    record)

2.  select * from nameq where pid = 45676 and action = 'in progress'

3.  do your work, processing the image

4.  update nameq set action= 'done',  -- Or whatever is the appropriate
                                      -- state
      pid = NULL
    where [criterion for the processed image...]

This way only one of the PIDs will get ownership of any given row for
step #2...

At the Unix level, this would be like making a "work" directory for
each work process, and having Step #1 try to do "mv file
$pid_work_dir".

The file can only get placed in one spot; if one "mv" wins, the others
necessarily lose.  If one "set pid = my_pid" wins, no other one can do
so later.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/linux.html
"It's like  a house   of cards  that   Godzilla  has  been  blundering
through."  -- Moon, describing how system messages work on ITS