Re: [JDBC] Is what I want possible and if so how? - Mailing list pgsql-general

From Csaba Nagy
Subject Re: [JDBC] Is what I want possible and if so how?
Date
Msg-id 1151939793.13456.40.camel@coppola.muc.ecircle.de
Whole thread Raw
In response to Re: [JDBC] Is what I want possible and if so how?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
On Mon, 2006-07-03 at 17:03, Tom Lane wrote:
> status and TX2's select will not return the row.  This isn't entirely
> perfect because LIMIT acts before FOR UPDATE: TX2's select will return
> nothing, rather than selecting the next available row as you might wish.
> So you might want to retry the select several times before deciding
> there's nothing to do.

We do have a table like this, and in fact we did observe this behavior
that if multiple clients ask for a row at the same time, the first gets
something and the rest nothing. We're actually still looking for an
optimal solution for this...

For now, we added a random field to the table (with values 0-9), and the
clients asks with a where clause for a random value in this field. This
way there's a good chance the clients will not tip on each other's toes
(i.e. the row asked for is not locked by another client). It is still
necessary to retry a few times, but after introducing this random number
mechanism we did notice a significant performance improvement in
emptying the queue... so it must work somehow. It's true that we usually
have 10-15 clients constantly polling the queue, and the queue itself is
usually loaded with at least a few hundred tasks, so the random numbers
are reasonably distributed to be effective.

Now I wonder if there's some other way to get the same result without
additional column in the table ?

Cheers,
Csaba.



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: How serious are these problems?
Next
From: Robert Treat
Date:
Subject: Re: Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4