I have an Oracle table (db2 soon also) which is being used as a work-queue. An external process inserts rows to this table with a status field set to 'Pending'.
My consumer process :
1. Locks a 'Pending' row, and retrieves it for work.
2. Processes the work item represented by this row
3. Updates the row status to 'Processed'.
Step 2 is a time intensive process (a human interface in some cases).
I want to be able to implement mulitple consumers, so the problem is to ensure that the row is locked and no other consumer will retrieve this row.
I've implemented several solutions that each looks like a hack to me.
I'm looking for a "best practice" solution, or at least some validation of my solutions
Use sys_guid() to lock row before access. All clients retrieve work via this SP.
PROCEDURE SecureNextFile(WORKSTEPfilter IN INTEGER, workitem OUT SYS_REFCURSOR) IS
UPDATE INBOUND_FILE SET PROC_GUID=MYGUID, WORKSTEP=WORKSTEPfilter+1 WHERE WORKSTEP = WORKSTEPfilter AND ROWNUM=1 AND PROC_GUID IS NULL;
OPEN WORKITEM FOR SELECT * FROM INBOUND_FILE INBF
Use Select For Update
FUNCTION Secure_NextTempadj RETURN INTEGER is
cursor c_f is
FROM TEMP_ADJUSTMENTS WHERE CBSTATUS='Selected' AND ROWNUM=1
ORDER BY CASE ORIGINFLAG WHEN 'FI' THEN 1 ELSE 2 END
fetch c_f into v_a;
if c_f%notfound then
update TEMP_ADJUSTMENTS set cbstatus='Locked' where current of c_f;