Csaba Nagy wrote:
> 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 ?
For a small number of processes and a large difference in time
between the 'loookup' speed and the 'work' I have used a two-step
process where you first get a batch of records and then try them
all in rapid succession. In pseudocode:
SELECT *
FROM table
WHERE condition
LIMIT number_of_queue_processes + 1;
LOOP;
BEGIN;
SELECT *
FROM table
WHERE condition AND pk = xxx
LIMIT 1 FOR UPDATE NOWAIT;
do something;
COMMIT;
END;
Jochem