Re: select from update from select?

On Tuesday 18 Feb 2003 8:57 am, Dave Gomboc wrote:
> On Mon, 17 Feb 2003, Tambet Matiisen wrote:
> > Why not just store the work_order_id in temporary variable? You need to
> > do SELECT FOR UDPATE in this case, to avoid reserving the same work
> > twice.
>
> This doesn't work -- for the reason you give:
> > While testing this, I discovered, that it doesn't work exactly as I
> > expected. Suppose one transaction locks row with work_queue_id = 1. Now
> > before the first transactions has finished, second comes in and tries to
> > get lock for the same row. SELECT FOR UPDATE in second transaction
> > blocks as expected. But when first transaction finishes, the SELECT in
> > second transaction returns 0 rows, not next row that satisfies the
> > conditions. I'm not sure if this should be considered bug or feature.
> > But you have to test if the query returned any rows anyway, because the
> > same happens when there are no unreserved works in queue.
>
> It's definitely a bug to me.  The purpose of the work_queue is hold the
> pool of tasks to be done, and to allow different machines to indicate that
> they will do a certain task without any master program to tell machines
> what work to do.  With the above process, concurrent attempts block
> (except for one), then they don't even get a row!  I definitely want
> concurrent requests to not haggle over the same row, there'd be way too
> much contention between machines, and it defeats the whole purpose of more
> than one job being available simultaneously.  If PostgreSQL had a SKIP
> LOCKED feature, I'd be in business, but (AFAIK) it doesn't?  Using NOWAIT
> and doing a large number of retries using some random back-off scheme
> seems like a big hack rather than something that would actually work
> acceptably.

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.

--  Richard Huxton