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.
