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
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) |