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.