From: Richard Huxton <dev@archonet.com>
To: Dave Gomboc <dave@cs.ualberta.ca>
To: Tambet Matiisen <tambet.matiisen@mail.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: select from update from select?
Date: Wed, 19 Feb 2003 12:49:03 +0000
Message-ID: <200302191249.04316.dev@archonet.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0302180139210.30967-100000@boyne.cs.ualberta.ca>
References: <Pine.LNX.4.44.0302180139210.30967-100000@boyne.cs.ualberta.ca>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, dave@cs.ualberta.ca, tambet.matiisen@mail.ee
Subject: Re: select from update from select?
In-Reply-To: <200302191249.04316.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox