Re: SELECT ... FOR UPDATE performance costs? alternatives?

"D. Dante Lorenso" <dante(at)lorenso(dot)com> writes:

> I need logic like "atomic test and set" or pop 1 item off the queue
> atomically and tell me what that item was.
>
> In my situation, there are a dozen or so machines polling this queue
> periodically looking for work to do. As more polling is occurring,
> the locks seem to be taking longer so I was worried table-level locks
> might be occurring.

How quickly after you update the row status are you comitting (and
releasing locks)? I have apps that basically do:

SELECT id FROM job_table WHERE status = 'New' FOR UPDATE;
UPDATE job_table SET status = 'Processing' WHERE id IN (<set of IDs>);
COMMIT; -- releases all locks

<process each job in the list we got and update its status>

This has worked very well for me.

-Doug