"D. Dante Lorenso" <dante@lorenso.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