Concurrent updates

I have a table that I want to use as a queue with all functionality (Insert,
update, delete) embodied in a stored procedure. Inserts and deletes are no
problem. An external program would call the stored procedure to get one or
more emails to work on, selecting on "state='N'", then updating the row so
"state='P'". My problem is having multiple threads calling the stored
procedure simultaneously and getting the same row(s). Selecting FOR UPDATE
won't work as, if thread #1 gets 3 rows and thread #2 starts before thread
#1 completes (Commits), thread #2 will select the same 3 rows as thread #1
except, since thread #1 will update the state (changing the state to 'P') so
that those rows no longer meet thread #2 criteria, and thread #2 will
receive zero rows. The table looks like:

CREATE TABLE dss.stage_email

(

emailid bigserial NOT NULL,

email_path text,

state character(1) DEFAULT 'N'::bpchar, -- N = New Email, P=Processing,
D=Deleting

fetch_date timestamp without time zone DEFAULT now(),

ingest_date timestamp without time zone

)

Steve Erickson

Senior Developer

266 East 7th Street, Floor 4

Saint Paul, MN 55101

651.925.3237 office

612.242.1343 cell

NOTICE: This email, including any attachments, is covered by the Electronic
Communications Privacy Act, is confidential and may be legally privileged.
If you are not the intended recipient, you are hereby notified that any
retention, dissemination, distribution or copying of this communication is
strictly prohibited. If you have received this communication in error,
please immediately notify our office by return email or at our telephone
number (651) 925-3200. Then delete and destroy all copies of this email and
any attachments.

Thank you.