From: | Scott Ribe <scott_ribe(at)killerbytes(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: using database for queuing operations? |
Date: | 2004-09-20 21:08:24 |
Message-ID: | BD74A2E8.50B5C%scott_ribe@killerbytes.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> So you:
>
> 1. select for update, with the criteria outlined
> 2. Check the state (again) to see of we had that particular race condition.
> 3. If already processed or in processing, somebody else must already be
> working on it....go back to step 1
> 4, change the state
> 5. process the image
> 6. delete.
> 7 go to step 1.
You can also rely on the old trick that, having selected min(serial) you
know that:
update nameq set state = 'processing'
where serial = xxx and state = 'unprocessed';
Will execute atomically and will set a row count of 0 or 1. You still have
some racing going on with the selects, but only 1 process ever gets hold of
a row to process. I've done similar things where tests showed that
collisions would be relatively rare--the following could really be bad if
processing didn't take "much time" and you had "a lot" of processes
extracting queue items. Excuse the atrocious mix of pseudo-sql and pseudo-C
and commentary:
select serial from nameq
where state = 'unprocessed' order by serial limit 10;
for( i = 0; i < 10 && i < actual num rows selected; ++i )
{
curserial = currow.seral;
update nameq set state = 'processing'
where serial = curserial and state = 'unprocessed';
if( rowcount == 1 )
{
process row;
update nameq set state = 'processed' where serial = curserial;
break;
}
else
{
pause some brief random time to prevent lock-step race
fetch next row
}
}
--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 665-7007 voice
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas F.O'Connell | 2004-09-20 21:13:23 | Re: Any reason not to use inheritance? |
Previous Message | Ron St-Pierre | 2004-09-20 21:04:29 | Re: using database for queuing operations? |