From: | Douglas McNaught <doug(at)mcnaught(dot)org> |
---|---|
To: | "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com> |
Cc: | Erik Jones <erik(at)myemma(dot)com>, btober(at)ct(dot)metrocast(dot)net, pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT ... FOR UPDATE performance costs? alternatives? |
Date: | 2007-08-16 14:20:35 |
Message-ID: | 87wsvvtvbg.fsf@suzuka.mcnaught.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2007-08-16 14:21:18 | Re: how to get id of currently executed query? |
Previous Message | Decibel! | 2007-08-16 14:16:33 | Re: Performance question |