Re: select from update from select?

> This has come up before - check the archives for details.
>
> One solution is to have a "processed" flag with values:
>
> U - unprocessed
> X - executing
> D - done
>
> You claim a job by changing from U to X - this takes very little time, so your
> worker processes can simply sit in a loop trying. When work is complete the
> flag is marked D.
> This has the added advantage that you can spot rows where processing started
> but didn't complete due to a crash etc.
>
> Another solution if you know how many workers you have is to have a manager
> process which tags each job with a worker ID, but this does tend to make the
> manager a point of failure.

In my system, when reservation_time is not null, the state is not U, and
when the result field (not described in my email) is not null, the state
is neither U nor X. I do use reservation_time (and 'now()') to reclaim
tasks that have not completed after a certain (dynamic) length of time
(e.g. due to crash). So I think I'm attempting to implement essentially
what you describe. ;-)

I guess what you're saying is that in the big picture, the period of
contention for jobs between, say, 60 concurrent clients will really be
quite small, so don't worry about eliminating it?

Dave