From: eric soroos <eric-psql@soroos.net>
To: pgsql-sql@postgresql.org
Subject: Row Locking?
Date: Mon, 21 Oct 2002 16:09:35 -0700
Message-ID: <38241230.1176905921@[4.42.179.151]> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: eric-psql@soroos.net
Subject: Re: Row Locking?
In-Reply-To: <38241230.1176905921@[4.42.179.151]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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