pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Row Locking? 2+ messages / 2 participants [nested] [flat]
* Row Locking? @ 2002-10-21 23:09 eric soroos <eric-psql@soroos.net> 0 siblings, 1 reply; 2+ messages in thread From: eric soroos @ 2002-10-21 23:09 UTC (permalink / raw) To: pgsql-sql I have a long running process that performs outside actions on the content of a table. The actions could all be done in parallel (if I had n processors), but I need to ensure that the process is attempted exactly one time per applicable row. My current design for one thread is the following (simplified to a test case) create table foo (pending boolean, done boolean, idx serial); select * from foo where pending='f' and done='f' limit 1; update foo set pending='t' where idx=[[returned idx]]; commit; do stuff outside database update foo set pending='f', done='t' where idx=[[returned idx]]; commit; Extending this to multiple threads if proving problematic. No locking leads to a race condition between the select and update. If I change the select to a SELECT ... FOR UPDATE it apparently locks the table against all other select for updates, then when the update is committed, the second thread returns nothing, even when there are other rows in the table that could be returned. Is there a single row locking against select? Or can I effeciently do the equivalent of update set pending, then select the row that I just updated to get the contents? (perhaps without doing a table scan to find the oid of the row that I just updated). I can't afford to lock the entire table. eric ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Row Locking? @ 2002-10-22 12:51 Richard Huxton <dev@archonet.com> parent: eric soroos <eric-psql@soroos.net> 0 siblings, 0 replies; 2+ messages in thread From: Richard Huxton @ 2002-10-22 12:51 UTC (permalink / raw) To: eric soroos <eric-psql@soroos.net>; pgsql-sql On Monday 21 Oct 2002 11:09 pm, eric soroos wrote: > I have a long running process that performs outside actions on the content > of a table. The actions could all be done in parallel (if I had n > processors), but I need to ensure that the process is attempted exactly one > time per applicable row. > Extending this to multiple threads if proving problematic. No locking leads > to a race condition between the select and update. If I change the select > to a SELECT ... FOR UPDATE it apparently locks the table against all other > select for updates, then when the update is committed, the second thread > returns nothing, even when there are other rows in the table that could be > returned. > > Is there a single row locking against select? Or can I effeciently do the > equivalent of update set pending, then select the row that I just updated > to get the contents? (perhaps without doing a table scan to find the oid of > the row that I just updated). I can't afford to lock the entire table. I think the issue is that both threads are hanging about for the same row. If the first thread rolled back rather than committing, the second thread might return the same row. You've only locked the one row, but that's the one row everyone wants. How about something like: 1. SELECT FOR UPDATE... 2. IF no id returned, sleep, goto 1 3. UPDATE foo set pending='t' ... A different solution might be to have one thread allocating rows to the others. So - make "pending" something that holds a thread id. That way the processing threads just sit there trying to fetch the first allocated row and sleep if none available. Alternatively, the allocating thread could pass the info directly to the processing threads. Might be worth checking the archives for this list and pgsql-general - someone had a similar question a few weeks/months ago. -- Richard Huxton ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-10-22 12:51 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2002-10-21 23:09 Row Locking? eric soroos <eric-psql@soroos.net> 2002-10-22 12:51 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox