New to concurrency

From: "John D(dot) Burger" <john(at)mitre(dot)org>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: New to concurrency
Date: 2007-04-07 00:03:42
Message-ID: 8650DF3B-FA7E-4D2D-8925-EC7571BBBBC6@mitre.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

For the first time, I find myself wanting to use some of PG's
concurrency control stuff, and I could use some advice.

I have requests showing up in a database, and I have one or more
servers picking these up with listen/notice. The requests go into a
table with a status column, which is initially NULL, so a server
finds requests to process like so:

begin;
select id from requests where status is null order by ts limit 1;

(ts is a timestamp column - process the earliest request first.)

A server takes responsibility for a request by setting the status for
the id it got with the previous query:

update requests set status = 'start' where qid = 42 and status is
null;
commit;

My client library (Python pgdb) lets me know how many rows got
updated, in this case 1, if the server set the status, or 0 if that
request's status is no longer NULL, like if some other server picked
it up in the interim.

Now I know that this last bit is not really any protection against
two servers both thinking they should process the same request. I
suspect I want to use SELECT FOR UPDATE in the first query - will
that be sufficient in this situation?

Thanks for any advice.

- John D. Burger
MITRE

Responses

Browse pgsql-general by date

  From Date Subject
Next Message tom 2007-04-07 00:48:22 Re: SQL WHERE: many sql or large IN()
Previous Message filippo 2007-04-06 23:47:08 Re: perl DBI: problems searching text strings with ' symbol (es d'ambrose)