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
